SQL调优之收集统计信息，重建索引

老外发来邮件，叫Mark做SQL调优。Mark是波兰资深的DBA，是波兰DBA leader。由于是晚上8点半发来的邮件。那么老外肯定认为我休息了。所以把我放在了CC list里面。

我有个习惯，就是晚上不开OC，但是邮件打开，如果是紧急的邮件，就会马上处理，不紧急就放到明天去做。谁叫我有5个项目

忙得不可开交呢，如果紧急问题不马上处理，那么我第二天肯定无法完成其余工作。

-----------------------邮件内容-------------------------------------

Hi Mark,

Please help in tuning of below sql – it is executed in ~3 minutes – and should be opened in 5-10 seconds

Explain plan looks pretty good (cost 90), maybe some index is needed. We cannot change below sql-code – it is generated by BO.

What we can change are all views which are used by this sql + we can add indexes to tables.

Issue is for 99% in view INTRC_RPT_T_INBR_VW or in INTRC_RPT_INBR_VW

Oracle: xxxx --我改了

User:xxxx --我改了

Pass:xxxx --我改了

SELECT

INTRC_INITV_DIM.INITV_NAME || ' ' || INTRC_INITV_DIM.INITV_ID || ' ' || INTRC_PROD_DIM.BRAND_SEG_NAME || ' - Power SKU: ' || INTRC_PROD_DIM.BRAND_SEG_PWR_SKU_NAME || ' - ' || INTRC_PROD_DIM.BRAND_SEG_PWR_SKU_ID ,

INTRC_PROD_DIM.BRAND_NAME,

INTRC_RPT_T_INBR_VW.HDR_ORDR,

INTRC_RPT_T_INBR_VW.MEASR_ORDR,

INTRC_RPT_T_INBR_VW.M00,

INTRC_RPT_T_INBR_VW.M01,

INTRC_RPT_T_INBR_VW.M02,

INTRC_RPT_T_INBR_VW.M03,

INTRC_RPT_T_INBR_VW.M04,

INTRC_RPT_T_INBR_VW.M05,

INTRC_RPT_T_INBR_VW.M06,

INTRC_RPT_T_INBR_VW.M07,

INTRC_RPT_T_INBR_VW.M08,

INTRC_RPT_T_INBR_VW.M09,

INTRC_RPT_T_INBR_VW.M10,

INTRC_RPT_T_INBR_VW.M11,

INTRC_RPT_T_INBR_VW.M12,

INTRC_RPT_T_INBR_VW.INITV_COPY,

INTRC_INITV_DIM.INITV_NAME || ' ' || INTRC_INITV_DIM.MKT_NAME || ' ' || INTRC_INITV_DIM.INITV_ID,

INTRC_RPT_DESC_PRC.HDR_NAME,

INTRC_RPT_DESC_PRC.MEASR_NAME,

INTRC_RPT_DESC_PRC.MEASR_DESC,

INTRC_RPT_DESC_PRC.MEASR_PRCSN,

INTRC_RPT_DESC_PRC.MEASR_TYPE

FROM

INTRC_INITV_DIM,

INTRC_PROD_DIM,

INTRC_RPT_T_INBR_VW,

INTRC_RPT_DESC_PRC,

INTRC_INITV_SEL_VW

WHERE

( INTRC_INITV_SEL_VW.INITV_SKID=INTRC_INITV_DIM.INITV_SKID )

AND ( INTRC_RPT_T_INBR_VW.INITV_SKID=INTRC_INITV_SEL_VW.INITV_SKID and INTRC_RPT_T_INBR_VW.SID=INTRC_INITV_SEL_VW.SID )

AND ( INTRC_RPT_T_INBR_VW.COL_NAME=INTRC_RPT_DESC_PRC.COL_NAME and INTRC_RPT_T_INBR_VW.TIME_PERD_LVL=INTRC_RPT_DESC_PRC.TIME_PERD_LVL )

AND ( INTRC_PROD_DIM.PROD_SKID=INTRC_RPT_T_INBR_VW.PROD_SKID )

AND

(

( INTRC_INITV_SEL_VW.SID IN ('1282123246366_51775798') )

AND

( INTRC_RPT_T_INBR_VW.RPT_NAME in ('3BRNN_MI') )

);

---------------------邮件内容---------------------------

老外的这些话引起了我的注意：

Please help in tuning of below sql – it is executed in ~3 minutes – and should be opened in 5-10 seconds

请帮我调整一下SQL，现在执行了3分钟，但是应该在5-10秒完成。

