[20151218]未提交事務儲存在那裡.txt

lfree發表於2015-12-18

[20151218]dml未提交事務最新內容到底儲存在什麼地方.txt

--剛剛看了http://www.itpub.net/thread-1940809-1-1.html上的討論。

--我自己的想法應該在buffer cache中,自己也做1些測試,說明問題:

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> create table t (id number, name varchar2(20)) ;
Table created.

SCOTT@book> insert into t values (1,'AAAA');
1 row created.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> select rowid,t.* from t;
ROWID                      ID NAME
------------------ ---------- --------------------
AAAVr7AAEAAAAIPAAA          1 AAAA

SCOTT@book> @ &r/rowid AAAVr7AAEAAAAIPAAA
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
     88827          4        527          0 4,527                alter system dump datafile 4 block 527 ;

--實際上如果你不寫盤,透過bbed觀察看到的內容是不對的。一般這個時候我要透過bbed觀察要執行alter system checkpoint,或者重新整理髒塊到磁碟。
SCOTT@book> alter system checkpoint;
System altered.


--這個時候透過bbed觀察可以發現資料已經寫盤。
BBED> set dba 4,527
        DBA             0x0100020f (16777743 4,527)

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @8177     0x2c

BBED> x /rnc
rowdata[0]                                  @8177
----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x00
cols@8179:    2

col    0[2] @8180: 1
col    1[4] @8183: AAAA


2.開始測試:
SYS@book> @ &r/bh 4 527

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
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000083EED060          4        527          1 data block         xcur                1          0          0          0          0          0 000000007469A000 T

SCOTT@book> update t set name='BBBB' where id=1;
1 row updated.
--不提交!

SYS@book> @ &r/bh 4 527
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
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000083EED060          4        527          1 data block         xcur                1          0          0          0          0          0 000000007B07E000 T
0000000083EED060          4        527          1 data block         cr                  1    1475062          0          0          0          0 000000007469A000 T

--可以發現兩個STATE不一樣。原來變成了BA=000000007469A000,xucr=>cr. XCUR表示當前塊的狀態。
-- BA 表示就是塊地址,如果檢查裡面的內容就知道問題。資料塊大小8192. 8192 = 0x2000.而且我僅僅插入一條應該靠近塊的底板。

SYS@book> oradebug setmypid
Statement processed.

--0x000000007B07E000+0x2000-0x10 = 0x7B07FFF0
--0x000000007469A000+0x2000-0x10 = 0x7469BFF0

SYS@book> oradebug peek 0x7B07FFF0 16
[07B07FFF0, 07B080000) = 02022C00 0402C102 42424242 81FA0601

SYS@book> oradebug peek 0x7469BFF0 16
[07469BFF0, 07469C000) = 02002C00 0402C102 41414141 7FBF0602

--ASCII=0x41=>對應的就是A , ASCII=0x42=>對應的就是B.從這裡可以證明DML未提交事務最新內容到底儲存在buffer cache中。
--實際上你這樣執行一個alter system checkpoint;或者alter system flush buffer_cache;再轉儲就可以看到。

3.轉儲問題:
--實際上這個問題一直困擾我以前的學習,執行dml未提交如果這個時候參考轉儲的塊,因為沒有寫盤,往往看到錯誤的資訊。
--換1句話講alter system dump datafile 4 block 527 ;看到的磁碟檔案的資訊,如何看記憶體的資訊呢?可以參考我寫另外一篇blog。
http://blog.itpub.net/267265/viewspace-1659981/

--繼續測試:

SCOTT@book> commit ;
Commit complete.

SCOTT@book> update t set name='CCCC' where id=1;
1 row updated.

--不提交!
SYS@book> @ &r/bh 4 527
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
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000083EED060          4        527          1 data block         xcur                1          0          0          0          0          0 0000000077F3A000 T
0000000083EED060          4        527          1 data block         cr                  1    1476290          0          3       3014        498 000000007839A000 T
0000000083EED060          4        527          1 data block         cr                  1    1476289          0          3       3014        498 0000000078372000 T
0000000083EED060          4        527          1 data block         cr                  1    1476338          0          0          0          0 00000000786CC000 T
0000000083EED060          4        527          1 data block         free                0          0          0          0          0          0 000000007B07E000 T
0000000083EED060          4        527          1 data block         free                0          0          0          0          0          0 000000007469A000 T
6 rows selected.

--注意看STATE=xcur,BA=0x0000000077F3A000,0x0000000077F3A000+0x2000-0x10=0x77F3BFF0.

SYS@book> oradebug peek 0x77F3BFF0 16
[077F3BFF0, 077F3C000) = 02012C00 0402C102 43434343 86EF0601

SCOTT@book> select dump('CCCC',16) from dual ;
DUMP('CCCC',16)
-------------------------
Typ=96 Len=4: 43,43,43,43

--正好對上。

BBED> set dba 4,527
        DBA             0x0100020f (16777743 4,527)
--DBA地址是16777743。

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @8177     0x2c

BBED> x /rnc
rowdata[0]                                  @8177
----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x02
cols@8179:    2

col    0[2] @8180: 1
col    1[4] @8183: BBBB

--可以發現依舊是舊的資訊。name='BBBB'.


SCOTT@book> alter session set events 'immediate trace name set_tsn_p1 level 5';
Session altered.
--注意這裡的level 要在ts# 原來基礎上+1,為什麼我不知道?參考原來的測試:http://blog.itpub.net/267265/viewspace-1659981/

