Gudu Software: Provides Oracle tools to facilitate your daily DBA tasks

Home

Products

Downloads

Registration

Articles

Scripts

Links

About Us

Free Newsletter

Memory Tuning: Make sure the SGA is tuned correctly

Tuning the Shared Pool
    For most applications,shared pool size is critical to Oracle performance(Shared pool size is less important only for applications that issue a very limited number of discrete SQL statements.) The shared pool holds both the data dictionary cache and the fully parsed or compiled representation of PL/SQL blocks and SQL statements.
    If the shared pool is too small,then the server must dedicate resources to managing the limited space available.This comsumes CPU resources and causes contention,because restrictions must be imposed on the parallel management of various caches. The more you use triggers and stored procedures,the larger the shared pool must be.It may even reach a size measured in hundreds of megabytes.

Monitor the statistics in the V$LIBRARYCACHE over a period of time with this query:

select sum(reloads)/sum(pins) "Lib Cache miss ratio" from v$librarycache;

    Total misses should be near 0. If the ratio of misses to executions is more than1%, try to reduce the library cache misses through the means of:

Allocating additional memory for the library cache by increasing the value of the initialization parameter SHARED_POOL_SIZE.
To take advantage of additional memory available for share SQL areas,you may also need to increase the number of cursors permitted for a session.You can do this by increasing the value of the initialization parameter OPEN_CURSORS.
Be careful not to intruduce paging and swapping by allocating too much memory for the library cache.The benefits of a library cache large enough to avoid cache misses can be partially offset by reading share SQL area into memory from disk whenever you need to access them.
Writing identical SQL statements whenever possible

Monitor the statistics in the v$ROWCACHE over a period of time while your application is runing with this query:

select sum(getmisses)/sum(gets) "Row Cache miss ratio" from v$rowcache;

For frequently accessed dictionary caches,the ratio of total GETMISSES to total GETS should be less than 10% or 15%.If the ratio continues to increasing above this threshold while your application is running,you should consider increasing the amount of memory availabe to the data dictionary cache.
To increase the memory available to the cache,increase the value of initializtion parameter SHARED_POOL_SIZE.

The amount of free memory in the shared pool is reported in V$SGASTAT.The instantaneous value can be reported using the query

select sum(bytes) bytes from v$sgastat where name='free memory';

If there is always free memory available within the shared pool,then increasing the size of the pool will have little or no beneficial effect. However,just because the shared pool is full does not necessarily mean that there is a problem. If the ratios discussed above are close to 0,there is no need to increase the pool size.
1 3