RE: flush shared_pool and query performance

No, I think 10046 trace and TkProf will probably show it. Or use T.Kyte's runstats script.

--
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: Friday, April 04, 2008 11:29 AM
To: oracle-l_at_freelists.org
Subject: RE: flush shared_pool and query performance

Mark,

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"
             <Mark.Bobak_at_proqu
             est.com>                                                   To
                                       "genegurevich_at_discover.com"
             04/04/2008 10:12          <genegurevich_at_discover.com>,
             AM                        "Allen, Brandon"


                                       <Brandon.Allen_at_OneNeck.com>,

                                       "oracle-l_at_freelists.org"


                                       <oracle-l_at_freelists.org>

                                                                        cc
                                       "lfcerri_at_gmail.com"


                                       <lfcerri_at_gmail.com>,

                                       "psingh_at_vmware.com"


                                       <psingh_at_vmware.com>,

                                       "Tim_Fleury_at_perlegen.com"


                                       <Tim_Fleury_at_perlegen.com>,

                                       "asif_oracle_at_yahoo.com"


                                       <asif_oracle_at_yahoo.com>,

                                       "ukja.dion_at_gmail.com"


                                       <ukja.dion_at_gmail.com>

                                                                   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.
Executing:
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

--
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: Friday, April 04, 2008 10:15 AM
To: Allen, Brandon; oracle-l_at_freelists.org
Cc: lfcerri_at_gmail.com; psingh_at_vmware.com; Tim_Fleury_at_perlegen.com;
asif_oracle_at_yahoo.com; ukja.dion_at_gmail.com
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"
             <Brandon.Allen_at_On
             eNeck.com>                                                 To


                                       <genegurevich_at_discover.com>,

             04/03/2008 06:15          <oracle-l_at_freelists.org>
             PM                                                         cc

                                                                   Subject
                                       RE: flush shared_pool and query
                                       performance










Do you have the parameter cursor_sharing=similar set?

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
genegurevich_at_discover.com

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.





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






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


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