Hi,

I will explain Alter System Flush Buffer Cache in Oracle in this post.

 

Buffer Cache

Data of any transaction is stored in this memory space.

 

 

 

For example: When an insert, update, or delete operation is performed on a table, the corresponding change is not written directly to the datafiles. It is stored in the buffer cache for a while(This data called as dirty data).

The most frequently used and most up-to-date data in the database is stored in here, and this memory space is common to all users. Data stored in the Buffer cache are written to the data files after a while.

 

However, you should not execute the above command in production systems unless required. Because when the buffer cache is emptied, all queries or transactions will make I / O from the physical disk, which means that our queries will slow down.

 

Flush Buffer Cache

You can flush the data in the Buffer Cache with the following query:

For single instances:

SQL> alter system flush buffer_cache;

 

For RAC Environment:

alter system flush buffer_cache global;

 

 

Check the Buffer Cache

You can check the buffer cache using the following scripts.

select * from v$bh where status != 'free';

 

You can check the buffer cache for RAC environment using the following scripts

select inst_id,file#,block#,status,dirty from gv$bh where file# = 63 and block# = 145363 and status != 'free' order by inst_id,status;

 

 

If you want to flush shared pool, you can read the following post.

Alter System Flush Shared pool in Oracle

 

 

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database