oracle x$bh及v$bh與table cache表快取系列(一)

wisdomone1發表於2013-01-28
---測試oracle緩衝表x$bh及v$bh的相關知識點
-----------插入90w記錄到表t_target
19:34:50 SQL> insert into t_target select level,level+1 from dual connect by level<9e5;
 
899999 rows inserted
---經查x$bh和v$bh無記錄,說明oracle未快取此表的資料到記憶體中
SQL> select * from v$bh where bjd=67396;
 
     FILE#     BLOCK#     CLASS# STATUS            XNC FORCED_READS FORCED_WRITES LOCK_ELEMENT_ADDR LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS DIRTY TEMP PING STALE DIRECT NEW       OBJD        TS#      LOBID  CACHEHINT
---------- ---------- ---------- ---------- ---------- ------------ ------------- ----------------- ----------------- ------------------ ----- ---- ---- ----- ------ --- ---------- ---------- ---------- ----------
 
SQL> /
 
     FILE#     BLOCK#     CLASS# STATUS            XNC FORCED_READS FORCED_WRITES LOCK_ELEMENT_ADDR LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS DIRTY TEMP PING STALE DIRECT NEW       OBJD        TS#      LOBID  CACHEHINT
---------- ---------- ---------- ---------- ---------- ------------ ------------- ----------------- ----------------- ------------------ ----- ---- ---- ----- ------ --- ---------- ---------- ---------- ----------
---建立另一個表
19:36:49 SQL> create table t_sml(a int);
 
Table created
 
Executed in 0.639 seconds
 
--僅插入一條記錄
19:37:22 SQL> insert into t_sml values(1);
 
1 row inserted
 
Executed in 0.078 seconds
 
19:37:30 SQL> commit;
 
Commit complete
 
Executed in 0 seconds
---查詢此表的object_id
19:37:32 SQL> select object_id from user_objects where object_name='T_SML';
 
 OBJECT_ID
----------
     67414
 
Executed in 0.047 seconds

---再次在x$bh及v$bh查詢,上述表已快取在記憶體中,初步說明oracle會根據表資料量大小不同,進而判斷是否要快取此表
SQL> select * from v$bh where bjd=67414;
 
     FILE#     BLOCK#     CLASS# STATUS            XNC FORCED_READS FORCED_WRITES LOCK_ELEMENT_ADDR LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS DIRTY TEMP PING STALE DIRECT NEW       OBJD        TS#      LOBID  CACHEHINT
---------- ---------- ---------- ---------- ---------- ------------ ------------- ----------------- ----------------- ------------------ ----- ---- ---- ----- ------ --- ---------- ---------- ---------- ----------
        10     220403          1 xcur                0            0             0 00                                                     Y     N    N    N     N      N        67414          8          0         15
        10     220406          1 xcur                0            0             0 00                                                     Y     N    N    N     N      N        67414          8          0         15
        10     220401          9 xcur                0            0             0 00                                                     Y     N    N    N     N      N        67414          8          0         15
        10     220404          1 xcur                0            0             0 00                                                     Y     N    N    N     N      N        67414          8          0         15
        10     220407          1 xcur                0            0             0 00                                                     Y     N    N    N     N      N        67414          8          0         15
        10     220402          4 xcur                0            0             0 00                                                     Y     N    N    N     N      N        67414          8          0         15
        10     220405          1 xcur                0            0             0 00                                                     Y     N    N    N     N      N        67414          8          0         15
        10     220400          8 xcur                0            0             0 00                                                     Y     N    N    N     N      N        67414          8          0         15
 
8 rows selected
 
SQL> select * from x$bh where bj=67414;
 
