Sizing up the Shared Pool

Shared Pool is also a part of the Oracle SGA in the memory. This shared pool holds the Library Cache, which is a piece of memory that Oracle uses to store SQL statements. When a Process issues an SQL statement, the text of the statement goes into the Library Cache where the statement is parsed and validated.

The Shared Pool also records the Data Dictionary Cache with definitions of tables, views, and other dictionary objects. There are indicator ratios that can be used to determine when to increase the size of the shared pool, which is determined by the SHARED_POOL_SIZE parameter. One such indicative ratio is of Library Cache Hit Ratio that shows how many cursors are being shared by SQL statements, which were found and parsed in the shared pool. In the v$librarycache table Oracle keeps tracks of all the library cache activities. This ratio can be calculated with the following query.

Select namespace, pinhits / pins*100 "Hit Ratio" from v$librarycache;

The average hit ratio of various namespace column objects should be at least 85%. If the value is not 85%, then the initialization parameter SHARED_POOL_SIZE needs to be increased. Oracle has a table v$parameter, where all the initialization parameters used by Oracle are stored. The values for those parameters can be altered through this table.

The Gets and Misses of individual activities, give you the idea of 'Immediate miss', which indicates whether you need to increase the buffer size