问题:
    投产支持时，发现一个生成报表的sql语句执行了2个小时,还是没有任何输出。而同样的语句，在以前只需要10分钟就可以出来了。

1,问题SQL分析：
SELECT ...
  FROM customer cus,
       (SELECT DISTINCT cust_id
          FROM accounts act
         WHERE act.ibknum = '40303'
           AND act.TYPE NOT IN ('00', '01', '02', '03')) actt,
       bancs_cif_mig bcm,
       accounts act,
       bocorg org
 WHERE cus.cust_id = actt.cust_id
   AND cus.cust_id = bcm.cust_id
   AND bcm.cp_flag = 'P'
   AND bcm.ncif > 1
   AND cus.cust_id = act.cust_id
   AND cus.bank_id_m = org.bank_id
 ORDER BY org.bank_id, cus.cust_id, cus.cif, act.actseq;

等待事件分析：
    分析系统当前会话，发现有并行会话；经检查，发现脚本中有如下语句：
    alter session force parallel query parallel 8;
    检查并行会话当前的等待事件，有几个会话等待cache buffers chains latch。推测问题的是由于不良的执行计划，在并行执行时导致热块。

查看执行计划：

Execution Plan
----------------------------------------------------------
Plan hash value: 4024296431

------------------------------------------------------------------------------------------
| Id  | Operation                | Name          | Rows  |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |               |   241K|       |   821K  (1)| 02:44:17 |
|   1 |  SORT ORDER BY           |               |   241K|    85M|   821K  (1)| 02:44:17 |
|*  2 |   HASH JOIN              |               |   241K|       |   812K  (1)| 02:42:26 |
|   3 |    TABLE ACCESS FULL     | BOCORG        | 12212 |       |    84   (0)| 00:00:02 |
|*  4 |    HASH JOIN             |               |   241K|    16M|   812K  (1)| 02:42:25 |
|*  5 |     HASH JOIN            |               |   222K|    72M|   524K  (1)| 01:44:58 |
|*  6 |      TABLE ACCESS FULL   | BANCS_CIF_MIG |  3326K|       |  6520   (4)| 00:01:19 |
|*  7 |      HASH JOIN           |               |  2505K|    43M|   506K  (1)| 01:41:17 |
|   8 |       VIEW               |               |  2505K|       |   182K  (2)| 00:36:32 |
|   9 |        HASH UNIQUE       |               |  2505K|   134M|   182K  (2)| 00:36:32 |
|* 10 |         TABLE ACCESS FULL| ACCOUNTS      |  2512K|       |   169K  (2)| 00:33:51 |
|  11 |       TABLE ACCESS FULL  | CUSTOMER      |    38M|       |   205K  (1)| 00:41:11 |
|  12 |     TABLE ACCESS FULL    | ACCOUNTS      |    40M|       |   168K  (1)| 00:33:46 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CUS"."BANK_ID_M"="ORG"."BANK_ID")
   4 - access("CUS"."CUST_ID"="ACT"."CUST_ID")
   5 - access("CUS"."CUST_ID"="BCM"."CUST_ID")
   6 - filter("BCM"."CP_FLAG"='P' AND "BCM"."NCIF">1)
   7 - access("CUS"."CUST_ID"="ACTT"."CUST_ID")
  10 - filter("ACT"."IBKNUM"='40303' AND "ACT"."TYPE"<>'00' AND "ACT"."TYPE"<>'01' AND
              "ACT"."TYPE"<>'02' AND "ACT"."TYPE"<>'03')

   查询计划中出现5次全表扫描（其中3个表数据约3000万）,4次HASH JOIN，成本高达821K。

执行计划比较:  
   查询优化统计数据,发现表上的统计信息比较准确。对比测试环境（数据量约为当前环境1/2，该SQL运行正常）同样语句的执行计划：

SQL> set autotrace trace exp
SQL>
Execution Plan
----------------------------------------------------------
Plan hash value: 3772091147

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   | 88072 |       |   747K  (1)| 02:29:35 |
|   1 |  SORT ORDER BY               |                   | 88072 |    15M|   747K  (1)| 02:29:35 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ACCOUNTS          |     1 |       |     3   (0)| 00:00:01 |
|   3 |    NESTED LOOPS              |                   | 88072 |       |   744K  (1)| 02:28:56 |
|*  4 |     HASH JOIN                |                   | 81468 |       |   500K  (1)| 01:40:02 |
|   5 |      TABLE ACCESS FULL       | BOCORG            | 12238 |       |    63   (0)| 00:00:01 |
|*  6 |      HASH JOIN               |                   | 81468 |    36M|   500K  (1)| 01:40:01 |
|   7 |       VIEW                   |                   |  2121K|       |   176K  (2)| 00:35:19 |
|   8 |        HASH UNIQUE           |                   |  2121K|    57M|   176K  (2)| 00:35:19 |
|*  9 |         TABLE ACCESS FULL    | ACCOUNTS          |  2126K|       |   165K  (2)| 00:33:02 |
|* 10 |       HASH JOIN              |                   |  1298K|    28M|   317K  (1)| 01:03:29 |
|* 11 |        TABLE ACCESS FULL     | BANCS_CIF_MIG     |  1298K|       |  2835   (4)| 00:00:35 |
|  12 |        TABLE ACCESS FULL     | CUSTOMER          |    39M|       |   205K  (1)| 00:41:03 |
|* 13 |     INDEX RANGE SCAN         | IDX_ACCOUNTS_CUST |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("CUS"."BANK_ID_M"="ORG"."BANK_ID")
   6 - access("CUS"."CUST_ID"="ACTT"."CUST_ID")
   9 - filter("ACT"."IBKNUM"='40303' AND "ACT"."TYPE"<>'00' AND "ACT"."TYPE"<>'01' AND
              "ACT"."TYPE"<>'02' AND "ACT"."TYPE"<>'03')
  10 - access("CUS"."CUST_ID"="BCM"."CUST_ID")
  11 - filter("BCM"."CP_FLAG"='P' AND "BCM"."NCIF">1)
  13 - access("CUS"."CUST_ID"="ACT"."CUST_ID")

    通过比较，会发现仍然涉及4次全表扫描（其中2个表数据约3000万），3次HASH JOIN，1次NESTED LOOP，成本高达747K。与前面成本821K比较，可以认为：由于数据量变化，优化器通过比较所有可用的访问路径，认为成本821K为最低，所以选择了5次全表扫描、4次HASH JOIN的执行计划。

