续: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;