ITPub博客

首页 > 数据库 > Oracle > SQL调优一则。关键字:执行计划 统计信息 optimizer_mode

SQL调优一则。关键字:执行计划 统计信息 optimizer_mode

原创 Oracle 作者:aspen1982 时间:2011-07-14 11:00:25 0 删除 编辑

Jessie提供的生产问题。感谢Jessie,为我提供了不少实战机会。

关键字:执行计划 统计信息 optimizer_mode

[@more@]

问题描述

在一个系统切换新的DB Instance之后,下面SQL性能下降,执行时间变长。查询此时的optimizer_mode=all_rows。如果使用set optimizer_mode=rule则性能如切换前一样好。

Select Distinct Res.*

From Bizs_Orderinfo o,

Bizs_Workorder Wo,

Bizs_Workorderprvinfo Wop,

Bizs_Woprvinforesserinsassoc Assoc,

Bizs_Resserviceinstance Res

Where o.Id = Wo.Orderid

And Wo.Id = Wop.Workorderid

And Wop.Id = Assoc.Workorderprvinfoid

And Res.Id = Assoc.Resserviceinstanceid

And Res.Instanceid > 0

And Res.Type = 5

And o.Regionid = 77

And o.Sameorderno = '409120256607643';

环境

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

基础数据

各表数据量:

bizs_orderinfo 11827142;

bizs_workorder 4139461;

bizs_workdorderprvinfo 1893796;

bizs_woprvinforesserinsassoc 1893796;

Bizs_Resserviceinstance 3076724;

处理过程

查看执行计划

由于是新的DB Instance,数据量,统计信息可能都与原Instance有较大不同,从而可能影响到SQL的执行计划,而使SQL性能下降。使用set autotrace on获得SQL执行计划如下:

Execution Plan

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

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=115179 Card=78488 Bytes=274394048)

1 0 HASH (UNIQUE) (Cost=115179 Card=78488 Bytes=274394048)

2 1 HASH JOIN (Cost=57973 Card=78488 Bytes=274394048)

3 2 TABLE ACCESS (BY INDEX ROWID) OF 'BIZS_ORDERINFO' (TABLE) (Cost=16 Card=974 Bytes=1001272) o

4 3 INDEX (RANGE SCAN) OF 'IDX13ORDERINFO' (INDEX) (Cost=15 Card=10562)

5 2 NESTED LOOPS (Cost=57952 Card=582878 Bytes=1438542904)

6 5 NESTED LOOPS (Cost=25568 Card=58792 Bytes=143570064)

7 6 HASH JOIN (Cost=23428 Card=69045 Bytes=166812720)

8 7 INDEX (FAST FULL SCAN) OF 'XAK_WOPRVINFORESSERINSASSOC_1' (INDEX (UNIQUE)) (Cost=1250 Card=2204583 Bytes=57319158) assoc

9 7 TABLE ACCESS (FULL) OF 'BIZS_RESSERVICEINSTANCE'(TABLE) (Cost=6398 Card=103765 Bytes=247998350) res

10 6 TABLE ACCESS (BY INDEX ROWID) OF 'BIZS_WORKORDERPRVINFO' (TABLE) (Cost=1 Card=1 Bytes=26) wop

11 10 INDEX (UNIQUE SCAN) OF 'SYS_C0013198' (INDEX (UNIQUE)) (Cost=0 Card=1)

12 5 TABLE ACCESS (BY INDEX ROWID) OF 'BIZS_WORKORDER' (TABLE) (Cost=1 Card=10 Bytes=260) wo

13 12 INDEX (UNIQUE SCAN) OF 'SYS_C0013122' (INDEX (UNIQUE)) (Cost=0 Card=1)

Statistics

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

16 recursive calls

0 db block gets

263504 consistent gets

69632 physical reads

128 redo size

493 bytes sent via SQL*Net to client

237 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

5 sorts (memory)

0 sorts (disk)

0 rows processed

分析SQL和执行计划

