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