排除Cache影響手動強制刷新Buffer Cache

2024-07-21 02:43:16

字體：大中小

來源：轉載

供稿：網友

手動刷新Buffer Cache主要的原因是為了排除Cache對于測試的影響，以此促使Oracle重新執行物理訪問。以下為具體的示例參考：

◆Oracle 9i里，Oracle提供了一個內部事件，用來強制刷新Buffer Cache，它的語法如下:

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

或者如下所示:

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

◆同樣我們也可以使用alter system系統級來設置。

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

◆ Oracle 10g里，Oracle提供一個新的特性，可以通過以下的命令來刷新Buffer Cache:

alter system flush buffer_cache;

下面這個示例演示了刷新Cache后的作用:

（1）首先我們來創建測試表

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

表T共有79個Block.

（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

在這里你可以發現flush_cache以后，所有的Buffer都被標記為free。

（3）最后請留意flush_cache對于查詢的影響

SQL> set autotrace trace statSQL> select count(*) from t;Statistics----------------------------------         0  recursive calls          0  db block gets         81  consistent gets         79  physical reads          0  redo size....SQL> SQL> select count(*) from t;Statistics-----------------------         0  recursive calls          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 calls          0  db block gets         81  consistent gets         79  physical reads          0  redo size....SQL>

上一篇：有助于數據創建安全環境的重要功能說明

下一篇：快速解決DB2創建存儲過程時所遇到的錯誤