oracle x$bh及v$bh与table cache表缓存系列(一)
---测试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
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
---------- ---------- ---------- ---------- ---------- ------------ ------------- ----------------- ----------------- ------------------ ----- ---- ---- ----- ------ --- ---------- ---------- ---------- ----------
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
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
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
-----而且记录数变少,由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博客 ” ，链接：https://blog.itpub.net/9240380/viewspace-753456/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- redhat要持续学习哟加加油
- 升级redhat 6.8 默认gdb 7.2到gdb 8.3.1操作指南
- redhat 6.8升级默认cmake 2.8到cmake 3.9
- mysql source code源代安装的前提条件requirement
- mysql handler语句之一
- mysql show open tables相关知识体系之一
- mysql snapshot read快照读及current read当前读与锁lock之一
- mysql 5.7 _rowid虚列之一
- mysql innodb lock锁之record lock之一
- mysql隐式提交事务transaction一点笔记
