How Does Oracle force refresh Buffer Cache?

In Oracle9i, Oracle provides an internal event to force refresh the Buffer Cache. Its syntax is:

Alter session set events 'immediate trace name flush_cache level 1 ';

Or:

Alter session set events = 'immediate trace name flush_cache ';

Similarly, you can use alter system-level settings:

Alter system set events = 'immediate trace name flush_cache ';

In Oracle 10 Gb, Oracle provides a new feature. You can use the following command to refresh the Buffer Cache:

Alter system flush buffer_cache;

Let's test the function of refreshing the Cache:

1. Create a test table

SQL> create table t as select * from dba_objects; Table created. SQL> analyze table t compute statistics; Table analyzed. SQL> select blocks, empty_blocks from dba_tables
2 where table_name = 'T' and owner = 'sys'; BLOCKS EMPTY_BLOCKS
----------------------
78 1

Table T has a total of 79 blocks.

2. x $ bh

SQL> select count (*) from x $ bh; COUNT (*)
----------
14375SQL> select count (*) from x $ bh where state = 0; -- state = 0 is free COUNT (*)
----------
13960SQL> alter system set events = 'immediate trace name flush_cache '; System altered. SQL> select count (*) from x $ bh where state = 0; COUNT (*)
----------
14375

We noticed that after flush_cache, all buffers are marked as free.

3. Observe the impact of flush_cache on queries.

SQL> set autotrace trace stat
SQL> select count (*) from t; Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
81 consistent gets
79 physical reads
0 redo size
... SQL> select count (*) from t; Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
81 consistent gets
0 physical reads
0 redo size
... SQL> alter system set events = 'immediate trace name flush_cache '; System altered. SQL> select count (*) from t; Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
81 consistent gets
79 physical reads
0 redo size
... SQL>

The preceding description is for reference only.