Explain plan looks pretty good (cost 90), maybe some index is needed. We cannot change below sql-code – it is generated by BO.

执行计划看起来想当好(成本只有90)，可能是需要添加某些索引，我们不能更改下面的SQL代码，因为他是BO(SAP)自动生成的。

What we can change are all views which are used by this sql + we can add indexes to tables.

我们能做的就是更改视图，或者在表上面添加索引。

Issue is for 99% in view INTRC_RPT_T_INBR_VW or in INTRC_RPT_INBR_VW

问题的99%的可能性是出在 这2个视图上面 INTRC_RPT_T_INBR_VW 或者in INTRC_RPT_INBR_VW

发邮件的是BO tech leader，他说这个SQL应该在5-10秒以内完成，那么隐含的意思就是以前这个SQL是能够在10秒以内完成的，同时他也希望我们能让这个SQL在10秒以内跑完。

根据老外的阐述，我认为，由于某种原因，执行计划发生了改变，导致SQL以前能在10秒以内完成，现在却要花3分钟。那么导致执行计划的改变的原因通常是由于统计信息过期导致的。

于是执行下面操作：

exec dbms_stats.flush_database_monitoring_info;
select owner || '.' || table_name name , object_type,stale_stats,last_analyzed
from dba_tab_statistics where owner like 'ADWGQ%' AND table_name in
(
'INTRC_INBR_FCT',
'INTRC_RPT_LYOUT_PRC',
'INTRC_INPR_BRDG_DIM',
'INTRC_INITV_DIM',
'INTRC_INPR_BRDG_DIM',
'INTRC_USER_SELCT_PRC',
'INTRC_PROD_DIM',
'INTRC_INITV_TIME_BRDG_DIM',
'INTRC_RPT_DESC_PRC'
);

结果就不贴出了---因为用的TOAD工具，而且调优完成后电脑卡死了，奶奶的大悲剧。

发现果然有2个表统计信息过期了，如何查看统计信息过期？请看stale_stats字段，如果为YES,表示统计信息过期了。而这里，确实有2个表统计信息过期了。我使用了如下脚本收集统计信息。

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ADWGQ_INTRC',
tabname => 'INTRC_RPT_DESC_PRC',
estimate_percent => 50,
method_opt => 'for all columns size repeat',
degree => DBMS_STATS.AUTO_DEGREE,
cascade=>TRUE
);
END;
/

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ADWGQ_INTRC',
tabname => 'INTRC_INBR_FCT',
estimate_percent => 50,
method_opt => 'for all columns size repeat',
degree => DBMS_STATS.AUTO_DEGREE,
granularity => 'ALL',
cascade=>TRUE
);
END;
/

除了对上面2个过期的表收集统计信息外，我还另外对其他表重新收集了统计信息，因为这些表并不大，最大的才几十万条数据，而我这里是4节点，8CPU，HPUX 环境，收集统计信息是非常快的。

事实上也非常快，基本上10秒以内搞定。

收集统计信息之后，跑了一下SQL，16秒搞定

------老外又回复邮件，感谢-----

Great J it is much much better J

Thank you very much

------邮件回复内容-------------

然而到这里，优化并没完成，老外的要求应该是10秒以内出结果。那怎么办呢？

根据执行计划，我发现该执行计划使用了10个索引。于是我又rebuild了这10个索引

那么这次8秒钟就出结果了，好了终于完成了老外的优化请求。

总结：这次SQL调优，其实很简单，再简单不过了？不是吗。不过也有值得我们学习的地方。

首先，你需要根据他人提供的信息，快速定位问题的根源。比如这里我就根据老外说应该(should be)

5-10秒以内完成，那么我就有80%以上的把握肯定统计信息出问题了。

其次就是，即使老外不提应该在5-10秒内完成，我也会去检查统计信息的时效性。

下面列出我做SQL调优的步骤：

一：仔细观察该SQL语句，分析表结构，表大小，行数。

二：分析统计信息的时效性，段高水位，如果统计信息失效，那么收集统计信息，然后继续下面步骤。

三：查看连接列基数，选择性，where条件列基数，选择性，直方图信息。

四：查看表上面有哪些索引，哪些类型的索引，分别在那些列上面。

五：分析现有索引的选择性，聚簇因子等信息。

六：查看表，索引的degree，instance 因为这可能导致并行。

另外得到的一个经验就是：定期rebuild index吧，肯定是有好处的

这里rebuild index之后从16秒提高到8秒，性能整整提升了1倍。