Good Morning.
I am working in oracle 11g R2.
I have following query takes more than 20 mins to display results.
Taskid,snapid, timings,findings from addm,percentage of impact, count of occurrence of problem,message, action message using addm dictionary views
SELECT Y_TASK_ID,
Y_SNAP_ID,
y_BEGIN_INTERVAL_TIME,
"Finding",
"Finding Type",
"PERC_IMPACT",
"Impact ",
"Occurances",
"x_message",
"x_impact",
"x_action_message"
FROM ( SELECT y.task_id Y_TASK_ID,
y.snap_id Y_SNAP_ID,
y.BEGIN_INTERVAL_TIME y_BEGIN_INTERVAL_TIME,
f.finding_name AS "Finding",
f.TYPE AS "Finding Type",
x.PERC_IMPACT AS "PERC_IMPACT",
f.impact AS "Impact ",
history.finding_count AS "Occurances",
f.MESSAGE AS "Message",
x.impact "x_impact",
x.action_message "x_action_message",
x.MESSAGE "x_message"
FROM (SELECT /*+ NO_MERGE */
f.finding_id,
f.finding_name,
f.impact,
f.TYPE,
f.MESSAGE
FROM dba_addm_findings f, dba_addm_tasks t
WHERE f.parent = 0
AND f.task_id = t.task_id
AND SUBSTR (t.task_name, 1, 4) = 'ADDM') f,
( SELECT /*+ NO_MERGE */
finding_name, COUNT (task_id) AS finding_count
FROM (SELECT /*+ NO_MERGE */
task_id, finding_name
FROM dba_advisor_findings
WHERE parent = 0
AND task_id IN (SELECT t.task_id
FROM DBA_ADDM_TASKS t,
dba_addm_tasks fixed,
DBA_ADDM_INSTANCES i,
dba_addm_tasks ta
WHERE t.end_time >
fixed.end_time
- 7
AND t.end_time <=
fixed.end_time
AND t.how_created =
'AUTO'
AND t.REQUESTED_ANALYSIS =
'INSTANCE'
AND t.task_id =
i.task_id
AND fixed.task_id =
ta.task_id
AND SUBSTR (
ta.task_name,
1,
4) = 'ADDM'))
GROUP BY finding_name) history,
( SELECT /*+ NO_MERGE */
a.execution_end,
b.TYPE,
a.task_id TASK_ID,
b.impact AS impact,
d.RANK,
d.TYPE,
e.PERC_IMPACT PERC_IMPACT,
'Message : ' || b.MESSAGE MESSAGE,
'Command To correct: ' || c.command COMMAND,
'Action Message : ' || c.MESSAGE ACTION_MESSAGE
FROM dba_advisor_tasks a,
dba_advisor_findings b,
Dba_advisor_actions c,
dba_advisor_recommendations d,
dba_advisor_fdg_breakdown e
WHERE a.EXECUTION_END BETWEEN SYSDATE - 7 AND SYSDATE
AND a.owner = b.owner
AND a.task_id = b.task_id
AND e.task_id = b.task_id
AND b.task_id = d.task_id
AND b.finding_id = d.finding_id
AND a.task_id = c.task_id
AND d.rec_id = c.rec_Id
AND a.task_name LIKE 'ADDM%'
AND a.status = 'COMPLETED'
ORDER BY b.impact, d.RANK) x,
(SELECT DISTINCT
b.task_id TASK_ID,
A.SNAP_ID SNAP_ID,
A.BEGIN_INTERVAL_TIME BEGIN_INTERVAL_TIME
FROM dba_hist_snapshot a,
dba_advisor_tasks b,
dba_advisor_log l
WHERE a.begin_interval_time > SYSDATE - 7
AND a.dbid = (SELECT dbid FROM v$database)
AND a.INSTANCE_NUMBER =
(SELECT INSTANCE_NUMBER FROM v$instance)
AND TO_CHAR (a.begin_interval_time, 'yyyymmddHH24') =
TO_CHAR (b.created, 'yyyymmddHH24')
AND b.advisor_name = 'ADDM'
AND b.task_id = l.task_id
AND l.status = 'COMPLETED') y
WHERE f.finding_name = history.finding_name(+)
AND F.TYPE IN 'PROBLEM'
AND f.impact = x.impact
AND x.task_id = y.task_id
ORDER BY "PERC_IMPACT" DESC)
Explain plan for the above code is as follows.
Plan hash value: 123456879
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1003 | 7928K| | 34735 (1)| 10:45:21 |
| 1 | SORT ORDER BY | | 1003 | 7928K| 8032K| 34735 (1)| 10:45:21 |
|* 2 | HASH JOIN OUTER | | 1003 | 7928K| | 33305 (1)| 10:18:47 |
|* 3 | HASH JOIN | | 1 | 6080 | | 31445 (1)| 09:44:13 |
| 4 | VIEW | | 323 | 637K| | 1082 (1)| 00:20:07 |
| 5 | NESTED LOOPS | | 323 | 42313 | | 1082 (1)| 00:20:07 |
|* 6 | HASH JOIN | | 323 | 39729 | | 759 (1)| 00:14:07 |
|* 7 | HASH JOIN | | 323 | 37145 | | 657 (1)| 00:12:12 |
|* 8 | FILTER | | | | | | |
|* 9 | HASH JOIN OUTER | | 310 | 24490 | | 207 (1)| 00:03:51 |
|* 10 | HASH JOIN | | 310 | 17050 | | 172 (1)| 00:03:12 |
|* 11 | TABLE ACCESS FULL | WRI$_ADV_TASKS | 310 | 15500 | | 102 (0)| 00:01:54 |
| 12 | TABLE ACCESS FULL | WRI$_ADV_ADDM_TASKS | 31015 | 151K| | 69 (0)| 00:01:17 |
| 13 | TABLE ACCESS FULL | WRI$_ADV_EXECUTIONS | 31025 | 727K| | 35 (0)| 00:00:40 |
|* 14 | TABLE ACCESS FULL | WRI$_ADV_FINDINGS | 2460 | 88560 | | 449 (0)| 00:08:21 |
|* 15 | TABLE ACCESS FULL | WRI$_ADV_TASKS | 31022 | 242K| | 102 (0)| 00:01:54 |
|* 16 | INDEX UNIQUE SCAN | SYS_C00235007 | 1 | 8 | | 1 (0)| 00:00:02 |
|* 17 | HASH JOIN | | 324 | 1284K| | 30362 (1)| 09:24:06 |
| 18 | VIEW | | 9 | 279 | | 154 (1)| 00:02:53 |
| 19 | HASH UNIQUE | | 9 | 1008 | | 154 (1)| 00:02:53 |
|* 20 | FILTER | | | | | | |
| 21 | NESTED LOOPS OUTER | | 9 | 1008 | | 153 (0)| 00:02:52 |
| 22 | NESTED LOOPS OUTER | | 9 | 792 | | 135 (0)| 00:02:32 |
|* 23 | HASH JOIN | | 9 | 603 | | 118 (1)| 00:02:11 |
|* 24 | TABLE ACCESS BY INDEX ROWID| WRM$_SNAPSHOT | 1 | 27 | | 15 (0)| 00:00:17 |
|* 25 | INDEX RANGE SCAN | WRM$_SNAPSHOT_PK | 13 | | | 1 (0)| 00:00:02 |
| 26 | MERGE JOIN CARTESIAN | | 1 | 16 | | 0 (0)| 00:00:01 |
| 27 | MERGE JOIN CARTESIAN | | 1 | 6 | | 0 (0)| 00:00:01 |
|* 28 | FIXED TABLE FULL | X$KSUXSINST | 1 | 6 | | 0 (0)| 00:00:01 |
| 29 | BUFFER SORT | | 1 | | | 0 (0)| 00:00:01 |
| 30 | FIXED TABLE FULL | X$QUIESCE | 1 | | | 0 (0)| 00:00:01 |
| 31 | BUFFER SORT | | 1 | 10 | | 0 (0)| 00:00:01 |
|* 32 | FIXED TABLE FULL | X$KVIT | 1 | 10 | | 0 (0)| 00:00:01 |
| 33 | MERGE JOIN CARTESIAN | | 10000 | 97K| | 0 (0)| 00:00:01 |
|* 34 | FIXED TABLE FULL | X$KCCDI | 100 | 1000 | | 0 (0)| 00:00:01 |
| 35 | BUFFER SORT | | 100 | | | 0 (0)| 00:00:01 |
| 36 | FIXED TABLE FULL | X$KCCDI2 | 100 | | | 0 (0)| 00:00:01 |
|* 37 | TABLE ACCESS FULL | WRI$_ADV_TASKS | 31011 | 1211K| | 102 (0)| 00:01:54 |
|* 38 | TABLE ACCESS BY INDEX ROWID | WRI$_ADV_EXECUTIONS | 1 | 21 | | 2 (0)| 00:00:03 |
|* 39 | INDEX UNIQUE SCAN | WRI$_ADV_EXECS_PK | 1 | | | 1 (0)| 00:00:02 |
|* 40 | TABLE ACCESS BY INDEX ROWID | WRI$_ADV_EXECUTIONS | 1 | 24 | | 2 (0)| 00:00:03 |
|* 41 | INDEX UNIQUE SCAN | WRI$_ADV_EXECS_PK | 1 | | | 1 (0)| 00:00:02 |
| 42 | VIEW | | 1118K| 4296M| | 30207 (1)| 09:21:13 |
|* 43 | FILTER | | | | | | |
|* 44 | HASH JOIN | | 1118K| 230M| | 30207 (1)| 09:21:13 |
|* 45 | TABLE ACCESS FULL | WRI$_ADV_TASKS | 31022 | 242K| | 102 (0)| 00:01:54 |
|* 46 | HASH JOIN | | 1118K| 221M| | 30104 (1)| 09:19:19 |
|* 47 | TABLE ACCESS FULL | WRI$_ADV_TASKS | 31022 | 242K| | 102 (0)| 00:01:54 |
|* 48 | HASH JOIN | | 1118K| 213M| | 30002 (1)| 09:17:25 |
|* 49 | TABLE ACCESS FULL | WRI$_ADV_TASKS | 31022 | 393K| | 102 (0)| 00:01:54 |
|* 50 | HASH JOIN | | 1119K| 199M| 7616K| 29899 (1)| 09:15:30 |
| 51 | TABLE ACCESS FULL | WRI$_ADV_FINDINGS | 389K| 3045K| | 449 (0)| 00:08:21 |
|* 52 | HASH JOIN | | 1119K| 191M| | 26073 (1)| 08:04:25 |
| 53 | TABLE ACCESS FULL | WRI$_ADV_INST_FDG | 65339 | 765K| | 77 (0)| 00:01:26 |
|* 54 | HASH JOIN | | 490K| 78M| 12M| 25995 (1)| 08:02:58 |
| 55 | TABLE ACCESS FULL | WRI$_ADV_RECOMMENDATIONS | 517K| 7078K| | 508 (0)| 00:09:27 |
|* 56 | HASH JOIN | | 8000K| 1167M| 13M| 5163 (1)| 01:35:56 |
| 57 | TABLE ACCESS FULL | WRI$_ADV_FINDINGS | 389K| 9137K| | 449 (0)| 00:08:21 |
|* 58 | HASH JOIN | | 634K| 78M| | 3136 (1)| 00:58:16 |
| 59 | JOIN FILTER CREATE | :BF0000 | 31022 | 333K| | 102 (0)| 00:01:54 |
|* 60 | TABLE ACCESS FULL | WRI$_ADV_TASKS | 31022 | 333K| | 102 (0)| 00:01:54 |
|* 61 | FILTER | | | | | | |
| 62 | JOIN FILTER USE | :BF0000 | 634K| 71M| | 3033 (1)| 00:56:22 |
|* 63 | HASH JOIN RIGHT OUTER| | 634K| 71M| | 3033 (1)| 00:56:22 |
| 64 | TABLE ACCESS FULL | WRI$_ADV_EXECUTIONS | 31025 | 878K| | 35 (0)| 00:00:40 |
|* 65 | HASH JOIN | | 634K| 53M| 2056K| 2998 (1)| 00:55:42 |
|* 66 | TABLE ACCESS FULL | WRI$_ADV_TASKS | 30900 | 1689K| | 102 (0)| 00:01:54 |
|* 67 | HASH JOIN | | 634K| 19M| 13M| 2427 (1)| 00:45:06 |
| 68 | TABLE ACCESS FULL | WRI$_ADV_REC_ACTIONS | 634K| 6812K| | 170 (0)| 00:03:10 |
|* 69 | HASH JOIN | | 634K| 13M| | 1705 (1)| 00:31:41 |
| 70 | FIXED TABLE FULL | X$KEACMDN | 48 | 144 | | 0 (0)| 00:00:01 |
| 71 | TABLE ACCESS FULL| WRI$_ADV_ACTIONS | 634K| 11M| | 1704 (0)| 00:31:40 |
| 72 | VIEW | | 245K| 472M| | 1860 (1)| 00:34:34 |
| 73 | HASH GROUP BY | | 245K| 472M| | 1860 (1)| 00:34:34 |
| 74 | VIEW | | 245K| 472M| | 1859 (1)| 00:34:33 |
|* 75 | HASH JOIN | | 245K| 9128K| | 1859 (1)| 00:34:33 |
|* 76 | TABLE ACCESS FULL | WRI$_ADV_TASKS | 31022 | 242K| | 102 (0)| 00:01:54 |
|* 77 | HASH JOIN SEMI | | 245K| 7206K| 6968K| 1757 (1)| 00:32:39 |
| 78 | JOIN FILTER CREATE | :BF0001 | 246K| 4084K| | 449 (0)| 00:08:21 |
|* 79 | TABLE ACCESS FULL | WRI$_ADV_FINDINGS | 246K| 4084K| | 449 (0)| 00:08:21 |
| 80 | VIEW | VW_NSO_1 | 549K| 6980K| | 988 (1)| 00:18:22 |
|* 81 | FILTER | | | | | | |
| 82 | JOIN FILTER USE | :BF0001 | 549K| 104M| | 988 (1)| 00:18:22 |
|* 83 | HASH JOIN RIGHT OUTER | | 549K| 104M| | 988 (1)| 00:18:22 |
| 84 | TABLE ACCESS FULL | WRI$_ADV_EXECUTIONS | 31025 | 727K| | 35 (0)| 00:00:40 |
|* 85 | HASH JOIN | | 549K| 91M| 2208K| 953 (1)| 00:17:42 |
| 86 | TABLE ACCESS FULL | WRI$_ADV_ADDM_INST | 132K| 648K| | 145 (0)| 00:02:42 |
|* 87 | HASH JOIN | | 128K| 20M| | 416 (1)| 00:07:44 |
|* 88 | TABLE ACCESS FULL | WRI$_ADV_TASKS | 31011 | 938K| | 102 (0)| 00:01:54 |
| 89 | MERGE JOIN | | 128K| 17M| | 314 (2)| 00:05:50 |
| 90 | SORT JOIN | | 310 | 35340 | | 244 (2)| 00:04:32 |
|* 91 | FILTER | | | | | | |
|* 92 | HASH JOIN OUTER | | 310 | 35340 | | 243 (1)| 00:04:31 |
|* 93 | FILTER | | | | | | |
|* 94 | HASH JOIN OUTER | | 310 | 27900 | | 207 (1)| 00:03:51 |
|* 95 | HASH JOIN | | 310 | 20460 | | 172 (1)| 00:03:12 |
|* 96 | TABLE ACCESS FULL| WRI$_ADV_TASKS | 310 | 15500 | | 102 (0)| 00:01:54 |
| 97 | TABLE ACCESS FULL| WRI$_ADV_ADDM_TASKS | 31015 | 484K| | 69 (0)| 00:01:17 |
| 98 | TABLE ACCESS FULL | WRI$_ADV_EXECUTIONS | 31025 | 727K| | 35 (0)| 00:00:40 |
| 99 | TABLE ACCESS FULL | WRI$_ADV_EXECUTIONS | 31025 | 727K| | 35 (0)| 00:00:40 |
|*100 | FILTER | | | | | | |
|*101 | SORT JOIN | | 16548 | 404K| | 70 (2)| 00:01:19 |
|*102 | TABLE ACCESS FULL | WRI$_ADV_ADDM_TASKS | 16548 | 404K| | 69 (0)| 00:01:17 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("F"."FINDING_NAME"="HISTORY"."FINDING_NAME"(+))
3 - access("F"."IMPACT"="X"."IMPACT")
6 - access("A"."TASK_ID"="B"."ID")
7 - access("A"."TASK_ID"="A"."ID")
8 - filter(DECODE(NVL("E"."STATUS","A"."STATUS"),1,'INITIAL',2,'EXECUTING',3,'COMPLETED',4,'INTERRUPTED',5,'CAN
CELLED',6,'FATAL ERROR')='COMPLETED')
9 - access("A"."ID"="E"."TASK_ID"(+) AND "A"."ADVISOR_ID"="E"."ADVISOR_ID"(+) AND
"A"."LAST_EXEC_NAME"="E"."NAME"(+))
10 - access("A"."ID"="A"."TASK_ID")
11 - filter(SUBSTR("A"."NAME",1,4)='ADDM' AND BITAND("A"."PROPERTY",6)=4)
14 - filter("A"."PARENT"=0 AND DECODE("A"."TYPE",1,'PROBLEM',2,'SYMPTOM',3,'ERROR',4,'INFORMATION',5,'WARNING')=
'PROBLEM')
15 - filter(BITAND("B"."PROPERTY",6)=4)
16 - access("A"."TASK_ID"="A"."TASK_ID" AND "A"."ID"="A"."FINDING_ID")
17 - access("X"."TASK_ID"="Y"."TASK_ID")
20 - filter(DECODE(NVL("E"."STATUS","STATUS"),1,'INITIAL',2,'EXECUTING',3,'COMPLETED',4,'INTERRUPTED',5,'CANCELL
ED',6,'FATAL ERROR')='COMPLETED')
23 - access(TO_CHAR(INTERNAL_FUNCTION("BEGIN_INTERVAL_TIME"),'yyyymmddHH24')=TO_CHAR(INTERNAL_FUNCTION("A"."CTIM
E"),'yyyymmddHH24'))
24 - filter("STATUS"=0 AND "BEGIN_INTERVAL_TIME">SYSDATE@!-7)
25 - access("DBID"= (SELECT "DI"."DIDBI" FROM SYS."X$KCCDI2" "DI2",SYS."X$KCCDI" "DI" WHERE
"DI"."INST_ID"=USERENV('INSTANCE')) AND "INSTANCE_NUMBER"= (SELECT "KSUXSINS" FROM SYS."X$QUIESCE"
"QU",SYS."X$KVIT" "KV",SYS."X$KSUXSINST" "KS" WHERE "KS"."INST_ID"=USERENV('INSTANCE') AND "KVITTAG"='kcbwst'))
filter("INSTANCE_NUMBER"= (SELECT "KSUXSINS" FROM SYS."X$QUIESCE" "QU",SYS."X$KVIT" "KV",SYS."X$KSUXSINST"
"KS" WHERE "KS"."INST_ID"=USERENV('INSTANCE') AND "KVITTAG"='kcbwst'))
28 - filter("KS"."INST_ID"=USERENV('INSTANCE'))
32 - filter("KVITTAG"='kcbwst')
34 - filter("DI"."INST_ID"=USERENV('INSTANCE'))
37 - filter("A"."ADVISOR_NAME"='ADDM' AND BITAND("PROPERTY",6)=4)
38 - filter("A"."ADVISOR_ID"="E"."ADVISOR_ID"(+))
39 - access("A"."ID"="E"."TASK_ID"(+) AND "A"."LAST_EXEC_NAME"="E"."NAME"(+))
40 - filter("ADVISOR_ID"="E"."ADVISOR_ID"(+))
41 - access("A"."ID"="E"."TASK_ID"(+) AND "LAST_EXEC_NAME"="E"."NAME"(+))
43 - filter(SYSDATE@!-7<=SYSDATE@!)
44 - access("A"."TASK_ID"="B"."ID")
45 - filter(BITAND("B"."PROPERTY",6)=4)
46 - access("A"."TASK_ID"="B"."ID")
47 - filter(BITAND("B"."PROPERTY",6)=4)
48 - access("A"."OWNER_NAME"="B"."OWNER_NAME" AND "A"."TASK_ID"="B"."ID")
49 - filter(BITAND("B"."PROPERTY",6)=4)
50 - access("A"."TASK_ID"="A"."TASK_ID" AND "A"."FINDING_ID"="A"."ID")
52 - access("A"."TASK_ID"="A"."TASK_ID")
54 - access("A"."TASK_ID"="A"."TASK_ID" AND "A"."ID"="A"."FINDING_ID" AND "A"."ID"="D"."REC_ID")
56 - access("A"."ID"="A"."TASK_ID")
58 - access("A"."TASK_ID"="B"."ID")
filter("B"."ADVISOR_ID"=2 AND BITAND("A"."FLAGS",2048)=0 OR "B"."ADVISOR_ID"<>2)
60 - filter(BITAND("B"."PROPERTY",6)=4)
61 - filter(NVL("E"."EXEC_END","A"."EXEC_END")>=SYSDATE@!-7 AND NVL("E"."EXEC_END","A"."EXEC_END")<=SYSDATE@!
AND DECODE(NVL("E"."STATUS","A"."STATUS"),1,'INITIAL',2,'EXECUTING',3,'COMPLETED',4,'INTERRUPTED',5,'CANCELLED',6,
'FATAL ERROR')='COMPLETED')
63 - access("A"."ID"="E"."TASK_ID"(+) AND "A"."ADVISOR_ID"="E"."ADVISOR_ID"(+) AND
"A"."LAST_EXEC_NAME"="E"."NAME"(+))
65 - access("A"."ID"="A"."TASK_ID")
66 - filter("A"."NAME" LIKE 'ADDM%' AND BITAND("A"."PROPERTY",6)=4)
67 - access("D"."TASK_ID"="A"."TASK_ID" AND "D"."ACT_ID"="A"."ID")
69 - access("A"."COMMAND"="C"."INDX")
75 - access("A"."TASK_ID"="B"."ID")
76 - filter(BITAND("B"."PROPERTY",6)=4)
77 - access("A"."TASK_ID"="TASK_ID")
79 - filter("A"."PARENT"=0)
81 - filter(DECODE(NVL("E"."STATUS","A"."STATUS"),1,'INITIAL',2,'EXECUTING',3,'COMPLETED',4,'INTERRUPTED',5,'CAN
CELLED',6,'FATAL ERROR')='COMPLETED')
83 - access("A"."ID"="E"."TASK_ID"(+) AND "A"."ADVISOR_ID"="E"."ADVISOR_ID"(+) AND
"A"."LAST_EXEC_NAME"="E"."NAME"(+))
85 - access("A"."ID"="A"."TASK_ID")
87 - access("A"."ID"="A"."TASK_ID")
88 - filter("A"."HOW_CREATED"='AUTO' AND BITAND("A"."PROPERTY",6)=4)
91 - filter(DECODE(NVL("E"."STATUS","STATUS"),1,'INITIAL',2,'EXECUTING',3,'COMPLETED',4,'INTERRUPTED',5,'CANCELL
ED',6,'FATAL ERROR')='COMPLETED')
92 - access("A"."ID"="E"."TASK_ID"(+) AND "ADVISOR_ID"="E"."ADVISOR_ID"(+) AND "LAST_EXEC_NAME"="E"."NAME"(+))
93 - filter(DECODE(NVL("E"."STATUS","A"."STATUS"),1,'INITIAL',2,'EXECUTING',3,'COMPLETED',4,'INTERRUPTED',5,'CAN
CELLED',6,'FATAL ERROR')='COMPLETED')
94 - access("A"."ID"="E"."TASK_ID"(+) AND "A"."ADVISOR_ID"="E"."ADVISOR_ID"(+) AND
"A"."LAST_EXEC_NAME"="E"."NAME"(+))
95 - access("A"."ID"="A"."TASK_ID")
96 - filter(SUBSTR("NAME",1,4)='ADDM' AND BITAND("PROPERTY",6)=4)
100 - filter("A"."END_TIME"<="END_TIME")
101 - access("A"."END_TIME">INTERNAL_FUNCTION("END_TIME")-7)
filter("A"."END_TIME">INTERNAL_FUNCTION("END_TIME")-7)
102 - filter("A"."REQUESTED_ANALYSIS"='INSTANCE')
193 rows selected.
Kindly let me know if you need more information to help.