Flush Shared pool & Buffer Cache in Oracle

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';

1 thought on “Flush Shared pool & Buffer Cache in Oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.