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

續:http://space.itpub.net/9240380/viewspace-753456

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

 

---1000條也不快取
19:58:39 SQL> select count(*) from t_sml
           2  ;
 
  COUNT(*)
----------
      1000
 
Executed in 0.109 seconds

SQL> show user
User is "SYS"
 
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
---------------- ---------- ---------- ---------------- ---------- ---------------- ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------- ---------------- ---------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ----------
 
SQL> /

---500條記錄同樣不快取
20:03:15 SQL> select count(*) from t_sml;
 
  COUNT(*)
----------
       500
 
Executed in 0.046 seconds

--同上不快取
20:04:03 SQL> select count(*) from t_sml;
 
  COUNT(*)
----------
       300
 
Executed in 0.032 seconds

---依舊不快取
20:04:31 SQL> select count(*) from t_sml;
 
  COUNT(*)
----------
       100
 
Executed in 0.031 seconds

---表50條記錄仍不快取
20:05:09 SQL> select count(*) from t_sml;
 
  COUNT(*)
----------
        51
 
Executed in 0.047 seconds
 
----9條記錄仍不快取,6條也不快取
20:07:17 SQL> select count(*) from t_sml;
 
  COUNT(*)
----------
         9
        
---表中僅一條記錄仍不快取,oracle快取一個表的標準到底是什麼,表的資料量,表的訪問次數;sga data buffer的大小        
20:09:31 SQL> select * from t_sml;
 
                                      A
---------------------------------------
                                      1
 
Executed in 0.031 seconds
         
---重啟庫        
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Database mounted.
Database opened.
SQL>

---發現仍不快取表         
20:13:14 SQL> select * from t_sml;
 
                                      A
---------------------------------------
                                      1
 
Executed in 0.047 seconds       

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
---------------- ---------- ---------- ---------------- ---------- ---------------- ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------- ---------------- ---------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ----------
 
SQL> /
 
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
---------------- ---------- ---------- ---------------- ---------- ---------------- ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------- ---------------- ---------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ----------
 
 
---建立一個表僅插入一條記錄
20:15:43 SQL> insert into t_other select 1 from dual;
 
1 row inserted
 
Executed in 0.11 seconds
 
20:16:20 SQL> commit;