[20170428]延迟块清除测试.txt

[20170428]延迟块清除测试.txt

--//做一个延迟块清除测试,前面的测试太乱了,思路非常不清楚.

1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> alter database flashback on;
Database altered.

--//打开flashback ，就关闭了IMU,这样测试稳妥一些,不会出现奇怪的问题.

SCOTT@book> select flashback_on from v$database ;
FLASHBACK_ON
------------------
YES

CREATE TABLESPACE TEA DATAFILE
  '/mnt/ramdisk/book/tea01.dbf' SIZE 40M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;

--//安全起见重启数据库再测试。

2.测试:
SCOTT@book> create table deptx tablespace tea as select * from dept ;
Table created.

--//dba = 6,129 .
SCOTT@book> update deptx set dname=lower(dname) where deptno=10;
1 row updated.

SCOTT@book> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
2.21.1789

C70                                                                          XIDUSN      XIDSLOT       XIDSQN       UBAFIL       UBABLK       UBASQN       UBAREC STATUS              USED_UBLK    USED_UREC XID              ADDR             START_DATE
---------------------------------------------------------------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ---------------- ------------ ------------ ---------------- ---------------- -------------------
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU2_2996391332$' XID 2 21 1789;                2           21         1789            3         4210          652           57 ACTIVE                      1            1 02001500FD060000 00000000818AE050 2017-04-28 10:45:47
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU2_2996391332$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 4210;

SYS@book> alter system flush buffer_cache;
System altered.

SCOTT@book> alter system dump datafile '/mnt/ramdisk/book/tea01.dbf' block 129;
System altered.

Block header dump:  0x01800081
Object id on Block? Y
seg/obj: 0x1677c  csc: 0x03.4281a558  itc: 3  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0003.4281a558
0x02   0x0002.015.000006fd  0x00c01072.028c.39  ----    1  fsc 0x0000.00000000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01800081
block_row_dump:
tab 0, row 0, @0x1f6e
tl: 26 fb: --H-FL-- lb: 0x2  cc: 3
~~~~~~~~~~~~~~~~~~~~=> 使用第2个事务槽.
col  0: [ 2]  c1 0b
col  1: [10]  61 63 63 6f 75 6e 74 69 6e 67
col  2: [ 8]  4e 45 57 20 59 4f 52 4b
--//lock没有清除. xid相关信息也和前面能对上.

SYS@book> ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU2_2996391332$';
System altered.

  TRN CTL:: seq: 0x028c chd: 0x001b ctl: 0x000e inc: 0x00000000 nfb: 0x0001
            mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00c01077.028c.26 scn: 0x0003.4281871a
Version: 0x01
FREE BLOCK POOL::
  uba: 0x00c01077.028c.26 ext: 0x14 spc: 0x1142
  uba: 0x00000000.028c.19 ext: 0x14 spc: 0x1510
  uba: 0x00000000.028c.25 ext: 0x14 spc: 0x11cc
  uba: 0x00000000.021b.01 ext: 0x2  spc: 0x1f84
  uba: 0x00000000.021b.01 ext: 0x2  spc: 0x1f84
TRN TBL::

index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
------------------------------------------------------------------------------------------------
0x14    9    0x00  0x06fc  0x000b  0x0003.42819ec6  0x00c01072  0x0000.000.00000000  0x00000001   0x00000000  1493346210
0x15   10    0x80  0x06fd  0x0014  0x0003.4281a5b5  0x00c01072  0x0000.000.00000000  0x00000001   0x00000000  0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0x16    9    0x00  0x06fb  0x000e  0x0003.4281a41d  0x00c01072  0x0000.000.00000000  0x00000001   0x00000000  1493347262

--//scn = 0x0003.4281a5b5.

SCOTT@book> @ &r/scn10 0x0003.4281a5b5
C20                  SCN_ SCN_BASE     SCN_WRAP     SCN_BASE          SCN
-------------------- ---- -------- ------------ ------------ ------------
0x0003.4281a5b5      0003 4281a5b5            3   1115792821  14000694709

SCOTT@book> select ora_rowscn,deptx.*,dbms_flashback.get_system_change_number from scott.deptx where rownum=1;
  ORA_ROWSCN       DEPTNO DNAME          LOC           GET_SYSTEM_CHANGE_NUMBER
------------ ------------ -------------- ------------- ------------------------
14000694616           10 accounting     NEW YORK                   14000694958

select 14000694616,trunc(14000694616/power(2,32)) scn_wrap,mod(14000694616,power(2,32))  scn_base from dual
14000694616     SCN_WRAP     SCN_BASE SCN_WRAP16 SCN_BASE16
------------ ------------ ------------ ---------- ----------
14000694616            3   1115792728          3   4281a558

--//看看来之哪里:
$ cat scn.cmd
set count 8192
set width 210

