优化器也搞“一国两制”--索引与分区全扫描性能PK

客户现场看到这样一个比较奇怪的现象：某个SQL的两段内联视图的代码基本上一致，但是却生成了两段不同的执行计划，一个使用了部分分区扫描做hash join，另一个却使用了索引做nested loop。

SQL代码如下，红色部分代码基本相同：

INSERT INTO FACT_KPI_TOTAL_DAY (

......

)

SELECT /*+ use_hash(a,b)*/

......

FROM (

SELECT *

FROM FACT_KPI_TOTAL_DUCT T

WHERE STAT_DATE = :B5 AND ID IN

(

SELECT ID

FROM CFG_KPI_DUCT T

WHERE T.GROUP_ID = :B4

) AND ID NOT IN (SELECT ID FROM CFG_KPI_RELATION )

) A

,(

SELECT T.ID ,T.REGION_ID ,T.BRAND_ID

,SUM(DECODE(T.STAT_DATE, :B3, T.CUSTOMER, '0')) CUSTOMER_1

,SUM(DECODE(T.STAT_DATE, :B2, T.CUSTOMER, '0')) CUSTOMER_2

,SUM(DECODE(T.STAT_DATE, :B1, T.CUSTOMER, '0')) CUSTOMER_3

FROM FACT_KPI_TOTAL_DUCT T

WHERE STAT_DATE IN (:B3, :B2, :B1) AND ID IN

(

SELECT ID

FROM CFG_KPI_DUCT T

WHERE T.GROUP_ID = :B4

) AND ID NOT IN ( SELECT ID FROM CFG_KPI_RELATION )

GROUP BY T.ID ,T.REGION_ID ,T.BRAND_ID

) B

WHERE A.ID = B.ID(+) AND A.REGION_ID = B.REGION_ID(+) AND A.BRAND_ID = B.BRAND_ID(+)；

执行计划：执行时间316秒，主要消耗在Index Range Scan的步骤

3个表的记录数：

其中最大的表FACT_KPI_TOTAL_DUCT按照ID做list 分区，一共200个分区，有21亿多的记录数。表上只要一个索引IDX_FACT_KPI_TOTAL_DUCT_ALL，由下列5个字段组成（注意，其中第二个字段KPI_ID没有出现在SQL中）：

"ID", "KPI_ID", "STAT_DATE", "BRAND_ID", "REGION_ID"

从执行计划可以看出，FACT_KPI_TOTAL_DUCT表和CFG_KPI_DUCT join之后的估值都是1074K，三表join之后的估值都是11K，但是却生成了两段截然不同的执行计划：上面一段partition list subquery后做hash join，下面一段Index Range Scan 做Nested loops。优化器生成这样的执行计划应该是不正常的表现。

通过两段执行计划的对比可以发现，使用Index Range scan做Nested loops的这段执行计划是低效的。根据这个信息，我们就可以通过hint（use_hash(T) 或Full(T) 都能实现同样的效果），来让这部分执行计划与上半部分的执行计划相同：

INSERT INTO FACT_KPI_TOTAL_DAY (

......

)

SELECT /*+ use_hash(a,b)*/

......

FROM (

SELECT *

FROM FACT_KPI_TOTAL_DUCT T

WHERE STAT_DATE = :B5AND ID IN

(

SELECT ID

FROM CFG_KPI_DUCT T

WHERE T.GROUP_ID = :B4

) AND ID NOT IN (SELECT ID FROM CFG_KPI_RELATION )

) A

,(

SELECT /*+ use_hash(T) */

T.ID ,T.REGION_ID ,T.BRAND_ID

,SUM(DECODE(T.STAT_DATE, :B3, T.CUSTOMER, '0')) CUSTOMER_1

,SUM(DECODE(T.STAT_DATE, :B2, T.CUSTOMER, '0')) CUSTOMER_2

,SUM(DECODE(T.STAT_DATE, :B1, T.CUSTOMER, '0')) CUSTOMER_3

FROM FACT_KPI_TOTAL_DUCT T

WHERE STAT_DATE IN (:B3, :B2, :B1) AND ID IN

(

SELECT ID

FROM CFG_KPI_DUCT T

WHERE T.GROUP_ID = :B4

) AND ID NOT IN ( SELECT ID FROM CFG_KPI_RELATION )

GROUP BY T.ID ,T.REGION_ID ,T.BRAND_ID

) B

WHERE A.ID = B.ID(+) AND A.REGION_ID = B.REGION_ID(+) AND A.BRAND_ID = B.BRAND_ID(+)；

使用hint后的执行计划：执行时间只需要 7 秒 （为了方便，只测试了select部分，insert 部分时间可以忽略不计）

两个执行计划最大的区别就是IO requests从原来的52K，下降到优化后的677+625，这也是执行时间由原来的316秒下降到7秒的原因。

有人可能会比较困惑，为什么一个20多亿记录的表，返回了几千条的记录，使用索引反而会更慢？

这是因为，ID字段是表的分区字段，经过in 和 not in的分区过滤操作后，可能返回的只有一个值（根据实际IO requests数推断），就是只要访问一个分区。因为索引的第二个字段是KPI_ID，谓词条件和关联条件没有这个字段，所以索引相当于只用到了ID字段，而这个字段的选择性是非常差的。需要扫描的索引块较多，而且索引扫描每次IO size只有8k，而分区扫描每次IO size接近1M，两种扫描方式的性能差异就体现出来了。

如果索引是"ID", "STAT_DATE", "BRAND_ID", "REGION_ID" 几个字段的组合，那样索引和分区扫描的差别应该就不会那么明显了。

总结：

1、sql monitor是发现执行计划瓶颈的最佳工具

2、Hint可以对优化器的不合理行为做出纠正

3、索引扫描的IO size是8k（一个block size），而table（partition） scan/index fast full scan的IO size 一般 <= db_file_multiblock_read_count * 8k