SQL調整優化與10053跟蹤分析一例(zt)

tolywang發表於2007-03-16

SQL優化

關於Oracle效能優化.

SQL語句調整優化與10053跟蹤分析一例

平臺：windows 2000

DB：Oracle 9.0.1.1.1

概述

本文介紹瞭如何使用first_rows提示調整sql語句，並通過10053跟蹤檔案的分析對不同的優化模式下CBO如何選擇執行計劃作了簡要的介紹。

過程

首先我們看看需要調整的檢視，其定義為：

create or replace view vw_hyb_tbgrjbxx

select t1.*,t2.yzbz,t2.grbh_new

from tb_grjbxx t1,hyb_yzbz t2

where t1.grbh = t2.grbh

通過grbh進行表連線。兩張表在列grbh上均建有唯一索引，在執行查詢前均已對錶作了分析。

Sql>analyze table tb_grjbxx compute stastistics for table for all indexes;

Sql>analyze table hyb_yzbz compute stastistics for table for all indexes;

前臺查詢語句：

sql>select *From VW_HYB_TBGRJBXX where dwbh = '341004' and yzbz = '0’;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=449 Card=466 Bytes=1

15102)

1 0 HASH JOIN (Cost=449 Card=466 Bytes=115102)

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_GRJBXX' (Cost=19 Ca

rd=466 Bytes=106714)

3 2 INDEX (RANGE SCAN) OF 'IDX_TB_GRJBXX_DWBH' (NON-UNIQUE

) (Cost=7 Card=466)

4 1 TABLE ACCESS (FULL) OF 'HYB_YZBZ' (Cost=397 Card=129156

Bytes=2324808)

Statistics

----------------------------------------------------------

124 recursive calls

5 db block gets

2717 consistent gets

2695 physical reads

0 redo size

3568 bytes sent via SQL*Net to client

372 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

0 rows processed

根據執行計劃,我們看到,表連線使用了HA join,在表HYB_YZBZ上執行了全表掃描。從統計資訊可以看到有2695物理讀取。

下面我們分別加hint /*+first_rows*/和/*+all_rows*/來看看：

1. 加first_rows

sql>select /*+first_rows*/ *From VW_HYB_TBGRJBXX where dwbh = '34100’ and yzbz = ‘0’;

已用時間: 00: 00: 00.02

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=485 Card=4

66 Bytes=115102)

1 0 NESTED LOOPS (Cost=485 Card=466 Bytes=115102)

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_GRJBXX' (Cost=19 Ca

rd=466 Bytes=106714)

3 2 INDEX (RANGE SCAN) OF 'IDX_TB_GRJBXX_DWBH' (NON-UNIQUE

) (Cost=7 Card=466)

4 1 TABLE ACCESS (BY INDEX ROWID) OF 'HYB_YZBZ' (Cost=1 Card

=129281 Bytes=2327058)

5 4 INDEX (UNIQUE SCAN) OF 'SYS_C004851' (UNIQUE) (Cost=1

Card=129281)

Statistics

----------------------------------------------------------

7 recursive calls

0 db block gets

2449 consistent gets

0 physical reads

0 redo size

3568 bytes sent via SQL*Net to client

372 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

執行計劃選擇了NL Join，而不是HA Join，在表HYB_YZBZ使用了索引。從統計資訊上來看，物理讀取和緩衝區讀取的數目均為0，調整收到很好的效果。

2. 加all_rows

sql>select /*+all_rows*/ *From VW_HYB_TBGRJBXX where dwbh = '34100’ and yzbz = ‘0’;

已用時間: 00: 00: 04.06

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=455 Card=466

Bytes=115102)

1 0 HASH JOIN (Cost=455 Card=466 Bytes=115102)

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_GRJBXX' (Cost=19 Ca

rd=466 Bytes=106714)

3 2 INDEX (RANGE SCAN) OF 'IDX_TB_GRJBXX_DWBH' (NON-UNIQUE

) (Cost=7 Card=466)

4 1 TABLE ACCESS (FULL) OF 'HYB_YZBZ' (Cost=403 Card=129281

Bytes=2327058)

Statistics

----------------------------------------------------------

0 recursive calls

5 db block gets

2719 consistent gets

2574 physical reads

0 redo size

3568 bytes sent via SQL*Net to client

372 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

可以看到，執行計劃與優化器模式為Choose時一致，從表象上可以認為，在執行該查詢時CBO錯誤的選擇了ALL_ROWS而不是FIRST_ROWS（其實這是一個繆論，不管是使用CHOOSE還是ALL_ROWS都是使用COST最小的計劃）。通過改變檢視定義：

create or replace view vw_hyb_tbgrjbxx

select /*+first_rows*/ t1.*,t2.yzbz,t2.grbh_new

from tb_grjbxx t1,hyb_yzbz t2

