[20121116]通過bbed觀察行連結與行遷移.txt

lfree發表於2012-11-20
[20121116]通過bbed觀察行連結與行遷移.txt

    如果應用中出現大量的行連結與行遷移,對應用的效能多少存在影響。一般情況下,行遷移主要是update後,行記錄變大,導致原來
的資料塊無法容納,在原來的塊保留指標,其他資訊放在其他塊中。而行連結主要是行記錄太大,1個資料塊無法容納,導致使用多塊儲存。
我想通過bbed簡單觀察這種情況:

1.建立測試環境:

SQL> select * from v$version where rownum<=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> create table t  ( a number,b varchar2(3000),c varchar2(3000) , d varchar2(3000), e varchar2(3000) ) tablespace test;
Table created.

SQL> create unique index p_t on t(a);
Index created.

SQL> insert into t (a) values (1);
1 row created.

SQL> commit ;
Commit complete.

SQL> select rowid ,t.a from t;
ROWID                       A
------------------ ----------
AAAdD/AAIAAAACOAAA          1

SQL> @ lookup_rowid AAAdD/AAIAAAACOAAA

    OBJECT       FILE      BLOCK        ROW
---------- ---------- ---------- ----------
    119039          8        142          0

SQL> alter system checkpoint;
System altered.


2.使用bbed觀察:
BBED> set dba 8,142
        DBA             0x0200008e (33554574 8,142)

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

BBED> x /rn
rowdata[0]                                  @8182
----------
flag@8182: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8183: 0x01
cols@8184:    1

col    0[2] @8185: 1

--可以發現僅僅記錄一個值a,其他因為都是NULL,不記錄。利用這個特性,在建表時,把經常為NULL的欄位放在後面,可以一定程度節約空間。

3.現在修改欄位c,看看情況:
SQL> update t set c=lpad('c',3000,'c') where a=1;
1 row updated.

SQL> commit ;
Commit complete.

SQL> alter system checkpoint;
System altered.

--注意要退出bbed再進入,才能看到資訊:
BBED> set dba 8,142
        DBA             0x0200008e (33554574 8,142)

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

BBED> x /rncc
rowdata[0]                                  @5172
----------
flag@5172: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5173: 0x02
cols@5174:    3

col    0[2] @5175: 1
col    1[0] @5178: *NULL*
col 2[3000] @5179: ccccccccccccccccc (太長截斷)

-- 可以發現修改c欄位後,一個資料塊依舊能容納記錄,並沒有出現行連結或者遷移的情況。
--另外對比前面看,前面的行記錄在偏移8182處,修改後行記錄在偏移5175處。
--如果檢視8182處資訊,可以發現修改前的資訊依舊存在。

BBED>  set offset 8182
        OFFSET          8182

BBED> x /rncc
rowdata[3010]                               @8182
-------------
flag@8182: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8183: 0x00
cols@8184:    1

col    0[2] @8185: 1

4.現在修改欄位b,d,看看情況:
SQL> update t set b=lpad('b',3000,'b') , d=lpad('d',3000,'d') where a=1;
1 row updated.

SQL> commit ;
Commit complete.

SQL> alter system checkpoint;
System altered.

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @2157     0x28

BBED> x /rnc
rowdata[0]                                  @2157
----------
flag@2157: 0x28 (KDRHFF, KDRHFH)
lock@2158: 0x01
cols@2159:    2
nrid@2160:0x0200008f.0

col    0[2] @2166: 1
col 1[3000] @2169: bbbbbbbbbbbbbbbbbbbbbbbbbbbbb (太長截斷)

--可以發現cols=2記錄兩個欄位,nrid在偏移2160處記錄了0x0200008f.0,這個就是欄位的其他資訊在dba=0x0200008f,小數點後面的0,表示行號。
--另外可以發現前面修改的資訊依舊存在。
BBED> set offset 8182
        OFFSET          8182

BBED> x /rnc
rowdata[6025]                               @8182
-------------
flag@8182: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8183: 0x00
cols@8184:    1

col    0[2] @8185: 1

BBED> set offset 5172
        OFFSET          5172

BBED> x /rncc
rowdata[3015]                               @5172
-------------
flag@5172: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5173: 0x01
cols@5174:    3

col    0[2] @5175: 1
col    1[0] @5178: *NULL*
col 2[3000] @5179: ccccccccccccccccccccc (太長截斷)

現在再看看DBA=0x0200008f情況。

BBED> set dba 0x0200008f
        DBA             0x0200008f (33554575 8,143)

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @2179     0x04

BBED> x /rcc
rowdata[0]                                  @2179
----------
flag@2179: 0x04 (KDRHFL)
lock@2180: 0x01
cols@2181:    2

