Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Oracle Tips by Burleson Consulting

Changing the shared_pool_size parameter

We all know from Oracle8, that Oracle offers several queries for determining when the Oracle shared pool is too small. The library cache miss ratio tells the DBA whether or not 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 a SQL statement consists of two phases: the parse phase and the execute phase. When the time comes to parse a SQL statement, Oracle first 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.

set lines 80;
set pages 999;

column mydate heading 'Yr. Mo Dy Hr.' format a16
column c1 heading "execs" format 9,999,999
column c2 heading "Cache Misses|While Executing" format 9,999,999
column c3 heading "Library Cache|Miss Ratio" format 999.99999

break on mydate skip 2;

to_char(snap_time,'yyyy-mm-dd HH24') mydate,
sum(new.pins-old.pins) c1,
sum(new.reloads-old.reloads) c2,
sum(new.pins-old.pins) library_cache_miss_ratio
stats$librarycache old,
stats$librarycache new,
stats$snapshot sn
new.snap_id = sn.snap_id
old.snap_id = new.snap_id-1
old.namespace = new.namespace
group by
to_char(snap_time,'yyyy-mm-dd HH24')

Here is the output. The report above can easily be customized to alert the DBA during times when there are excessive executions or library cache misses.
Cache Misses
Yr. Mo Dy Hr. execs While Executing LIBRARY_CACHE_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 above example, we see a clear RAM shortage in the shared pool between 10:00 AM and 11:00 AM each day. In this case we could dynamically re-configure the shared pool with additional RAM memory from the db_cache_size during this period.

The above is an excerpt from the "Oracle UNIX Administration Handbook" by Oracle press, authored by Donald K. Burleson.


Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA




Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter