This discussion is archived
11 Replies Latest reply: Aug 8, 2012 8:09 AM by 792453 RSS

Need help on query tuning

792453 Newbie
Currently Being Moderated
Hi experts,

I have the following query which takes more than 30 minutes to retrieve data. We are using Oracle 11g.
SELECT B.serv_item_id,
  B.document_number,
  DECODE(B.activity_cd,'I','C',B.activity_cd) activity_cd,
  DECODE(B.activity_cd, 'N', 'New', 'I', 'Change', 'C', 'Change', 'D', 'Disconnect', B.activity_cd ) order_activity,
  b.due_date,
  A.order_due_date ,
  A.activity_cd order_activty_cd
FROM
  (SELECT SRSI2.serv_item_id ,
    NVL(to_date(TO_CHAR(asap.PKG_GMT.sf_gmt_as_local(TASK2.revised_completion_date),'J'),'J'), SR2.desired_due_date) order_due_date ,
    'D' activity_cd
  FROM asap.serv_req_si SRSI2,
    asap.serv_req SR2,
    asap.task TASK2
  WHERE SRSI2.document_number = 10685440
  AND SRSI2.document_number   = SR2.document_number
  AND SRSI2.document_number   = TASK2.document_number (+)
  AND SRSI2.activity_cd       = 'D'
  AND TASK2.task_type (+)     = 'DD'
  ) A ,
  (SELECT SRSI1.serv_item_id,
    SR1.document_number,
    SRSI1.activity_cd,
    NVL(to_date(TO_CHAR(asap.PKG_GMT.sf_gmt_as_local(TASK1.revised_completion_date),'J'),'J'), SR1.desired_due_date) due_date
  FROM asap.serv_req_si SRSI1,
    asap.serv_req SR1,
    asap.task TASK1,
    asap.serv_req_si CURORD
  WHERE CURORD.document_number   = 10685440
  AND SRSI1.document_number      = SR1.document_number
  AND SRSI1.document_number     != CURORD.document_number
  AND SRSI1.serv_item_id         = CURORD.serv_item_id
  AND SRSI1.document_number      = TASK1.document_number (+)
  AND TASK1.task_type (+)        = 'DD'
  AND SR1.type_of_sr             = 'SO'
  AND SR1.service_request_status < 801
  AND SRSI1.activity_cd         IN ('I', 'C', 'N')
  ) B
WHERE B.serv_item_id = A.serv_item_id;
If I run the inline queries (sub-queries) A and B separately it comes in few seconds, but when i try to join both of them it take to close to 1 Hr sometimes. In my specific case query A returns 52 records and query B returns 120 records only.

For me it looks like optimizer's failure to figure out how much data it will return for each sub-query. I feel it require to fool the optimizer through a workaround to get the result faster. But I am not able to find out a work around for this. If any of you can give some light on this, it would be really helpful.

Thanks a lot
Gaf

Edited by: user780504 on Aug 7, 2012 2:16 AM

