行連結
消除方法建立大的block塊
------------------ 參考tom kyte的例子--------------------------------------------
--建立4k blocksize的表空間
SQL> alter system set db_4k_cache_size=1m scope=both;
System altered.
SQL> create tablespace tbs1 datafile '/u01/app/oracle/oradata/test/tbs_1.dbf' size 100m blocksize 4k;
Tablespace created.
--行連結測試
--使用定列寬的char型別來建立行連結測試表
SQL> create table row_chain_demo(
x int primary key,
a char(1000),
b char(1000),
c char(1000),
d char(1000)
) tablespace tbs1;
Table created.
--插入資料
SQL> insert into row_chain_demo(x,a,b,c,d) values(1,'a','b','c','d');
1 row created.
SQL> commit;
Commit complete
--分析測試表，檢查行連結
--首先建chaind_rows相關表
SQL> @?/rdbms/admin/utlchain.sql
Table created.
--分析表
SQL> analyze table row_chain_demo list chained rows into chained_rows;
Table analyzed.
--查詢行連結
SQL> select * from chained_rows where table_name='ROW_CHAIN_DEMO';
OWNER_NAME TABLE_NAME CLUSTER_NAME PARTITION_NAME SUBPARTITION_NAME HEAD_ROWID ANALYZE_T
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------ ---------
SYS ROW_CHAIN_DEMO N/A AAAFytAAGAAAAEIAAA 20-AUG-18
記錄下此時的執行計劃：
SQL> set autotrace traceonly statistics
SQL> set linesize 100
SQL> select /*+index(ROW_CHAIN_DEMO,x)*/* from ROW_CHAIN_DEMO where x=1;
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
4815 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--找出row_chain_demo這張表所對應的資料塊
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from row_chain_demo;
FILE# BLOCK#
---------- ----------
6 264
--dump該資料塊進行分析
SQL> alter system dump datafile 6 block 264;
System altered.
--以下為trace檔案的內容
data_block_dump,data header at 0x7f28ca793264
===============
tsiz: 0xf98
hsiz: 0x14
pbl: 0x7f28ca793264
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0xba1
avsp=0xb8d
tosp=0xb8d
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0xba1
block_row_dump:
tab 0, row 0, @0xba1
tl: 1015 fb: --H-F lb: 0x1 cc: 2
--正常的行記錄為--H-FL--,而這裡為只有F(fisrt)而沒有L(last)，說明在這個資料塊中只有行的開始，而沒有行的結束，同樣cc為2說明這個塊中只包含了表的兩個列 */
nrid: 0x01c00105.0
-- nrid表示資料塊的下一個指標，即其他列資料存放的資料塊地址
--通過bbed分析
BBED> p kdbr
sb2 kdbr[0] @118 2977
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @3077 0x28
BBED> x /rnccccccc
rowdata[0] @3077
----------
flag@3077: 0x28 (KDRHFF, KDRHFH)
lock@3078: 0x01
cols@3079: 2 --從這也可以看出資料塊中只有表的兩個列
nrid@3080:0x01c00105.0
col 0[2] @3086: 1
col 1[1000] @3089: a
--消除行連結
--建立blocksize為32的表空間，並將測試表移動到該表空間
SQL> alter system set db_32k_cache_size=1m scope=both;
System altered.
SQL> create tablespace tbs2 datafile '/u01/app/oracle/oradata/test/tbs2.dbf' size 100m blocksize 32k;
Tablespace created.
SQL> alter table row_chain_demo move tablespace tbs2;
Table altered.
SQL> select index_name from dba_indexes where table_name='ROW_CHAIN_DEMO';
INDEX_NAME
------------------------------
SYS_C0010231
SQL> alter index SYS_C0010231 rebuild;
Index altered.
--檢查行連結
SQL> delete from chained_rows;
1 row deleted.
SQL> commit;
Commit complete.
SQL> analyze table ROW_CHAIN_DEMO list chained rows into chained_rows;
Table analyzed.
SQL> select * from chained_rows where table_name='CHAIN_ROW_DEMO';
no rows selected
--檢視消除行連結後的執行計劃狀態
SQL> select /*+index(ROW_CHAIN_DEMO,x)*/* from ROW_CHAIN_DEMO where x=1;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
4668 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)http://write.blog.csdn.net/postedit/52963389
1 rows processed
--可以看出只發生了兩次一致性讀
行遷移
消除方法，重建資料表
--建立測試表，保證表的欄位能夠大於blocksize
SQL> create table row_mig_demo(
x int primary key,
a char(1000),
b char(1000),
c char(1000),
d char(1000)
) tablespace tbs1;
Table created.
--插入資料，只插入一個欄位的值
SQL> insert into row_mig_demo(x) values(1);
1 row created.
SQL> commit;
Commit complete.
--分析表，檢視是否有行遷移情況(此時必然沒有)
SQL> analyze table row_mig_demo list chained rows into chained_rows;
Table analyzed.
SQL> select * from chained_rows where table_name='ROW_MIG_DEMO';
no rows selected
--檢視測試表所在的資料塊
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from row_mig_demo;
FILE# BLOCK#
---------- ----------
6 279
--dump資料塊檢視
SQL> alter system dump datafile 6 block 279;
System altered.
--trace檔案內容
data_block_dump,data header at 0x7fa1ea9e1864
===============
tsiz: 0xf98
hsiz: 0x14
pbl: 0x7fa1ea9e1864
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0xf8c
avsp=0xf7b
tosp=0xf7b
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0xf8c
block_row_dump:
tab 0, row 0, @0xf8c
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1 --FL:說明此時資料行的頭和尾都在block內，cc:1,即只有一個欄位的資料
col 0: [ 2] c1 02
end_of_block_dump
--通過bbed檢視block資料層內容
BBED> p kdbr
sb2 kdbr[0] @118 3980
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @4080 0x2c
BBED> x /rnccccccc
rowdata[0] @4080
----------
flag@4080: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@4081: 0x01
cols@4082: 1
col 0[2] @4083: 1
--此時的執行計劃狀態
SQL> select * from ROW_MIG_DEMO where x=1;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
639 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--update測試表，是表的行資料大於blocksize
SQL> update row_mig_demo set a='a',b='b',c='c',d='d' where x=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> delete from chained_rows;
0 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze table row_mig_demo list chained rows into chained_rows;
Table analyzed.
SQL> select * from chained_rows where table_name='ROW_MIG_DEMO';
OWNER_NAME TABLE_NAME
------------------------------ ------------------------------
CLUSTER_NAME PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME HEAD_ROWID ANALYZE_T
------------------------------ ------------------ ---------
SYS ROW_MIG_DEMO
N/A AAAVqrAAHAAAAEVAAA 29-OCT-16
--可以看到已經產生了行遷移
--dump資料塊檢視
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from row_mig_demo;
FILE# BLOCK#
---------- ----------
6 279 --表所在資料塊地址並未改變
SQL> alter system dump datafile 6 block 279;
System altered.
--trace檔案內容
data_block_dump,data header at 0x7f28ca793264
===============
tsiz: 0xf98
hsiz: 0x14
pbl: 0x7f28ca793264
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0xba1
avsp=0xb8d
tosp=0xb8d
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0xba1
block_row_dump:
tab 0, row 0, @0xba1
tl: 1015 fb: --H-F--- lb: 0x1 cc: 2
nrid: 0x01c00105.0
col 0: [ 2] c1 02
col 1: [1000]
--bbed檢視
BBED> p kdbr
sb2 kdbr[0] @118 2965
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @3065 0x28
BBED> x /rnccccccc
rowdata[0] @3065
----------
flag@3065: 0x28 (KDRHFF, KDRHFH)
lock@3066: 0x02
cols@3067: 2
nrid@3068:0x01c00116.0
col 0[2] @3074: 1
col 1[1000] @3077: a
--消除行遷移
SQL> create table row_mig_demp1 as select * from row_mig_demo;
Table created