Home » RDBMS Server » Performance Tuning » Sql query taking High CPU and execution time in oracle 11gR2 (Oracle 11g R2)
Sql query taking High CPU and execution time in oracle 11gR2 [message #573195] Mon, 24 December 2012 00:29 Go to next message
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 #573197 is a reply to message #573195] Mon, 24 December 2012 00:39 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Can you please give us the query plan please ?

Explain plan


   set pagesize 25
   set linesize 121

   EXPLAIN PLAN FOR
   < Your Query >;


    SELECT * FROM TABLE(dbms_xplan.display);





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 Go to previous messageGo to next message
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 #573205 is a reply to message #573198] Mon, 24 December 2012 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unreadable.


Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
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 Go to previous messageGo to next message
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.
Re: Sql query taking High CPU and execution time in oracle 11gR2 [message #573211 is a reply to message #573209] Mon, 24 December 2012 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: Sql query taking High CPU and execution time in oracle 11gR2 [message #573212 is a reply to message #573211] Mon, 24 December 2012 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now the plan is readable, given that all tables are accessed via INDEX UNIQUE SCAN (or INDEX SCAN for CONNECT BY clause) which are the fastest way to access them I don't see what you can do more.
You can see that the cost is very low for a so complex query.
In addition, CONNECT BY operation is a high CPU one.
What do you mean with "query is taking ... longer time to execute ", longer than what?

Regards
Michel
Re: Sql query taking High CPU and execution time in oracle 11gR2 [message #573213 is a reply to message #573212] Mon, 24 December 2012 03:12 Go to previous messageGo to next message
pappu123
Messages: 7
Registered: December 2012
Junior Member
Hi Michel,

Time taking is ok but the problem is when this query is getting executed, oracle process cpu is going to 99.5% and more ...

Can we reduce the cpu utilization of the query..

Thanks ,
Pappu
Re: Sql query taking High CPU and execution time in oracle 11gR2 [message #573216 is a reply to message #573213] Mon, 24 December 2012 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is expected, as I said, CONNECT BY is a high CPU consumer operation and it takes all cpu as it can but if your figures are correct it should last 1 second.

If this is not the case then your statistics are not up to date and so regather them and repost the execution plan (formatted).

Regards
Michel
Re: Sql query taking High CPU and execution time in oracle 11gR2 [message #573258 is a reply to message #573216] Tue, 25 December 2012 23:32 Go to previous messageGo to next message
pappu123
Messages: 7
Registered: December 2012
Junior Member
Hi Michel,

Yes this above figures are correct .. But here my question is can we reduce the cpu consumption by any chance ( re-wrighting the query or adding hint etc)

Regards,
Pappu
Re: Sql query taking High CPU and execution time in oracle 11gR2 [message #573260 is a reply to message #573258] Tue, 25 December 2012 23:43 Go to previous messageGo to next message
BlackSwan
Messages: 23065
Registered: January 2009
Senior Member
>Yes this above figures are correct .. But here my question is can we reduce the cpu consumption by any chance ( re-wrighting the query or adding hint etc)

How can we reproduce what you report?


Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Sql query taking High CPU and execution time in oracle 11gR2 [message #573263 is a reply to message #573258] Wed, 26 December 2012 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
pappu123 wrote on Wed, 26 December 2012 06:32
Hi Michel,

Yes this above figures are correct .. But here my question is can we reduce the cpu consumption by any chance ( re-wrighting the query or adding hint etc)

Regards,
Pappu


If the figures are correct then what is the problem with a query taking 100% of the CPU during 1/5 second?

Regards
Michel
Re: Sql query taking High CPU and execution time in oracle 11gR2 [message #573273 is a reply to message #573263] Wed, 26 December 2012 03:18 Go to previous messageGo to next message
pappu123
Messages: 7
Registered: December 2012
Junior Member
Hi Michel,

Thanks for the reply

This query polling time is once in every 10 second for one user and if multiple user will open the same page then oracle cpu will not come down below 99.8%. It will be always 99.8% and above ..

Please help ..

Regards,
Pappu


Re: Sql query taking High CPU and execution time in oracle 11gR2 [message #573274 is a reply to message #573273] Wed, 26 December 2012 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So the problem is why each session have to query this every 10 seconds?
The problem is in the application not in Oracle.
Investigate in this.

Regards
Michel
Re: Sql query taking High CPU and execution time in oracle 11gR2 [message #573343 is a reply to message #573274] Wed, 26 December 2012 23:34 Go to previous messageGo to next message
pappu123
Messages: 7
Registered: December 2012
Junior Member
Hi Michel,
Thanks for the info..

But one thing is we have 2 server one is 10.2.0.4 with application and other one is 11.2.0.2 with application.
In 10gR2 the same query is working fine and CPU utilization is below 10% where as in 11gR2 CPU cost is more than99.5%..

Hardware , OS and other configuration are same.

Regards,
Pappu
Re: Sql query taking High CPU and execution time in oracle 11gR2 [message #573344 is a reply to message #573343] Wed, 26 December 2012 23:49 Go to previous message
BlackSwan
Messages: 23065
Registered: January 2009
Senior Member
>Hardware , OS and other configuration are same.
same inputs produce same results

when you have different results, then obviously something is different.
Only YOU can determine what really is different.
If you insist that things 'are same", then you live with existing results.
Previous Topic: Help in tuning the query
Next Topic: indexing
Goto Forum:
  


Current Time: Fri Nov 28 13:39:53 CST 2014

Total time taken to generate the page: 0.13725 seconds