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 flush the buffer cache and shared pool
alter system flush buffer_cache;
alter system flush shared_pool;
Flush the buffer cache and shared pool in 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';
Full of error.
LikeLike
for a single cache how do you flush for RAC? thanks.
LikeLike
Connect to that instance in RAC and use simple command to flush current instance:
alter system flush buffer_cache;
LikeLike
Lastly I am confused but
Your description is all doubt clear
You have to be good explanation nice
LikeLiked by 1 person
Thanks
LikeLike