Diving 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

So what have we seen so far? We have examined reports that show both gross and detailed shared pool usage and whether or not shared areas are being reused. What can we do with this data? Ideally we will use the results to size our shared pool properly. Let's set out a few general guidelines for shared pool sizing:

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. 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. Establish a routine flush cycle to filter non-reusable code from the pool.

SEE CODE DEPOT FOR FULL SCRIPTS

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.

You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.

��

Diving 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

SEE CODE DEPOT FOR FULL SCRIPTS

Diving Into the Shared Pool - An In Depth Look at Tuning the Shared Pool (Part 1)

Oracle Tips by Mike Ault