Following OEM alerts are received from databases of 11R2 to 12R2:
... .. . Target name=RACTEST Incident creation time=28/11/2017 9:48:17 PM AEDT Last updated time=28/11/2017 9:48:17 PM AEDT Message= ORA-01013: user requested cancel of current operation Severity=Critical Incident ID=294770 ... .. .
CAUSES
SQL ID 4d43by1zzjfna runs slowly until failed with ‘ORA-01013’ error.
SOLUTION
As per “low Performance Oracle Enterprise Manager SQL Metrics SQL statement (SQL ID: 4d43by1zzjfna) Automatically Run (Doc ID 2266608.1)”, the fololowing actions are taken :
(a) Stopping EM agent monitoring the RAC instance(s).
$AGENT_HOME/bin/emctl stop agent
(b) Test the current execution time ( 36 s ), and record the execution plan via SQL PLUS:
SQL> set timing on SQL> set autotrace on SQL> WITH last_run AS ( SELECT all_runs.OWNER , all_runs.JOB_NAME, all_runs.STATUS FROM DBA_SCHEDULER_JOB_RUN_DETAILS all_runs, ( SELECT OWNER , JOB_NAME, MAX(ACTUAL_START_DATE) AS START_DATE FROM DBA_SCHEDULER_JOB_RUN_DETAILS GROUP BY OWNER, JOB_NAME ) latest_runs WHERE all_runs.OWNER = latest_runs.OWNER AND all_runs.JOB_NAME = latest_runs.JOB_NAME AND all_runs.ACTUAL_START_DATE=latest_runs.START_DATE ... .. . SUM(BROKEN) SUM(FAILED) ----------- ----------- 0 0 Elapsed: 00:00:36.49 Execution Plan ---------------------------------------------------------- Plan hash value: 204035645 ... .. . Statistics ---------------------------------------------------------- 338 recursive calls 0 db block gets 58842 consistent gets 0 physical reads 0 redo size 603 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 8671 sorts (memory) 0 sorts (disk) 1 rows processed
c)Purge any job history that is no longer needed to reduce the size of job related tables:
SQL> exec DBMS_SCHEDULER.PURGE_LOG(7,which_log=>'JOB_LOG');
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.95
SQL> exec DBMS_SCHEDULER.PURGE_LOG(7,which_log=>'WINDOW_LOG');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
SQL> exec DBMS_SCHEDULER.PURGE_LOG(log_history => 7, job_name => 'job1, sys.class2');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','7');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
d) Regather statistics to ensure that the CBO has the right understanding of cardinality/cost for execution plans involving JOB related tables:
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
PL/SQL procedure successfully completed.
Elapsed: 00:01:26.37
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
Elapsed: 00:01:35.82
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
PL/SQL procedure successfully completed.
Elapsed: 00:10:53.24
e) rerun the same sql_id=’4d43by1zzjfna’. We can see there is a new execution plan. The running time is shorted from 36 seconds from 1 second.
SQL> WITH last_run AS 2 ( 3 SELECT all_runs.OWNER , 4 all_runs.JOB_NAME, 5 all_runs.STATUS 6 FROM DBA_SCHEDULER_JOB_RUN_DETAILS all_runs, 7 ( 8 SELECT OWNER , 9 JOB_NAME, 10 MAX(ACTUAL_START_DATE) AS START_DATE 11 FROM DBA_SCHEDULER_JOB_RUN_DETAILS 12 GROUP BY OWNER, 13 JOB_NAME 14 ) 15 latest_runs 16 WHERE all_runs.OWNER = latest_runs.OWNER 17 AND all_runs.JOB_NAME = latest_runs.JOB_NAME 18 AND all_runs.ACTUAL_START_DATE=latest_runs.START_DATE 19 ) 20 SELECT SUM(broken), 21 SUM(failed) 22 FROM 23 ( 24 SELECT DECODE(broken, 'N', 0, 1) broken, 25 DECODE(NVL(failures,0), 0, 0, 1) failed 26 FROM dba_jobs 27 UNION ALL 28 SELECT DECODE(STATE,'BROKEN',1,0) broken, 29 DECODE(STATUS ,'FAILED',DECODE(STATE,'BROKEN',0,'DISABLED',0,1),0) failed 30 FROM 31 ( 32 SELECT all_jobs.OWNER , 33 all_jobs.JOB_NAME, 34 last_run.STATUS , 35 all_jobs.STATE 36 FROM last_run, 37 DBA_SCHEDULER_JOBS all_jobs 38 WHERE last_run.OWNER =all_jobs.OWNER 39 AND last_run.JOB_NAME=all_jobs.JOB_NAME 40 ) 41 ) 42 / SUM(BROKEN) SUM(FAILED) ----------- ----------- 0 0 Elapsed: 00:00:01.00 Execution Plan ---------------------------------------------------------- Plan hash value: 2148836375 ... .. . Statistics ---------------------------------------------------------- 50 recursive calls 0 db block gets 3585 consistent gets 0 physical reads 0 redo size 603 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 51 sorts (memory) 0 sorts (disk) 1 rows processed
(f) Re-enable EM Agents monitoring the RAC instance(s):
$AGENT_HOME/bin/emctl start agent
Make DBA Life Easy 