[20180327]行遷移與ITL浪費.txt

lfree發表於2018-03-27

[20180327]行遷移與ITL浪費.txt

--//生產系統遇到的一個問題,增加一個欄位到表結構,修改資料字典,導致出現行遷移,而更加嚴重的是沒有修改pctfree值,
--//以後的業務操作,依舊會導致大量的行遷移,不僅僅是操作時IO增加,而且還導致的問題ITL槽浪費,特別在密集的dml操作的
--//情況下:

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 as select rownum id from dual connect by level<=2000;
Table created.

--//分析表略.

SCOTT@book>  alter table t add (vc  varchar2(10) default lpad('a',10,'a'));
Table altered.

--//建立chained_rows表.
SCOTT@book> @ /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlchain.sql
Table created.
--//我個人喜歡修改指令碼建立臨時表:
CREATE GLOBAL TEMPORARY TABLE CHAINED_ROWS
(
   owner_name          VARCHAR2 (30)
  ,table_name          VARCHAR2 (30)
  ,cluster_name        VARCHAR2 (30)
  ,partition_name      VARCHAR2 (30)
  ,subpartition_name   VARCHAR2 (30)
  ,head_rowid          ROWID
  ,analyze_timestamp   DATE
) ON COMMIT PRESERVE ROWS;

SCOTT@book> Analyze Table t Compute Statistics;
Table analyzed.

SCOTT@book> select NUM_ROWS,BLOCKS,CHAIN_CNT from dba_tables where owner=user and table_name='T';
  NUM_ROWS     BLOCKS  CHAIN_CNT
---------- ---------- ----------
      2000         23       1690

--//1690條記錄出現行遷移.

SCOTT@book> analyze table t list chained rows into chained_rows;
Table analyzed.

SCOTT@book> select TABLE_NAME,HEAD_ROWID from chained_rows where rownum<=10;
TABLE_NAME HEAD_ROWID
---------- ------------------
T          AAAWHJAAEAAAAIjABl
T          AAAWHJAAEAAAAIjABm
T          AAAWHJAAEAAAAIjABn
T          AAAWHJAAEAAAAIjABo
T          AAAWHJAAEAAAAIjABp
T          AAAWHJAAEAAAAIjABq
T          AAAWHJAAEAAAAIjABr
T          AAAWHJAAEAAAAIjABs
T          AAAWHJAAEAAAAIjABt
T          AAAWHJAAEAAAAIjABu
10 rows selected.

SCOTT@book> @ &r/rowid AAAWHJAAEAAAAIjABl
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     90569          4        547        101  0x1000223           4,547                alter system dump datafile 4 block 547 ;

SCOTT@book> @ &r/rowid AAAWHJAAEAAAAIjABm
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     90569          4        547        102  0x1000223           4,547                alter system dump datafile 4 block 547 ;

--//這些記錄出現行遷移.

2.bbed觀察:
SCOTT@book> select * from t where rowid in ('AAAWHJAAEAAAAIjABl','AAAWHJAAEAAAAIjABm');
        ID VC
---------- ----------
       102 aaaaaaaaaa
       103 aaaaaaaaaa

BBED> x  /rnc *kdbr[101]
rowdata[5002]                               @6461
-------------
flag@6461: 0x20 (KDRHFH)
lock@6462: 0x02
cols@6463:    0
nrid@6464:0x01000227.0

BBED> x  /rnc *kdbr[102]
rowdata[4993]                               @6452
-------------
flag@6452: 0x20 (KDRHFH)
lock@6453: 0x02
cols@6454:    0
nrid@6455:0x01000227.1

--//資料依舊保持在原來位置,但是資料資訊遷移到dba=0x01000227.

BBED> set dba 0x01000227
        DBA             0x01000227 (16777767 4,551)

BBED> x  /rnc *kdbr[0]
rowdata[3455]                               @8164
-------------
flag@8164: 0x0c (KDRHFL, KDRHFF)
lock@8165: 0x01
cols@8166:    2
hrid@8167:0x01000223.65

col    0[3] @8173: 102
col   1[10] @8177: aaaaaaaaaa


BBED> x  /rnc *kdbr[1]
rowdata[3431]                               @8140
-------------
flag@8140: 0x0c (KDRHFL, KDRHFF)
lock@8141: 0x01
cols@8142:    2
hrid@8143:0x01000223.66

