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

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。由于谓词中没有对assoc和res的任何过滤，所以是两个大表关联，产生了一个较大的COST基数（23428），但是关联的结果集比较小（Card=69045，当然这只是CBO的判断1）。然后再去关联其他表，大COST基数的影响被进一步放大。

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

分析结论

由于Assoc.Resserviceinstanceid为FK指向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表再一次关联wo，wop，assoc，res。

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21129591/viewspace-1052455/