oracle 一存储过程执行超过9小时诊断

      今天查看正式环境awr报告，1:00-10:00有个存储过程一直在执行。找到这个存储过程，分析了下结构和功能，结构是26个update语句，功能是统计各区设备数量，将查询结果插入一个表A中。处理过程如下：

        1. 在测试环境下执行，非常快啊，2分钟就执行完成。确认了一下测试环境和正式环境的数据量，确定是一样的。

        2. 怀疑是update A表的时候被锁定。次日一大早来到公司执行下列脚本，没有发现堵塞。

      select b.sid,b.serial#,b.process,d.ctime "LOCK TIME (sec)",c.object_name,
         decode(a.locked_mode,
             1, 'NULL',
             2,'ROW SHARED',
             3,'ROW EXCLUSIVE',
             4,'SHARED',
             5,'S/ROW EXCLUSIVE',
             6, 'EXCLUSIVE') "LOCK MODE",
      b.last_call_et "INACTIVE FOR(sec)",
      decode(d.block, 1, 'Yes', 0, 'No') "BLOCKING",
      dbms_rowid.rowid_create(1,c.data_object_id,b.ROW_WAIT_FILE#,
       b.ROW_WAIT_BLOCK#,b.ROW_WAIT_ROW#) "LOCKed ROWID"
 from v$locked_object a, v$session b, dba_objects c, v$lock d
 where b.sid = a.session_id
  and c.object_id = a.object_id
  and d.sid = a.session_id
  and d.id1 = a.object_id;

       3. 一切又回到原点，只有看执行计划，在存储过程中26条SQL中找到一个执行慢的。

UPDATE DML_REPORT_DIS_EQUIPMENT T
   SET T.MIN_SWITCH_STATION =
       (SELECT L.MIN_SWITCH_STATION
          FROM (SELECT RO.MRID, COUNT(C.ID) AS MIN_SWITCH_STATION
                  FROM DML_FL_REGION_LINE     RL,
                       DML_FL_LINE_SUBSTATION S,
                       DML_FL_SUBSTATION_KGZ  K,
                       DML_FL_OBJECT          RO,
                       DML_INSTALL_HISTORY    H,
                       DML_A_COMMON           C
                 WHERE RL.OBJECT_A_ID = RO.ID
                   AND RL.OBJECT_B_ID = S.OBJECT_A_ID
                   AND S.OBJECT_B_ID = K.ID
                   AND K.ID = H.FUNCTION_ID
                   AND H.ASSET_ID = C.ID
                 GROUP BY RO.MRID) L
         WHERE T.ID = L.MRID);

      -------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                    |                             |       |       | 19343 (100)|          |
|   1 |  UPDATE                             | DM_REPORT_DIS_EQUIPMENT     |       |       |            |          |
|   2 |   TABLE ACCESS FULL                 | DM_REPORT_DIS_EQUIPMENT     |    20 |   280 |     3   (0)| 00:00:01 |
|   3 |   VIEW                              |                             |     1 |    31 |   966   (2)| 00:00:12 |
|   4 |    SORT GROUP BY                    |                             |     1 |    90 |   966   (2)| 00:00:12 |
|   5 |     NESTED LOOPS                    |                             |     1 |    90 |   966   (2)| 00:00:12 |
|   6 |      NESTED LOOPS                   |                             |     1 |    81 |   965   (2)| 00:00:12 |
|   7 |       NESTED LOOPS                  |                             |     1 |    63 |   965   (2)| 00:00:12 |
|   8 |        MERGE JOIN CARTESIAN         |                             |  3584 |   189K|   965   (2)| 00:00:12 |
|   9 |         NESTED LOOPS                |                             |     1 |    36 |     4   (0)| 00:00:01 |
|  10 |          TABLE ACCESS BY INDEX ROWID| DM_FL_OBJECT                |     1 |    18 |     3   (0)| 00:00:01 |
|* 11 |           INDEX UNIQUE SCAN         | OBJECT_MRID                 |     1 |       |     2   (0)| 00:00:01 |
|* 12 |          INDEX RANGE SCAN           | UNIQUE_DM_FL_ASSOCIATION_04 |     1 |    18 |     1   (0)| 00:00:01 |
|  13 |         BUFFER SORT                 |                             |   789K|    13M|   964   (2)| 00:00:12 |
|  14 |          TABLE ACCESS FULL          | DM_INSTALL_HISTORY          |   789K|    13M|   961   (2)| 00:00:12 |
|* 15 |        INDEX UNIQUE SCAN            | PK_DM_FL_SUBSTATION_KGZ     |     1 |     9 |     0   (0)|          |
|* 16 |       INDEX UNIQUE SCAN             | UNIQUE_DM_FL_ASSOCIATION_07 |     1 |    18 |     0   (0)|          |
|* 17 |      INDEX UNIQUE SCAN              | PK_DM_A_COMMON              |     1 |     9 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  11 - access("RO"."MRID"=:B1)
  12 - access("RL"."OBJECT_A_ID"="RO"."ID")
  15 - access("K"."ID"="H"."FUNCTION_ID")
  16 - access("RL"."OBJECT_B_ID"="S"."OBJECT_A_ID" AND "S"."OBJECT_B_ID"="K"."ID")
  17 - access("H"."ASSET_ID"="C"."ID")
42 rows selected.

           在正式环境上的执行计划中看到笛卡尔集，原来执行计划当中第八行出现迪卡尔积运算，效率很低;并且DM_INSTALL_HISTORY表做了BUFFER SORT用于做迪卡尔运算，而该表有K.ID = H.FUNCTION_ID表连接；在DM_INSTALL_HISTORY.FUNCTION_ID字段创建索引：create index DFWMS.ind01_INSTALL_HISTORY on DFWMS.DM_INSTALL_HISTORY("FUNCTION_ID");

     再次执行此存储过程，1分钟执行完成。在这里值得注意的是单独执行update内嵌的查询语句是没有笛卡尔集，有了update以后就有了笛卡尔集。