How Oracle empties buffer cache ____oracle

In Oracle9i, Oracle provides an internal event that forces a flush buffer Cache with the syntax:

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 oracle10g, Oracle provides a new feature that refreshes the buffer Cache with the following command:

alter system flush Buffer_cache;

Let's take a look at the effect 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 79 blocks in total.

2. X$bh

Sql> Select COUNT (*) from X$BH; COUNT (*) ---------- 14375 Sql> Select COUNT (*) from X$BH where state=0; --State =0 are free COUNT (*) ---------- 13960 Sql> alter system SET events = ' Immediate trace name Flush_cache '; System altered. Sql> Select COUNT (*) from X$BH where state=0; COUNT (*) ---------- 14375

We notice that after flush_cache, all the buffer is marked free.

3. Observe the impact of Flush_cache on queries

sql> set Autotrace trace stat sql> Select COUNT (*) from T; Statistics ---------------------------------------------------------- 0 recursive calls 0 DB block gets bayi consistent gets physical reads 0 Redo size . ... Sql> Select COUNT (*) from T; Statistics ---------------------------------------------------------- 0 recursive calls 0 DB block gets bayi 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 calls 0 DB block gets bayi consistent gets physical reads 0 Redo size . ...