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，说明行迁移不存在了

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31479729/viewspace-2199501/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31479729/viewspace-2199501/