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

最新推荐文章于 2024-07-26 14:20:58 发布

csdn7755

最新推荐文章于 2024-07-26 14:20:58 发布

阅读量161

点赞数

文章标签：数据库

一、实验环境准备

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/

csdn7755

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle优化--表优化（行链接、迁移的影响与优化）

一、实验环境准备SQL> create table t_pctfree_tmp 2 ( 3 id number, 4 name var...
复制链接

扫一扫