Re: Suggestions Needed: Latch free

From: Yong Huang <yong321_at_yahoo.com>
Date: Thu, 08 Jan 2004 10:09:26 -0800
Message-ID: <F001.005DC299.20040108100926@fatcity.com>

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: yong321_at_yahoo.com

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: ListGuru_at_fatcity.com (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).

Received on Thu Jan 08 2004 - 12:09:26 CST

Re: Suggestions Needed: Latch free - library cache