RE: Query plans in SGA Trace and Session Browser

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


From: [email protected] [mailto:[email protected]] On Behalf Of Matt Birchall
Sent: Wednesday, November 16, 2011 10:48 AM
To: [email protected]
Subject: [toad] Query plans in SGA Trace and Session Browser



Message from: Matt Birchall


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.


_______________________________________

Historical Messages


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.

__
_______________________________________