RE: flush shared_pool and query performance
Date: Fri, 4 Apr 2008 11:12:59 -0400
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 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
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
"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?
[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 Received on Fri Apr 04 2008 - 10:12:59 CDT