[20150311]x$bh的tch.txt

最新推荐文章于 2018-06-08 13:45:23 发布

cuixun7780

最新推荐文章于 2018-06-08 13:45:23 发布

阅读量160

点赞数

[20150311]x$bh的tch.txt

--昨天别人问一个问题，就是不断执行某个语句，对应的数据块的x$bh的字段tch为什么没有跟上变化。实际上tch的变化是每3秒如果有
--对该块的逻辑读，增加1次。可以简单做一个测试。

1.测试环境：
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> select rowid,dept.* from dept where deptno=10;

ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AABJVUAAEAAAAdzAAA 10 ACCOUNTING NEW YORK

SCOTT@test> @lookup_rowid AABJVUAAEAAAAdzAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
300372 4 1907 0 4,1907 alter system dump datafile 4 block 1907

$ cat bh.sql
set echo off
--------------------------------------------------------------------------------
-- @name: bh
-- @author: dion cho
-- @note: show block header
-- @usage: @bh f# b#
--------------------------------------------------------------------------------

col object_name format a20
col state format a10

select
b.hladdr,
b.dbarfil,
b.dbablk,
b.class,
decode(b.class,1,'data block',2,'sort block',3,'save undo block', 4,
'segment header',5,'save undo header',6,'free list',7,'extent map',
8,'1st level bmb',9,'2nd level bmb',10,'3rd level bmb', 11,'bitmap block',
12,'bitmap index block',13,'file header block',14,'unused',
15,'system undo header',16,'system undo block', 17,'undo header',
18,'undo block') class_type,
decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated') as state,
b.tch,
cr_scn_bas,
cr_scn_wrp,
cr_uba_fil,
cr_uba_blk,
cr_uba_seq,
(select object_name from dba_objects where object_id = b.obj) as object_name
from x$bh b
where
dbarfil = &1 and
dbablk = &2
;

2.测试：
SCOTT@test> set verify off
SCOTT@test> @bh 4       1907
HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------
00000000BC9D31E8          4       1907          1 data block         xcur                1          0          0          0          0          0

-- TCH =1
--建立脚本
$ cat ax.sql
select rowid,dept.* from dept where deptno=10;
host sleep &1
select rowid,dept.* from dept where deptno=10;

SCOTT@test> @ax 2.8
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AABJVUAAEAAAAdzAAA 10 ACCOUNTING NEW YORK

ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AABJVUAAEAAAAdzAAA 10 ACCOUNTING NEW YORK

--可以发现仅仅增加1次。换成3秒

SCOTT@test> @ax 3
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AABJVUAAEAAAAdzAAA 10 ACCOUNTING NEW YORK

ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AABJVUAAEAAAAdzAAA 10 ACCOUNTING NEW YORK

--可以发现这样tch增加2次。

3.另外别人还问块CR的问题。
SCOTT@test> alter session set statistics_level=all ;
Session altered.

SCOTT@test> select rowid,dept.* from dept where deptno=10;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AABJVUAAEAAAAdzAAA 10 ACCOUNTING NEW YORK

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5pmthxxszvjx3, child number 1
-------------------------------------
select rowid,dept.* from dept where deptno=10
Plan hash value: 2852011669
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                   | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |       |     1 (100)|          |      1 |00:00:00.01 |       2 |
|   1 | TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |    20 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|* 2 |   INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------------------------------------------

--正常一般是2个逻辑读。如果另外的会话修改。

--session 1:
SCOTT@test> update dept set DNAME='ACCOUNTING' where deptno=10;
1 row updated.

--执行6次,不提交。

--session 2，如果这个时候执行逻辑读要增加，主要还有通过undo构造块，读undo记录，这样逻辑读会增加。
SCOTT@test> select rowid,dept.* from dept where deptno=10;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AABJVUAAEAAAAdzAAA 10 ACCOUNTING NEW YORK

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5pmthxxszvjx3, child number 1
-------------------------------------
select rowid,dept.* from dept where deptno=10
Plan hash value: 2852011669
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                   | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |       |     1 (100)|          |      1 |00:00:00.01 |      10 |
|   1 | TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |    20 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |      10 |
|* 2 |   INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------------------------------------------

