alter session in explain plan window

[Marco van der Linden]

Is it possible to issue an alter session command in the explain plan window?
I would particularly like to compare the effects of changing optimizer_index_cost_adj and optimizer_index_caching on the explain plan.

 

[Marco Kalter]

This is currently not possible. I have added this to the list of enhancement requests though.

The only workaround is to switch to "Single Session Mode" and execute your alter session statements in a separate SQL Window. Since all windows share the same session, the session of the Explain Plan Window is now affected by the SQL Window.

 

[ScottSD]

This didn't work for me. I wanted to compare a parallel execution plan to a serial execution plan.

I switched to single session, restarted the application and then executed:

ALTER SESSION DISABLE PARALLEL QUERY ;

The explain plan window still gave me a parallel execution plan (even if I added the no_parallel hint).

I ended up using sqlplus to get the serial execution plan.

Would be nice if this worked in PL/SQL developer.

Scott

 

[Marco Kalter]

In the current PL/SQL Developer version you can use alter session statements in the Plan Window itself.

 

[Marco van der Linden]

@Marco

It's nice to be able to issue alter session statement in Explain Plan Window..........however, you need to clear the window contents first, otherwise you'll get an 'ORA-00911: Invalid character' error. It would be nice if the behaviour would be the same as in the SQL Window, with 'Autoselect Statement'.

 

[ScottSD]

I agree wholeheartedly. While I was able to get my serial explain plan by issuing the alter session command in the explain window, it was cumbersome -- only 1 command in the window at a time.

The explain plan window definitely needs something like 'Autoselect Statement' to make it more user-friendly with alter session commands.

 

[Marco van der Linden]

Another option might be to have a seperate 'Alter session' text box next to the 'optimizer goal' drop down list, where you only have to enter session parameter and value.

 

[Marco Kalter]

I have added this to the list of enhancement requests.