Monitoring the Library Cache Miss Ratio Oracle Tips by Burleson Consulting

The library cache miss ratio tells the Remote DBA whether or not to add space to the shared pool, and it represents the ratio of the sum of library cache reloads to the sum of pins. In general, if the library cache ratio is over 1, you should consider adding to the shared_pool_size. Library cache misses occur during the parsing and preparation of the execution plans for SQL statements.

Library cache misses are an indication that the shared pool is not big enough to hold the shared SQL for all currently running programs. If you have no library cache misses (PINS = 0), you may get a small increase in performance by setting cursor_space_for_time = true, which prevents ORACLE from deallocating a shared SQL area while an application cursor associated with it is open. Library cache misses during the execute phase occur when the parsed representation exists in the library cache but has been bounced out of the shared pool.

The compilation of a SQL statement consists of two phases: the parse phase and the execute phase. When the time comes to parse a SQL statement, Oracle first checks to see if the parsed representation of the statement already exists in the library cache. If not, Oracle will allocate a shared SQL area within the library cache and then parse the SQL statement. At execution time, Oracle checks to see if a parsed representation of the SQL statement already exists in the library cache. If not, Oracle will reparse and execute the statement.

The following STATSPACK script will compute the library cache miss ratio. Note that the script sums all of the values for the individual components within the library cache and provides an instance-wide view of the health of the library cache.

rpt_lib_miss.sql

set lines 80;
set pages 999;

column mydate heading 'Yr.  Mo Dy  Hr.'                  format a16
column c1     heading     "execs"                        format 9,999,999
column c2     heading     "Cache Misses|While Executing" format 9,999,999
column c3     heading     "Library Cache|Miss Ratio"     format 999.99999

break on mydate skip 2;

select
   to_char(snap_time,'yyyy-mm-dd HH24')  mydate,
   sum(new.pins-old.pins)                c1,
   sum(new.reloads-old.reloads)          c2,
   sum(new.reloads-old.reloads)/
   sum(new.pins-old.pins)                c3
from
   stats$librarycache old,
   stats$librarycache new,
   stats$snapshot     sn
where
   new.snap_id = sn.snap_id
and
   old.snap_id = new.snap_id-1
and
   old.namespace = new.namespace
group by
   to_char(snap_time,'yyyy-mm-dd HH24')
;

Here is the output. The preceding report can easily be customized to alert the Remote DBA during times when there are excessive executions or library cache misses.

                               Cache Misses            Library Cache
Yr.  Mo Dy  Hr.       execs While Executing               Miss Ratio
---------------- ---------- --------------- ------------------------
2000-12-20 10        10,338               3                   .00029
2000-12-20 11       182,477             134                   .00073
2000-12-20 12       190,707             202                   .00106
2000-12-20 13         2,803              11                   .00392

Once this report identifies a time period where there may be a problem, STATSPACK provides the ability to run detailed reports to show the behavior of the objects within the library cache.

Monitoring Objects Within the Library Cache with STATSPACK

Within the library cache, hit ratios can be determined for all dictionary objects that are loaded into the RAM buffer. These objects include tables, procedures, triggers, indexes, package bodies, and clusters. None of these objects should be experiencing problems within the library cache. If any of the hit ratios fall below 75 percent, you can increase the size of the shared pool by adding to the shared_pool_size init.ora parameter.

The STATSPACK table stats$librarycache is the table that keeps information about library cache activity. The table has three relevant columns: namespace, pins, and reloads. The first column is the namespace, which indicates whether the measurement is for the SQL area, a table, a procedure, a package body, or a trigger. The second value in this table is pins, which counts the number of times an item in the library cache is executed. The reloads column counts the number of times the parsed representation did not exist in the library cache, forcing Oracle to allocate the private SQL areas in order to parse and execute the statement.

Let’s look at the STATSPACK scripts that we can use to monitor these objects inside the library cache.

STATSPACK Reports for the Library Cache

The following script reports on the details within the objects inside the library cache. While it is often useful to see the specifics for each object, you must remember that the only objects that can be pinned into storage are PL/SQL packages. We will be covering the pinning of packages into the SGA later in this chapter.

rpt_lib.sql

set lines 80;
set pages 999;

column mydate heading 'Yr.  Mo Dy  Hr.' format a16
column reloads       format 999,999,999
column hit_ratio     format 999.99
column pin_hit_ratio format 999.99

break on mydate skip 2;

select
   to_char(snap_time,'yyyy-mm-dd HH24')  mydate,
   new.namespace,
   (new.gethits-old.gethits)/(new.gets-old.gets) hit_ratio,
   (new.pinhits-old.pinhits)/(new.pins-old.pins) pin_hit_ratio,
   new.reloads
from
   stats$librarycache old,
   stats$librarycache new,
   stats$snapshot     sn
where
   new.snap_id = sn.snap_id
and
   old.snap_id = new.snap_id-1
and
   old.namespace = new.namespace
and
   new.gets-old.gets > 0
and
   new.pins-old.pins > 0
;

Here is the output. One nice feature of this STATSPACK report is that it shows the activity within the library cache between each snapshot period.

Yr.  Mo Dy  Hr.  NAMESPACE       HIT_RATIO PIN_HIT_RATIO      RELOADS
---------------- --------------- --------- ------------- ------------
2000-12-20 10    BODY                 1.00          1.00            5
                 PIPE                 1.00          1.00            0
                 SQL AREA              .99           .96        2,957
                 TABLE/PROCEDURE      1.00           .91          212
                 TRIGGER              1.00          1.00            0
                 BODY                 1.00          1.00            5
                 INDEX                1.00          1.00            0

2000-12-20 11    BODY                  .99           .99            5
                 CLUSTER              1.00          1.00            1
                 INDEX                1.00          1.00            0
                 PIPE                 1.00          1.00            0
                 SQL AREA              .98           .99        2,999
                 TABLE/PROCEDURE       .99          1.00          221
                 TRIGGER              1.00          1.00            0

From this report, the Remote DBA can track the loading of each type of object and see the balance of the different object types inside the library cache.

Now let’s look at the how to monitor the amount of SQL sorting within Oracle.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.