Rules for adjusting shared_pool_size
From Oracle8 onward, there are several queries used 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 miss ratio is greater than one, the
DBA 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 script will compute the library cache miss ratio. 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.
rpt_lib_miss_oracle9i.sql
SEE CODE DEPOT FOR FULL SCRIPTS
The output is shown below. 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, there is clearly a RAM shortage in the
shared pool between 10:00 a.m. and 11:00 a.m. each day. In this
case, the shared pool could be reconfigured dynamically with
additional RAM memory from the
db_cache_size during
this period.
SEE CODE DEPOT FOR FULL SCRIPTS
