oracle优化--表优化（行链接、迁移的影响与优化）

一、实验环境准备 SQL> create table t_pctfree_tmp   2  (   3  id number,   4  name varchar2(2000),   5  name1 varchar2(2000),   6  name2 varchar2(2000),   7  name3 varchar2(2000),   8  name4 varchar2(2000)    9  ) tablespace users pctfree 5; Table created. SQL> create index idx_tpt_id on t_pctfree_tmp(id); Index created. 1、新增无行链接、迁移数据 SQL> insert into t_pctfree_tmp(id,name4) values (1,dbms_random.string('u', 2000)); 1 row created. 2、新增行链接数据 SQL> insert into t_pctfree_tmp values (3,dbms_random.string('u', 2000),dbms_random.string('u', 2000),dbms_random.string('u', 2000),dbms_random.string('u', 2000),dbms_random.string('u', 2000)); 1 row created. SQL> commit; Commit complete. 3、新增用于行迁移数据（还为出现行迁移） SQL> begin   2  for i in 101 .. 10000 loop   3  insert into scott.t_pctfree_tmp(id) values (i);   4  end loop;   5  end;   6  / PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL> conn / as sysdba Connected. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. 二、通过dump查看各数据的块信息 SQL> select dbms_rowid.rowid_block_number(rowid) b,dbms_rowid.rowid_relative_fno(rowid) f,id from scott.t_pctfree_tmp; B     F       ID ---------- ---------- ----------        188     4        3        191     4        1        339     5      101 SQL> alter system dump datafile 4 block 191; System altered. SQL> alter system dump datafile 4 block 188; System altered. SQL> alter system dump datafile 5 block 339; System altered. --无行迁移链接（ID：1） tab 0, row 0, @0x17bb tl: 2013 fb: --H-FL-- lb: 0x1  cc: 6 col  0: [ 2]  c1 02 col  1: *NULL* col  2: *NULL* col  3: *NULL* col  4: *NULL* col  5: [2000]  55 46 57 4d 44 54 45 43 42 41 44 4a 43 56 53 58 43 44 4c 52 45 4e 5a 43 53  49 58 56 4e 4d 43 58 4b 49 45 5a 47 4d 44 42 50 53 4f 45 54 44 41 58 50 51  55 54 56 59 51 4e 4f 55 49 52 46 47 41 4c 49 49 4a 49 49 57 5a 53 57 41 57  58 51 5a 41 49 5a 53 50 4a 4c 53 4c 59 43 4e 52 4d 54 44 4e 58 42 59 4e 42 --行链接（ID：3） tab 0, row 0, @0xfe6 tl: 4018 fb: --H-F--- lb: 0x1  cc: 3 nrid:  0x010000bb.0 col  0: [ 2]  c1 04 col  1: [2000] col  2: [2000] tab 0, row 0, @0x804 tl: 6012 fb: -----L-- lb: 0x1  cc: 3 col  0: [2000] col  1: [2000] col  2: [2000] --行迁移前（ID：101） tab 0, row 0, @0x1f91 tl: 7 fb: --H-FL-- lb: 0x1  cc: 1 col  0: [ 3]  c2 02 02 三、修改id为101的数据，使其产生行迁移 SQL> update scott.t_pctfree_tmp set name1=dbms_random.string('u', 2000) where id=101; 1 row updated. SQL> commit; Commit complete. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> select dbms_rowid.rowid_block_number(rowid) b,dbms_rowid.rowid_relative_fno(rowid) f,id from scott.t_pctfree_tmp where id=101; B     F       ID ---------- ---------- ----------        339     5      101 SQL> alter system dump datafile 5 block 339; System altered. --行迁移后（ID：101） tab 0, row 0, @0xc86 tl: 9 fb: --H----- lb: 0x2  cc: 0 nrid:  0x01400158.a3 tab 0, row 163, @0x379 tl: 2017 fb: ----FL-- lb: 0x2  cc: 3 hrid: 0x01400153.0 col  0: [ 3]  c2 02 02 col  1: *NULL* col  2: [2000] 四、查看会话跟踪查看行链接与行迁移的影响 **************************************无行链接、迁移数据****************************************** SQL ID: 1bu7wp64fuxdb Plan Hash: 4060251387 select id,name4  from  scott.t_pctfree_tmp where id=1 call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          2          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        2      0.00       0.00          0          4          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        4      0.00       0.00          0          6          0           1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------          1          1          1  TABLE ACCESS BY INDEX ROWID T_PCTFREE_TMP ( cr=4 pr=0 pw=0 time=23 us cost=1 size=1015 card=1)          1          1          1   INDEX RANGE SCAN IDX_TPT_ID (cr=3 pr=0 pw=0 time=7 us cost=1 size=0 card=1)(object id 87463) *****************************************行链接数据（未查询到链接块上的数据）************************************** SQL ID: 43tkz7nmfv49r Plan Hash: 4060251387 select id,name1  from  scott.t_pctfree_tmp where id=3 call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          3          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        2      0.00       0.00          0          4          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        4      0.00       0.00          0          7          0           1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------          1          1          1  TABLE ACCESS BY INDEX ROWID T_PCTFREE_TMP ( cr=4 pr=0 pw=0 time=8 us cost=1 size=1015 card=1)          1          1          1   INDEX RANGE SCAN IDX_TPT_ID (cr=3 pr=0 pw=0 time=6 us cost=1 size=0 card=1)(object id 87463) ***********************************行链接数据（查询到链接块上的数据）******************************************** SQL ID: 14s8whvqqtfd1 Plan Hash: 4060251387 select id,name4  from  scott.t_pctfree_tmp where id=3 call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          2          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        2      0.00       0.00          0          5          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        4      0.00       0.00          0          7          0           1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------          1          1          1  TABLE ACCESS BY INDEX ROWID T_PCTFREE_TMP ( cr=5 pr=0 pw=0 time=16 us cost=1 size=1015 card=1)          1          1          1   INDEX RANGE SCAN IDX_TPT_ID (cr=3 pr=0 pw=0 time=7 us cost=1 size=0 card=1)(object id 87463) ************************************行迁移数据******************************************** SQL ID: d5tac70fgj6g5 Plan Hash: 4060251387 select id,name1  from  scott.t_pctfree_tmp where id=101 call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          2          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        2      0.00       0.00          0          5          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        4      0.00       0.00          0          7          0           1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------          1          1          1  TABLE ACCESS BY INDEX ROWID T_PCTFREE_TMP ( cr=5 pr=0 pw=0 time=10 us cost=1 size=1015 card=1)          1          1          1   INDEX RANGE SCAN IDX_TPT_ID (cr=3 pr=0 pw=0 time=6 us cost=1 size=0 card=1)(object id 87463) ******************************************************************************** 可以看到无行链接、迁移的数据和有行链接但未查询到链接块上的数据查询cr=4，存在行链接并查询到链接块上的数据和存在行迁移的数据查询cr=5，io次数多了一次； 五、消除行迁移 SQL> alter table scott.t_pctfree_tmp move; Table altered. SQL> alter index scott.idx_tpt_id rebuild online; Index altered. SQL> select dbms_rowid.rowid_block_number(rowid) b,dbms_rowid.rowid_relative_fno(rowid) f,id from scott.t_pctfree_tmp where id=101; B     F       ID ---------- ---------- ----------       2066     5      101 SQL> alter system dump datafile 5 block 2066; System altered. --dump信息 tab 0, row 0, @0x17a5 tl: 2011 fb: --H-FL-- lb: 0x0  cc: 3 col  0: [ 3]  c2 02 02 col  1: *NULL* col  2: [2000] --会话跟踪 SQL ID: d5tac70fgj6g5 Plan Hash: 4060251387 select id,name1  from  scott.t_pctfree_tmp where id=101 call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          2          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        2      0.00       0.00          0          4          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        4      0.00       0.00          0          6          0           1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------          1          1          1  TABLE ACCESS BY INDEX ROWID T_PCTFREE_TMP ( cr=4 pr=0 pw=0 time=15 us cost=2 size=1015 card=1)          1          1          1   INDEX RANGE SCAN IDX_TPT_ID (cr=3 pr=0 pw=0 time=26 us cost=1 size=0 card=1)(object id 87463) --dump信息里不存在nrid，且cr=4，说明行迁移不存在了