ANBOB™

The first is the Session Cursor Cache, which is simply an array (or an hash table) in the server process memory (assuming dedicated connections), whose max dimension is dictated by session_cached_cursors.This cache contains a pointer into the library cache where the cursor was when it was closed; when a parse for the same statement is requested,the process follows the pointer hoping to find the cursor still intact in the library cache (if that is the case, a lot of latching is avoided since the pointer is basically a shortcut that avoids reading -and thus latching -some memory structures).

But being in this cache doesn’t prevent the cursor from being erased by the library cache if needed (the library cache knows nothing about this cache, it’s a process thing), hence it cannot influence fragmentation.

Another cache is the PL/SQL Cursor Cache, which is different – a cursor in this cache is still open as far as the library cache is concerned, hence it cannot be removed from the library cache – so it can influence fragmentation.And recent versions of Oracle (especially 10g AFAIK) try to use standard sizes for the memory “pages” allocated to cursors, hence the fragmentation is less likely to occur.

That is qualitatively how it works – so your papers are correct in saying that an excessively high session_cached_cursors *might* cause some fragmentation,but not necessarily in a measurable way if any.

Session cursor caching does not mean that server process caches the whole SQL area in the PGA.It just means that Oracle does not release the cursor object when the same statement was executed more than 2 times in the same process. The key point here is that the cursor object is just the pointer to shared library cache object of cursor type.When the cursor object is cached in the PGA, the server process does not release the cursor even when the cursor was considered to be closed.

To summarize.
1. The server process caches the cursor when the same statement is executed more than 2 times.
2. With cached cursor, Oracle does not release the cursor object and caches it in PGA.
3. Cached cursor holds the pointer to the shared library cache object(which is located in SGA).
4. The server process does not need to search the library cache chain to find matching statement just because the cursor is not closed. Hence, cursor sharing. This enables the fast and light soft parse

SESSION_CACHED_CURSORS are placed in Shared_pool which is part of SGA.increasing the value of session_cached_cursors will requires a larger shared_pool_size to cache the cursors.

Here are some great tips by Steve Adams for sizing your session_cached_cursors

Note: If ‘session cursor cache count’ = session_cached_cursors, then session_cached_cursors should be increased.
If ‘opened cursors current’ + ‘session cursor cache count’ =open_cursors, then open_cursors should be increased.

SELECT MAX (s.VALUE) used
FROM sys.v_$statname n, sys.v_$sesstat s
WHERE     n.name = 'session cursor cache count'
                   AND s.statistic# = n.statistic#;

select value from sys.v_$parameter where name = 'session_cached_cursors';

COLUMN parameter FORMAT a29
COLUMN value     FORMAT a5
COLUMN usage     FORMAT a5

SELECT 'session_cached_cursors' parameter,
       LPAD (VALUE, 5) VALUE,
       DECODE (VALUE, 0, '  n/a', TO_CHAR (100 * used / VALUE, '990') || '%')
          usage
  FROM (SELECT MAX (s.VALUE) used
          FROM sys.v_$statname n, sys.v_$sesstat s
         WHERE     n.name = 'session cursor cache count'
               AND s.statistic# = n.statistic#),
       (SELECT VALUE
          FROM sys.v_$parameter
         WHERE name = 'session_cached_cursors')
UNION ALL
SELECT 'open_cursors',
       LPAD (VALUE, 5),
       TO_CHAR (100 * used / VALUE, '990') || '%'
  FROM (  SELECT MAX (SUM (s.VALUE)) used
            FROM sys.v_$statname n, sys.v_$sesstat s
           WHERE     n.name IN
                        ('opened cursors current', 'session cursor cache count')
                 AND s.statistic# = n.statistic#
        GROUP BY s.sid),
       (SELECT VALUE
          FROM sys.v_$parameter
         WHERE name = 'open_cursors');


Note: If you try run a query which has a parse error built into it (such as ’select ssdate from dual’) then the optimizer will return a suitable Oracle error message (ORA-

00904 in the given example).  The statistic ‘parse count (failures)’ will go up by one.

column cursor_cache_hits format a17
column soft_parses format a11
column hard_parses format a11
column fails_parses format a11

select
  to_char(100 * sess / (calls-fails), '999999999990.00') || '%'  cursor_cache_hits,
  to_char(100 * (calls -fails- sess - hard) / calls, '999990.00') || '%'  soft_parses,
  to_char(100 * hard / calls, '999990.00') || '%'  hard_parses,
 to_char(100 * fails / calls, '999990.00') || '%'  fails_parses
from
  ( select value calls from sys.v_$sysstat where name = 'parse count (total)' ),
  ( select value hard from sys.v_$sysstat where name = 'parse count (hard)' ),
  ( select value fails from sys.v_$sysstat where name = 'parse count (failures)' ),
  ( select value sess from sys.v_$sysstat where name = 'session cursor cache hits' )
/


refrences freelists andixora

打赏

微信扫一扫，打赏作者吧～