RE: flush shared_pool and query performance

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Fri, 4 Apr 2008 11:12:59 -0400
Message-ID: <667C10D184B2674A82068E06A78382B51DDE78A3@AAPQMAILBX01V.proque.st>


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 Received on Fri Apr 04 2008 - 10:12:59 CDT

Original text of this message