Yong, In case you missed it, see my previous reply to Jonathan's mail. I'll expand my test case and see what I can come up with for the other cases you motion.
-Mark Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI "Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is." --Unknown -----Original Message----- Sent: Thursday, January 08, 2004 1:09 PM To: Multiple recipients of list ORACLE-L It would be good if Oracle could break SQL parse down into not just hard and soft, not just hard-soft-softer (Tom Kyte's wording), but different levels. Oracle may have to work slightly harder to update these new statistics but the benefit for OLTP databases is huge. Other than the four parse invocations in your message, I think we can add one between your first and second: Invoke a parse to create a new version of the same cursor (same in the sense of same address and hash) due to either bind threshold change or execution plan change. In fact, these two types of changes may be broken down to two statistics. Looking at the columns in v$sql_shared_cursor, I'm afraid we may need much more statistics? To the OP: Other people point out common reasons for library cache latch contention. A less common reason is extensive use of public synonyms. If that's the reason, you also see row cache objects latch contention. Yong Huang Jonathan Lewis wrote: ... Code that issues a parse call may: Invoke the whole parse/optimize cycle Invoke a permissions cycle on an existing statement Invoke a search and execute cycle on an existing statement with valid permission Invoke a 'this is where it is and I know I've got permission, so just do it' cycle ... NOTE: This description is probably not complete and I'd welcome any corrections and refinements that anyone can supply. __________________________________ Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bobak, Mark INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).