Flush Buffer Cache

> We are in the middle of performance tuning and need to flush the buffer cache. 
> Do you know any command that will flush the buffer cache on Oracle 9.2.0.8 
> database.  The only command I found - alter session set events = 'immediate 
> trace name flush_cache' does not seem to work.  Any other suggestions?  

It should work. Why do you say it doesn't? For example, set autotrace on, run 
a query, check the physical read, run again, check it again (should be less or 
0), flush cache, run again and check. Can you show me your result if it's not 
working?

Before 9i, the only method is to offline the tablespace(s) where the tables 
referenced in the query reside. Of course this brutal trick works in all versions 
of Oracle.

> Please see the following steps I did this morning in one of our test db.  
> 
> 1.  shutdown and startup monroe test db - monore75
> 
> 2.  Perform the query below and attached 1_v$bh_aft_shutdown_startup_db.xls
>      select o.owner,o.object_name,o.object_type,count(b.BLOCK#) "Blocks in Cache" 
>      from v$bh b, dba_objects o 
>      where b.OBJD=o.data_object_id and o.owner <> 'SYS'
>      group by o.owner,o.object_name,o.object_type order by 4 desc
>   
>      << File: 1_v$bh_aft_shutdown_startup_db.xls >> 
> OWNER	OBJECT_NAME	OBJECT_TYPE	Blocks in Cache
> SYSTEM	AQ$_QUEUE_TABLES	TABLE	7
> SYSTEM	AQ$_QUEUES	TABLE	2
> SYSTEM	REPCAT$_REPPROP	TABLE	1
> SYSTEM	SQLPLUS_PRODUCT_PROFILE	TABLE	1
> SYSTEM	AQ$_QUEUE_TABLES_PRIMARY	INDEX	1
> JOHNDOE	ASSIGNMENTS	TABLE	1
> JOHNDOE	PK_ASSIGNMENTS	INDEX	1
> 
> 3.  Perform test query  
>       select NF.NAMEPLURAL NAMEP
> 	, addrcity City
> 	, addrplace ST 
> 	, addrzipcod ZIP_CODE
> 	from  JOHNDOE.name_full nf,
>               johndoe.address_full AGP 
>         where  nf.nameid = agp.addrid
>         and    nf.nametype = 'a' 
>         and    exists
>             (  select  'x'
>                from    johndoe.relation_full rel
>                where   rel.relid     = nf.nameid 
>                and    rel.relrelatid = '0003481779'   
> 	       and ( 
> 	           ((rel.relisa = 'lead' and rel.relwhose = 'leadmg') 
>                   and ((rel.relstopdat is null) or (rel.relstopdat > sysdate))
>                     )
> 	 			or 
> 	        ((rel.relisa = 'p' and rel.relwhose in( 'pm','stwmgr'))
>                   and ((rel.relstopdat is null) or (rel.relstopdat > sysdate))
>                  )				   
> 	     ))		
>         and AGP.addrMC1 = 'Y'  
> 
> 4.    Perform the query below and attached 3_v$bh_aft_query.xls
> select o.owner,o.object_name,o.object_type,count(b.BLOCK#) "Blocks in Cache" 
> from v$bh b, dba_objects o 
> where b.OBJD=o.data_object_id and o.owner <> 'SYS'
> group by o.owner,o.object_name,o.object_type order by 4 desc
> 
>  << File: 3_v$bh_aft_query.xls >> 
> OWNER	OBJECT_NAME	OBJECT_TYPE	Blocks in Cache
> JOHNDOE	ADDRESS	TABLE	555
> JOHNDOE	RELATION	TABLE	309
> JOHNDOE	NAME	TABLE	298
> JOHNDOE	NAME03	INDEX	296
> JOHNDOE	ADDRESS02	INDEX	286
> SYSTEM	AQ$_QUEUE_TABLES	TABLE	7
> JOHNDOE	RELATION05	INDEX	5
> SYSTEM	AQ$_QUEUES	TABLE	2
> SYSTEM	REPCAT$_REPPROP	TABLE	1
> JOHNDOE	ASSIGNMENTS	TABLE	1
> JOHNDOE	PK_ASSIGNMENTS	INDEX	1
> SYSTEM	SQLPLUS_PRODUCT_PROFILE	TABLE	1
> SYSTEM	AQ$_QUEUE_TABLES_PRIMARY	INDEX	1
> 
> 5.    Issue alter session set events = 'immediate trace name flush_cache'
> 
> 6.   Perform the query below and attached 4_v$bh_aft_flush_cache.xls
> 
> select o.owner,o.object_name,o.object_type,count(b.BLOCK#) "Blocks in Cache" 
> from v$bh b, dba_objects o 
> where b.OBJD=o.data_object_id and o.owner <> 'SYS'
> group by o.owner,o.object_name,o.object_type order by 4 desc
> 
>  << File: 4_v$bh_aft_flush_cache.xls >> 
> OWNER	OBJECT_NAME	OBJECT_TYPE	Blocks in Cache
> JOHNDOE	ADDRESS	TABLE	385
> JOHNDOE	NAME03	INDEX	278
> JOHNDOE	NAME	TABLE	195
> JOHNDOE	RELATION	TABLE	152
> JOHNDOE	ADDRESS02	INDEX	121
> SYSTEM	AQ$_QUEUE_TABLES	TABLE	6
> JOHNDOE	RELATION05	INDEX	4
> SYSTEM	AQ$_QUEUES	TABLE	2
> SYSTEM	REPCAT$_REPPROP	TABLE	1
> JOHNDOE	ASSIGNMENTS	TABLE	1
> JOHNDOE	PK_ASSIGNMENTS	INDEX	1
> SYSTEM	SQLPLUS_PRODUCT_PROFILE	TABLE	1
> SYSTEM	AQ$_QUEUE_TABLES_PRIMARY	INDEX	1

Your test reminds me of a discussion on a Chinese DBA forum 
(http://www.itpub.net/viewthread.php?tid=1007648), although it's not a direct answer. 
What happens is that neither 9i flush_cache event nor 10g alter system flush_cache 
command removes the buffers or cleans (zeroes out) the buffers. The word 
"flush" does not mean remove; it means mark them free, even though the headers in 
those buffers still have the (original) file# and block#. If you were an Oracle kernel 
developer, you wouldn't do unnecessary work to clean up all the content in the headers; 
just changing the status flags in the headers to free is enough. You can check 
v$bh.status to find out.

For performance testing purposes, the flush_cache event or command *is* the one you 
should use. Every time after you flush_cache, the next read will be a physical read, 
regardless the residual file#'s and block#'s left in the cache buffers.

> If the content of the buffers remains, why does Oracle still read from the disk?

There's something more than I know here. I guess Oracle doesn't have the concept 
equivalent to minor page fault in operating systems (a page fault is minor, or soft in 
Windows, when the page is still in memory, not reused or overwritten yet). Perhaps 
Oracle thinks it's easier and safer to read from the disk than assume the buffer 
with the free status has the image on the disk.