Lesson 6	Identifying swapped-out items from the shared pool
Objective	Gather information about number of reloads required.

Identifying swapped-out Items from the Shared Pool Tuning

There are two methods for finding out the number of times a non-pinned stored procedure is swapped out of memory and reloaded into the shared pool:

Run the estat-bstat utility^[1] (usually located in ~/rdbms/admin/utlbstat.sql and utlestat.sql) to measure SGA consumption over a range of time.
Write your own SQL script to interrogate the V$ memory structures and note any exceptional information relating to the library cache. This must include the following measurements:
1. Data dictionary hit ratio
2. Library cache miss ratio
3. Individual hit ratios for all namespaces in the library cache

One parameter fits all

Also, be aware that the relevant parameter, shared_pool_size is used for other objects besides stored procedures. This means that one parameter fits all, and Oracle offers no method for isolating the amount of storage allocated to any subset of the shared pool.

Report for gathering information

The code below is a sample report for gathering information related to shared_pool_size.

PROMPT
PROMPT
PROMPT         ========================= 
PROMPT         DATA DICT HIT RATIO 
PROMPT         ========================= 
PROMPT (should be higher than 90 
PROMPT  else increase shared_pool_size in init.ora
PROMPT

COLUMN "Data Dict. Gets"            FORMAT 999,999,999
COLUMN "Data Dict. cache misses"    FORMAT 999,999,999
SELECT sum(gets) "Data Dict. Gets",
       sum(getmisses) "Data Dict. cache misses",
       trunc((1-(sum(getmisses)/sum(gets)))*100) 
       "DATA DICT CACHE HIT RATIO"
FROM v$rowcache;

PROMPT
PROMPT         ========================= 
PROMPT         LIBRARY CACHE MISS RATIO
PROMPT         ========================= 
PROMPT (If > 1 then increase the shared_pool_size in init.ora)
PROMPT
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; 

PROMPT
PROMPT         ========================= 
PROMPT          LIBRARY CACHE SECTION
PROMPT         ========================= 
PROMPT hit ratio should be > 70, and pin ratio > 70 ...
PROMPT

COLUMN "reloads" FORMAT 999,999,999
SELECT namespace, trunc(gethitratio * 100) 
       "Hit ratio",
trunc(pinhitratio * 100) "pin hit ratio", 
       reloads "reloads"
FROM v$librarycache;

The code below contains the SQL*Plus script that generated the report.
It will eventually become desirable to have all of an application's SQL and code loaded into the Oracle library cache.

=========================
DATA DICT HIT RATIO
=========================
(should be higher than 90 else increase shared_pool_size in init.ora)

  Data Dict. Gets    Data Dict. cache misses    DATA DICT CACHE HIT RATIO
  ---------------    -----------------------    -------------------------
     41,750,549              407,609                       99          

=========================
LIBRARY CACHE MISS RATIO
=========================
(If > 1 then increase the shared_pool_size in init.ora)


  executions   Cache misses while executing    LIBRARY CACHE MISS RATIO         
  ----------   ----------------------------    ------------------------         
  22,909,643             171,127                       .0075         

=========================
Library Cache Section
=========================
hit ratio should be > 70, and pin ratio > 70 ...

NAMESPACE             	Hit ratio          pin hit ratio        reloads 
----------          -------------       ---------------     ------------ 
SQL AREA               84                  94              25,885 
TABLE/PROCEDURE 98     99                  43,559 
BODY                   98                  84               486 
TRIGGER                98                  97               1,145 
INDEX                  0                   0 
CLUSTER                31                  33 
OBJECT                 100                 100 
PIPE                   99                  99               52

In the next lesson, we will explore the library cache in more detail.

[1]estat-bstat utility: Oracle begin statistics (utlbstat.sql) and end statistics (utlestat.sql). These are used to create an elapsed-time snapshot of Oracle activity.