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