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
The command set to perform a flush on a
once every 30 minute cycle when the pool reaches 95% full would be:
VARIABLE x NUMBER;
:X,'BEGIN dbms_revealnet.flush_it(95); END;',SYSDATE,'SYSDATE
(Always commit after assigning a job or the job
will not be run and queued)
There is always a discussion as to whether this
really does help performance so I set up a test on a production
instance where on day 1 I did no automated flushing and on day 2 I
instituted the automated flushing. Figure 13 shows the graphs of
performance indicators, flush cycles and users.
SEE CODE DEPOT FOR FULL SCRIPTS
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.