Tune Your Oracle Database

Continued from page: 1

Thursday, August 09, 2007

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

DB tuning tools

There are some freeware tools available that help in monitoring the performance of Oracle. Such tools like DB Tuning Expert available with DB Tools Package for Oracle can be of great usage. This tool runs a database performance test and also a diagnostic test, after which it presents a comprehensive report of existing performance problems along with the suggestions for solving such problems. Through such tools the database performance can be monitored in real-time and necessary changes for parameter tweaking can be done beforehand to keep the database performance optimal. We have given this tool in this month's DVD.

DB Tuning Expert performs an analysis (left) of the database and reports (right) all the shortcomings that need to be overcome to enhance the Oracle performance

While altering the initialization parameter values, it should be noted that the database needs to be restarted so that the changes could reflect in performance of the database.

Page(s)   1  2