How to Tune Library Cache in Oracle 11g.

Library Cache Tuning

Library Cache is used to store SQL statements and PL/SQL block that are to be shared by users. Those stored SQL statements and PL/SQL block are managed within Library Cache according to the least recently used (LRU) algorithm. Library cache prevents reparsing of the statements. The diagonostic information about the tuning Library Cache can be queried on views as v$librarycache, v$sqlarea, v$sql , v$sqltext, v$db_object_cache. While these views are affected by the parameter like

 SHARED_POOL_SIZE,

OPEN_CURSORS,

SESSION_CACHED_CURSORS,

CURSOR_SPACE_FOR_TIME,

CURSOR_SHARING,

SHARED_POOL_RESERVED_SIZE.

Some of important column from v$librarycache.

• GETS: Show the total number of requests for information on the corresponding item
• PINS: Show the number of executions of SQL statements or procedures
• RELOADS: Show the number of times, during the execution phase, that the shared SQL area containing the parsed representation of the statement is aged out of the library cache to make room for another statement. The oracle server implicitly reloads the statement and parses it again.
• INVALIDATIONS: show the number of statements that have been made invalid due to the modification of a dependent object. Invalidations also cause reloads.

Tuning Tips

The get hit ratio of the v$librarycache view should be in the high 90s in OLTP. If not so, improve efficiency of your application code or increase the size of shared pool

SQL> SELECt get hitratio

      FROM v$librarycache

     WHERE namespace='SQL AREA';

Library Cache Reloads should be less than 1% of the pins, if the reloads to pin is greater than 1%, increase the value of the SHARED_POOL_SIZE.

SQL> SELECT SUM(pins) as "Executions",

  2              SUM(reloads) as  "Cache Misses",

  3              SUM(reloads) /SUM(pins)  as "Hit Ratio"

  4         FROM v$librarycache;

Executions Cache Misses  Hit Ratio

---------- ------------ ---------- ---------------

   6348913             26666         .004200089

Collecting Library Cache Statistics

to collect statistics query the V$LIBRARYCACHE table over a period of time with this query:

SELECT SUM(pins) "Executions",

   SUM(reloads) "Cache Misses while Executing"

   FROM v$librarycache;

Executions Cache Misses while Executing

---------- ----------------------------

    320871                          549

If the library cache ratio is over 1t,

You should add to the shared_pool_size.

OR

If below parameter already set than need to set next Parameter.

cursor_sharing= similar

open_cursors= 3000

session_cached_cursors= 100

OR

CURSOR_SHARING=FORCE

SESSION_CACHED_CURSOR=300

Certaily you can dynamicaly do CURSOR_SHARING=FORCE

ALTER SYSTEM SET CURSOR_SHARING=FORCE SCOPE=BOTH;

v$sesstat also provides a statistic to monitor the number of cursors each session has in its session cursor cache.
Session cached cursors, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = ‘session cursor cache count’;
Session cached cursor usage.
select a.value curr_cached, p.value max_cached, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s, v$parameter2 p
where a.statistic# = b.statistic#  and s.sid=a.sid
and p.name=’session_cached_cursors’
and b.name = ‘session cursor cache count‘
Here is a script for measuring the library cache hit ratio:
SELECT 'Buffer Cache' NAME,
ROUND ( (congets.VALUE + dbgets.VALUE - physreads.VALUE)
* 100
/ (congets.VALUE + dbgets.VALUE),
2
) VALUE
FROM v$sysstat congets, v$sysstat dbgets, v$sysstat physreads
WHERE congets.NAME = 'consistent gets'
AND dbgets.NAME = 'db block gets'
AND physreads.NAME = 'physical reads'
UNION ALL
SELECT 'Execute/NoParse',
DECODE (SIGN (ROUND ( (ec.VALUE - pc.VALUE)
* 100
/ DECODE (ec.VALUE, 0, 1, ec.VALUE),
2
)
),
-1, 0,
ROUND ( (ec.VALUE - pc.VALUE)
* 100
/ DECODE (ec.VALUE, 0, 1, ec.VALUE),
2
)
)
FROM v$sysstat ec, v$sysstat pc
WHERE ec.NAME = 'execute count'
AND pc.NAME IN ('parse count', 'parse count (total)')
UNION ALL
SELECT 'Memory Sort',
ROUND ( ms.VALUE
/ DECODE ((ds.VALUE + ms.VALUE), 0, 1, (ds.VALUE + ms.VALUE))
* 100,
2
)
FROM v$sysstat ds, v$sysstat ms
WHERE ms.NAME = 'sorts (memory)' AND ds.NAME = 'sorts (disk)'
UNION ALL
SELECT 'SQL Area get hitrate', ROUND (gethitratio * 100, 2)
FROM v$librarycache
WHERE namespace = 'SQL AREA'
UNION ALL
SELECT 'Avg Latch Hit (No Miss)',
ROUND ((SUM (gets) - SUM (misses)) * 100 / SUM (gets), 2)
FROM v$latch
UNION ALL
SELECT 'Avg Latch Hit (No Sleep)',
ROUND ((SUM (gets) - SUM (sleeps)) * 100 / SUM (gets), 2)
FROM v$latch;

Cursor related views

SELECT view_name
FROM dba_views
WHERE view_name LIKE '%CURSOR%'
ORDER BY 1;

