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).