Edited by: BluShadow on 07-Aug-2012 10:17
added {noformat}
{noformat} tags for readability and replace &lt;&gt; with != to circumvent forum issue.  Please read {message:id=9360002}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 1. Re: Need help on query optimization
    BluShadow Guru Moderator
    Currently Being Moderated
    Please read the two threads linked to in the following FAQ post: {message:id=9360003}
  • 2. Re: Need help on query tuning
    Etbin Guru
    Currently Being Moderated
    Knowing that both inline queries are fast, my first attempt would be
    with
    one as
    (SELECT /*+ materialize */
            SRSI2.serv_item_id,
            NVL(to_date(TO_CHAR(asap.PKG_GMT.sf_gmt_as_local(TASK2.revised_completion_date),'J'),'J'),
            SR2.desired_due_date) order_due_date,
            'D' activity_cd
       FROM asap.serv_req_si SRSI2,
            asap.serv_req SR2,
            asap.task TASK2
      WHERE SRSI2.document_number = 10685440
        AND SRSI2.document_number = SR2.document_number
        AND SRSI2.document_number = TASK2.document_number 
        AND SRSI2.activity_cd = 'D'
        AND TASK2.task_type  = 'DD'
    ),
    two as
    (SELECT /*+ materialize */
            SRSI1.serv_item_id,
            SR1.document_number,
            SRSI1.activity_cd,
            NVL(to_date(TO_CHAR(asap.PKG_GMT.sf_gmt_as_local(TASK1.revised_completion_date),'J'),'J'),
            SR1.desired_due_date) due_date
       FROM asap.serv_req_si SRSI1,
            asap.serv_req SR1,
            asap.task TASK1,
            asap.serv_req_si CURORD
      WHERE CURORD.document_number = 10685440
        AND SRSI1.document_number = SR1.document_number
        AND SRSI1.document_number CURORD.document_number
        AND SRSI1.serv_item_id = CURORD.serv_item_id
        AND SRSI1.document_number = TASK1.document_number 
        AND TASK1.task_type  = 'DD'
        AND SR1.type_of_sr = 'SO'
        AND SR1.service_request_status < 801
        AND SRSI1.activity_cd IN ('I', 'C', 'N')
    )
    SELECT B.serv_item_id,
           B.document_number,
           DECODE(B.activity_cd,'I','C',B.activity_cd) activity_cd,
           DECODE(B.activity_cd,'N','New','I','Change','C','Change','D','Disconnect',B.activity_cd ) order_activity,
           b.due_date,
           A.order_due_date ,
           A.activity_cd order_activty_cd
      FROM one A,
           two B
     WHERE B.serv_item_id = A.serv_item_id
    Regards

    Etbin

    Edited by: Etbin on 7.8.2012 12:27
    /*+ materialize */ hints added
  • 3. Re: Need help on query tuning
    792453 Newbie
    Currently Being Moderated
    Thanks Etbin for the response. I tried the WITH clause as you suggested, but did not help me. It is also taking lot of time (running for 20 minutes now). Can you please suggest me something else which I can try.

    Thanks once again.
  • 4. Re: Need help on query tuning
    Etbin Guru
    Currently Being Moderated
    Maybe using /*+ materialize */ hints ? See above

    Regards

    Etbin
  • 5. Re: Need help on query tuning
    792453 Newbie
    Currently Being Moderated
    Thanks Etbin.. It worked!! Now the query returns in few seconds!!

    I googled about the WITH clause with hint and came across some concern people expressed. What is your thought on this.

    ' Each time the WITH query clause is run with /*+ materialize */ hint , a global temporary table is created and used in the query, causing many versions to be created in the shared pool and ultimately causing fragmentation and ORA-4031 errors '

    How much the above statement is true? is it safe to use the WITH clause with /*+ materialize */ hint?

    Thanks,
  • 6. Re: Need help on query tuning
    Etbin Guru
    Currently Being Moderated
    is it safe to use the WITH clause with /*+ materialize */ hint?
    Better said the other way around: should not be unsafe
    If you have a lot of users running the query simultaneously you could create those two GTT yourself to avoid the creation of many versions ...

    Regards

    Etbin
  • 7. Re: Need help on query tuning
    792453 Newbie
    Currently Being Moderated
    Etbin,

    Also /*+ materialize */ hint is not documented right? So I am confused about using it. may be you can give more light on this concern

    If you can suggest any alternative option to solve this issue other than /*+ materialize */ would be really great.

    Thanks a lot once again
    Gaf
  • 8. Re: Need help on query tuning
    792453 Newbie
    Currently Being Moderated
    Hope I am not frustrating you..
    If you have a lot of users running the query simultaneously you could create those two GTT yourself to avoid the creation of many versions ...
    Just FYI.. the above query I mentioned is small part of a monster query actually (It span almost 15 pages).

    Yes, we will have many users using this query simultaneously and I don't think that manually creating two GTTs would be possible in my case (Our customer wants to tune this single query, they may not agree creation of two temporary tables i guess).

    Can you please suggest some other options I can try out to get the query faster if at all possible

    Thanks in Advance
    Gaf

    Edited by: user780504 on Aug 8, 2012 3:39 AM
  • 9. Re: Need help on query tuning
    Boneist Guru
    Currently Being Moderated
    Is serv_item_id the primary key of asap.serv_req_si, by any chance? If so, I'd question the need to have both subqueries.

    Also, why are you doing the to_date(to_char(...., 'J'), 'J')? If it's to get rid of the time part of the date, why not use TRUNC instead? You'd replace two function calls with one and simultaneously make it more obvious what's being done and why, making it easier to maintain in the future.
  • 10. Re: Need help on query tuning
    792453 Newbie
    Currently Being Moderated
    no serv_item_id is not just the PK. It is part of the composite PK.
  • 11. Re: Need help on query tuning
    792453 Newbie
    Currently Being Moderated
    I have figured out a simple way to fool the optimizer by doing the following

    in place of
     
    
    B.serv_item_id = A.serv_item_id
    I have added the following line,
    B.serv_item_id in(select A.serv_item_id from dual)
    This worked like magic.... the result came in less than a second



    The WITH clause etbin suggested also worked (it took 2-3 seconds). So i am accepting that as the answer

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points