关于Oracle性能优化.

SQL语句调整优化与10053跟踪分析一例

平台：windows 2000

DB：Oracle 9.0.1.1.1

概述

本文介绍了如何使用first_rows提示调整sql语句，并通过10053跟踪文件的分析对不同的优化模式下CBO如何选择执行计划作了简要的介绍。

过程

首先我们看看需要调整的视图，其定义为：

create or replace view vw_hyb_tbgrjbxx

as

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

as

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