ITPub博客

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

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

原创 Linux操作系统 作者:husthxd 时间:2004-10-08 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<

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

上一篇: 开工了.
请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1395
  • 访问量
    3841994