2,解决方案：
    通过分析应用逻辑，会发现查询语句中过滤性最强的谓词为：BCM.NCIF>1。如果能优先执行该表的查询，所需逻辑读最少。
  
创建索引:
create index idx_bancs_cif_mig_ncif on bancs_cif_mig(ncif) parallel 8;
 
alter index idx_bancs_cif_mig_ncif noparallel;
 
begin
   dbms_stats.gather_table_stats(user,'bancs_cif_mig');
end;
/  
----------------------------

Execution Plan
----------------------------------------------------------
Plan hash value: 86001731

------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |  2018 |   146K  (1)| 00:29:18 |
|   1 |  SORT ORDER BY                      |                        |  2018 |   146K  (1)| 00:29:18 |
|   2 |   VIEW                              |                        |  2018 |   146K  (1)| 00:29:18 |
|   3 |    HASH UNIQUE                      |                        |  2018 |   146K  (1)| 00:29:18 |
|*  4 |     HASH JOIN                       |                        |  2018 |   146K  (1)| 00:29:18 |
|   5 |      TABLE ACCESS BY INDEX ROWID    | ACCOUNTS               |     1 |     2   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                  |                        |  2018 |   146K  (1)| 00:29:17 |
|   7 |        NESTED LOOPS                 |                        |  1864 |   142K  (1)| 00:28:33 |
|   8 |         NESTED LOOPS                |                        | 28616 | 57628   (1)| 00:11:32 |
|*  9 |          TABLE ACCESS BY INDEX ROWID| BANCS_CIF_MIG          | 28616 |   355   (1)| 00:00:05 |
|* 10 |           INDEX RANGE SCAN          | IDX_BANCS_CIF_MIG_NCIF | 29268 |    61   (2)| 00:00:01 |
|  11 |          TABLE ACCESS BY INDEX ROWID| CUSTOMER               |     1 |     2   (0)| 00:00:01 |
|* 12 |           INDEX UNIQUE SCAN         | PK_CUSTOMER            |     1 |     1   (0)| 00:00:01 |
|* 13 |         TABLE ACCESS BY INDEX ROWID | ACCOUNTS               |     1 |     3   (0)| 00:00:01 |
|* 14 |          INDEX RANGE SCAN           | IDX_ACCOUNTS_CUST      |     1 |     2   (0)| 00:00:01 |
|* 15 |        INDEX RANGE SCAN             | IDX_ACCOUNTS_CUST      |     1 |     2   (0)| 00:00:01 |
|  16 |      TABLE ACCESS FULL              | BOCORG                 | 12212 |    84   (0)| 00:00:02 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("CUS"."BANK_ID_M"="ORG"."BANK_ID")
   9 - filter("BCM"."CP_FLAG"='P')
  10 - access("BCM"."NCIF">1)
  12 - access("CUS"."CUST_ID"="BCM"."CUST_ID")
  13 - filter("ACT"."IBKNUM"='40303' AND "ACT"."TYPE"<>'00' AND "ACT"."TYPE"<>'01' AND
              "ACT"."TYPE"<>'02' AND "ACT"."TYPE"<>'03')
  14 - access("CUS"."CUST_ID"="CUST_ID")
  15 - access("CUS"."CUST_ID"="ACT"."CUST_ID")

执行计划中使用了新建的索引，进行BCM.NCIF>1过滤后，仅有29268行满足要求，后续通过NESTED LOOP依次访问其他表，仅有一次对小表的全表扫描。运行查询，8分钟后执行完毕。

3,总结：
   在本案例中，并不是优化器失效。就目前阶段，优化器能做的只是从所有可用的访问路径中选择最优方案；只有在DBA依据应用特点，提供了有效的访问路径（索引、物化视图、约束等），优化器才能提供最优的、满足需求的执行计划。
   我们不能期望优化器，通过实时采样分析数据分布特征，创建“临时”索引，提供新的访问路径，这就是优化器的局限，这也是DBA的价值所在。