Shared Pool   «Prev  Next»
Lesson 8 Viewing the library cache statistics
Objective Determine library cache misses and parse calls for SQL.

Viewing the Library Cache Statistics

Add space to shared pool

The library cache miss ratio tells the 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 more than one, you should consider adding to the shared_pool_size.

Library Cache Misses

Library cache misses occur during the compilation of SQL statements. The compilation of a SQL statement consists of two phases:
  1. Parse 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
  2. Execute phase: 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 re-parse and execute the statement.

Within the library cache, hit ratios can be determined for all dictionary objects that are loaded. If any of the hit ratios fall below 75 percent, you should add to the shared_pool_size. These include:
  1. Table/procedures
  2. Triggers
  3. Indices
  4. Package bodies
  5. Clusters

Library cache activity

The table V$LIBRARYCACHE is the V$ table that keeps information about library cache activity.
Namespace Indicates whether the measurement is for the SQL area, a table or procedure, a package body, or a trigger
Pins Counts the number of times an item in the library cache is executed.
Reloads 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

View the code below to see an example of library cache activity.
prompt         =========================
prompt         =========================
prompt (If > 1 then increase the shared_pool_size in init.ora)
column "LIBRARY CACHE MISS RATIO" format 99.9999
column "executions"    format 999,999,999
column "Cache misses while executing"    format 999,999,999
select sum(pins) "executions", sum(reloads) "Cache misses while executing",
    (((sum(reloads)/sum(pins)))) "LIBRARY CACHE MISS RATIO"
from V$librarycache;
(If > 1 then increase the shared_pool_size in init.ora)
  executions Cache misses while executing LIBRARY CACHE MISS RATIO
------------ ---------------------------- ------------------------
     650,059                          967                    .0015
prompt         =========================
prompt          Library Cache Section
prompt         =========================
prompt hit ratio should be > 70, and pin ratio > 70 ...
column "reloads" format 999,999,999
select namespace, trunc(gethitratio * 100) "Hit ratio",
trunc(pinhitratio * 100) "pin hit ratio", reloads "reloads"
from V$librarycache;
Library Cache Section
hit ratio should be > 70, and pin ratio > 70 ...

Roll your cursor over the areas outlined below to read about the library cache section.
1) NAMESPACE 2) Hit ratio 3) pin hit ratio 4) reloads

Library Cache Section
If the library cache miss ratio is >1 then a larger shared_pool_size may be appropriate. If any of the library cache hit ratios or pin hit ratios is less than 90percent, then an increase in shared_pool_size may be appropriate.

Parse calls

High parse calls indicate those SQL statements that cannot be re-used and must be re-parsed at each execution. You will want to inspect these SQL statements to see if they can be made reentrant by adding host variables.
prompt SQL High parse calls prompt
prompt select substr(sql_text,1,60), parse_calls, executions 
V$sqlarea where parse_calls > 300 and executions < 2*parse_calls and executions > 1; SQL_TEXT
PARSE_CALLS EXECUTIONS ----------- ------------ 
dbms_output.disable; end; 593 593 begin dbms_output.enable(1000000); 
end; 886 886 
begin dbms_output.get_lines(:lines, :numlines);
          end; 832 836   

In the listing above we will see all SQL statements that have a high number of parse calls. In an ideal world, an SQL statement should be parsed once and executed numerous times. High parse counts and their solutions are addressed in the next module.
In the next lesson, we will examine the re-use in the library cache.

Viewing Library Cache Statistics - Exercise

Before you move on to the next lesson, click the Exercise link below to practice analyzing library cache statistics.
Viewing Library Cache Statistics - Exercise