ADDR                   INDX    INST_ID HLADDR                BLSIZ NXT_HASH         PRV_HASH         NXT_REPL         PRV_REPL               FLAG      FLAG2      LOBID      RFLAG      SFLAG   LRU_FLAG        TS#      FILE#    DBARFIL     DBABLK      CLASS      STATE  MODE_HELD    CHANGES     CSTATE LE_ADDR          DIRTY_QUEUE SET_DS                  OBJ BA               CR_SCN_BAS CR_SCN_WRP CR_XID_USN CR_XID_SLT CR_XID_SQN CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ CR_UBA_REC     CR_SFL CR_CLS_BAS CR_CLS_WRP   LRBA_SEQ   LRBA_BNO   HSCN_BAS   HSCN_WRP   HSUB_SCN US_NXT           US_PRV           WA_NXT           WA_PRV           OQ_NXT           OQ_PRV           AQ_NXT           AQ_PRV             OBJ_FLAG        TCH        TIM   CR_RFCNT  SHR_RFCNT
---------------- ---------- ---------- ---------------- ---------- ---------------- ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------- ---------------- ---------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ----------
000000001E885678       1888          1 000007FFFB2FA5E8       8192 000007FFFB2FE348 000007FFFB2FE348 000007FFE0F9ED70 000007FFE0F9EB10   33554433          0          0          0          0          0          8         10         10     220400          8          1          0          1          0 00                         0 000007FFFBDA4BD8      67414 000007FFE05FA000          0          0          0          0          0          0          0          0          0          0          0          0        116      37564    2550807          0          1 000007FFE0F9EB78 000007FFE0F9EB78 000007FFE0F9EB88 000007FFE0F9EB88 000007FFF69EC7E0 000007FFE0F9EA08 000007FFF69EC7C0 000007FFE0F9EA18        242          1 1359373050          0          0
000000001E885678       3573          1 000007FFFB3FA7D0       8192 000007FFFB3FEAC8 000007FFFB3FEAC8 000007FFE0FC43F0 000007FFFBDA5760   33554433          0          0          0          0          0          8         10         10     220405          1          1          0          1          0 00                         0 000007FFFBDA52A8      67414 000007FFE09EA000          0          0          0          0          0          0          0          0          0          0          0          0        116      37574    2550807          0          1 000007FFE0FC41F8 000007FFE0FC41F8 000007FFE0FC4208 000007FFE0FC4208 000007FFF69DCA70 000007FFF69DCA70 000007FFF69DCA50 000007FFF69DCA50        242          1 1359373050          0          0
000000001E885678       6298          1 000007FFFB5973C8       8192 000007FFFB59D248 000007FFFB59D248 000007FFE0FEA3F0 000007FFE0FEA190   33554433          0          0          0          0          0          8         10         10     220402          4          1          0          1          0 00                         0 000007FFFBDA5978      67414 000007FFE0DEA000          0          0          0          0          0          0          0          0          0          0          0          0        116      37564    2550807          0          1 000007FFE0FEA1F8 000007FFE0FEA1F8 000007FFE0FEA208 000007FFE0FEA208 000007FFF69E8890 000007FFE0FE9F58 000007FFF69E8870 000007FFE0FE9F68        242          1 1359373050          0          0
000000001E885678       7969          1 000007FFFB6975B0       8192 000007FFFB69D9C8 000007FFFB69D9C8 000007FFE0F7B830 000007FFFBDA6500   33554433          0          0          0          0          0          8         10         10     220407          1          1          0          1          0 00                         0 000007FFFBDA6048      67414 000007FFE0242000          0          0          0          0          0          0          0          0          0          0          0          0        116      37574    2550808          0          1 000007FFE0F7B638 000007FFE0F7B638 000007FFE0F7B648 000007FFE0F7B648 000007FFE0F7B858 000007FFF69E9AB0 000007FFE0F7B868 000007FFF69E9A90        242          1 1359373050          0          0
000000001E885678      10675          1 000007FFFB8340E0       8192 000007FFFB83C148 000007FFFB83C148 000007FFE0F9EB10 000007FFFBDA5090   33554433          0          0          0          0          0          8         10         10     220404          1          1          0          1          0 00                         0 000007FFFBDA4BD8      67414 000007FFE05F6000          0          0          0          0          0          0          0          0          0          0          0          0        116      37574    2550807          0          1 000007FFE0F9E918 000007FFE0F9E918 000007FFE0F9E928 000007FFE0F9E928 000007FFE0F9EC68 000007FFF69EC7E0 000007FFE0F9EC78 000007FFF69EC7C0        242          1 1359373050          0          0
000000001E885678      13371          1 000007FFFB9D8CD8       8192 000007FFFB9DDAC8 000007FFFB9DDAC8 000007FFE0F7BA90 000007FFE0F7B830   33554433          0          0          0          0          0          8         10         10     220401          9          1          0          1          0 00                         0 000007FFFBDA6048      67414 000007FFE0246000          0          0          0          0          0          0          0          0          0          0          0          0        116      37564    2550796          0          1 000007FFE0F7B898 000007FFE0F7B898 000007FFE0F7B8A8 000007FFE0F7B8A8 000007FFF69E9AB0 000007FFE0F7B858 000007FFF69E9A90 000007FFE0F7B868        242          1 1359373050          0          0
000000001E885678      15039          1 000007FFFBAD8EC0       8192 000007FFFBADE248 000007FFFBADE248 000007FFE0FEA060 000007FFFBDA5E30   33554433          0          0          0          0          0          8         10         10     220406          1          1          0          1          0 00                         0 000007FFFBDA5978      67414 000007FFE0DE4000          0          0          0          0          0          0          0          0          0          0          0          0        116      37574    2550807          0          1 000007FFE0FE9E68 000007FFE0FE9E68 000007FFE0FE9E78 000007FFE0FE9E78 000007FFE0FEA2E8 000007FFF69E8890 000007FFE0FEA2F8 000007FFF69E8870        242          1 1359373050          0          0
000000001E885678      17746          1 000007FFFBC75AB8       8192 000007FFFBC7C9C8 000007FFFBC7C9C8 000007FFE0F7B960 000007FFE0F7B700   33554433          0          0          0          0          0          8         10         10     220403          1          1          0          1          0 00                         0 000007FFFBDA6048      67414 000007FFE0244000          0          0          0          0          0          0          0          0          0          0          0          0        116      37574    2550807          0          1 000007FFE0F7B768 000007FFE0F7B768 000007FFE0F7B778 000007FFE0F7B778 000007FFE0F7B988 000007FFE0F7B728 000007FFE0F7B998 000007FFE0F7B738        242          1 1359373050          0          0
 
