RE: Re: RE: Query plans in SGA Trace and Session Browser

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.


From: [email protected] [mailto:[email protected]] On Behalf Of Matt Birchall
Sent: Thursday, November 17, 2011 11:42 AM
To: [email protected]
Subject: [toad] Re: RE: Query plans in SGA Trace and Session Browser



Message from: Matt Birchall


Thanks for that. That's another facility in Toad that I was not aware of (only 32,768 more features to discover now :-)
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)


_______________________________________

Historical Messages


Author: Matt Birchall
Date: Thu Nov 17 09:42:08 PST 2011
Thanks for that. That's another facility in Toad that I was not aware of (only 32,768 more features to discover now :-)
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)

__

Author: John Dorlon
Date: Wed Nov 16 09:05:02 PST 2011
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
__

Author: Matt Birchall
Date: Wed Nov 16 08:48:06 PST 2011
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.

__
_______________________________________