SCOTT@book> ALTER SESSION SET EVENTS 'immediate trace name buffer level 16777743';
Session altered.

--也可以執行ALTER SESSION SET EVENTS 'immediate trace name buffer level 0x0100020f';

Dump of buffer cache at level 10 for tsn=4 rdba=16777743
BH (0x77ff5638) file#: 4 rdba: 0x0100020f (4/527) class: 1 ba: 0x77f3a000
  set: 56 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 1 obj: 88827 objn: 88827 tsn: 4 afn: 4 hint: f
  hash: [0x783f9170,0x83eee1e0] lru: [0x77ff5860,0x77ff55f0]
  ckptq: [NULL] fileq: [NULL] objq: [0x7c43a348,0x7c43a348] objaq: [0x7c43a338,0x7c43a338]
  st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1
  flags: block_written_once redo_since_read
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
  buffer tsn: 4 rdba: 0x0100020f (4/527)
  scn: 0x0000.0016875c seq: 0x01 flg: 0x04 tail: 0x875c0601
  frmt: 0x02 chkval: 0x6d86 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000077F3A000 to 0x0000000077F3C000
077F3A000 0000A206 0100020F 0016875C 04010000  [........\.......]
077F3A010 00006D86 00000001 00015AFB 001686F3  [.m.......Z......]
077F3A020 00000000 00320002 01000208 00120004  [......2.........]
077F3A030 000002FD 00C0014B 0020011C 00000001  [....K..... .....]
077F3A040 00000000 000D000A 00000A1A 00C00BC6  [................]
077F3A050 001801F2 00008000 001686EF 00000000  [................]
077F3A060 00000000 00010100 0014FFFF 1F791F8D  [..............y.]
077F3A070 00001F79 1F8D0001 00000000 00000000  [y...............]
077F3A080 00000000 00000000 00000000 00000000  [................]
        Repeat 502 times
077F3BFF0 02012C00 0402C102 43434343 875C0601  [.,......CCCC..\.]
Block header dump:  0x0100020f
Object id on Block? Y
seg/obj: 0x15afb  csc: 0x00.1686f3  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000208 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0004.012.000002fd  0x00c0014b.011c.20  ----    1  fsc 0x0000.00000000
0x02   0x000a.00d.00000a1a  0x00c00bc6.01f2.18  C---    0  scn 0x0000.001686ef
bdba: 0x0100020f
data_block_dump,data header at 0x77f3a064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x77f3a064
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f8d
avsp=0x1f79
tosp=0x1f79
0xe:pti[0]  nrow=1  offs=0
0x12:pri[0] offs=0x1f8d
block_row_dump:
tab 0, row 0, @0x1f8d
tl: 11 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [ 4]  43 43 43 43
end_of_block_dump


BH (0x783f90b8) file#: 4 rdba: 0x0100020f (4/527) class: 1 ba: 0x7839a000
  set: 59 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 1,15
  dbwrid: 1 obj: 88827 objn: 88827 tsn: 4 afn: 4 hint: f
  hash: [0x783f7910,0x77ff56f0] lru: [0x7b3e5c18,0x783f9070]
  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  st: CR md: NULL tch: 1
  cr: [scn: 0x0.1686c2],[xid: 0xa.d.a1a],[uba: 0xc00bc6.1f2.18],[cls: 0x0.1686c2],[sfl: 0x0],[lc: 0x0.1686c2]
  flags: only_sequential_access
  buffer tsn: 4 rdba: 0x0100020f (4/527)
  scn: 0x0000.001686c3 seq: 0x00 flg: 0x00 tail: 0x86c30600
  frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000007839A000 to 0x000000007839C000
07839A000 0000A206 0100020F 001686C3 00000000  [................]
07839A010 00000000 00000001 00015AFB 001686C2  [.........Z......]
07839A020 00000000 00320002 01000208 0009000A  [......2.........]
07839A030 00000A19 00C00BC2 001E01F2 00008000  [................]
07839A040 00167F29 00000000 00000000 00000000  [)...............]
07839A050 00000000 00000000 00000000 00000000  [................]
07839A060 00000000 00010100 0014FFFF 1F791F8D  [..............y.]
07839A070 00001F79 1F8D0001 00000000 00000000  [y...............]
07839A080 00000000 00000000 00000000 00000000  [................]
        Repeat 502 times
07839BFF0 02002C00 0402C102 41414141 86C30600  [.,......AAAA....]
Block header dump:  0x0100020f
Object id on Block? Y
seg/obj: 0x15afb  csc: 0x00.1686c2  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000208 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.009.00000a19  0x00c00bc2.01f2.1e  C---    0  scn 0x0000.00167f29
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x0100020f
data_block_dump,data header at 0x7839a064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x7839a064
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f8d
avsp=0x1f79
tosp=0x1f79
0xe:pti[0]  nrow=1  offs=0
0x12:pri[0] offs=0x1f8d
block_row_dump:
tab 0, row 0, @0x1f8d
tl: 11 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 02
col  1: [ 4]  41 41 41 41
end_of_block_dump

--我這裡僅僅擷取其中1段,ba: 0x77f3a000,ba: 0x7839a000。這樣就可以看到記憶體中該資料塊的資訊了。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-1874666/,如需轉載,請註明出處,否則將追究法律責任。

相關文章