Shows how to flush buffer cache and v$bh, you may only want to do this on Dev or Test enviornment as it would affect performance on production. -- displays the status and number of pings for every buffer in the SGA SQL> select distinct status from v$bh; STATUS ------- xcur cr -- flush buffer cache … Continue reading How to free buffer cache?
Tag: flush
Clear procedure cache (execution plan)
For SQLServer: -- Clears all the procedure cache (works on SQLServer 2005 and SQLServer 2008) DBCC FREEPROCCACHE http://msdn.microsoft.com/en-us/library/ms174283%28v=SQL.90%29.aspx -- On SQL Server 2008 one can clear cache for a specific plan/sql by specifying the handle [ ( { plan_handle | sql_handle | pool_name } ) ] DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle ) … Continue reading Clear procedure cache (execution plan)
How to flush shared pool?
One can flush the shared pool using the following DDL. SQL> ALTER SYSTEM FLUSH SHARED_POOL; System altered. Using the following one can implement the functionality in a store procedure. SQL> CREATE OR REPLACE procedure flush_shared_pool IS v_cur INTEGER; v_result INTEGER; BEGIN v_cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(v_cur, 'ALTER SYSTEM FLUSH SHARED_POOL', DBMS_SQL.NATIVE); v_result := DBMS_SQL.EXECUTE(v_cur); DBMS_SQL.CLOSE_CURSOR(v_cur); END; … Continue reading How to flush shared pool?
alexlima.com 