RE: flush shared_pool and query performance

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
  • To: "genegurevich@xxxxxxxxxxxx" <genegurevich@xxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 3 Apr 2008 17:59:31 -0400

Sure, I don't think that's unreasonable at all.  As an added confirmation, if 
you have 0.01 secs, and then you flush and first execution is 0.3 secs, does 
the second execution after the flush go back to 0.01 secs?  If so, then the 
difference is the hard parse.


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: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of genegurevich@xxxxxxxxxxxx
Sent: Thursday, April 03, 2008 5:40 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: flush shared_pool and query performance

Hi all:

I am noticing that the same SQL executed before and after 'alter system
flush shared_pool' completes in VASTLY
different time - 0.01s before the command and 0.3s after the command. I
wonder why is that. When I flush the
shared_pool, the library cache is flushed and so my SQL needs to be
reparsed.  Is that reasonable for parsing
to take 0.3s?

thank you

Gene Gurevich



Other related posts: