Into the Shared Pool - An In Depth Look at Tuning the Shared Pool (Part 1)
Oracle Tips by Mike Ault
Putting it All In Perspective
Guideline 3: If the shared pool shows that no
reusable SQL is being used establish a shared pool large enough to
hold the fixed size portions plus a few megabytes (usually not more
than 40) and allow the shared pool modified least recently used (LRU)
algorithm to manage the pool.
In guidelines 1, 2 and 3, start at around 40
megabytes for a standard size system. Notice in guideline 2 it is
stated that a routine flush cycle should be instituted. This flies
in the face of what Oracle Support pushes in their shared pool white
papers, however, they work from the assumption that proper SQL is
being generated and you want to reuse the SQL present in the shared
pool. In a mixed environment where there is a mixture of reusable
and non-reusable SQL the non-reusable SQL will act as a drag against
the other SQL (I call this shared pool thrashing) unless it is
periodically removed by flushing. Figure 12 shows a PL/SQL package
which can be used by the DBMS_JOB job queues to periodically flush
the shared pool only when it exceeds a specified percent full.
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.