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

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;
BEGIN
dbms_job.submit(
:X,'BEGIN dbms_revealnet.flush_it(95); END;',SYSDATE,'SYSDATE
(30/1440)’);
END;
/
COMMIT;

(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.