Question about Alter Session and Explain Plan

[B_P_100]

I am trying to debug a complex query. This problem involves parallel query.

Using the SQL Window I attempted to modify the session by issuing commands such as

-- toggle to enable parallel
alter session force parallel query parallel 8;
-- and then toggle to disable parallel
alter session disable parallel query;

After issuing the above commands then I attempted to use the Explain Plan (F5) feature on the questionable SQL statement. The plans displayed never show the parallel version. When I workaround this by putting a hint directly in the SQL, the plan does display properly as a parallel plan.

Can you explain why the ALTER SESSION command appears to be executed but the subsequent EXPLAIN PLAN shows no evidence that the ALTER SESSION was executed?

I need to know how PL/SQL Developer works w.r.t. the SQL Window in order to figure out how to debug. If I ALTER SESSION I would like to see results that reflect such commands.

It almost seems as if the ALTER SESSION was executed in some alternate spawned off session and not within the session of the SQL Window that I was operating within.

Or alternatively it might be that the EXPLAIN PLAN itself is executed in a different session.

If either of these scenarios are true then how could one do things like ALTER SESSION and then observe the effects of such changes on the EXPLAIN PLAN results?

Thanks, BP

 

[B_P_100]

Fyi I am using Version 10.0.4.1708.

 

[Marco Kalter]

You will need to execute the "alter session" statement in the Explain Plan Window. It has its own session.

 

[B_P_100]

Altering session within Explain Plan Window does not seem to work for me.

If I execute the following in the Explain Plan Window via F8

alter session force parallel query parallel 8;

and then follow that by refreshing the query's plan via F5 then nothing changes in the plan.

 

[Marco Kalter]

You first need to enter the alter session statement in the Plan Window and press "Execute".

Next, enter the select statement in the Plan Window and press "Execute".

 

[B_P_100]

Ok, the Explain Plan window is much less functional than the SQL Window
and behaves totally differently.

I tried to run the following in that window (after figuring out that
I had to remove the trailing semi-colons):

------------------------------------------------------------------

--alter session force parallel query parallel 8
--alter session disable parallel query

select count(*) from all_objects

-------------------------------------------------------------------

and then toggle back and forth with the 'alter session' commands
by selectively commenting and un-commenting the three commands or
selecting (highlighting) the command I wanted to execute.

The selecting feature doesn't work in the Explain Plan window. Ok.
Then I tried just commenting out things. That didn't work either
for the Alter Session commands unless I placed the one I wanted at
the top and then commented out the other two commands.

This is kind of awkward compared to the rest of the tool which is
usually very user friendly. It's not very convenient for this type of analysis as it is currently set up.

Rgds, bp