Oracle-L: Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 30 Nov 2003 11:34:24 -0800
Message-ID: <F001.005D83F2.20031130113424@fatcity.com>

You still have to hit the library cache to execute a statement as it needs to be pinned in share mode, and unpinned when you finish with it. Library cache latch waits can be a symptom of excessive executions.

Have you checked the library cache latch children to see if the load is evenly balanced, or whether there is a single library cache latch that is suffering most of the sleeps.

Good news for 9.2 - v$sql, and a couple of others include the library cache child latch number, so you can see which objects are protected by the hot latch without having to use Steve's algorithm.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___November

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Sunday, November 30, 2003 1:29 PM

What's the value for your cursor_space_for_time parameter?

Tanel.

Original Message ----- From: Hemant K Chitale To: Multiple recipients of list ORACLE-L Sent: Sunday, November 30, 2003 8:54 AM Subject: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute

  I have taken SESSION_CACHED_CURSORS from 0 to 100 to 400. On occassion I still see
  very high LIBRARY CACHE LATCH contention and am considering upping the value again.
  Currently, I set it at the Instance level. Since I am running Oracle Apps, I have suggested
  to the application team to put a custom ALTER SESSION trigger into the specific first
  responsibility form for users who do navigate between forms a lot and where we see
  high contention.
  Running Steve Adams's query, I get
  SQL> @Session_Cursor_Cache.sql

  PARAMETER                     VALUE USAGE
  ----------------------------- ----- -----
  session_cached_cursors          400   50%
  open_cursors                   1024   36%

CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES

----------- ----------- 35.10% 63.09% 1.81%

MAX_CACHEABLE_CURSORS

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jonathan Lewis
INET: jonathan_at_jlcomp.demon.co.uk

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 Sun Nov 30 2003 - 13:34:24 CST