案例4　“月底难过”

1.案例说明

某大型电商公司数据仓库系统经常出现在月底运行缓慢的情况，但在平时系统运行却非常正常。这是因为月底往往有月报等大批量作业运行，而就在这个时间点上，常常会出现缓慢情况，因此业务人员一到月底就非常紧张。这也成了一个老大难问题，困扰了很长时间。

DBA介入处理，发现一个很奇怪的现象：某条主要SQL是造成执行缓慢的主因，其执行计划是不确定的，也就是说因为执行计划的改变，导致其运行效率不同。而往往较差的执行计划发生在月底几天，且由于月底大批作业的影响，整体性能比较饱和，更突显了这个问题。针对某个出现问题的时间段，做了进一步分析，结果表明是由于统计信息的缺失导致了优化器产生了较差的执行计划，并据此指定了人工策略，彻底解决了这个问题。

（1）具体分析

先来看下面的代码：

select...
from xxx a join xxx b on a.order_id = b.lyywzdid
left join xxx c on b.gysid = c.gysid
whereb.cdate>= to_date（'2012-03-31'， 'yyyy-mm-dd'） – 3 and ...
a.send_date>= to_date（'2012-03-31'， 'yyyy-mm-dd'） - 1 and a.send_date<to_date（'2012-03-31'， 'yyyy-mm-dd'）；
--------------------------------------------------------------------------------
|Id  | Operation          |Name  |  Rows  |  Bytes  | Cost （%CPU） |Pstart|Pstop|
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |      |      1 |     104 |      9743（1）|      |     |
|  1 |  HASH JOIN OUTER   |      |      1 |     104 |      9743（1）|      |     |
|  2 |   TABLE ACCESS BY LOCAL INDEX ROWID
                          | XXXX |      1 |      22 |         0（0）| 1189 | 1189|
|  3 |    NESTED LOOPS    |      |      1 |      94 |      9739（1）|      |     |
|  4 |     PARTITION RANGE ITERATOR    
                          |      |   1032 |   74304 |      9739（1）|  123 | 518 |
|  5 |      TABLE ACCESS FULL 
                          | XXXX |   1032 |   74304 |      9739（1）|  123 | 518 |
|  6 |     PARTITION RANGE SINGLE
                          |      |      1 |         |         0（0）| 1189 | 1189 |
|  7 |      INDEX RANGE SCAN 
                          | XXXX |      1 |         |         0（0）| 1189 | 1189 |
|  8 |   TABLE ACCESS FULL
                          | XXXX |    183 |    1830 |         3（0）|      |     |
--------------------------------------------------------------------------------

执行计划中，多表关联私用了嵌套循环，这点对于OLAP系统来说是比较少见的。一般优化器更倾向于使用SM和HJ。进一步检查发现其成本竟然是0，怪不得优化器使用了嵌套循环。

（2）深入分析

检查发现索引数据统计信息异常，这是分区索引，仅两天的分区统计信息都是0。导致优化器认为嵌套循环的执行效率更高，而不是使用哈希连接。结合业务发现，月底是业务高峰期，对于系统统计信息的作业收集，在指定的时间窗口内无法完成。最后导致统计信息不完整，优化器采用了错误的执行计划。

（3）解决方法

解决的代码如下：

exec dbms_stats.gather_index_stats（
       ownname=>'xxx'，
       indname=>'xxx'，
       partname=>'PART_xxx'，
       estimate_percent => 10）；

分析完对象的统计信息即恢复正常。

2.给我们的启示
  • 统计信息是优化器优化的重要参考依据，一个完整、准确的统计信息是必要条件。往往在优化过程中，第一步就是查看相关对象的统计信息。
  • 分区机制是Oracle针对大数据的重要解决手段，但其也很容易造成所谓“放大效应”。即对于普通表而言，统计信息更新不及时可能不会导致执行计划偏差过大；但对于分区表、索引来说，很容易出现因更新不及时出现0的情况，进而导致执行计划产生严重偏差。
上一章目录下一章