SQL 大量消耗TEMP问题诊断分析，以及如何解决，

年纪大了，废话不多了， 直接说问题。

【oracle大查询】 SLZX一库，ankole 采样的SQLID:6wdj1qr22jdrx,在2019/2/11 9:23:28 消耗近24G TEMP 

该文针对刁钻问题 ,如根本原因是什么？  之前为什么不出现？ 如何做最根本解决，而且成本还要最少。 以后防止以后再出现类似的问题，

诊断分析的过程，思路不写了， 但是从我的报告中， 也应该能联想的出来， 多思考， 

经过排查发现， 24G的TEMP 用在hash 关联上面，  发现SQl使用绑定变量， 根据告警采集时间看， 绑定变量值情况看
（绑定时间为2019/2/11 9:15:41，  ‘业务开始时间，结束时间’分别是 2018-01-11 00:00:00，2019-02-11 23:59:59 ）时间跨度为1年，而其他时间跨度通常为1天为多，1月也偶尔出现。 
SQL：
SELECT * FROM (SELECT XX.*, ROWNUM AS RN  FROM (select count(*)
                  from (select distinct c.CUST_ORDER_ID, ....     c.SYS_SOURCE
                          from CUSTOMER_ORDER          c,  order_item              a,
                               ord_offer_prod_inst_rel b
                         where 1 = 1
                           and a.order_item_id = b.order_item_id
                           and c.cust_order_id = a.cust_order_id
                           and b.acc_num = :1
                           and c.ACCEPT_DATE >=  to_date(:2, 'yyyy-MM-dd hh24:mi:ss')
                           and c.ACCEPT_DATE <=   to_date(:3, 'yyyy-MM-dd hh24:mi:ss')
                           and a.REGION_ID in (:4,   :18)) table_count) XX
         WHERE ROWNUM <= 500) XXX
 WHERE RN > 0

另外结合执行计划看：  
|   8 |         HASH JOIN                     |                              |     1 |   283 |    21   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                 |                              |    14 |  3668 |    15   (0)| 00:00:01 |
|  10 |           NESTED LOOPS                |                              |    16 |  3668 |    15   (0)| 00:00:01 |
|  11 |            TABLE ACCESS BY INDEX ROWID| CUSTOMER_ORDER               |     2 |   476 |     5   (0)| 00:00:01 |
|  12 |             INDEX RANGE SCAN          | IDX_CUSTOMER_ORDER_AD        |     2 |       |     3   (0)| 00:00:01 |
|  13 |            INDEX RANGE SCAN           | IDX_ORDER_ITEM_CUST_ORDER_ID |     8 |       |     3   (0)| 00:00:01 |
|  14 |           TABLE ACCESS BY INDEX ROWID | ORDER_ITEM                   |     8 |   192 |     5   (0)| 00:00:01 |
执行计划第12步就是走的时间索引，   时间跨度为一年，rows 估算成2， 基数估算错误。怀疑和"谓词越界"有关， 又使得执行计划共享了。

期间查询 CUSTOMER_ORDER  收集统计信息情况（数据来源DBA_TAB_STATS_HISTORY )，发现这个表都是在晚上19点收集统计信息。 统计信息更新是发现在前一天晚上,因此rows 估算错误。

产生错误的执行计划， 又使得执行计划共享了， 于是出现查询跨度为一年的SQl。就可能造成性能问题。 

从绑定变量情况以及查询粒度看，  大多数业务查询最新一天的数据（大多数在8点左右开始查）， 而统计信息是在前一天晚上19点收集。因此可能出现错误执行计划。

给出的建议。 1 加大收集统计信息的力度。 

 2 因为时间跨度不定，有时候查询1天(大概8万条)，也有查询1月，甚至查询一年的。   不适合做驱动表(最先执行)

通过SQL本身的特征，   and b.acc_num = :1做多才1000多条数据，适合做驱动表，测试后7千逻辑读/次， 效率远远高于现在的执行计划。

  估计受到谓词越界的影响才用性能不好的执行计划。  加大收集统计信息的力度应该能解决。

3 也可以通过绑定执行计划的方式优化(不建议)
 

思考， 纠正后的执行计划是 7000逻辑读/次， 如果进一步优化。