--但是如果再次执行，还会建立新的cr块。oracle受隐含参数的_db_block_max_cr_dba的影响最多6块。
SCOTT@test> @hide _db_block_max_cr_dba
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
--------------------- --------------------------------------------- -------------- -------------- ------------
_db_block_max_cr_dba Maximum Allowed Number of CR buffers per dba TRUE 6 6

SCOTT@test> @bh 4 1907
HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------
00000000BC9D31E8          4       1907          1 data block         cr                  1 3416997443          2          3        262       9007
00000000BC9D31E8          4       1907          1 data block         xcur               10          0          0          0          0          0
00000000BC9D31E8          4       1907          1 data block         cr                  1 3416997441          2          3        262       9007
00000000BC9D31E8          4       1907          1 data block         cr                  1 3416996818          2          3        262       9007
00000000BC9D31E8          4       1907          1 data block         cr                  1 3416996788          2          3        262       9007
00000000BC9D31E8          4       1907          1 data block         cr                  1 3416996744          2          3        262       9007
6 rows selected.

4.同事还问了一个问题，就是STATE=CR的tch都是1吗？
--实际上这些块是最容易被覆盖的块，基本不会再用，如果你查询一个大表，以上STATE=CR的基本被清除掉，除非你内存很大。

--使用as of scn查询看看，3416996818 +2^32*2 =12006931410 ( CR_SCN_WRP=2)
SCOTT@test> select rowid,dept.* from dept as of scn 12006931410 where deptno=10;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AABJVUAAEAAAAdzAAA 10 ACCOUNTING NEW YORK

SCOTT@test> @bh 4 1907
HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------
00000000BC9D31E8          4       1907          1 data block         cr                  2 3416997443          2          3        262       9007
00000000BC9D31E8          4       1907          1 data block         xcur               10          0          0          0          0          0
00000000BC9D31E8          4       1907          1 data block         cr                  1 3416997441          2          3        262       9007
00000000BC9D31E8          4       1907          1 data block         cr                  1 3416996818          2          3        262       9007
00000000BC9D31E8          4       1907          1 data block         cr                  1 3416996788          2          3        262       9007
00000000BC9D31E8          4       1907          1 data block         cr                  1 3416996744          2          3        262       9007
6 rows selected.

--很奇怪tch=2的行是CR_SCN_BAS=3416997443.不过至少说明这些tch可以不是1。做一个猜测如果sql执行很复杂，执行在3秒之外，touch
--该块多次。这样STATE=cr的tch有可能不是1.

5.继续测试：
--建立函数sleep。

CREATE OR REPLACE FUNCTION SCOTT.sleep (seconds IN NUMBER)
RETURN NUMBER AS
BEGIN
sys.dbms_lock.sleep(seconds);
RETURN seconds;
END;
/

set array 2
select rowid,dept.*,sleep(3) from dept ;

SCOTT@test> set array 2
SCOTT@test> select rowid,dept.*,sleep(3) from dept ;
ROWID                  DEPTNO DNAME          LOC             SLEEP(3)
------------------ ---------- -------------- ------------- ----------
AABJVUAAEAAAAdzAAA         10 ACCOUNTING     NEW YORK               3
AABJVUAAEAAAAdzAAB         20 RESEARCH       DALLAS1                3
AABJVUAAEAAAAdzAAC         30 SALES          CHICAGO                3
AABJVUAAEAAAAdzAAD         40 OPERATIONS     BOSTON                 3

SCOTT@test> @bh 4 1907
HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------
00000000BC9D31E8          4       1907          1 data block         cr                  3 3416998699          2          3        262       9007
00000000BC9D31E8          4       1907          1 data block         cr                  2 3416997443          2          3        262       9007
00000000BC9D31E8          4       1907          1 data block         xcur               10          0          0          0          0          0
00000000BC9D31E8          4       1907          1 data block         cr                  1 3416997441          2          3        262       9007
00000000BC9D31E8          4       1907          1 data block         cr                  1 3416996818          2          3        262       9007

--可以发现CR_SCN_BAS=3416998699的行tch=3,STATE=cr.

总结：

1.正常第1次读取的块state=XCUR.
2.正常再次读取仅仅在3秒之内tch增加1.
3.受_db_block_max_cr_dba=6,最多cr块是6.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1455777/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1455777/