[20180327]行遷移與ITL浪費.txt--//生產系統遇到的一個問題,增加一個欄位到表結構,修改數據字典,導致出現行遷移,而更加嚴重的是沒有修改pctfree值,--//以後的業務操作,依舊會導致大量的行遷移,不僅僅是操作時IO增加,而且還導致的問題ITL槽浪費,特別在密集的dml操作的-- ...
[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槽使用呢....
--//那位解析看看,為了回滾操作嗎?