8 rows selected

19:37:43 SQL> truncate table t_sml;
 
Table truncated
 
Executed in 0.577 seconds
 
-----truncate表,發現flag狀態變化了,由原0變為64,要查閱下flag列各個值代表的含義
-----而且記錄數變少,由8減少為6,說明快取的內容變少了
---說明表中資料有變化,oracle會同步到快取中去
SQL> select * from x$bh where bj=67414;
 
ADDR                   INDX    INST_ID HLADDR                BLSIZ NXT_HASH         PRV_HASH         NXT_REPL         PRV_REPL               FLAG      FLAG2      LOBID      RFLAG      SFLAG   LRU_FLAG        TS#      FILE#    DBARFIL     DBABLK      CLASS      STATE  MODE_HELD    CHANGES     CSTATE LE_ADDR          DIRTY_QUEUE SET_DS                  OBJ BA               CR_SCN_BAS CR_SCN_WRP CR_XID_USN CR_XID_SLT CR_XID_SQN CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ CR_UBA_REC     CR_SFL CR_CLS_BAS CR_CLS_WRP   LRBA_SEQ   LRBA_BNO   HSCN_BAS   HSCN_WRP   HSUB_SCN US_NXT           US_PRV           WA_NXT           WA_PRV           OQ_NXT           OQ_PRV           AQ_NXT           AQ_PRV             OBJ_FLAG        TCH        TIM   CR_RFCNT  SHR_RFCNT
---------------- ---------- ---------- ---------------- ---------- ---------------- ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------- ---------------- ---------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ----------
000000001E885678       3595          1 000007FFFB3FA7D0       8192 000007FFFB3FEAC8 000007FFFB3FEAC8 000007FF67F8AA70 000007FFFBDA5770          0          0          0          0          0          4          8         10         10     220405          1          0          0          1          0 00                         0 000007FFFBDA52A8      67414 000007FFE09EA000          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0 000007FFE0FC41F8 000007FFE0FC41F8 000007FFE0FC4208 000007FFE0FC4208 000007FFE0FC42E8 000007FFE0FC42E8 000007FFE0FC42F8 000007FFE0FC42F8        240          0 4294967295          0          0
000000001E8853E8       6343          1 000007FFFB5973C8       8192 000007FFFB59D248 000007FFE0FE78F8 000007FFE0FE9F30 000007FFFBDA5E40         64          0          0          0          0          4          8         10         10     220402          4          0          0          1          0 00                         0 000007FFFBDA5978      67414 000007FFE0DEA000          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0 000007FFE0FEA1F8 000007FFE0FEA1F8 000007FFE0FEA208 000007FFE0FEA208 000007FFE0FEA2E8 000007FFE0FEA2E8 000007FFE0FEA2F8 000007FFE0FEA2F8        240          0 4294967295          0          0
000000001E885678       8028          1 000007FFFB6975B0       8192 000007FFFB69D9C8 000007FFFB69D9C8 000007FF67FD3D50 000007FFE0F7B830          0          0          0          0          0          4          8         10         10     220407          1          0          0          1          0 00                         0 000007FFFBDA6048      67414 000007FFE0242000          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0 000007FFE0F7B638 000007FFE0F7B638 000007FFE0F7B648 000007FFE0F7B648 000007FFE0F7B728 000007FFE0F7B728 000007FFE0F7B738 000007FFE0F7B738        240          0 4294967295          0          0
000000001E885678      10753          1 000007FFFB8340E0       8192 000007FFFB83C148 000007FFFB83C148 000007FF67F66100 000007FFFBDA50A0          0          0          0          0          0          4          8         10         10     220404          1          0          0          1          0 00                         0 000007FFFBDA4BD8      67414 000007FFE05F6000          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0 000007FFE0F9E918 000007FFE0F9E918 000007FFE0F9E928 000007FFE0F9E928 000007FFE0F9EA08 000007FFE0F9EA08 000007FFE0F9EA18 000007FFE0F9EA18        240          0 4294967295          0          0
000000001E885678      15155          1 000007FFFBAD8EC0       8192 000007FFFBADE248 000007FFFBADE248 000007FF67FAF3E0 000007FFE0FEA2C0          0          0          0          0          0          4          8         10         10     220406          1          0          0          1          0 00                         0 000007FFFBDA5978      67414 000007FFE0DE4000          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0 000007FFE0FE9E68 000007FFE0FE9E68 000007FFE0FE9E78 000007FFE0FE9E78 000007FFE0FE9F58 000007FFE0FE9F58 000007FFE0FE9F68 000007FFE0FE9F68        240          0 4294967295          0          0
000000001E885678      17882          1 000007FFFBC75AB8       8192 000007FFFBC7C9C8 000007FFFBC7C9C8 000007FFE0F7B700 000007FFFBDA6510          0          0          0          0          0          4          8         10         10     220403          1          0          0          1          0 00                         0 000007FFFBDA6048      67414 000007FFE0244000          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0 000007FFE0F7B768 000007FFE0F7B768 000007FFE0F7B778 000007FFE0F7B778 000007FFE0F7B858 000007FFE0F7B858 000007FFE0F7B868 000007FFE0F7B868        240          0 4294967295          0          0
 
6 rows selected

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

相關文章