Insert long time operation-CodePudding

Recently in use process run a SQL execution time is too long to suddenly ora - 01555, performed manually without doing more than four hours to stopped, haven't seen this kind of problem before, check the table in the table space, undo tablespace, temp is no problem, the select operation on 20 minutes alone can also be a result, the insert table data about 1600 W, insert data every day in about 15, 6 W, strives for the big support

 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) 
The SELECT/* + FULL (A) */a. d. EV_ID, A.A REA_ID, MAX (A.E MS_ID), a. d. EV_TYPE_ID, a. d. EV_SUB_TYPE_ID, Amy polumbo ORT_ID, Amy polumbo ORT_CLASS, 
Anderson F_POSITION, Anderson F_INDEX, MAX (Anderson F_NAME), MAX (Anderson F_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) the GREATEST (NVL (MAX (IN_SPEED), 0), NVL (MAX (OUT_SPEED), 0)), '| | t_DATE_C | |', the SUM (TIME_INCREMENTS), 
TO_DATE (SUBSTR (MAX (TO_CHAR (IN_SPEED, 'FM0000000000.0000 ") | | TO_CHAR (A.C OLLECT_TIME, "' - DD YYYY - MM HH24: MI: SS ')), and 19th), "' - DD YYYY - MM HH24: MI: SS '), 
TO_DATE (SUBSTR (MAX (TO_CHAR (OUT_SPEED, 'FM0000000000.0000 ") | | TO_CHAR (A.C OLLECT_TIME, "' - DD YYYY - MM HH24: MI: SS ')), and 19th), "' - DD YYYY - MM HH24: MI: SS ') 
The FROM IAM. T_PERF_PORT_FLOW_5MIN A 
WHERE EXECUTE_FLAG=1 AND PERCENT_IN_BAND_WIDTH & lt; 100 AND PERCENT_OUT_BAND_WIDTH & lt; 100 
AND IN_SPEED & gt;=0 AND OUT_SPEED & gt;=0 AND TIME_INCREMENTS & gt;=20 AND TIME_INCREMENTS & lt; 900 
AND COLLECT_TIME & gt;=TO_DATE (" '| | TO_CHAR (t_DATE,' YYYY - MM - DD) | | ' ' ', '- DD YYYY - MM') 
AND COLLECT_TIME & lt; TO_DATE (" '| | TO_CHAR (t_DATE + 1,' YYYY - MM - DD) | | ' ' ', '- DD YYYY - MM') 
GROUP BY a. d. EV_ID, A.A REA_ID, a. d. EV_TYPE_ID, a. d. EV_SUB_TYPE_ID, Amy polumbo ORT_ID, Amy polumbo ORT_CLASS, Anderson F_POSITION, Anderson F_INDEX

CodePudding user response:

Partial insert, insert 100 data, for example, a commit

CodePudding user response:

Insert a dozen w data is not a problem, the problem should be not on the insert, should be in the back that the select performance problems, you can take a look at this statement execution plan, in addition, there is on the insert table locks, both and have a look,