ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL调整优化与10053跟踪分析一例(zt)

SQL调整优化与10053跟踪分析一例(zt)

原创 Linux操作系统 作者:tolywang 时间:2007-03-16 00:00:00 0 删除 编辑

关于Oracle性能优化.

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

平台:windows 2000

DBOracle 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 OPTIMIZERBASE 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

Access path: index (no sta/stp keys)

Index: IDX_HYB_YZBZ_DWBH

TABLE: HYB_YZBZ

RSC_CPU: 0 RSC_IO: 2514

IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000

Access path: index (no sta/stp keys)

Index: IDX_HYB_YZBZ_GRBH_NEW

TABLE: HYB_YZBZ

RSC_CPU: 0 RSC_IO: 790

IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000

Access path: index (no sta/stp keys)

Index: SYS_C004851

TABLE: HYB_YZBZ

RSC_CPU: 0 RSC_IO: 1641

IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000

Access path: index (no sta/stp keys)

Index: IDX_HYB_YZBZ_DWBH

TABLE: HYB_YZBZ

RSC_CPU: 0 RSC_IO: 2514

IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000

Access path: index (no sta/stp keys)

Index: IDX_HYB_YZBZ_GRBH_NEW

TABLE: HYB_YZBZ

RSC_CPU: 0 RSC_IO: 790

IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000

Access path: index (no sta/stp keys)

Index: SYS_C004851

TABLE: HYB_YZBZ

RSC_CPU: 0 RSC_IO: 1641

IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000

Access path: index (no sta/stp keys)

Index: IDX_HYB_YZBZ_GRBH_NEW

TABLE: HYB_YZBZ<

http://blog.itpub.net/post/11/2813

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

请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13133314