Explain Plan Window Request

[-IceTea-]

Many times I do an alter session in an SQL window
and want to know how this affects the execution plan of the SQL. Because PL/SQL openes a different session for the explain plan and the sql window, this does not affect the explain plan showed.

The only solution I've found for this is to switch to single session mode, but this is very annoying, I lose many of the advantages that multi-session mode gives me.

Would it be possible that an Explain Plan Window share all the non default paramters with its parent sql window? (if there is one) The two windows will not share the same session, only the parameters changed by the alter session commands.

Everytime an alter session command would be issued, it would propogate to the explain plan window.

This would really help me and I believe it will help many other people too.

 

[Marco Kalter]

I have added this to the list of enhancement requests.

Note however that you can already run these "alter session" statements in the Plan Window.

 

[-IceTea-]

That's Great!
Thank you!

 

[mhthomas]

FYI: I use the Hotsos test harness, and they use a single script, bind.sql, which the user configures for their tuning work. The harness always calls @bind.sql before doing its tasks. Might be nice if we could configure a script file to do same?

 

[mhthomas]

Forgot to say, its called "@bind.sql" because you can also define bind and substitution variables in it. E.g.

alter session set use_stored_outlines = foooutline ;
alter session set "_optimizer_cost_model" = io ;
variable k varchar2(10) ;
exec :k := 'aaaa' ;
exec :min_row := 1 ;

 

[bcoulam]

As long as there is an existing thread for Explain Plan window enhancements, I'd like to add:

1) Please make the data grid used in the bottom half of the Explain Plan window more like regular data grids, or at least less of a stooge in the default width allocated to Selected columns.

I like to see a number of non-default columns from the PLAN_TABLE. When I do this, it adds columns to the lower half of the Explain Plan. With a regular data grid (like SQL Window query results), if the number of columns is too great, a horizontal scroll bar appears; you can then scroll to the right to see the extra columns). But in the Explain Window, it isn't until I get to about 12 columns that the horizontal scrollbar appears. Ordinarily, I have 9 columns. Without the scrollbar, the columns on the far right, like Access and Filter predicates, end up compressed to the point I can only see a few characters. I end up manually adjusting the columns on the left to try to see a little more of the far right columns.

The easiest way to see this is to add all available PLAN_TABLE columns to the Selected Columns. Now pretend that you want to see the entire contents of Projection. As you manually adjust the width of Projection, the plan grid "jumps" back to the left. You have to scroll back over to Projection to guess again and try to expand it to the point you need, and again, and again, until it's finally wide enough. Now try to do the same thing for the Timestamp column on the far right. I won't adjust. You only see one character from the time component of the timestamp. You end up having to manually compress all of the columns on the left, to give timestamp some room to adjust. This is exactly the behavior I'm seeing when I have 9 columns and I'm trying to see all of the rightmost columns where I don't have a scrollbar.

If this were a regular data grid, it would automatically adjust itself when columns are widened and it wouldn't jump back to the left every time a column is manually dragged wider.

This is a royal pain and not in keeping with the rest of PSD's extraordinary intuitiveness.

 

[Marco Kalter]

We'll have a new grid control in the Plan Window for 7.1, with normal scrolling behavior.