Alter System Flush Buffer Cache in Oracle

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

 

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *