||Oracle Tips by Burleson
Rules for adjusting Oracle
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 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.
code depot for full scripts
. . .
. . . ;
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
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
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.
||Need an Oracle Health Check?
Does your boss blame you for an Oracle performance problem?
Need to prove that your database is properly optimized?
BC Oracle performance guru's can quickly verify every aspect of your
Oracle database and provide a complete certification that your database
is fully optimized.