Question: How can I flush the buffer cache in Oracle? I'm
doing testing and I don't want to have to bounce the database. I'm on
Oracle 10g release 2.
Answer: When performing performance tests, it's important to
replicate the real-world production environment as closely as
possible, especially with regard to disk reads since they are a major
time-consuming event. Flushing the data buffer cache is a great testing
tool, and save you from having to bounce (stop and re-start) your database
instance between test runs. We DO NOT recommend flushing your data buffer
cache on a production system!
The opposite of flushing the data buffers is to use the KEEP pool to eliminate
disk I/O, and do the performance test without disk reads.
In Oracle 9i and beyond you can flush the buffer cache with this command:
alter session set events ?immediate
trace name flush_cache?;
In Oracle 10g and beyond, this command will flush the buffer cache:
alter system flush buffer_cache;
Note: Flushing the data
buffer cache imposes a serious performance overhead, especially on RAC
databases. Using the flush buffer cache was intended only for test system.
In Oracle you can also flush the shared pool, if needed. See
flushing the shared pool
In the past, we had a facility to flush the shared pool. The FLUSH
SHARED POOL clause of ALTER SYSTEM lets you clear all data from the
shared pool in the SGA (system global area). This is a useful feature
to clear existing data and re-load fresh data. Now, with 10g, it
becomes possible for users to flush the cache buffers also.
Before 10g, Oracle used to internally flush the buffer cache blocks
as needed. The FLUSH SHAREDPOOL clause is useful if you need to
measure the performance of rewritten queries or a suite of queries
from identical starting points. Use the following statement to flush
the buffer cache.
SQL> ALTER SYSTEM FLUSH
BUFFER_CACHE;
System altered.
However, note that this clause is intended for use only on a test
database. It is not advisable to use this clause on a production
database, because subsequent queries will have no hits, only misses.
The following example shows the effect of flush buffer_cache. There
are 50,000 rows in the table ?POLICYREC?.
SQL> update POLICYREC set
sum_assured = sum_assured + 15;
50000 rows updated.
SQL> commit;
Commit complete.
SQL> 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 = 'NYUSER' group by o.object_type,
o.object_name,b.objd, b.status ;
OBJECT_TYPE OBJNAME OBJD STATUS COUNT(B.OBJD)
----------- ------------- ------ ------ -------
TABLE TEST1 43058 free 6
TABLE POLICYREC 43061 cr 47
TABLE POLICYREC 43061 free 238
TABLE POLICYREC 43061 xcur 376
SQL> alter system flush buffer_cache;
System altered.
SQL> 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 = 'NYUSER' group by o.object_type,
o.object_name,b.objd, b.status ;
OBJECT_TYPE OBJNAME OBJD STATUS COUNT(B.OBJD)
---------- -------------- ----- ----- -------------
TABLE TEST1 43058 free 6
TABLE POLICYREC 43061 free 660
and include the URL for the page.
