一条SQL语句的执行计划变化探究

最近有个同事碰到一个问题，想让我给点思路。我大体了解了一下，是一个系统目前在做压力测试，但是经业务反馈发现某个环节的处理时间有些长，排查了一圈，最后这件事情就落在了DB这边，希望DB能够给点意见，是否存在一些性能瓶颈。

我们从开发同学那里得到的一个基本的SQL语句，根据关键字从v$sql中做了提取，发现对应的SQL语句的执行时间还是OK的。

得到的SQL语句如下:

SQL_ID SQL_FULLTEXT

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

6h4w0u8stp3z0 select APP_ID,GOODS_ID,ORDER_ID,ORDER_STATUS,GOODS_REGISTER_ID,GOODS_NUMBER,GROUP_ID,GOODS_PRICE,US

ER_ID,ROLE_ID,ROLE_NAME,CHANNEL_ID,PUSH_NUM,PUSH_INFO from TB_ORDE

R WHERE ORDER_ID=:1 AND USER_ID=:2 AND SUBSTR(CHANNEL_ID,0,4)=:3

这样一个语句，如此来看性能上应该是没有多少改进的空间了。我查看了数据库层面的相关统计信息，发现DB time极低，比如Elapsed time为60分钟，DB time就不到1分钟，

类似下面这样的输出。

Snap Id Snap Time Sessions Curs/Sess

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

Begin Snap: 3295 24-Aug-16 13:00:17 38 .9

End Snap: 3296 24-Aug-16 14:00:20 44 .9

Elapsed: 60.05 (mins)

DB Time: 0.03 (mins)

如此之低的情况下，很难和性能问题联系起来。通过得到的数据情况分析，细化到ASH报告也没有发现任何异常，所以我们可以说DB层面没有性能瓶颈，这个问题还需要进一步的确认。

当然交代完这件事情，主要任务就完成了。就简单再看了看这个问题。

执行计划的情况如下，看到这样的执行计划似乎也没有任何可挑剔的。

谓词信息如下：

看到这里我开始有一些疑惑，作为一个订单表，订单号应该是作为主键的，看到索引的情况，发现确实是。

表结构如下所示，在分析之前还是需要说明这些基本情况的。

那么问题就来了，按道理是需要走唯一性索引代价最低，为什么执行计划缺走了另外一个索引，由期望中的唯一性索引扫描变为了范围索引扫描，这是疑点1.

解答这个问题的过程中发现，其实会引出更多的问题，原本的问题只是开始，因为后面要走的路还有很多。

对于这个问题，我们得求助于10053事件，这个诊断事件能够从根本上解释清楚这个原因来。

当然开启10053，我开启了1级，日志量相对要更多一些。

开启10053事件

ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';

运行SQL语句

结束10053事件

ALTER SESSION SET EVENTS '10053 trace name context off';

其中需要说明一点的是，如果采用如下的这种方式开启诊断事件，是不行的。

alter session set current_schema=ordermob;

select 。。。。

from OP_ORDER WHERE ORDER_ID='160824165342672424' AND USER_ID='15000501196112' AND SUBSTR(CHANNEL_ID,0,4)=5046 ;

可以使用如下的方式来代替。

select 。。。

from ordermob.OP_ORDER WHERE ORDER_ID='160824165342672424' AND USER_ID='15000501196112' AND SUBSTR(CHANNEL_ID,0,4)=5046 ;

10053事件中查询转换结果如下：

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT "OP_ORDER"."APP_ID" "APP_ID",。。。。 FROM "ORDERMOB"."TB_ORDER" "OP_ORDER" WHERE "OP_ORDER"."ORDER_ID"='160824165342672424' AND "OP_ORDER"."USER_ID"='15000501196112' AND TO_NUMBER(SUBSTR(TO_CHAR("OP_ORDER"."CHANNEL_ID"),0,4))=5046

