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
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.
