Monday, January 16, 2012

Measuring Shared Pool Performance

Shared pool maintains the most recently executed SQL and PLSQL in the memory.

Shared Pool maintains there memory segments

  1. Library cache
  2. Row cache
  3. UGA if Shared server options is configured rather than dedicated server process
Library cache:

Measuring the performance of the library cache requires the analysis of 'HIT RATIOS'

Cache Miss can occur at either parse or execution phases of SQL statement processing. For information about what Cache Miss is along with parse and execution phases of SQL statement, please visit my article at


This dynamic performance view can be used to measure the miss or hit ratios.

SQL>SELECT namespace, gets,gethits,pins,pinhits,gethitratio,pinhitratio,reloads, invalidations
2        FROM v$librarycache

gethitratio is related to parse phase, pinhitratio is related to execution


Each time a statement is parsed, the value for GETS in the V$LIBRARYCACHE view is incremented by 1.The column GETHIT stores the number of times that the SQL and PL/SQL statements issued by the application found a parsed copy of themselves already in memory thus does not require parsing.
Higher end of 90 % is a good ratio


 while GETS are  linked with locks that occur at parse time, PINS are related to locks that occur at execution time.  Each time a statement is executed, the value for PINS is incremented by 1. The PINHITRATIO column in V$LIBRARYCACHE indicates how frequently executed statements found the associated parsed 
Higher end of 90 % is a good ratio


The RELOADS column shows the number of times that an executed statement had to be re-parsed because the Library Cache had aged out or invalidated the parsed version of the statement. 

The reloads ratio can be calculated by comparing the statements been executed (PINS) to the number of statements that were reloaded.

SQL> SELECT SUM(reloads)/SUM(pins) "Reload Ratio"
  2  FROM v$librarycache

Reload Ratio

0.15 percent means that we are not re-parsing statements that were previously loaded in the Library 
Cache very often.


Invalidations occur when a cached SQL statement is marked as invalid and therefore forced to parse even though it was already in the Library Cache. Cached statements are marked as invalid whenever the objects they reference are modified in some way. For example, recompiling a views

ROW Cache:

This Dictionary Cache hit ratio shows how frequently the application finds the data dictionary information it needs in memory, instead of having to read it from disk

This hit-ratio information is contained in a dynamic performance view called V$ROWCACHE.

SQL> SELECT 1 - (SUM(getmisses)/SUM(gets))
  2  "Data Dictionary Hit Ratio"
  3  FROM v$rowcache;

Data Dictionary Hit Ratio

Reference: Sybex Oracle PT

No comments:

Post a Comment