最近用程序在跑的一個sql突然執行時間過長報ora-01555，手動執行四個多小時也沒完事就給停了，之前都沒出現過這種問題，查了表所在的表空間，undo表空間，temp都沒有問題，單獨執行select操作20分鐘也能出結果，insert表大概有1600W的資料，每天執行插入資料也就在15、6W左右，求大佬支援
INSERT/*+APPEND NOLOGGING*/ INTO '||t_TAB_NAME||' PARTITION('||t_TAB_PART||') (DEV_ID,AREA_ID,EMS_ID,
DEV_TYPE_ID,DEV_SUB_TYPE_ID,PORT_ID,PORT_CLASS,IF_POSITION,IF_INDEX,IF_NAME,IF_SPEED,
IF_IN_OCTETS_MAX,IF_IN_OCTETS_SUM,IF_IN_OCTETS_NUM,
IF_OUT_OCTETS_MAX,IF_OUT_OCTETS_SUM,IF_OUT_OCTETS_NUM,
IN_SPEED_MAX,IN_SPEED_SUM,IN_SPEED_NUM,PERCENT_IN_BAND_WIDTH_MAX,PERCENT_IN_BAND_WIDTH_SUM,PERCENT_IN_BAND_WIDTH_NUM,
OUT_SPEED_MAX,OUT_SPEED_SUM,OUT_SPEED_NUM,PERCENT_OUT_BAND_WIDTH_MAX,PERCENT_OUT_BAND_WIDTH_SUM,PERCENT_OUT_BAND_WIDTH_NUM,
TOTAL_COLLECT_NUM,PORT_SPEED_MAX,COLLECT_TIME,RESERVE4,IN_SPEED_MAX_TIME,OUT_SPEED_MAX_TIME)
SELECT /*+FULL(A)*/A.DEV_ID,A.AREA_ID,MAX(A.EMS_ID),A.DEV_TYPE_ID,A.DEV_SUB_TYPE_ID,A.PORT_ID,A.PORT_CLASS,
A.IF_POSITION,A.IF_INDEX,MAX(A.IF_NAME),MAX(A.IF_SPEED),
MAX(IF_IN_OCTETS),SUM(IF_IN_OCTETS),COUNT(IN_SPEED),
MAX(IF_OUT_OCTETS),SUM(IF_OUT_OCTETS),COUNT(OUT_SPEED),
MAX(IN_SPEED),SUM(IN_SPEED),COUNT(IN_SPEED),MAX(PERCENT_IN_BAND_WIDTH),SUM(PERCENT_IN_BAND_WIDTH),COUNT(PERCENT_IN_BAND_WIDTH),
MAX(OUT_SPEED),SUM(OUT_SPEED),COUNT(OUT_SPEED),MAX(PERCENT_OUT_BAND_WIDTH),SUM(PERCENT_OUT_BAND_WIDTH),COUNT(PERCENT_OUT_BAND_WIDTH),
COUNT(1),GREATEST(NVL(MAX(IN_SPEED),0),NVL(MAX(OUT_SPEED),0)),'||t_DATE_C||',SUM(TIME_INCREMENTS),
TO_DATE(SUBSTR(MAX(TO_CHAR(IN_SPEED,''FM0000000000.0000'')||TO_CHAR(A.COLLECT_TIME,''YYYY-MM-DD HH24:MI:SS'')),-19),''YYYY-MM-DD HH24:MI:SS''),
TO_DATE(SUBSTR(MAX(TO_CHAR(OUT_SPEED,''FM0000000000.0000'')||TO_CHAR(A.COLLECT_TIME,''YYYY-MM-DD HH24:MI:SS'')),-19),''YYYY-MM-DD HH24:MI:SS'')
FROM IAM.T_PERF_PORT_FLOW_5MIN A
WHERE EXECUTE_FLAG=1 AND PERCENT_IN_BAND_WIDTH<100 AND PERCENT_OUT_BAND_WIDTH<100
AND IN_SPEED>=0 AND OUT_SPEED>=0 AND TIME_INCREMENTS>=20 AND TIME_INCREMENTS<900
AND COLLECT_TIME>=TO_DATE('''||TO_CHAR(t_DATE,'YYYY-MM-DD')||''',''YYYY-MM-DD'')
AND COLLECT_TIME< TO_DATE('''||TO_CHAR(t_DATE+1,'YYYY-MM-DD')||''',''YYYY-MM-DD'')
GROUP BY A.DEV_ID,A.AREA_ID,A.DEV_TYPE_ID,A.DEV_SUB_TYPE_ID,A.PORT_ID,A.PORT_CLASS,A.IF_POSITION,A.IF_INDEX
uj5u.com熱心網友回復：
分批插入，比如插入100條資料，就commit一下uj5u.com熱心網友回復：
insert十幾w資料不是問題，問題應該不在insert上，應該在后面那個select出現了性能問題，可以看看這個陳述句的執行計劃，另外，就是有insert表上有鎖，兩種情況都看看吧。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/11573.html
標籤：基礎和管理