Into the Shared Pool - An In Depth Look at Tuning the Shared Pool (Part 2)
Oracle Tips by Mike Ault
Monitoring Library and Data
enter to continue
set pages 22
Figure 9: Example Script To Monitor The Library
Look at the example output from the script in
Figure 9 in Figure 10. In Figure 10 we see that all Get Hit% (gethitratio
in the view) except for indexes are greater than 80-90 percent. This
is the desired state, the value for indexes is low because of the
few accesses of that type of object. Notice that the Pin Hit% is
also greater than 90% (except for indexes) this is also to be
desired. The other goals of tuning this area are to reduce reloads
to as small a value as possible (this is done by proper sizing and
pinning) and to reduce invalidations. Invalidations happen when for
one reason or another an object becomes unusable. However, if you
must use flushing of the shared pool reloads and invalidations may
occur as objects are swapped in and out of the shared pool. Proper
pinning can reduce the number of objects reloaded and invalidated.
Guideline 7: In a system where there is no
flushing increase the shared pool size in 20% increments to reduce
reloads and invalidations and increase hit ratios.
For more information on this topic, I recommend Don
Burleson's latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 50%-off and get
instant access to the code depot of Oracle tuning scripts:
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.