PERFORMANCE TUNING GUIDE
| SITE | MCS | 业务名称 |
|
| 顾问姓名 | 金某 | 开发负责人 |
|
| TYPE | ■ Batch | 优化结果确认人 |
|
| 填写日期 | 2010-03-23 | 优化所需时间 |
|
| 优化前执行时间 | 335.05秒 | 优化后执行时间 | 0.857秒 |
SQL
原程序 – CUR_2, CUR_3按照过程化的方式在执行
/* UPDATE MONTH ACT TAR_STEPRESULT */
CURSOR CUR_2 IS
select
GBM, AREA, ITEM, MODELNAME, PAREA, STEP,
to_number(sum(DLYIN)) CUMIN,
to_number(sum(DLYOUT)) CUMOUT
from tar_STEPRESULT a,
mst_time b
where a.yymmdd = b.dateid
and a.yymmdd <= v_actdate
and b.year||b.month = (select year||month from mst_time where dateid = v_actdate)
group by GBM, AREA, ITEM, MODELNAME, PAREA, STEP
;
CUR_REC2 CUR_2%ROWTYPE;
/* UPDATE WEEK ACT TAR_STEPRESULT */
CURSOR CUR_3 IS
select
GBM, AREA, ITEM, MODELNAME, PAREA, STEP,
to_number(sum(DLYIN)) WLYIN,
to_number(sum(DLYOUT)) WLYOUT
from tar_STEPRESULT a,
mst_time b
where a.yymmdd = b.dateid
and a.yymmdd <= v_actdate
and b.yearweek = (select yearweek from mst_time where dateid = v_actdate)
group by GBM, AREA, ITEM, MODELNAME, PAREA, STEP
;
CUR_REC3 CUR_3%ROWTYPE;
原程序 – 单独执行FETCH INSERT操作
FETCH SEL_CUR INTO v_SEL_CUR_REC;
EXIT WHEN SEL_CUR%NOTFOUND;
v_tempcnt := 1;
BEGIN
INSERT INTO TAR_STEPRESULT
(
GBM,
AREA,
ITEM,
MODELNAME,
YYMMDD,
PAREA,
STEP,
DLYBOH,
CUMBOH,
DLYIN,
DLYOUT,
WLYIN,
WLYOUT,
CUMIN,
CUMOUT,
EOH,
INITDTTM,
INITBY
)VALUES(
v_SEL_CUR_REC.GBM
, v_SEL_CUR_REC.AREA
, v_SEL_CUR_REC.PART_NO
, v_SEL_CUR_REC.SALES_CODE
, v_SEL_CUR_REC.YYMMDD
, v_SEL_CUR_REC.P_AREA
, v_SEL_CUR_REC.STEP
, v_SEL_CUR_REC.DLYBOH
, v_SEL_CUR_REC.CUMBOH
, v_SEL_CUR_REC.DLYIN
, v_SEL_CUR_REC.DLYOUT
, v_SEL_CUR_REC.WLYIN
, v_SEL_CUR_REC.WLYOUT
, v_SEL_CUR_REC.CUMIN
, v_SEL_CUR_REC.CUMOUT
, v_SEL_CUR_REC.EOH
, sysdate
, v_progname
);
v_insertcnt := v_insertcnt + 1;
IF mod(v_insertcnt,1000) = 0 THEN
commit;
DBMS_OUTPUT.PUT_LINE('commit');
END IF;
1. 绑定变量
ACTDATE IN VARCHAR2 DEFAULT NULL
2. 问题及原因
1) 为了记录日志内容， 单独执行INSERT语句。
2) 插入数据后，分别以周和月为单位创建数据集合，并以行为单位执行修改操作- 发生大量RANDOM I/O(CUR_2, CUR_3 按照过程化的方式在执行)
3) 按照过程化的方式处理海量数据 => 对11,000,000行数据执行TABLE FULL SCAN
3. 解决方案
1) ARRAY PROCESSING INSERT.
2) 合并CUR_2, CUR_3后，利用ROWID来执行UPDATE (ARRAY PROCESSING ) – DIRECT ACCESS
3) 利用PARALLEL(/*+ PARALLEL(A 8) */
4. 优化后的SQL
CURSOR合并
CURSOR CUR_I IS
SELECT ROW_ID, CUMIN, CUMOUT, WLYIN, WLYOUT
FROM (
SELECT /*+ PARALLEL_INDEX(B PK_TAR_STEPRESULT 8) INDEX_FFS(B PK_TAR_STEPRESULT) USE_HASH(A B) */
A.GBM, A.AREA, A.ITEM, A.MODELNAME, A.PAREA, A.STEP, A.CUMIN, a.CUMOUT, A.WLYIN, A.WLYOUT, B.ROWID ROW_ID
FROM (
SELECT GBM, AREA, ITEM, MODELNAME, PAREA, STEP,
SUM(CUMIN) CUMIN,
SUM(CUMOUT) CUMOUT,
SUM(WLYIN) WLYIN,
SUM(WLYOUT) WLYOUT
FROM (
SELECT /*+ PARALLEL(A 8) PQ_DISTRIBUTE(A BROADCAST, NONE) */
GBM, AREA, ITEM, MODELNAME, PAREA, STEP,
TO_NUMBER(SUM(DLYIN)) CUMIN,
TO_NUMBER(SUM(DLYOUT)) CUMOUT,
NULL WLYIN,
NULL WLYOUT
FROM TAR_STEPRESULT A,
MST_TIME B
WHERE A.YYMMDD = B.DATEID
AND A.YYMMDD <= V_ACTDATE
AND B.YEAR||B.MONTH = (SELECT YEAR||MONTH FROM MST_TIME WHERE DATEID =V_ACTDATE)
GROUP BY GBM, AREA, ITEM, MODELNAME, PAREA, STEP
UNION ALL
SELECT /*+ PARALLEL(A 8) PQ_DISTRIBUTE(A BROADCAST, NONE) */
GBM, AREA, ITEM, MODELNAME, PAREA, STEP,
NULL, NULL,
TO_NUMBER(SUM(DLYIN)) WLYIN,
TO_NUMBER(SUM(DLYOUT)) WLYOUT
FROM TAR_STEPRESULT A,
MST_TIME B
WHERE A.YYMMDD = B.DATEID
AND A.YYMMDD <= V_ACTDATE
AND B.YEARWEEK = (SELECT YEARWEEK FROM MST_TIME WHERE DATEID =V_ACTDATE)
GROUP BY GBM, AREA, ITEM, MODELNAME, PAREA, STEP
)
GROUP BY GBM, AREA, ITEM, MODELNAME, PAREA, STEP
) A, TAR_STEPRESULT B
WHERE A.GBM = B.GBM
AND A.AREA = B.AREA
AND A.ITEM = B.ITEM
AND A.MODELNAME = B.MODELNAME
AND A.PAREA = B.PAREA
AND A.STEP = B.STEP
AND B.YYMMDD =V_ACTDATE
)
;
ARRAY PROCESSING
FETCH SEL_CUR BULK COLLECT
INTO l_gbm , l_area, l_item , l_modelname , l_yymmdd , l_parea , l_step
, l_dlyboh, l_cumboh, l_dlyin , l_dlyout, l_wlyin , l_wlyout , l_cumin , l_cumout, l_eoh
LIMIT c_batch;
BEGIN
FORALL i IN l_gbm.FIRST .. l_gbm.LAST
SAVE EXCEPTIONS
INSERT INTO TAR_STEPRESULT
(
GBM,
AREA,
ITEM,
MODELNAME,
YYMMDD,
PAREA,
STEP,
DLYBOH,
CUMBOH,
DLYIN,
DLYOUT,
WLYIN,
WLYOUT,
CUMIN,
CUMOUT,
EOH,
INITDTTM,
INITBY
)VALUES(
l_gbm(i)
, l_area(i)
, l_item(i)
, l_modelname(i)
, l_yymmdd(i)
, l_parea(i)
, l_step(i)
, l_dlyboh(i)
, l_cumboh(i)
, l_dlyin(i)
, l_dlyout(i)
, l_wlyin(i)
, l_wlyout(i)
, l_cumin(i)
, l_cumout(i)
, l_eoh(i)
, SYSDATE
, V_PROGNAME
);
v_insertcnt := v_insertcnt + SQL%ROWCOUNT;
EXCEPTION
WHEN bulk_errors THEN
FOR indx IN 1..SQL%BULK_EXCEPTIONS.COUNT
LOOP
v_errmessage := SUBSTR(SQLERRM(-1*SQL%BULK_EXCEPTIONS(indx).ERROR_CODE)
||' ERROR_DATA('
||' GBM ' || l_gbm(SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX)
||' AREA ' || l_area(SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX)
||' PART_NO ' || l_item(SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX)
||' SALES_CODE ' || l_modelname(SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX)
||' YYMMDD ' || l_yymmdd(SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX)
||' P_AREA ' || l_parea(SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX)
||' STEP ' || l_step(SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX)
||')',1,2000); -- errormessage
SP_LOGGING_DETAIL_REG(v_progname,v_startdate,v_errmessage,v_logseq);
v_errorcnt := v_errorcnt+1;
END LOOP;
END;
5. 性能比较
1) 执行计划
优化前 - CUR_2
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 6908 |00:00:10.79 | 93421 |
|* 2 | HASH JOIN | | 1 | 6908 |00:00:07.67 | 93421 |
|* 3 | TABLE ACCESS BY INDEX ROWID | MST_TIME | 1 | 2 |00:00:00.01 | 37 |
|* 4 | INDEX RANGE SCAN | MST_TIME_PK | 1 | 33 |00:00:00.01 | 6 |
| 5 | TABLE ACCESS BY INDEX ROWID| MST_TIME | 1 | 1 |00:00:00.01 | 3 |
|* 6 | INDEX UNIQUE SCAN | MST_TIME_PK | 1 | 1 |00:00:00.01 | 2 |
|* 7 | TABLE ACCESS FULL | TAR_STEPRESULT | 1 | 514K|00:00:00.01 | 93384 |
--------------------------------------------------------------------------------------------------
优化前 - CUR_3
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 1323 |00:00:08.18 | 93421 |
|* 2 | HASH JOIN | | 1 | 1323 |00:00:07.69 | 93421 |
|* 3 | TABLE ACCESS BY INDEX ROWID | MST_TIME | 1 | 1 |00:00:00.01 | 37 |
|* 4 | INDEX RANGE SCAN | MST_TIME_PK | 1 | 33 |00:00:00.01 | 6 |
| 5 | TABLE ACCESS BY INDEX ROWID| MST_TIME | 1 | 1 |00:00:00.01 | 3 |
|* 6 | INDEX UNIQUE SCAN | MST_TIME_PK | 1 | 1 |00:00:00.01 | 2 |
|* 7 | TABLE ACCESS FULL | TAR_STEPRESULT | 1 | 514K|00:00:00.01 | 93384 |
--------------------------------------------------------------------------------------------------
优化后 - 合并
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
| 1 | PX COORDINATOR | | 1 | |00:00:03.41 | 48593 |
| 2 | PX SEND QC (RANDOM) | :TQ10007 | 0 | 147 |00:00:00.01 | 0 |
|* 3 | HASH JOIN | | 0 | 147 |00:00:00.01 | 0 |
| 4 | PX RECEIVE | | 0 | 22307 |00:00:00.01 | 0 |
| 5 | PX SEND HASH | :TQ10006 | 0 | 22307 |00:00:00.01 | 0 |
| 6 | VIEW | | 0 | 22307 |00:00:00.01 | 0 |
| 7 | HASH GROUP BY | | 0 | 22307 |00:00:00.01 | 0 |
| 8 | PX RECEIVE | | 0 | 22307 |00:00:00.01 | 0 |
| 9 | PX SEND HASH | :TQ10005 | 0 | 22307 |00:00:00.01 | 0 |
| 10 | HASH GROUP BY | | 0 | 22307 |00:00:00.01 | 0 |
| 11 | VIEW | | 0 | 22307 |00:00:00.01 | 0 |
| 12 | UNION-ALL | | 0 | |00:00:00.01 | 0 |
| 13 | HASH GROUP BY | | 0 | 18721 |00:00:00.01 | 0 |
| 14 | PX RECEIVE | | 0 | 18721 |00:00:00.01 | 0 |
| 15 | PX SEND HASH | :TQ10003 | 0 | 18721 |00:00:00.01 | 0 |
| 16 | HASH GROUP BY | | 0 | 18721 |00:00:00.01 | 0 |
|* 17 | HASH JOIN | | 0 | 18721 |00:00:00.01 | 0 |
| 18 | BUFFER SORT | | 0 | |00:00:00.01 | 0 |
| 19 | PX RECEIVE | | 0 | 2 |00:00:00.01 | 0 |
| 20 | PX SEND BROADCAST | :TQ10000 | 0 | 2 |00:00:00.01 | 0 |
|* 21 | TABLE ACCESS BY INDEX ROWID | MST_TIME | 1 | 2 |00:00:00.01 | 0 |
|* 22 | INDEX RANGE SCAN | MST_TIME_PK | 1 | 33 |00:00:00.01 | 0 |
| 23 | TABLE ACCESS BY INDEX ROWID| MST_TIME | 0 | 1 |00:00:00.01 | 0 |
|* 24 | INDEX UNIQUE SCAN | MST_TIME_PK | 0 | 1 |00:00:00.01 | 0 |
| 25 | PX BLOCK ITERATOR | | 0 | 502K|00:00:00.01 | 0 |
|* 26 | TABLE ACCESS FULL | ENC_TAR_STEPRESULT_2 | 0 | 502K|00:00:00.01 | 0 |
| 27 | HASH GROUP BY | | 0 | 3586 |00:00:00.01 | 0 |
| 28 | PX RECEIVE | | 0 | 3586 |00:00:00.01 | 0 |
| 29 | PX SEND HASH | :TQ10004 | 0 | 3586 |00:00:00.01 | 0 |
| 30 | HASH GROUP BY | | 0 | 3586 |00:00:00.01 | 0 |
|* 31 | HASH JOIN | | 0 | 3586 |00:00:00.01 | 0 |
| 32 | BUFFER SORT | | 0 | |00:00:00.01 | 0 |
| 33 | PX RECEIVE | | 0 | 1 |00:00:00.01 | 0 |
| 34 | PX SEND BROADCAST | :TQ10001 | 0 | 1 |00:00:00.01 | 0 |
|* 35 | TABLE ACCESS BY INDEX ROWID | MST_TIME | 1 | 1 |00:00:00.01 | 0 |
|* 36 | INDEX RANGE SCAN | MST_TIME_PK | 1 | 33 |00:00:00.01 | 0 |
| 37 | TABLE ACCESS BY INDEX ROWID| MST_TIME | 0 | 1 |00:00:00.01 | 0 |
|* 38 | INDEX UNIQUE SCAN | MST_TIME_PK | 0 | 1 |00:00:00.01 | 0 |
| 39 | PX BLOCK ITERATOR | | 0 | 502K|00:00:00.01 | 0 |
|* 40 | TABLE ACCESS FULL | ENC_TAR_STEPRESULT_2 | 0 | 502K|00:00:00.01 | 0 |
| 41 | BUFFER SORT | | 0 | |00:00:00.01 | 0 |
| 42 | PX RECEIVE | | 0 | 100K|00:00:00.01 | 0 |
| 43 | PX SEND HASH | :TQ10002 | 0 | 100K|00:00:00.01 | 0 |
|* 44 | INDEX FAST FULL SCAN | PK_ENC_TAR_STEPRESULT_2 | 1 | 100K|00:00:03.31 | 48587 |
-----------------------------------------------------------------------------------------------------------------------------
2) 执行时间
| 区分 | 优化CURSOR | ARRAY SIZE | 执行时间(秒) |
| 优化前 | 无 | 无 | 335.05 |
| 优化后 | 应用 | 100 | 38.80 |
| 500 | 34.58 | ||
| 1000 | 19.85 | ||
| 10000 | 19.37 |
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25708791/viewspace-695504/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/25708791/viewspace-695504/
7824
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
