Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 02 Dec 2003 12:34:33 -0800
Message-ID: <F001.005D86F4.20031202123433@fatcity.com>

I have to admit that I wasn't thinking about replying to your comment when I sent this email. However, I think you are correct - there is an effect of extra items not being releasable from the shared pool when cursor_space_for_time is true. (From memory of one of Steve's seminars, it is the Heap 6 that ceases to be freeable). Whether this eliminates the creation and dropping of an x$kglpn entry I haven't yet checked.

Just as a quick test of what sorts of benefits could be achieved on latches by setting this parameter, I ran up a quick pl/sql loop and got the following results -
(included in-line in case attachments get rejected, so the
formatting is probably rubbish). (Version 9.2.0.3)

Comments on cursor_space_for_time = true

Setting this parameter does change the latching in the library cache, but does not achieve total elimination.

Significantly, more benefit comes from session_cached_cursors than cursor_space_for_time.

Tested:

declare
m_junk varchar2(20);
begin
for i in 1..100 loop
execute immediate
'select ''abc'' from dual' into m_junk; end loop;

end;
/

Environment changes:

session_cached_cursors = 0 / 100
cursor_space_for_time = true / false

Counts taken from:

v$latch
v$library_cache

Latch cs4t = true cs4t = false
----- ---------- ------------
(Sess cache = 0)

shared pool                      379   492
library cache                    925            1035
library cache pin                466             674
library cache pin alloca         440   440



(Sess cache != 0)

shared pool                      102   203
library cache                    310             416
library cache pin                209             415
library cache pin alloca    0     0


Cache                    Gets        Hits Pins        Hits
-----                    ----        ---- ----        ----

cs4t=true

SQL Area                  108         107  225         223   (sess cache =
0)
SQL Area                    1           1  101         101   (sess cache !=

cs4t=false

SQL Area                  108         107  333         331   (sess cache =
0)
SQL Area                    1           1  208         208   (sess cache !=

The execute immediate is deliberate to emulate a common coding paradigm, with explicit parse calls.

You'll notice that the most significant fraction of the reduction in latch costs comes from the switch to using session cached cursors. But there is a further benefit from the cursor_space_for_time - and the most obvious change is in the 'library cache pin' - but it doesn't drop to zero.

The point I was trying to make earlier, though, was about the general issues regarding latching in this area - even when you have a fantastically perfectly written application that only ever does 'parse once execute many' you can still get library cache (etc.) latch contention purely on extreme numbers and concurrency of execution.

For those who haven't found it yet, there is a paper by Bjorn Ensig on OTN about (a.o) the cursor_space_for_time parameter and what it's doing to the shared memory.

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: Tuesday, December 02, 2003 9:59 AM

> Jonathan,
>
> I've understood that when cursor_space_for_time is true, then unpin is

only

> done when cursor is closed, thus there's no need for pinning/unpinning for
> every execution of a cursor. This should reduce hits on library cache
> latches since pinning is not done so often?
>
> Hermant,
>
> I've sometimes seen this parameter recommended when having library cache
> latching issues in large Apps installations, I have not used it myself in
> Apps though.
>
> Also note, that cursor_space_for_time requires 50-100% larger shared_pool
> (and some more private SQL area in PGA, shared_pool or large_pool,

depending

> on configuration), since shared cursor's frames can't be aged out from
> library cache until all corresponding cursors are closed (normally if
> there's not enough free memory in shared pool when parsing a new

statement,

> some unpinned, but open cursors can be thrown out, but with
> cursor_space_for_time they can't be).
>
> So, if you don't find any better cure and decide to use this parameter,

you

> should first increase your shared pool quite much to avoid ORA-4031 errors
> and then start reducing in small amounts, based on v$librarycache,
> v$rowcache, x$kghlu and shared pool/library cache latch wait statistics.
> It's not good idea to leave shared pool too large, otherwise your memory
> allocations from there (hard parses for example) will get slow (shared

pool

> latch (or latches in 9i) are kept too long when searching for
> free/recreatable chunks).
>
> Tanel.
>

-- 
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 Tue Dec 02 2003 - 14:34:33 CST