Into the Shared Pool - An In Depth Look at Tuning the Shared Pool (Part 2)
Oracle Tips by Mike Ault
Last month in part one of this article we
discussed what exactly the shared pool is, monitoring and tuning the
SQL areas of the shared pool and when should the shared pool be
flushed. In addition I presented three initial general guidelines
for tuning the shared pool:
Guideline 1: If gross usage of the shared pool
in a non-ad-hoc environment exceeds 95% (rises to 95% or greater and
stays there) establish a shared pool size large enough to hold the
fixed size portions, pin reusable packages and procedures and then
increase shared pool by 20% increments until usage drops below 90%
on the average.
Guideline 2: If the shared pool shows a mixed
ad-hoc and reuse environment establish a shared pool size large
enough to hold the fixed size portions, pin reusable packages and
establish a comfort level above this required level of pool fill,
then establish a routine flush cycle to filter non-reusable code
from the pool.
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.