| 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:
- Data dictionary hit ratio
- Library cache miss ratio
- 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.