col    0[3] @8149: 103
col   1[10] @8153: aaaaaaaaaa

--//在dba=4,551中記錄資料資訊.也就是發生了行遷移情況.

3.看看dba=4,551的情況:

BBED> map /v dba 4,551
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 551                                   Dba:0x01000227
------------------------------------------------------------
KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes                      @0
    ub1 type_kcbh                           @0
    ub1 frmt_kcbh                           @1
    ub1 spare1_kcbh                         @2
    ub1 spare2_kcbh                         @3
    ub4 rdba_kcbh                           @4
    ub4 bas_kcbh                            @8
    ub2 wrp_kcbh                            @12
    ub1 seq_kcbh                            @14
    ub1 flg_kcbh                            @15
    ub2 chkval_kcbh                         @16
    ub2 spare3_kcbh                         @18

struct ktbbh, 3552 bytes                   @20
    ub1 ktbbhtyp                            @20
    union ktbbhsid, 4 bytes                 @24
    struct ktbbhcsc, 8 bytes                @28
    sb2 ktbbhict                            @36
    ub1 ktbbhflg                            @38
    ub1 ktbbhfsl                            @39
    ub4 ktbbhfnx                            @40
    struct ktbbhitl[147], 3528 bytes        @44
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
struct kdbh, 14 bytes                      @3580
    ub1 kdbhflag                            @3580
    sb1 kdbhntab                            @3581
    sb2 kdbhnrow                            @3582
    sb2 kdbhfrre                            @3584
    sb2 kdbhfsbo                            @3586
    sb2 kdbhfseo                            @3588
    sb2 kdbhavsp                            @3590
    sb2 kdbhtosp                            @3592

struct kdbt[1], 4 bytes                    @3594
    sb2 kdbtoffs                            @3594
    sb2 kdbtnrow                            @3596

sb2 kdbr[145]                              @3598
ub1 freespace[821]                         @3888
ub1 rowdata[3479]                          @4709
ub4 tailchk                                @8188

--//可以發現ktbbhitl=147,也就是佔用147槽.而僅僅145條記錄.

SCOTT@book> alter system dump datafile 4 block 551;
System altered.

Block header dump:  0x01000227
Object id on Block? Y
seg/obj: 0x161c9  csc: 0x03.1766e4bf  itc: 147  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000220 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.00b.00005161  0x00c00586.0ff3.3c  --U-  145  fsc 0x0000.1766e4d4
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x04   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x05   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x06   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x07   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x08   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x09   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x0a   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x0b   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x0c   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x0d   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x0e   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x0f   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x10   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
....
0x86   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x87   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x88   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x89   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x8a   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x8b   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x8c   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x8d   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x8e   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x8f   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x90   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x91   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x92   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
0x93   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
bdba: 0x01000227

--//可以發現dba=4,551存在大量的ITL槽.你可以發現我執行的是alter table t add (vc  varchar2(10) default lpad('a',10,'a'));
--//理論講就是1個事務,而oracle沒發生1次行遷移就產生1個ITL槽.

--//突然想起來我以前的測試:
[20160726]行連結行遷移與ITL槽.txt
[20160727]行連結行遷移與ITL槽2.txt
[20160728]行連結行遷移與ITL槽3.txt
[20160728]行連結行遷移與ITL槽4.txt
[20160729]行連結行遷移與ITL槽4.txt

http://blog.itpub.net/267265/viewspace-2122700/
http://blog.itpub.net/267265/viewspace-2122663/
http://blog.itpub.net/267265/viewspace-2122599/
http://blog.itpub.net/267265/viewspace-2122712/

--//測試再次說明,大量的行遷移行連結會導致ITL槽數量的異常增加.
--//哎,才想起來以前也遇到過類似問題.
--//看來無論是開發還是dba應該一定程度要重視行連結與行遷移問題.看看我們的團隊實在太無語...
--//再重複看了我以前的測試:
http://blog.itpub.net/267265/viewspace-2122712/
--//還是有點不明白,我的dml是順序執行的,oracle為什麼不重用ITL槽,而是不斷增加ITL槽使用呢....
--//那位解析看看,為了回滾操作嗎?

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

相關文章