SQL> select name from v$database;

NAME

---------

idea

SQL> show parameter cursor

NAME                                 TYPE                             VALUE

------------------------------------ -------------------------------- ------------------------------

cursor_bind_capture_destination      string                           memory+disk

cursor_sharing                                        string                           FORCE

cursor_space_for_time                        boolean                          FALSE

open_cursors                                            integer                          600

session_cached_cursors                          integer                          50

SQL>

SQL> SELECT view_name

  2  FROM dba_views

  3  WHERE view_name LIKE '%CURSOR%'

  4  ORDER BY 1;

VIEW_NAME

------------------------------

GV_$OPEN_CURSOR

GV_$SESSION_CURSOR_CACHE

GV_$SQL_CURSOR

GV_$SQL_SHARED_CURSOR

GV_$SYSTEM_CURSOR_CACHE

V_$OPEN_CURSOR

V_$SESSION_CURSOR_CACHE

V_$SQL_CURSOR

V_$SQL_SHARED_CURSOR

V_$SYSTEM_CURSOR_CACHE

10 rows selected.

SQL> SELECT 'Buffer Cache' NAME,

  2  ROUND ( (congets.VALUE + dbgets.VALUE - physreads.VALUE)

  3  * 100

  4  / (congets.VALUE + dbgets.VALUE),

  5  2

  6  ) VALUE

  7  FROM v$sysstat congets, v$sysstat dbgets, v$sysstat physreads

  8  WHERE congets.NAME = 'consistent gets'

  9  AND dbgets.NAME = 'db block gets'

 10  AND physreads.NAME = 'physical reads'

 11  UNION ALL

 12  SELECT 'Execute/NoParse',

 13  DECODE (SIGN (ROUND ( (ec.VALUE - pc.VALUE)

 14  * 100

 15  / DECODE (ec.VALUE, 0, 1, ec.VALUE),

 16  2

 17  )

 18  ),

 19  -1, 0,

 20  ROUND ( (ec.VALUE - pc.VALUE)

 21  * 100

 22  / DECODE (ec.VALUE, 0, 1, ec.VALUE),

 23  2

 24  )

 25  )

 26  FROM v$sysstat ec, v$sysstat pc

 27  WHERE ec.NAME = 'execute count'

 28  AND pc.NAME IN ('parse count', 'parse count (total)')

 29  UNION ALL

 30  SELECT 'Memory Sort',

 31  ROUND ( ms.VALUE

 32  / DECODE ((ds.VALUE + ms.VALUE), 0, 1, (ds.VALUE + ms.VALUE))

 33  * 100,

 34  2

 35  )

 36  FROM v$sysstat ds, v$sysstat ms

 37  WHERE ms.NAME = 'sorts (memory)' AND ds.NAME = 'sorts (disk)'

 38  UNION ALL

 39  SELECT 'SQL Area get hitrate', ROUND (gethitratio * 100, 2)

 40  FROM v$librarycache

 41  WHERE namespace = 'SQL AREA'

 42  UNION ALL

 43  SELECT 'Avg Latch Hit (No Miss)',

 44  ROUND ((SUM (gets) - SUM (misses)) * 100 / SUM (gets), 2)

 45  FROM v$latch

 46  UNION ALL

 47  SELECT 'Avg Latch Hit (No Sleep)',

 48  ROUND ((SUM (gets) - SUM (sleeps)) * 100 / SUM (gets), 2)

 49  FROM v$latch;

NAME                          VALUE

------------------------ ----------

Buffer Cache                  86.11

Execute/NoParse               65.93

Memory Sort                   99.99

SQL Area get hitrate          73.85

Avg Latch Hit (No Miss)         100

Avg Latch Hit (No Sleep)        100

6 rows selected.

SQL> show parameter sga_

NAME                                 TYPE                             VALUE

------------------------------------ -------------------------------- -------------

sga_max_size                         big integer                      10G

sga_target                           big integer                      0

SQL> show parameterpga_

SP2-0735: unknown SHOW option beginning "parameterp..."

SQL> show parameter pga_

NAME                                 TYPE                             VALUE

------------------------------------ -------------------------------- -------------

pga_aggregate_target                 big integer                      8G

SQL> show parameter memory

NAME                                 TYPE                             VALUE

------------------------------------ -------------------------------- -------------

hi_shared_memory_address             integer                          0

memory_max_target                    big integer                      20G

memory_target                        big integer                      18G

shared_memory_address                integer                          0

Calculating the Buffer Cache Hit Ratio

SELECT NAME, VALUE

  FROM V$SYSSTAT

WHERE NAME IN ('db block gets from cache', 'consistent gets from cache', 'physical reads cache');

Calculate the cache hit ratio for the library cache with the following query:

    Select sum(pinhits) / sum(pins) "Hit Ratio",

        sum(reloads) / sum(pins) "Reload percent"

    From v$librarycache

    Where namespace in

    ('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER');

-- The hit ratio should be above 85 percent.

-- The reload percent should be very low, 2% or less.

-- If this is not the case, increase the initialisation parameter SHARED_POOL_SIZE.

-- OPEN_CURSORS may also need to increase.

You can monitor the library cache in a couple of ways. The first method is to execute the STATSPACK report. The second is to use the V$LIBRARYCACHE view.