11 Replies Latest reply: Aug 8, 2012 10:09 AM by 792453 RSS

    Need help on query tuning

    792453
      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
          Please read the two threads linked to in the following FAQ post: {message:id=9360003}
          • 2. Re: Need help on query tuning
            Etbin
            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
              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
                Maybe using /*+ materialize */ hints ? See above

                Regards

                Etbin
                • 5. Re: Need help on query tuning
                  792453
                  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
                    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
                      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
                        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
                          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
                            no serv_item_id is not just the PK. It is part of the composite PK.
                            • 11. Re: Need help on query tuning
                              792453
                              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