RE: flush shared_pool and query performance

Yes, the timing goes back to around 0.01s . Thank you, Mark

thank you

Gene Gurevich

                                                                           
             "Bobak, Mark"                                                 
             <Mark.Bobak_at_proqu                                             
             est.com>                                                   To 
             Sent by:                  "genegurevich_at_discover.com"         
             oracle-l-bounce_at_f         <genegurevich_at_discover.com>,        
             reelists.org              "oracle-l_at_freelists.org"            
                                       <oracle-l_at_freelists.org>            
                                                                        cc 
             04/03/2008 04:59                                              
             PM                                                    Subject 
                                       RE: flush shared_pool and query     
                                       performance                         
             Please respond to                                             
             Mark.Bobak_at_proque                                             
                  st.com                                                   
                                                                           
                                                                           
                                                                           

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

--

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

ProQuest...Start here.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of genegurevich_at_discover.com
Sent: Thursday, April 03, 2008 5:40 PM
To: oracle-l_at_freelists.org
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

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Apr 03 2008 - 17:12:03 CDT