今天查看正式环境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以后就有了笛卡尔集。