Monitoring the Shared Pool (Oracle)

The shared pool is the area of the System Global Area (SGA) that contains structures such as the data dictionary cache and the shared SQL area. This is one of the most important storage structures in an Oracle database system.

The Database Monitor displays the following information on the shared pool:

Size of the Shared Pool

The size of the shared pool is specified in Kilobytes. For a productive system, this value should not be less than 50 MB. Depending on the system workload, it may be necessary to increase this value (taking into account the total amount of storage space available). This size of the shared pool is controlled by the init<SID>.ora parameter shared_pool_size ( SHARED_POOL_SIZE (Oracle)). Note that you must restart the database instance for the change to this parameter to take effect.

Data Dictionary Cache Quality (DD Cache quality)

The data dictionary cache holds information needed by Oracle administrators, users and the Oracle database itself. Since the data dictionary is accessed often, it is best to retain as much of this information as possible in the SGA. The data dictionary cache quality statistics show the overall average hit ratio for the various Oracle dictionary caches. This value should ideally be above 90% for production systems.

The data dictionary cache will be empty when Oracle is started and will fill with use. For this reason, it is not practical to examine these statistics until the database has reached its normal operating activity.

Shared SQL Area (SQL Area getratio/pinratio)

A shared SQL area (or shared cursor cache) is an area in the shared pool which contains the parse tree and execution schedule for an individual SQL statement. Shared SQL areas are shared by identical SQL statements.

The values under SQL Area get/pinratio measure the success rate for accessing SQL statements in the Oracle shared SQL area. The ability to reuse identical SQL statements greatly reduces the work load associated with parsing and loading statements into working memory. Reusing identical SQL statements not only improves the transaction response time, but also allows for more efficient space management within the shared pool since fewer parsed statements are moved in and out of the shared SQL area.

Most important here is the pinratio, which should be close to 99%. SAP recommends Note that SQL statements must be parsed when executing transactions for the first time after database instance startup. This results in a low shared SQL area cache quality, which should however improve over time. If these figures remain low when normal activity level is reached, you should check the text of the SQL statements in the shared SQL area ( SQL Request (Shared SQL Area)). Determine whether some of the statements can be re-coded for common use. If this is not possible, increase the value of the init<SID>.ora parameter shared_pool_size ( SHARED_POOL_SIZE (Oracle)).