1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Exceding cpu ussage limit sql developer

Discussion in 'SQL PL/SQL' started by Vanmon, Nov 27, 2015.

  1. Vanmon

    Vanmon Newly Initiated

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Bangkok, Thailand
    Hi, would someone please tell me if my query is wrong or I really have a problem with the CPU limit?
    When running the sub-queries separately I don't have any problem. Thank you in advance.

    SELECT L.WONUM,P1.ATRB, P2.PROBLM,P3.CAUSE, P4.REMEDY,P5.FAILCODE
    FROM MAXIMO.BTV_WORKORDER L
    LEFT JOIN
    (SELECT J.WONUM,J.SITEID,case when J.TYPE ='ATTRIB' then (J.FAILURECODE ||'-' ||K.DESCRIPTION) END AS ATRB FROM MAXIMO.BTV_FAILUREREPORT J JOIN MAXIMO.BTV_FAILURECODE K
    ON J.FAILURECODE=K.FAILURECODE WHERE J.SITEID='MMAD-ES' ) P1
    ON L.WONUM=P1.WONUM
    LEFT JOIN
    (SELECT J.WONUM,J.SITEID,case when J.TYPE ='PROBLEM' then (J.FAILURECODE ||'-' ||K.DESCRIPTION) END AS PROBLM FROM MAXIMO.BTV_FAILUREREPORT J JOIN MAXIMO.BTV_FAILURECODE K
    ON J.FAILURECODE=K.FAILURECODE WHERE J.SITEID='MMAD-ES') P2
    ON L.WONUM=P2.WONUM
    LEFT JOIN
    (SELECT J.WONUM, J.SITEID, case when J.TYPE ='CAUSE' OR J.TYPE='CAUSER' then (J.FAILURECODE ||'-' ||K.DESCRIPTION) END AS CAUSE FROM MAXIMO.BTV_FAILUREREPORT J JOIN MAXIMO.BTV_FAILURECODE K
    ON J.FAILURECODE=K.FAILURECODE WHERE J.SITEID='MMAD-ES') P3
    ON L.WONUM=P3.WONUM
    LEFT JOIN
    (SELECT J.WONUM,J.SITEID,case when J.TYPE ='REMEDY' then (J.FAILURECODE ||'-' ||K.DESCRIPTION) END AS REMEDY FROM MAXIMO.BTV_FAILUREREPORT J JOIN MAXIMO.BTV_FAILURECODE K
    ON J.FAILURECODE=K.FAILURECODE WHERE J.SITEID='MMAD-ES') P4
    ON L.WONUM=P4.WONUM
    LEFT JOIN
    (SELECT J.WONUM,J.SITEID,case when J.TYPE ='FAILCODE' then (J.FAILURECODE ||'-' ||K.DESCRIPTION) END AS FAILCODE FROM MAXIMO.BTV_FAILUREREPORT J JOIN MAXIMO.BTV_FAILURECODE K
    ON J.FAILURECODE=K.FAILURECODE WHERE J.SITEID='MMAD-ES') P5
    ON L.WONUM=P5.WONUM
    WHERE L.SITEID='MMAD-ES' AND L.SITEID=P1.SITEID AND L.SITEID=P2.SITEID AND L.SITEID=P3.SITEID AND L.SITEID=P4.SITEID AND L.SITEID=P5.SITEID AND
    P1.ATRB NOT IN ('A12-Invalid report, not applicable','A13-Poor maintenance','A05-Customer: Ignored design, modification, procedure advice','A06-Customer maintainer: poor maintenance',
    'A07-Customer: Operation error','A14-Subsupplier problem','A04-Customer: failure, wear out of component','A17-Accident','A02-Partner')
    AND P3.CAUSE <> ('C17-Misuse, abuse or vandalism')
    GROUP BY L.WONUM,P1.ATRB, P2.PROBLM,P3.CAUSE, P4.REMEDY,P5.FAILCODE;
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Provide here the plan of your sql-query.
    What is CPU limit : resource plan ?
     
  3. Vanmon

    Vanmon Newly Initiated

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Bangkok, Thailand
    Hi, thanks for your reply but I don't really understand your question. What do you mean with the plan? The objective of the query? Thanks
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Plan it is the execution plan of your sql of query. EXPLAIN PLAN


    Well. Then tell, what is problem?
    Wich version your oracle ?

    N.B. Your sql-query can be written in a different way..
     
    Last edited: Nov 28, 2015
  5. Vanmon

    Vanmon Newly Initiated

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Bangkok, Thailand
    SQL Error: ORA-01039: insufficient privileges on underlying objects of the view
    01039. 00000 - "insufficient privileges on underlying objects of the view"
    *Cause: Attempting to explain plan on other people's view without
    the necessary privileges on the underlying objects of the view.
    *Action: Get necessary privileges or do not perform the offending operation.
     
  6. Vanmon

    Vanmon Newly Initiated

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Bangkok, Thailand
    Hi Sergey, thank you for your reply.
    I'm not even sure SQL Developer is the right tool for me? I just would like to extract some data but I have no privileges to create tables, views etc..