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.