从执行计划可得出表连接顺序:[o HJ (((assoc HJ res) NL wop) NL wo)]。首先做的是assoc HJ res。由于谓词中没有对assocres的任何过滤,所以是两个大表关联,产生了一个较大的COST基数(23428),但是关联的结果集比较小(Card=69045,当然这只是CBO的判断1)。然后再去关联其他表,大COST基数的影响被进一步放大。

SQL中可以看出:谓词对o表有较强的选择(两个等值限制条件),结合执行计划,对o表应用谓词可以很快得到一个更小的结果集(Cost=16 Card=974,当然这也只是CBO的判断2

分析结论

由于Assoc.ResserviceinstanceidFK指向Res.Id PK),所以在条件Res.Id = Assoc.Resserviceinstanceid下做assoc HJ res的结果集记录数等于assoc表的记录数(1893796),CBO的判断1严重错误。

实际应用条件o.Regionid = 77 And o.Sameorderno = '409120256607643'筛选o表,结果只有一条记录,CBO的判断2也与实际相去甚远。

猜想高效的执行计划中标的连接顺序应该是:[(((o JOIN wo) JOIN wop) JOIN assoc) JOIN res]。最先筛选o表再一次关联wowopassocres

Jessie提供的情况“如果使用set optimizer_mode=rule则性能如切换前一样好。”也应证了这一猜想。使用RBO时,表连接顺序同from关键字后的表书写顺序(从左到右)。在原SQL中加入HINT/*+ ORDERED*/或者/*+ RULE*/效果一样,根本上都是指定了表连接顺序。

解决方法

CBO的判断错误源于统计信息的错误,重新收集相关表的统计信息(推荐使用dbms_stats包),SQL性能恢复。执行计划如下:

Execution Plan

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

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=1 Bytes=113)

1 0 HASH (UNIQUE) (Cost=16 Card=1 Bytes=113)

2 1 NESTED LOOPS (Cost=15 Card=1 Bytes=113)

3 2 NESTED LOOPS (Cost=13 Card=1 Bytes=52)

4 3 NESTED LOOPS (Cost=11 Card=1 Bytes=40)

5 4 NESTED LOOPS (Cost=8 Card=1 Bytes=28)

6 5 TABLE ACCESS (BY INDEX ROWID) OF 'BIZS_ORDERINFO' (TABLE) (Cost=4 Card=1 Bytes=16)

7 6 INDEX (RANGE SCAN) OF 'IDX13ORDERINFO' (INDEX)(Cost=3 Card=1)

8 5 TABLE ACCESS (BY INDEX ROWID) OF 'BIZS_WORKORDER' (TABLE) (Cost=4 Card=2 Bytes=24)

9 8 INDEX (RANGE SCAN) OF 'XFK4WORKORDER' (INDEX)(Cost=2 Card=2)

10 4 TABLE ACCESS (BY INDEX ROWID) OF 'BIZS_WORKORDERPRVINFO' (TABLE) (Cost=3 Card=1 Bytes=12)

11 10 INDEX (RANGE SCAN) OF 'XFK1WORKORDERPRVINFO' (INDEX) (Cost=2 Card=1)

12 3 INDEX (RANGE SCAN) OF 'XAK_WOPRVINFORESSERINSASSOC_1' (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=12)

13 2 TABLE ACCESS (BY INDEX ROWID) OF 'BIZS_RESSERVICEINSTANCE' (TABLE) (Cost=2 Card=1 Bytes=61)

14 13 INDEX (UNIQUE SCAN) OF 'SYS_C0013196' (INDEX (UNIQUE)) (Cost=1 Card=1)

Statistics

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

1 recursive calls

0 db block gets

36 consistent gets

13 physical reads

0 redo size

493 bytes sent via SQL*Net to client

237 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

COST、物理读、一致性读均大幅下降。

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

请登录后发表评论 登录
全部评论

注册时间:2011-07-06

  • 博文量
    24
  • 访问量
    60634