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

Oracle Tips by Mike Ault

alter system flush shared_pool

alter system statements are privileged operations usually run by the DBA or someone with SYSDBA privileges. 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.

Specifically, alter system flush shared_pool clears all data from the shared pool.

The correct syntax for the alter system flush shared_pool operation is:

SQL> alter system flush shared_pool;

One use of the alter system flush shared_pool function is shown in the following example.

Putting it All In Perspective

BEGIN
OPEN get_share;
OPEN get_var;
FETCH get_share INTO share_mem;
DBMS_OUTPUT.PUT_LINE('share_mem: '||to_char(share_mem));
FETCH get_var INTO variable_mem;
DBMS_OUTPUT.PUT_LINE('variable_mem: '||to_char(variable_mem));
mem_ratio:=share_mem/variable_mem;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(mem_ratio,'99.999')||' '||TO_CHA
(p_free/100,'99.999'));
IF mem_ratio>p_free/100 THEN
cur:=DBMS_SQL.OPEN_CURSOR;
sql_com:='ALTER SYSTEM FLUSH SHARED_POOL';
DBMS_SQL.PARSE(cur,sql_com,dbms_sql.v7);
row_proc:=DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
OPEN get_time;
FETCH get_time INTO todays_date;
INSERT INTO dba_running_stats VALUES ('Flush of Shared
Pool',mem_ratio,35,todays_date,0);
COMMIT;
END IF;
END flush_it;

Figure 12: Example Script to Run an Alter System Flush Shared_Pool Routine

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.

��