p dba 6,129  kcbh.bas_kcbh
p dba 6,129  ktbbh.ktbbhcsc.kscnbas

p dba 6,129  ktbbh.ktbbhitl[0].ktbitflg
p dba 6,129  ktbbh.ktbbhitl[0].ktbitbas

p dba 6,129  ktbbh.ktbbhitl[1].ktbitflg
p dba 6,129  ktbbh.ktbbhitl[1].ktbitbas

p dba 6,129  ktbbh.ktbbhitl[2].ktbitflg
p dba 6,129  ktbbh.ktbbhitl[2].ktbitbas

quit

$ $ORACLE_HOME/bin/bbed parfile=bbed.par cmdfile=scn.cmd

BBED> p dba 6,129  kcbh.bas_kcbh
ub4 bas_kcbh                                @8        0x4281a5b5            <==块scn,现在对应undo事务槽的scn

BBED> p dba 6,129  ktbbh.ktbbhcsc.kscnbas
ub4 kscnbas                                 @28       0x4281a558           

BBED> p dba 6,129  ktbbh.ktbbhitl[0].ktbitflg
ub2 ktbitflg                                @60       0x8000 (KTBFCOM)

BBED> p dba 6,129  ktbbh.ktbbhitl[0].ktbitbas
ub4 ktbitbas                                @64       0x4281a558            <==对应伪列的ORA_ROWSCN.

BBED> p dba 6,129  ktbbh.ktbbhitl[1].ktbitflg
ub2 ktbitflg                                @84       0x0001 (NONE)

BBED> p dba 6,129  ktbbh.ktbbhitl[1].ktbitbas
ub4 ktbitbas                                @88       0x00000000

BBED> p dba 6,129  ktbbh.ktbbhitl[2].ktbitflg
ub2 ktbitflg                                @108      0x0000 (NONE)

BBED> p dba 6,129  ktbbh.ktbbhitl[2].ktbitbas
ub4 ktbitbas                                @112      0x00000000
BBED> quit

--//终于清晰了.

3.提交在看看情况:(块延迟清除的情况)

SYS@book> @ &r/bh 6 129
HLADDR           DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               OBJECT_NAME
---------------- ------- ------ ----- ---------- ----- --- ---------- ---------- ---------- ---------- ---------- ---------------- -----------
0000000084B29130       6    129     1 data block xcur    1          0          0          0          0          0 0000000074DFE000 DEPTX
0000000084B29130       6    129     1 data block free    0          0          0          0          0          0 0000000074A74000 DEPTX
0000000084B29130       6    129     1 data block free    0          0          0          0          0          0 0000000074A76000 DEPTX
0000000084B29130       6    129     1 data block free    0          0          0          0          0          0 000000007500C000

SYS@book> alter system flush buffer_cache;
System altered.

SYS@book> @ &r/bh 6 129
HLADDR           DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               OBJECT_NAME
---------------- ------- ------ ----- ---------- ----- --- ---------- ---------- ---------- ---------- ---------- ---------------- -----------
0000000084B29130       6    129     1 data block free    0          0          0          0          0          0 0000000074DFE000 DEPTX
0000000084B29130       6    129     1 data block free    0          0          0          0          0          0 0000000074A74000 DEPTX
0000000084B29130       6    129     1 data block free    0          0          0          0          0          0 0000000074A76000 DEPTX
0000000084B29130       6    129     1 data block free    0          0          0          0          0          0 000000007500C000

SCOTT@book> commit ;
Commit complete.

SCOTT@book> alter system checkpoint ;
System altered.

SCOTT@book> ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU2_2996391332$';
System altered.

  TRN CTL:: seq: 0x028c chd: 0x001b ctl: 0x0015 inc: 0x00000000 nfb: 0x0002
            mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00c01077.028c.26 scn: 0x0003.4281871a
Version: 0x01
FREE BLOCK POOL::
  uba: 0x00c01077.028c.26 ext: 0x14 spc: 0x1142
  uba: 0x00c01072.028c.39 ext: 0x14 spc: 0x2dc
  uba: 0x00000000.028c.25 ext: 0x14 spc: 0x11cc
  uba: 0x00000000.021b.01 ext: 0x2  spc: 0x1f84
  uba: 0x00000000.021b.01 ext: 0x2  spc: 0x1f84

index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
------------------------------------------------------------------------------------------------
0x14    9    0x00  0x06fc  0x000b  0x0003.42819ec6  0x00c01072  0x0000.000.00000000  0x00000001   0x00000000  1493346210
0x15    9    0x00  0x06fd  0xffff  0x0003.4281a86c  0x00c01072  0x0000.000.00000000  0x00000001   0x00000000  1493348218
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0x16    9    0x00  0x06fb  0x000e  0x0003.4281a41d  0x00c01072  0x0000.000.00000000  0x00000001   0x00000000  1493347262

