Oracle中flush buffer cache和x$bh

PiscesCanon發表於2017-05-30
Oracle
實驗出發點：
今天做關於引數db_file_multiblock_read_count實驗的時候，詳情見http://blog.itpub.net/30174570/viewspace-2140241/，對oracle資料庫執行alter system flush buffer_cache;指令發現，x$bh表中已經存在的資訊並不會被清除。

實驗環境：
1.作業系統
  1. [oracle@oracle ~]$ uname -a
  2. Linux oracle.example.com 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
  3. [oracle@oracle ~]$ lsb_release -a
  4. LSB Version:    :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
  5. Distributor ID: RedHatEnterpriseServer
  6. Description:    Red Hat Enterprise Linux Server release 6.5 (Santiago)
  7. Release:        6.5
  8. Codename:       Santiago
2.資料庫版本
  1. SYS@proc> select * from v$version where rownum=1;

  3. BANNER
  4. --------------------------------------------------------------------------------
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

實驗過程：
  1. SYS@proc> drop table test purge;

  3. Table dropped.

  5. SYS@proc> create table test as select * from dba_objects;

  7. Table created.

  9. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='TEST') order by dbablk;

  11.      FILE#     DBABLK      STATE
  12. ---------- ---------- ----------
  13.          1      93952          1

  15. SYS@proc> shutdown immediate;
  16. Database closed.
  17. Database dismounted.
  18. ORACLE instance shut down.
  19. SYS@proc> startup;
  20. ORACLE instance started.

  22. Total System Global Area 521936896 bytes
  23. Fixed Size                 2254824 bytes
  24. Variable Size            159385624 bytes
  25. Database Buffers         356515840 bytes
  26. Redo Buffers               3780608 bytes
  27. Database mounted.
  28. Database opened.
  29. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='TEST') order by dbablk;

  31. no rows selected

  33. SYS@proc> select count(*) from test;

  35.   COUNT(*)
  36. ----------
  37.      86991

  39. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='TEST') order by dbablk;

  41.      FILE#     DBABLK      STATE
  42. ---------- ---------- ----------
  43.          1      93952          1
  44.          1      93952          3
  45.          1      93952          3
  46.          1      94668          1
  47. ...省略部分內容...
  48.      FILE#     DBABLK      STATE
  49. ---------- ---------- ----------
  50.          1      97609          1
  51.          1      97743          1
  52.          1      97744          1
  53.          1      97745          1

  55. 318 rows selected.
  56. SYS@proc> alter system flush buffer_cache;

  58. System altered.

  60. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='TEST') order by dbablk;

  62.      FILE#     DBABLK      STATE
  63. ---------- ---------- ----------
  64.          1      93952          0
  65.          1      93952          0
  66.          1      93952          0
  67.          1      94668          0
  68. ...省略部分內容...
  69.      FILE#     DBABLK      STATE
  70. ---------- ---------- ----------
  71.          1      97609          0
  72.          1      97654          0
  73.          1      97738          0
  74.          1      97739          0

需要注意的地方：
select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='TEST') order by dbablk;這裡的data_object_id在對錶做move後不能替換成object_id。
具體原因參見我的另一篇博文：http://blog.itpub.net/30174570/viewspace-2140058/

實驗結論：
oracle資料庫執行alter system flush buffer_cache;只能清除資料快取，並不能清除BH資訊。

其他資料：
The possible values of X$BH.STATE are:
0, FREE, no valid block image
1, XCUR, a current mode block, exclusive to this instance
2, SCUR, a current mode block, shared with other instances
3, CR,   a consistent read (stale) block image
4, READ, buffer is reserved for a block being read from disk
5, MREC, a block in media recovery mode
6, IREC, a block in instance (crash) recovery mode

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30174570/viewspace-2140062/，如需轉載，請註明出處，否則將追究法律責任。

相關文章