DBA Tips Archive for Oracle


[an error occurred while processing this directive]

No Title

[an error occurred while processing this directive]

by Michael New, MichaelNew@earthlink.net, Gradation LLC



A common question among developers is how to flush the Oracle data buffer cache. During performance testing, it is important to replicate a real-world production environment as closely as possible; especially when it involves reducing physical I/O (disk reads). Developers need the ability to test the effects of changing execution paths without getting accidental benefits from the buffered data in the database buffer cache. Having the ability to flush the data buffer cache is essential throughout this phase of the SQL tuning process.


Although it should go without saying, I'll go ahead and say it anyways. It is NOT recommend to flush the data buffer cache on a production system!

Flushing the data buffer cache can impose a serious performance overhead, especially on RAC databases. Flushing the data buffer cache should be reserved for test systems only.

Many developers resort to bouncing the Oracle instance between test runs in order to clear the buffer cache. While this method will accomplish the goal of purging the data buffer cache, it is extremely inefficient.

It is well-known that you can use alter system flush shared_pool; to clear the library cache of recently executed SQL. Prior to Oracle Database 10g, however, there was no matching command to flush the database buffer cache.

Bear in mind that if you are running Oracle on a file system, and not using direct I/O as the O/S level, then the blocks may still be buffered in the file-system buffer; so your tests may still suffer from some spurious buffering benefit; especially if your code is accessing some smallish tables through tablescans. Remember that a small table is one that is no more than 2% of the size of the db_block_buffer instance parameter, it is NOT, as is commonly believed, one that is only four blocks or less.

About the Oracle Buffer Cache

The Oracle buffer cache is part of the System Global Area (SGA). This part of the cache holds copies of data blocks so as they can be accessed quicker by the Oracle instance rather than reading them off of disk.

The purpose of the data buffer cache is to improve database performance by minimizing physical I/O. When a data block is read by the Oracle instance, it places this block into the buffer cache since there is a chance that this block is needed again. Reading a block from the buffer cache is less costly (in terms of time) than reading it from the disk.


In Oracle9i, there are two methods to flush the data buffer cache.

The first method used in Oracle9i makes use of an undocumented command to flush the buffer cache:

SQL> alter session set events = 'immediate trace name flush_cache'; Session altered.

The second method used to flush the buffer cache was to alter the tablespace containing the data buffer blocks OFFLINE:

alter tablespace XXX offline; alter tablespace XXX online;

When you alter the tablespace offline, any blocks which are in the buffer are invalidated, and therefore subject to rapid elimination as the buffer is re-used. In fact, even when you bring the tablespace back online, if some blocks are still buffered for any reason, they cannot be reused.

Oracle Database 10g and Higher

The process of flushing the data buffer cache was made much easier in Oracle Database 10g (and beyond) by using the following command:

SQL> alter system flush buffer_cache; System altered.

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.

Copyright (c) 1998-2019 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Wednesday, 28-Dec-2011 13:56:56 EST
Page Count: 32892