where t1.grbh = t2.grbh

把全表掃描變為通過索引訪問，完成該sql語句的調整。

下面我們通過10053跟蹤的分析可以知道CBO到底是如何選擇執行計劃的。

sidb > ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

會話已更改。

-- 依次執行以下語句:

Sql>select *From VW_HYB_TBGRJBXX where dwbh = '34100’ and yzbz = ‘0’;

Sql>select /*+frist_rows*/ *From VW_HYB_TBGRJBXX where dwbh = '34100’ and yzbz = ‘0’;

Sql>select /*+all_rows*/ *From VW_HYB_TBGRJBXX where dwbh = '34100’ and yzbz = ‘0’;

下面我們來看看生成的跟蹤檔案。

跟蹤檔案中，PARAMETERS USED BY THE OPTIMIZER、BASE STATISTICAL INFORMATION、都是一致的，如下所示：

***************************************

BASE STATISTICAL INFORMATION

***********************

Table stats Table: HYB_YZBZ Alias: T2

TOTAL :: CDN: 258561 NBLKS: 2654 TABLE_SCAN_CST: 403 AVG_ROW_LEN: 33

Column: GRBH Col#: 1 Table: HYB_YZBZ Alias: T2

NDV: 258312 NULLS: 0 DENS: 3.8713e-006

-- Index stats

INDEX NAME: IDX_HYB_YZBZ_DWBH COL#: 4

TOTAL :: LVLS: 2 #LB: 2512 #DK: 555 LB/K: 4 DB/K: 5 CLUF: 2777

INDEX NAME: IDX_HYB_YZBZ_GRBH_NEW COL#: 3

TOTAL :: LVLS: 2 #LB: 788 #DK: 86097 LB/K: 1 DB/K: 1 CLUF: 38877

INDEX NAME: SYS_C004851 COL#: 1

TOTAL :: LVLS: 2 #LB: 1639 #DK: 258561 LB/K: 1 DB/K: 1 CLUF: 5007

***********************

Table stats Table: TB_GRJBXX Alias: T1

TOTAL :: CDN: 258313 NBLKS: 17631 TABLE_SCAN_CST: 2677 AVG_ROW_LEN: 229

Column: GRBH Col#: 1 Table: TB_GRJBXX Alias: T1

NDV: 258313 NULLS: 0 DENS: 3.8713e-006

Column: GRBH Col#: 1 Table: TB_GRJBXX Alias: T1

NDV: 258313 NULLS: 0 DENS: 3.8713e-006

-- Index stats

INDEX NAME: IDX_TB_GRJBXX_DWBH COL#: 2

TOTAL :: LVLS: 2 #LB: 2599 #DK: 546 LB/K: 4 DB/K: 31 CLUF: 17063

INDEX NAME: IDX_TB_GRJBXX_GMSFHM COL#: 3

TOTAL :: LVLS: 2 #LB: 2997 #DK: 221668 LB/K: 1 DB/K: 1 CLUF: 215999

INDEX NAME: IDX_TB_GRJBXX_XM COL#: 6

TOTAL :: LVLS: 2 #LB: 2982 #DK: 176560 LB/K: 1 DB/K: 1 CLUF: 241229

INDEX NAME: SYS_C006085 COL#: 1

TOTAL :: LVLS: 2 #LB: 1548 #DK: 242702 LB/K: 1 DB/K: 1 CLUF: 18109

_OPTIMIZER_PERCENT_PARALLEL = 0

***************************************

SINGLE TABLE ACCESS PATH

Column: DWBH Col#: 2 Table: TB_GRJBXX Alias: T1

NDV: 554 NULLS: 0 DENS: 1.8051e-003

TABLE: TB_GRJBXX ORIG CDN: 258313 ROUNDED CDN: 466 CMPTD CDN: 466

Access path: tsc Resc: 2677 Resp: 2677

Access path: index (equal)

Index: IDX_TB_GRJBXX_DWBH

TABLE: TB_GRJBXX

RSC_CPU: 0 RSC_IO: 38

IX_SEL: 0.0000e+000 TB_SEL: 1.8051e-003

Access path: index (equal)

Index: IDX_TB_GRJBXX_DWBH

TABLE: TB_GRJBXX

RSC_CPU: 0 RSC_IO: 7

IX_SEL: 1.8051e-003 TB_SEL: 1.8051e-003

BEST_CST: 19.00 PATH: 4 Degree: 1

***************************************

SINGLE TABLE ACCESS PATH

Column: YZBZ Col#: 2 Table: HYB_YZBZ Alias: T2

NDV: 2 NULLS: 0 DENS: 5.0000e-001

TABLE: HYB_YZBZ ORIG CDN: 258561 ROUNDED CDN: 129281 CMPTD CDN: 129281

Access path: tsc Resc: 403 Resp: 403