Help with SQL query
- From: maheswara.rao@xxxxxxx
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 14 May 2008 16:08:08 -0400
Please help with the following query. This query is taking 200 minutes
to complete. Please help with suggestions for optimizing this query. I
am including the sql query and the plan.
Thank you for your help
Rao
= = = = Query = = =
SELECT t1."Group Name",
t1."Class Name",
t1."Alertgroup",
t1."Original Severity",
t1."Total Raw Events",
t1."Total Actionable Alerts",
t1."Total Acknowledged",
DECODE(t2."Generic (7mins)", NULL, 0, t2."Generic (7mins)") AS
"Generic (7mins)"
FROM (SELECT reporter.reporter_groups.NAME AS "Group Name",
reporter.reporter_classes.NAME AS "Class Name",
reporter.reporter_status.alertgroup AS "Alertgroup",
reporter.rep_severity_types.NAME AS "Original Severity",
SUM(reporter.reporter_status.tally) AS "Total Raw
Events",
COUNT(reporter.reporter_status.tally) AS "Total
Actionable Alerts",
SUM(reporter.reporter_status.acknowledged) AS "Total
Acknowledged"
FROM reporter.reporter_groups,
reporter.reporter_classes,
reporter.reporter_status,
reporter.rep_severity_types
WHERE
(reporter.reporter_status.CLASS = reporter.reporter_classes.CLASS
(+))
AND (reporter.reporter_status.originalseverity =
reporter.rep_severity_types.severity (+))
AND (reporter.reporter_status.ownergid =
reporter.reporter_groups.ownergid (+))
AND (reporter.reporter_groups.ownergid
IN (464, 239, 542, 100, 468, 8)
AND reporter.reporter_status.originalseverity
IN (1, 2, 3, 4, 5)
AND reporter.reporter_status.TYPE = 1
AND (reporter.reporter_status.firstoccurrence
BETWEEN TRUNC(SYSDATE - 7) AND TRUNC(SYSDATE)))
GROUP BY reporter.reporter_groups.NAME,
reporter.reporter_classes.NAME, reporter.reporter_status.alertgroup,
reporter.rep_severity_types.NAME) t1,
(SELECT reporter.reporter_groups.NAME AS "Group Name",
reporter.reporter_classes.NAME AS "Class Name",
reporter.reporter_status.alertgroup AS "Alertgroup",
reporter.rep_severity_types.NAME AS "Original Severity",
COUNT(reporter.reporter_status.tally) AS "Generic
(7mins)"
FROM reporter.reporter_groups,
reporter.reporter_classes,
reporter.reporter_status,
reporter.rep_severity_types
WHERE (reporter.reporter_status.CLASS =
reporter.reporter_classes.CLASS (+))
AND (reporter.reporter_status.originalseverity =
reporter.rep_severity_types.severity (+))
AND (reporter.reporter_status.ownergid =
reporter.reporter_groups.ownergid (+))
AND (reporter.reporter_groups.ownergid
IN (464, 239, 542, 100, 468, 8)
AND reporter.reporter_status.originalseverity
IN (1, 2, 3, 4, 5)
AND reporter.reporter_status.TYPE = 1
AND (reporter.reporter_status.firstoccurrence
BETWEEN TRUNC(SYSDATE - 7) AND TRUNC(SYSDATE))
AND reporter.reporter_status.expireid = 65533
AND reporter.reporter_status.acknowledged = 0
AND reporter.reporter_status.severity = 0
AND reporter.reporter_status.deletedat IS NOT NULL
AND (reporter.reporter_status.deletedat -
reporter.reporter_status.firstoccurrence) < 420 / 86400)
GROUP BY reporter.reporter_groups.NAME,
reporter.reporter_classes.NAME, reporter.reporter_status.alertgroup,
reporter.rep_severity_types.NAME) t2
WHERE t1."Group Name" = t2."Group Name" (+)
AND t1."Class Name" = t2."Class Name" (+)
AND t1."Alertgroup" = t2."Alertgroup" (+)
AND t1."Original Severity" = t2."Original Severity" (+)
ORDER BY t1."Group Name" ASC,
t1."Total Actionable Alerts" DESC
= = = = The plan = = = = =
SELECT STATEMENT CHOOSE
Cost : 1,967,940 Bytes : 33,323,136 Cardinality : 216,384
27 SORT ORDER BY
Cost : 1,967,940 Bytes : 33,323,136 Cardinality : 216,384
26 MERGE JOIN OUTER
Cost : 1,962,978 Bytes : 33,323,136 Cardinality : 216,384
11 VIEW REPORTER.
Cost : 983,227 Bytes : 19,474,560 Cardinality : 216,384
10 SORT GROUP BY Cost : 983,227 Bytes : 18,392,640 Cardinality : 216,384
9 FILTER TRUNC(SYSDATE@!-7)<=TRUNC(SYSDATE@!)
8 FILTER
"REPORTER_GROUPS"."OWNERGID"=8 OR
"REPORTER_GROUPS"."OWNERGID"=100 OR
"REPORTER_GROUPS"."OWNERGID"=239 OR
"REPORTER_GROUPS"."OWNERGID"=464 OR
"REPORTER_GROUPS"."OWNERGID"=468 OR
"REPORTER_GROUPS"."OWNERGID"=542
7 HASH JOIN OUTER
"REPORTER_STATUS"."OWNERGID"="REPORTER_GROUPS"."OWNERGID"(+)
5 HASH JOIN OUTER
"REPORTER_STATUS"."CLASS"="REPORTER_CLASSES"."CLASS"(+)
Cost : 980,102 Bytes : 14,064,960 Cardinality : 216,384
3 HASH JOIN OUTER
"REPORTER_STATUS"."ORIGINALSEVERITY"="REP_SEVERITY_TYPES"."SEVERITY"(+)
Cost : 979,902 Bytes : 10,386,432 Cardinality : 216,384
1 TABLE ACCESS FULL
REPORTER.REPORTER_STATUS
("REPORTER_STATUS"."ORIGINALSEVERITY"=1 OR
"REPORTER_STATUS"."ORIGINALSEVERITY"=2 OR
"REPORTER_STATUS"."ORIGINALSEVERITY"=3 OR
"REPORTER_STATUS"."ORIGINALSEVERITY"=4 OR
"REPORTER_STATUS"."ORIGINALSEVERITY"=5)AND
"REPORTER_STATUS"."TYPE"=1 AND
"REPORTER_STATUS"."FIRSTOCCURRENCE">=TRUNC(SYSDATE@!-7)
AND
"REPORTER_STATUS"."FIRSTOCCURRENCE"<=TRUNC(SYSDATE@!)
Cost : 979,741 Bytes : 7,789,824 Cardinality : 216,384
2 TABLE ACCESS FULL REPORTER.REP_SEVERITY_TYPES
Cost : 2 Bytes : 72 Cardinality : 6
4 TABLE ACCESS FULL REPORTER.REPORTER_CLASSES
Cost : 2 Bytes : 20,230 Cardinality : 1,190
6 TABLE ACCESS FULL REPORTER.REPORTER_GROUPS
Cost : 2 Bytes : 3,100 Cardinality : 155
25 SORT JOIN
"T1"."Group Name"="T2"."Group Name"(+) "T1"."Original
Severity"="T2"."Original Severity"(+) AND
"T1"."Alertgroup"="T2"."Alertgroup"(+) AND
"T1"."Class Name"="T2"."Class Name"(+) AND
"T1"."Group Name"="T2"."Group Name"(+)
Cost : 979,751 Bytes : 64 Cardinality : 1
24 VIEW REPORTER. Cost : 979,749 Bytes : 64 Cardinality : 1
23 SORT GROUP BY Cost : 979,749 Bytes : 98 Cardinality : 1
22 FILTER TRUNC(SYSDATE@!-7)<=TRUNC(SYSDATE@!)
21 NESTED LOOPS OUTER
Cost : 979,746 Bytes : 98 Cardinality : 1
18 FILTER "REPORTER_GROUPS"."OWNERGID"=8 OR
"REPORTER_GROUPS"."OWNERGID"=100 OR
"REPORTER_GROUPS"."OWNERGID"=239 OR
"REPORTER_GROUPS"."OWNERGID"=464 OR
"REPORTER_GROUPS"."OWNERGID"=468 OR
"REPORTER_GROUPS"."OWNERGID"=542
17 HASH JOIN OUTER
"REPORTER_STATUS"."OWNERGID"="REPORTER_GROUPS"."OWNERGID"(+)
15 NESTED LOOPS OUTER
Cost : 979,742 Bytes : 61 Cardinality : 1
12 TABLE ACCESS FULL
REPORTER.REPORTER_STATUS
("REPORTER_STATUS"."ORIGINALSEVERITY"=1 OR
"REPORTER_STATUS"."ORIGINALSEVERITY"=2 OR
"REPORTER_STATUS"."ORIGINALSEVERITY"=3 OR
"REPORTER_STATUS"."ORIGINALSEVERITY"=4 OR
"REPORTER_STATUS"."ORIGINALSEVERITY"=5) AND
"REPORTER_STATUS"."TYPE"=1 AND
"REPORTER_STATUS"."FIRSTOCCURRENCE">=TRUNC(SYSDATE@!-7) AND
"REPORTER_STATUS"."FIRSTOCCURRENCE"<=TRUNC(SYSDATE@!) AND
"REPORTER_STATUS"."EXPIREID"=65533
AND
"REPORTER_STATUS"."ACKNOWLEDGED"=0
AND
"REPORTER_STATUS"."SEVERITY"=0 AND
"REPORTER_STATUS"."DELETEDAT" IS NOT
NULL AND
"REPORTER_STATUS"."DELETEDAT"-"REPORTER_STATUS"."FIRSTOCCURRENCE"
Cost : 979,741 Bytes : 49 Cardinality : 1
14 TABLE ACCESS BY INDEX ROWID \
REPORTER.REP_SEVERITY_TYPES
Cost : 1 Bytes : 12 Cardinality : 1
13 INDEX UNIQUE SCAN
REPORTER.RST_INDEX_PK
"REPORTER_STATUS"."ORIGINALSEVERITY"="REP_SEVERITY_TYPES"."SEVERITY"(+)
Cardinality : 1
16 TABLE ACCESS FULL REPORTER.REPORTER_GROUPS
Cost : 2 Bytes : 3,100 Cardinality : 155
20 TABLE ACCESS BY INDEX ROWID
REPORTER.REPORTER_CLASSES
Cost : 1 Bytes : 17 Cardinality : 1
19 INDEX UNIQUE SCAN
REPORTER.RC_INDEX_PK
"REPORTER_STATUS"."CLASS"="REPORTER_CLASSES"."CLASS"(+)
Cardinality : 1
Please do not transmit orders or instructions regarding a UBS account by
e-mail. The information provided in this e-mail or any attachments is not an
official transaction confirmation or account statement. For your protection, do
not include account numbers, Social Security numbers, credit card numbers,
passwords or other non-public information in your e-mail. Because the
information contained in this message may be privileged, confidential,
proprietary or otherwise protected from disclosure, please notify us
immediately by replying to this message and deleting it from your computer if
you have received this communication in error. Thank you.
UBS Financial Services Inc.
UBS International Inc.
UBS Financial Services Incorporated of Puerto Rico
--
//www.freelists.org/webpage/oracle-l
Other related posts:
