Oracle Performance Tuning: Flush Cache and Share memory

If you are doing Performance tuning every time you execute the Program/Query its important that you Flush the Cache and Shared memory. If you don't the results of Performance tuning exercise will be incorrect as the query will use Data available in Shared memory or Cache instead of fetching from DB thus resulting in faster execution.

1. alter system flush shared_pool is one way the alter system command statement can be used to clear existing data and re-load fresh data.

SQL> alter system flush shared_pool;

  
2. The FLUSH SHARED POOL clause of ALTER SYSTEM lets you clear all data from the shared pool in the SGA (system global area).


SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;