Flush Shared pool & Buffer Cache in Oracle 12c or later

 

Flush Shared pool & Buffer Cache in Oracle

Flush Shared pool means flushing the cached execution plan and SQL Queries from memory.
FLush buffer cache means flushing the cached data of objects from memory.
Both is like when we restart the oracle database and all memory is cleared.

Flushing the data buffer cache & Shared pool is not recommend on Production Environment.
It may lead to increase the performance overhead, especially on RAC databases.
Using the flush buffer cache was only for test system. Please don’t use on production environment. It lead to disk I/0 overhead.

Note: Cleared the shared pool and buffer cache without restart the database:

Clear all objects and items from shared pool and buffer cache by running following commands:

For single instances:

alter system flush buffer_cache;

alter system flush shared_pool;

For RAC Environment:

alter system flush buffer_cache global;

alter system flush shared_pool global;


Check the Buffer Cache cleared:

-- For all buffer cache is empty
select * from v$bh where status != 'free';

-- For particular file or block such as particular row: (RAC Environment)
select inst_id,file#,block#,status,dirty from gv$bh where file# = 1 and block# = 101736 and status != 'free' order by inst_id,status;

-- For Single instance for specific user
select o.OBJECT_TYPE, substr(o.OBJECT_NAME,1,10) objname , b.objd , b.status, count(b.objd) from v$bh b, dba_objects o where b.objd = o.data_object_id and o.owner = 'SCOTT' group by o.object_type, o.object_name,b.objd, b.status ;


Note: Find out in which file and block our table record is located:

select name,num ,dbms_rowid.rowid_relative_fno(rowid) fileno, dbms_rowid.rowid_block_number(rowid) block_no from t1 where name='RAM';