kkoqbc: optimizing query block SEL$1 (#0)

统计信息的一些明细信息如下：比如CLUF是集群因子。

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

Table: OP_ORDER Alias: OP_ORDER

#Rows: 2143 #Blks: 50 AvgRowLen: 157.00 ChainCnt: 0.00

Index Stats::

Index: IDX_OP_ORDER_PUSH_STATE Col#: 25

LVLS: 1 #LB: 6 #DK: 1 LB/K: 6.00 DB/K: 50.00 CLUF: 50.00

Index: IND_ORDER_USERID Col#: 15

LVLS: 1 #LB: 10 #DK: 230 LB/K: 1.00 DB/K: 2.00 CLUF: 625.00

Index: SYS_C0011155 Col#: 4

LVLS: 1 #LB: 9 #DK: 2143 LB/K: 1.00 DB/K: 1.00 CLUF: 1271.00

而下面的这段内容就让人更加疑惑了。Density代表列的密度，可以看到Density的值ORDER_ID对应的为0.000467，而USER_ID对应的为0.000233，

表中目前存在2000多条记录，在Oracle中，表里没有直方图信息的时候，是按照1/NDV的形式来计算的。而这里OERDER_ID对应的值，其实就是1/2143得到的值就是0.000467

而user_id的值如果按照1/NDV的形式的话，应该是0,0043478这样的值，很显然这里不是这个值。

***************************************

1-ROW TABLES: OP_ORDER[OP_ORDER]#0

Access path analysis for OP_ORDER

***************************************

SINGLE TABLE ACCESS PATH

Single Table Cardinality Estimation for OP_ORDER[OP_ORDER]

Column (#4): ORDER_ID(

AvgLen: 19 NDV: 2143 Nulls: 0Density: 0.000467

Column (#15):

NewDensity:0.000233, OldDensity:0.000233 BktCnt:2143, PopBktCnt:2084, PopValCnt:171, NDV:230

Column (#15): USER_ID(

AvgLen: 15 NDV: 230 Nulls: 0 Density: 0.000233

Histogram: Freq #Bkts: 230 UncompBkts: 2143 EndPtVals: 230

这是为什么呢，我们来看看直方图的信息。可以看到ORDER_ID列是没有直方图信息的，而USER_ID列却含有。

SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,HISTOGRAM

FROM DBA_TAB_COL_STATISTICS WHERE OWNER='ORDERMOB' AND TABLE_NAME='OP_ORDER'

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM

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

APP_ID 1 1 FREQUENCY

CHANNEL_ID 6 6 FREQUENCY

ACCOUNT 230 1 NONE

ORDER_ID 2143 1 NONE

GOODS_ID 28 1 NONE

USER_ID 230 230 FREQUENCY

CREATE_DATE 2010 254 HEIGHT BALANCED

这里需要提一下直方图分为两种，一种是频率直方图，显示为：FREQUENCY，另外一种是高度平衡直方图，显示为：HEIGHT BALANCED

高度均衡直方图适用于 数据分布不均匀 ，由于列中数据很多，这时数据比较密集，不利于分析和评估，这时直方图需要均衡化。

频率直方图适用于数据分布很均匀的情况。当然如果数据很平均，其实也没有太大的意义，直方图本身就是适用于对应列中数据分布比较倾斜的列（不均匀）

那么问题似乎有了一些眉目，我们知道在Oracle中收集统计信息的时候是推荐使用FOR ALL COLUMNS SIZE AUTO的选项的。

收集统计信息的语句大概是这样的形式。

exec dbms_stats.gather_table_stats(tabname => 'OP_ORDER',ownname => 'ORDERMOB',method_opt => 'FOR ALL COLUMNS SIZE AUTO');

所以我大胆做了一个测试，那就是取消直方图的信息。

exec dbms_stats.gather_table_stats(tabname => 'OP_ORDER',ownname => 'ORDERMOB',method_opt => 'FOR ALL COLUMNS SIZE 1');

再次查看执行计划，发现就会采用唯一性索引扫描了，达到了我们预期的效果。

当然问题来了，这个是为什么呢,收集统计信息中的auto选项是什么含义呢。为什么两个数据类型一样的(varchar2(64)）的列，境遇却大大不同。且听我下回慢慢道来。