--//当前已经提交,事务scn=0x0003.4281a86c
SCOTT@book> @ &r/scn10 0x0003.4281a86c
C20                  SCN_ SCN_BASE     SCN_WRAP     SCN_BASE          SCN
-------------------- ---- -------- ------------ ------------ ------------
0x0003.4281a86c      0003 4281a86c            3   1115793516  14000695404

$ $ORACLE_HOME/bin/bbed parfile=bbed.par cmdfile=scn.cmd
BBED> p dba 6,129  kcbh.bas_kcbh
ub4 bas_kcbh                                @8        0x4281a5b5

BBED> p dba 6,129  ktbbh.ktbbhcsc.kscnbas
ub4 kscnbas                                 @28       0x4281a558

BBED> p dba 6,129  ktbbh.ktbbhitl[0].ktbitflg
ub2 ktbitflg                                @60       0x8000 (KTBFCOM)

BBED> p dba 6,129  ktbbh.ktbbhitl[0].ktbitbas
ub4 ktbitbas                                @64       0x4281a558

BBED> p dba 6,129  ktbbh.ktbbhitl[1].ktbitflg
ub2 ktbitflg                                @84       0x0001 (NONE)

BBED> p dba 6,129  ktbbh.ktbbhitl[1].ktbitbas
ub4 ktbitbas                                @88       0x00000000

BBED> p dba 6,129  ktbbh.ktbbhitl[2].ktbitflg
ub2 ktbitflg                                @108      0x0000 (NONE)

BBED> p dba 6,129  ktbbh.ktbbhitl[2].ktbitbas
ub4 ktbitbas                                @112      0x00000000

BBED> quit

--//可以发现块没有写入提交信息.因为块dba=6,129不在数据缓存,采用延迟块提交.

4.现在访问看看:

SCOTT@book> select ora_rowscn,deptx.*,dbms_flashback.get_system_change_number from scott.deptx where rownum=1;
  ORA_ROWSCN       DEPTNO DNAME          LOC           GET_SYSTEM_CHANGE_NUMBER
------------ ------------ -------------- ------------- ------------------------
14000695404           10 accounting     NEW YORK                   14000695738

select 14000695404,trunc(14000695404/power(2,32)) scn_wrap,mod(14000695404,power(2,32))  scn_base from dual
14000695404     SCN_WRAP     SCN_BASE SCN_WRAP16 SCN_BASE16
------------ ------------ ------------ ---------- ----------
14000695404            3   1115793516          3   4281a86c

--//现在ora_rowscn=14000695404,与undo事务槽的scn=0x0003.4281a86c.
select 14000695738,trunc(14000695738/power(2,32)) scn_wrap,mod(14000695738,power(2,32))  scn_base from dual
14000695738     SCN_WRAP     SCN_BASE SCN_WRAP16 SCN_BASE16
------------ ------------ ------------ ---------- ----------
14000695738            3   1115793850          3   4281a9ba

SCOTT@book> alter system checkpoint ;
System altered.

SCOTT@book> alter system dump datafile '/mnt/ramdisk/book/tea01.dbf' block 129;
System altered.

Block header dump:  0x01800081
Object id on Block? Y
seg/obj: 0x1677c  csc: 0x03.4281a9ba  itc: 3  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0003.4281a558
0x02   0x0002.015.000006fd  0x00c01072.028c.39  C---    0  scn 0x0003.4281a86c
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01800081
data_block_dump,data header at 0x7f97dcb9a274

..
block_row_dump:
tab 0, row 0, @0x1f6e
tl: 26 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 0b
col  1: [10]  61 63 63 6f 75 6e 74 69 6e 67
col  2: [ 8]  4e 45 57 20 59 4f 52 4b

--//看见了吗,提交了,块的lock表示也清除了.flag=C---. scn=0x0003.4281a86c

$ $ORACLE_HOME/bin/bbed parfile=bbed.par cmdfile=scn.cmd
BBED> p dba 6,129  kcbh.bas_kcbh
ub4 bas_kcbh                                @8        0x4281a9ba      <== dbms_flashback.get_system_change_number =0x3.4281a9ba

BBED> p dba 6,129  ktbbh.ktbbhcsc.kscnbas
ub4 kscnbas                                 @28       0x4281a9ba      <== csc 也对上了,最后一次块清除csc.

BBED> p dba 6,129  ktbbh.ktbbhitl[0].ktbitflg
ub2 ktbitflg                                @60       0x8000 (KTBFCOM)

BBED> p dba 6,129  ktbbh.ktbbhitl[0].ktbitbas
ub4 ktbitbas                                @64       0x4281a558

BBED> p dba 6,129  ktbbh.ktbbhitl[1].ktbitflg
ub2 ktbitflg                                @84       0x8000 (KTBFCOM) <==表示 C---.表示提交.

