Query plans in SGA Trace and Session Browser

I’m confused! I have a query running on a 11gr2 server and I can see the SGA Trace and Session Browser displays in Toad. The Cached Explain Plan in the session browser is different to the one shown in SGA trace!
I thought that both displays should be based on V$SQL_PLAN and should be identical. The IO stats in the session browser match the stats in the SGA trace so I am persuaded that both truly refer to the same query.
(Same behaviour in Toad 10 and Toad 11)
It may be that both query plans are in the cache since the client (Business Objects) does not use bind variables and different plans seem to be produced when there is a change in a numeric value used for a query filter…
The SGA Trace reports a “Version Count” of 2 - does this suggest that there are two versions in the SGA cache and that the SGA trace picks one whilst the Session Browser picks the other?
The Session Browser plan appears to be the one that is being executed.

Turn on spool sql in Toad. When we send the query to v$sql_plan , we’ll
look for a specific hash value to get the plan.

I am guessing the query in session browser and the one in SGA Trace are showing
a different hash. The “version count” seems to support that, but I
don’t know enough about the internals of Oracle to say for sure on this
one.

-John

Thanks for that. That’s another facility in Toad that I was not aware of (only 32,768 more features to discover now :slight_smile:
The Spool SQL shows different hash values as you suspected. This looks to me like a bug (feature?) in Toad. Both versions of the query plan are in the SGA but the SGA Trace should IMO show the latest version. I suppose that another session could be using the older version so they are both ‘valid’ but in this case both versions were put in the shared area by the same session.
Interestingly, Oracle appears to re-optimise the query using either cached subquery results or cached subquery statistics to override the stats gathered by dbms_stats. (which subject I have raised at Asktom)

In SGA Trace, we are looking at queries in the SGA. So hash_value is in the
result set of our query that loads main part of the screen.

In Session Browser, we’re looking at sessions. So when you go to the
“current statement” tab, we have to query Oracle to get the hash
value of the current statement for whatever session is selected in the top (or
left) half of the screen.

Are you sure that the statement being shown in SGA Trace is identical to that in
the session browser? You probably know this, but if they differ by as much as
one character, Oracle will consider them different statements (and have 2,
possibly different, explain plans). You can do put some text in the
“Search SQL Text” edit box in the SGA Trace screen to check that.

Looking here
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2129.htm
, I’m not sure that “version” means exactly as we guessed.

They are definitely the same SQL in each query. I can provoke the behaviour in toad at will if I follow the steps…

  1. Flush the SGA
  2. Execute the query (F9)
  3. Execute the query again (F9)

After step 2 the query plan in the SGA trace is the same as shown in the session browser. After step 3 the plan in the session browser changes whilst the SGA query plan is unchanged but shows a version count of 2 in the grid part of the SGA trace.
No useful response from Asktom yet but I’m hopeful of learning something. I may raise it on metalink if Tom doesn’t clear it up.

XPLAN shows that I am “benefitting” from Cardinality Feedback. (Thanks to Asktom for pointing me in this direction). It’s a new feature in 11g that enables Oracle to improve a query plan based on what it learned from a previous execution. In my case the “improved” query runs in 3mins 50 s rather than 174 milliseconds.
This is probably pretty rare and the inconsistency between the plans shown in Toad’s SGA vs session browser was the thing that set me on the path to working out what was going on. Not sure how Toad should deal with this situation? - Could it indicate the cardinality feedback somehow?
select * from table(dbms_xplan.display_cursor());
has a note that indicates the feature being used.

Is the note about cardinality feedback anywhere in your v$sql_plan view? If so,
which column? We can probably make it appear in Toad.

I have seen comments only appear when a plan is obtained from dbms_xplan.
Hopefully that isn’t the case here.