col 0[3000] @2182: ccccccccc (太長截斷)
col 1[3000] @5185: ddddddddd (太長截斷)

5.最後在修改e看看情況:

SQL> update t set e=lpad('e',3000,'e')  where a=1;
1 row updated.

SQL> commit ;
Commit complete.

SQL> alter system checkpoint;
System altered.

BBED> set dba 8,142
        DBA             0x0200008e (33554574 8,142)

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @2157     0x28

BBED> x /rnc
rowdata[0]                                  @2157
----------
flag@2157: 0x28 (KDRHFF, KDRHFH)
lock@2158: 0x02
cols@2159:    2
nrid@2160:0x0200008f.0

col    0[2] @2166: 1
col 1[3000] @2169: bbbbbbbbbbbb (太長截斷)

BBED> set dba 0x0200008f
        DBA             0x0200008f (33554575 8,143)

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @2179     0x00

BBED> x /rccc
rowdata[0]                                  @2179
----------
flag@2179: 0x00 (NONE)
lock@2180: 0x02
cols@2181:    1
nrid@2182:0x0200008b.0

col 0[3000] @2188: ccccccccccccc (太長截斷)

--可以dba=8,143存在nrid=0x0200008b.0,欄位d,e在另外的塊中。

BBED> set dba 0x0200008b
        DBA             0x0200008b (33554571 8,139)

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @2179     0x04

BBED> x /rcc
rowdata[0]                                  @2179
----------
flag@2179: 0x04 (KDRHFL)
lock@2180: 0x01
cols@2181:    2

col 0[3000] @2182: dddddddddddd (太長截斷)
col 1[3000] @5185: eeeeeeeeeeee (太長截斷)

--另外大家注意flag標誌的變化,這個超出我的能力。

6.看看一些sql語句的執行情況:

SQL> alter system flush BUFFER_CACHE;
System altered.

SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.

SQL> select substr(e,1,10) from t where a=1;

SUBSTR(E,1,10)
--------------------
eeeeeeeeee

SQL> alter session set events '10046 trace name context off';
Session altered.

--檢視跟蹤檔案發現:

PARSING IN CURSOR #8 len=38 dep=0 uid=84 ct=3 lid=84 tim=1353072283364176 hv=1310139427 ad='daec3c10' sqlid='69c8h6j71f913'
select substr(e,1,10) from t where a=1
END OF STMT
PARSE #8:c=2000,e=1548,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2144302522,tim=1353072283364170
EXEC #8:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2144302522,tim=1353072283364336
WAIT #8: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1353072283364411
WAIT #8: nam='db file sequential read' ela= 33 file#=4 block#=57171 blocks=1 obj#=119040 tim=1353072283364582
WAIT #8: nam='db file sequential read' ela= 24 file#=8 block#=142 blocks=1 obj#=119039 tim=1353072283364721
WAIT #8: nam='db file sequential read' ela= 16 file#=8 block#=143 blocks=1 obj#=119039 tim=1353072283364814
WAIT #8: nam='db file scattered read' ela= 75 file#=8 block#=136 blocks=6 obj#=119039 tim=1353072283365020
FETCH #8:c=1000,e=646,p=9,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=2144302522,tim=1353072283365093
STAT #8 id=1 cnt=1 pid=0 pos=1 bj=119039 p='TABLE ACCESS BY INDEX ROWID T (cr=4 pr=9 pw=0 time=0 us cost=0 size=1515 card=1)'
STAT #8 id=2 cnt=1 pid=1 pos=1 bj=119040 p='INDEX UNIQUE SCAN P_T (cr=1 pr=1 pw=0 time=0 us cost=0 size=0 card=1)'
WAIT #8: nam='SQL*Net message from client' ela= 224 driver id=1650815232 #bytes=1 p3=0 obj#=119039 tim=1353072283374046
FETCH #8:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2144302522,tim=1353072283374090
WAIT #8: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=119039 tim=1353072283374123

--可以發現出現db file sequential read,db file scattered read等待事件。
--file#=4 block#=57171(這個是索引)-》file#=8 block#=142 blocks=1=> file#=8 block#=143 blocks=1=>file#=8 block#=136 blocks=6 .
--不知道為什麼最後是db file scattered read,要讀6個塊。

SQL> select * from dba_extents where wner=user and segment_name='T';

OWNER  SEGMENT_NAME PARTITION_NAME       SEGMENT_TYPE       TABLESPACE_NAME       EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------ ------------ -------------------- ------------------ -------------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT  T                                 TABLE              TEST                          0          8        136      65536          8            8



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

相關文章