Check How much CPU & Time consumed By Query
EXPLAIN PLAN FOR
< Write Your Query Here >;
SELECT * FROM TABLE(dbms_xplan.display);
Result will be like that
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")
Result will be like that
No comments:
Post a Comment