Oracle Tips by Burleson

Rules for adjusting Oracle shared_pool_size

We all know from Oracle8 that there are several queries for determining when the Oracle shared pool is too small. The library cache miss ratio tells the DBA whether to add space to the shared pool, and it represents the ratio of the sum of library cache reloads to the sum of pins.

In general, if the library cache ratio is over 1, you should consider adding to the shared_pool_size. Library cache misses occur during the parsing and preparation of the execution plans for SQL statements.

The compilation of an SQL statement consists of two phases: the parse phase and the execute phase. When the time comes to parse an SQL statement, Oracle checks to see if the parsed representation of the statement already exists in the library cache. If not, Oracle will allocate a shared SQL area within the library cache and then parse the SQL statement. At execution time, Oracle checks to see if a parsed representation of the SQL statement already exists in the library cache. If not, Oracle will reparse and execute the statement.

The following STATSPACK script will compute the library cache miss ratio. Note that the script sums all of the values for the individual components within the library cache and provides an instance-wide view of the health of the library cache.

See code depot for full scripts

select
. . .

from
   stats$librarycache old,
   stats$librarycache new,
   stats$snapshot     sn
where
. . . ;

Here is the output. This report can easily be customized to alert the DBA when there are excessive executions or library cache misses.

                               Cache Misses  Library Cache
Yr.  Mo Dy  Hr.       execs While Executing   Miss Ratio
---------------- ---------- --------------- -----------------------
2001-12-11 10        10,338          3               .00029
2001-12-12 10       182,477        134               .00073
2001-12-14 10       190,707        202               .00106
2001-12-16 10         2,803         11               .00392

Once this report identifies a time period where there may be a problem, STATSPACK provides the ability to run detailed reports to show the behavior of the objects within the library cache.

In the preceding example, you see a clear RAM shortage in the shared pool between 10:00 A.M. and 11:00 A.M. each day. In this case, you could dynamically reconfigure the shared pool with additional RAM memory from the db_cache_size during this period.

The above text is an excerpt from "Creating a Self Tuning Oracle Database", by Rampant TechPress.  It is only $9.95 and all scripts in this tips can be immediately downloaded.