BBED> p dba 6,129  ktbbh.ktbbhitl[1].ktbitbas
ub4 ktbitbas                                @88       0x4281a86c      <==通过undo事务槽(没有覆盖)找到提交时scn.

BBED> p dba 6,129  ktbbh.ktbbhitl[2].ktbitflg
ub2 ktbitflg                                @108      0x0000 (NONE)

BBED> p dba 6,129  ktbbh.ktbbhitl[2].ktbitbas
ub4 ktbitbas                                @112      0x00000000

BBED> quit

--//终于完成了完整的分析过程,理解许多知识.

时间： 2024-09-21 02:46:16

[20170428]延迟块清除测试.txt的相关文章

[20170420]关于延迟块清除2.txt

[20170420]关于延迟块清除2.txt --昨天做延迟块清除测试时,有一个问题我自己一直没搞明白,就是把表空间设置为只读的情况下,当访问块时, --由于没法更新对应块,不知道为什么每次重启数据库,正常undo的事务槽不可能这么块覆盖,为什么ora_rowscn --总是变化,而且取的是control scn,要认真探究一下问题在那里. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BA

[20170420]关于延迟块清除3.txt

[20170420]关于延迟块清除3.txt --昨天做延迟块清除测试时,有一个问题我自己一直没搞明白,就是把表空间设置为只读的情况下,当访问块时, --由于没法更新对应块,不知道为什么每次重启数据库,正常undo的事务槽不可能这么块覆盖,为什么ora_rowscn --总是变化,而且取的是control scn,要认真探究一下问题在那里. --上午测试没有测试出来,链接http://blog.itpub.net/267265/viewspace-2137714/ => [20170420]关于

[20140324]延迟块提交测试.txt

[20140324]延迟块提交测试.txt 做一个延迟块提交测试.主要出现一般是修改许多记录,修改后当前块已经不在buffer,在commit时,如果这时要读取在磁盘的数据块代 价太大,oracle使用一种延迟机制,在下次select时读取它,提交修改信息,这个时候产生redo信息. SCOTT@test> @ver BANNER -------------------------------------------------------------------------------- Or

[20150409]只读表空间与延迟块清除.txt

[20150409]只读表空间与延迟块清除.txt --昨天测试只读表空间的数据库恢复问题,突然想到一种情况,如果只读表空间存在延迟块清除情况,这样在下次访问是会更新块的信息吗? --自己还是做1个测试: 1.首先在测试前,说明1点,设置表空间只读,仅仅阻止dml操作,并不能阻止ddl操作,ddl操作的是数据字典. SCOTT@test> @ &r/ver1 PORT_STRING                    VERSION        BANNER --------------

块清除

一般的清除是COMMIT清除,当需要修改(DML)的块大于BUFFER CACHE的10%的时候就会出现 延迟块清除,在一般的OLTP系统不会出现这样的情况.以为修改的数据量巨大,执行COMMIT的时候 只会修改ITL FLAG信息(ITL FLAG信息是U,一般的COMMIT清除是C,在没有提交的时候是没有记录的就是-----)和SCN,而对于在行上LB信息就不会清除(这叫FAST COMMIT,也是为什么COMMIT及时在数据量 很大的情况下依然很快的原因),只有依靠下次的延迟的块清除, 延

[20170419]关于块scn号.txt

[20170419]关于块scn号.txt --//数据块里面有许多scn号相关. --//数据块本身有三处记录的相应的SCN:数据块头的SCN(block scn).ktbbh结构下的 kscnbas,kscnwrp(cleanout scn).ITL信息中的 --//scn/fsc(commit scn 有时候会是control scn),有时候会存在一点点混乱,通过例子说明: 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                

[20170221]nocache工具的小测试.txt

[20170221]nocache工具的小测试.txt --nocache 这个小工具可以显示文件在缓存的数量. --其中 cachestats 有一个-v  参数可以以表格形式显示.看看它的标识是否正确. 1.环境: SYS@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------

[20150515]关于块转储问题.txt

[20150515]关于块转储问题.txt --我自己在学习oracle有时候使用块转储时,发现转储的内容跟我自己的想象不一样. --正好前一阵子ITPUB有人也遇到类似的问题,自己做一个简单探究: 1.建立测试环境: SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -----------------------

[20150321]索引空块的问题.txt

[20150321]索引空块的问题.txt --晚上看了: 索引空块较多造成index range scan的IO成本较高 http://www.dbaxiaoyu.com/archives/2504 --感觉有点怪怪的: SELECT /*+gather_plan_statistics ab*/ LOG.OID              OID, LOG.REGION           REGION, LOG.ACCEPT_SEQ       ACCEPT_SEQ, LOG.PROCESS_