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.

No comments:

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Used the system rollback segment for non...