| Sql query taking High CPU and execution time in oracle 11gR2 [message #573195] |
Mon, 24 December 2012 00:29  |
 |
pappu123
Messages: 7
Registered: December 2012
|
Junior Member |
|
|
Hi The below query is taking high CPU almost 98% and longer time to execute .. I am new to oracle. Please help me to tune this query.
SELECT ancestor,
Max(D.alarmstate) ALARMSTATE,
Max(D.sialarmstate) SIALARMSTATE,
Max(D.uncralarmstate) UNCRALARMSTATE,
Max(M.commstate) COMMSTATE,
Max(M.nncommstate) NNCOMMSTATE,
Max(M.servicestate) SERVICESTATE,
Max(M.abnormal) ABNORMAL,
CASE
WHEN Max(D.last_update) > Max(M.last_update) THEN Max(D.last_update)
ELSE Max(M.last_update)
END LUPDATE,
UN.username,
UN.sessionid,
UN.mapid,
UN.maprefid
FROM mapnode M,
mapnodedomain D,
node_v NV,
usernode UN,
(SELECT DISTINCT connect_by_root NA.mapnodeid ANCESTOR,
NA.mapnodeid CHILD,
connect_by_isleaf TYPE
FROM nodeaggregate NA
WHERE NA.viewtype = 2
START WITH NA.mapnodeid IN ((SELECT DISTINCT N.mapnodeid
FROM nodeaggregate N
WHERE N.viewtype = 2
START WITH N.mapnodeid IN (SELECT mapnodeid
FROM mapnode
WHERE
last_update > 1165500284
UNION
SELECT mapnodeid
FROM
mapnodedomain
WHERE
last_update > 1275809509)
CONNECT BY N.mapnodeid =
PRIOR N.parentid
AND N.viewtype =
PRIOR
N.viewtype)
INTERSECT
(SELECT DISTINCT mapnodeid
FROM usernode))
CONNECT BY PRIOR NA.mapnodeid = NA.parentid
AND PRIOR NA.viewtype = NA.viewtype)UNC
WHERE child = M.mapnodeid
AND child = D.mapnodeid
AND D.networkelementid = NV.handle
AND NV.username = UN.username
AND UN.mapnodeid = ancestor
GROUP BY ancestor,
UN.username,
UN.sessionid,
UN.mapid,
UN.maprefid
Thanks & Regards,
Pappu
[EDITED by LF: formatted code and applied [code] tags]
[Updated on: Wed, 26 December 2012 03:50] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Sql query taking High CPU and execution time in oracle 11gR2 [message #573198 is a reply to message #573197] |
Mon, 24 December 2012 01:01  |
|
pappu123
Messages: 7
Registered: December 2012
|
Junior Member |
|
|
Hi Rahul,
Please find the explain plan here ..
Plan hash value: 1378672608
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 173 | 12 (17)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 173 | 12 (17)| 00:00:01 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 173 | 11 (10)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 164 | 10 (10)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 155 | 9 (12)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 131 | 8 (13)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 124 | 7 (15)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 98 | 6 (17)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 87 | 5 (20)| 00:00:01 |
| 10 | VIEW | | 10 | 180 | 4 (25)| 00:00:01 |
| 11 | HASH UNIQUE | | 10 | 390 | 4 (25)| 00:00:01 |
|* 12 | FILTER | | | | | |
|* 13 | CONNECT BY NO FILTERING WITH SW (UNIQUE) | | | | | |
| 14 | INDEX FULL SCAN | NODEAGGREGATE_MPV | 4785 | 62205 | 3 (0)| 00:00:01 |
| 15 | INTERSECTION | | | | | |
| 16 | SORT UNIQUE | | 2690 | 102K| 4 (25)| 00:00:01 |
|* 17 | FILTER | | | | | |
|* 18 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | |
| 19 | INDEX FULL SCAN | NODEAGGREGATE_MPV | 4785 | 62205 | 3 (0)| 00:00:01 |
| 20 | SORT UNIQUE | | 2 | 24 | 4 (75)| 00:00:01 |
| 21 | UNION-ALL | | | | | |
|* 22 | TABLE ACCESS BY INDEX ROWID | MAPNODE | 1 | 12 | 1 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | MAPNODE_PKEY | 1 | | 1 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID | MAPNODEDOMAIN | 1 | 12 | 1 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | MAPNODEDOMAIN_MAPNODEID | 1 | | 1 (0)| 00:00:01 |
| 26 | SORT UNIQUE NOSORT | | 1 | 13 | 2 (50)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | USERNODE_MAPNODEID | 1 | 13 | 1 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID | USERNODE | 1 | 69 | 1 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | USERNODE_MAPNODEID | 1 | | 1 (0)| 00:00:01 |
|* 30 | INDEX FULL SCAN | ACTIVEDOMAINUSER_PKEY | 1 | 11 | 1 (0)| 00:00:01 |
| 31 | TABLE ACCESS BY INDEX ROWID | MAPNODEDOMAIN | 1 | 26 | 1 (0)| 00:00:01 |
|* 32 | INDEX RANGE SCAN | MAPNODEDOMAIN_MAPNODEID | 1 | | 1 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID | NODE | 1 | 7 | 1 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | NODE_PKEY | 1 | | 1 (0)| 00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | MAPNODE | 1 | 24 | 1 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | MAPNODE_PKEY | 1 | | 1 (0)| 00:00:01 |
| 37 | TABLE ACCESS BY INDEX ROWID | DOMAIN | 1 | 9 | 1 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | DOMAIN_PKEY | 1 | | 1 (0)| 00:00:01 |
|* 39 | INDEX UNIQUE SCAN | DOMAIN_PKEY | 1 | | 1 (0)| 00:00:01 |
|* 40 | TABLE ACCESS BY INDEX ROWID | DOMAIN | 1 | 9 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
12 - filter("NA"."VIEWTYPE"=2)
13 - access("NA"."PARENTID"=PRIOR "NA"."MAPNODEID" AND "NA"."VIEWTYPE"=PRIOR "NA"."VIEWTYPE")
filter( EXISTS ( (SELECT DISTINCT "N"."MAPNODEID" FROM "NODEAGGREGATE" "SYS_ALIAS_1" WHERE "N"."MAPNODEID"=:B1 AND
"N"."VIEWTYPE"=2 START WITH EXISTS ( (SELECT "MAPNODEID" FROM "MAPNODE" "MAPNODE" WHERE "MAPNODEID"=:B2 AND
"LAST_UPDATE">1165500284)UNION (SELECT "MAPNODEID" FROM "MAPNODEDOMAIN" "MAPNODEDOMAIN" WHERE "MAPNODEID"=:B3 AND
"LAST_UPDATE">1275809509)) CONNECT BY "N"."MAPNODEID"=PRIOR "N"."PARENTID" AND "N"."VIEWTYPE"=PRIOR
"N"."VIEWTYPE")INTERSECT (SELECT DISTINCT "MAPNODEID" FROM "USERNODE" "USERNODE" WHERE "MAPNODEID"=:B4)))
17 - filter("N"."MAPNODEID"=:B1 AND "N"."VIEWTYPE"=2)
18 - access("N"."MAPNODEID"=PRIOR "N"."PARENTID" AND "N"."VIEWTYPE"=PRIOR "N"."VIEWTYPE")
filter( EXISTS ( (SELECT "MAPNODEID" FROM "MAPNODE" "MAPNODE" WHERE "MAPNODEID"=:B1 AND
"LAST_UPDATE">1165500284)UNION (SELECT "MAPNODEID" FROM "MAPNODEDOMAIN" "MAPNODEDOMAIN" WHERE "MAPNODEID"=:B2 AND
"LAST_UPDATE">1275809509)))
22 - filter("LAST_UPDATE">1165500284)
23 - access("MAPNODEID"=:B1)
24 - filter("LAST_UPDATE">1275809509)
25 - access("MAPNODEID"=:B1)
27 - access("MAPNODEID"=:B1)
29 - access("UN"."MAPNODEID"="ANCESTOR")
30 - access("U"."USERNAME"="UN"."USERNAME")
filter("U"."USERNAME"="UN"."USERNAME")
32 - access("CHILD"="D"."MAPNODEID")
34 - access("D"."NETWORKELEMENTID"="N"."HANDLE")
36 - access("CHILD"="M"."MAPNODEID")
38 - access("N"."DOMAINID"="D1"."DOMAINID")
39 - access("D2"."DOMAINID"="U"."DOMAINID")
40 - filter("D1"."NODEINDEX">="D2"."NODEINDEX" AND "D1"."RIGHTCHILDINDEX"<="D2"."RIGHTCHILDINDEX")
Thanks,
Pappu
[Edit MC: format]
[Updated on: Mon, 24 December 2012 03:01] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Sql query taking High CPU and execution time in oracle 11gR2 [message #573209 is a reply to message #573205] |
Mon, 24 December 2012 02:55 |
|
pappu123
Messages: 7
Registered: December 2012
|
Junior Member |
|
|
SELECT ANCESTOR, MAX(D.ALARMSTATE) ALARMSTATE,
MAX(D.SIALARMSTATE) SIALARMSTATE,MAX(D.UNCRALARMSTATE) UNCRALARMSTATE,
MAX(M.COMMSTATE) COMMSTATE, MAX(M.NNCOMMSTATE) NNCOMMSTATE,
MAX(M.SERVICESTATE) SERVICESTATE, MAX(M.ABNORMAL) ABNORMAL,
CASE WHEN MAX(D.LAST_UPDATE) > MAX(M.LAST_UPDATE)
THEN MAX(D.LAST_UPDATE) ELSE MAX(M.LAST_UPDATE) END LUPDATE,
UN.USERNAME, UN.SESSIONID, UN.MAPID, UN.MAPREFID
FROM
MAPNODE M, MAPNODEDOMAIN D, NODE_V NV, USERNODE UN,
( SELECT DISTINCT CONNECT_BY_ROOT NA.MAPNODEID ANCESTOR, NA.MAPNODEID CHILD,
CONNECT_BY_ISLEAF TYPE FROM NODEAGGREGATE NA
WHERE NA.VIEWTYPE=2 START WITH NA.MAPNODEID IN
( ( SELECT DISTINCT N.MAPNODEID FROM NODEAGGREGATE N
WHERE N.VIEWTYPE=2 START WITH N.MAPNODEID IN
( SELECT MAPNODEID FROM MAPNODE WHERE LAST_UPDATE > 1165500284
UNION SELECT MAPNODEID FROM MAPNODEDOMAIN WHERE LAST_UPDATE > 1275809509
)
CONNECT BY N.MAPNODEID = PRIOR N.PARENTID AND N.VIEWTYPE = PRIOR N.VIEWTYPE
)
INTERSECT
(SELECT DISTINCT MAPNODEID FROM USERNODE)
)
CONNECT BY PRIOR NA.MAPNODEID=NA.PARENTID AND PRIOR NA.VIEWTYPE = NA.VIEWTYPE
)UNC
WHERE
CHILD=M.MAPNODEID AND CHILD=D.MAPNODEID AND
D.NETWORKELEMENTID=NV.HANDLE AND NV.USERNAME=UN.USERNAME AND
UN.MAPNODEID=ANCESTOR
GROUP BY ANCESTOR, UN.USERNAME, UN.SESSIONID, UN.MAPID, UN.MAPREFID;
Oracle version is 11.2.0.2 and the query is attached below.
Thanks.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
