RE: flush shared_pool and query performance

From: <>
Date: Fri, 4 Apr 2008 10:28:39 -0500
Message-ID: <>


Thanks for the explanation. The only test I can think of is a tkprof - to compare the data for both runs. Is there any other way to test your hypothesis?

thank you

Gene Gurevich

             "Bobak, Mark"                                                 
   >                                                   To 
             04/04/2008 10:12          <>,        
             AM                        "Allen, Brandon"                    

cc ""
Subject RE: flush shared_pool and query performance

Hmm...ok, after shared pool flush, you've dumped (most of) what's stored there. This would include the data dictionary data and recursive SQL generated during parsing.
select col1, col2 from table where col3='1123'; Would load data dictionary data for 'table', as well as hared parse all the recursive SQL.

So, that takes 0.3 seconds, due to extra work due to non-cached data dictionary information.
Then, when you execute:
select col1, col2 from table where col3='4567'; it's a hard parse, but a trivially simple query, and since the data dictionary info for 'table' is already cached, *and* the recursive SQL is also already parsed, so, it only takes 0.01 seconds?

So, I'm saying that the real work after a shared pool flush is not parsing your query, but the recursive SQL required to retrieve dictionary info that has been flushed, not to mention that the recursive query itself would need to be hard parsed the first time around.

That's just another hypothesis. Want to take a shot at designing a test that would prove or disprove what I'm saying, Gene...?


Mark J. Bobak
Senior Database Administrator, System & Product Technologies
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059  or +1.800.521.0600 x 4059

ProQuest...Start here.

-----Original Message-----
From: []
On Behalf Of
Sent: Friday, April 04, 2008 10:15 AM
To: Allen, Brandon;
Subject: RE: flush shared_pool and query performance

Cursor_sharing is set to exact:

17:31:00 EGUREV1 @dccs05 >show parameter cursor
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     TRUE
open_cursors                         integer     300
session_cached_cursors               integer     50

thank you

Gene Gurevich

             "Allen, Brandon"
   >                                                 To

04/03/2008 06:15 <> PM cc Subject RE: flush shared_pool and query performance Do you have the parameter cursor_sharing=similar set? -----Original Message----- From: [] On Behalf Of Why would the third SQL complete that fast. I have a different SQL because of the value 4567 (and I don't use bind variables), so shouldn't this be a hard parse too? Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it. -- --
Received on Fri Apr 04 2008 - 10:28:39 CDT

Original text of this message