1 2 Previous Next 20 Replies Latest reply: Feb 27, 2013 5:30 PM by khallas301 RSS

    subquery tuning...

    khallas301
      os: linux
      db: 11.2
      friends...
      newbie in oracle query tuning... could some expert look at this and see how can I finetune subquery or other
      part of this query to reduce 00:00:45 to much lower... query runs for 500 times in a day so trying to tune it...
      SELECT
           abc.id, def.username, def.lastname, abc.blob, xyz.time
      FROM
           abc, (1 mil row)
           def, (7 mil row)
           xyz  (400k row)
      where
           abc.id = def.id
           and abc.blah in ('op','qr')
           and def.id in (select id from def where otherid = :1)
           and abc.dt > trunc(sysdate - :2)
           and def.id = xyz.id
      order by
           def.id, abc.ity, abc.co_id
           
      Explain Plan:

                               Rows Bytes cost Time
      SELECT STATEMENT               
      SORT ORDER BY                    20     30000     3500 00:00:45
      NESTED LOOPS OUTER               20     30000     3490 00:00:45          
      NESTED LOOPS                20      30000     3355 00:00:45     
      NESTED LOOPS               15000     5000k     20 00:00:01     
      SORT UNIQUE
      TABLE ACCES BY INEX ROWID
      INEX RANGE SCAN
      TABLE ACCES BY INDEX ROWID
      INDEX RANGE SCAN

      tried to have explain plan paste properly but not happening
        • 1. Re: subquery tuning...
          sb92075
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ


          scroll down to #9 describing
           tags                                                                                                                                                                                                                                                                                                                        
          • 2. Re: subquery tuning...
            khallas301
            OS = Linux
            DB = Oracle 11.2

            appreciate your effort in posting link...

            added explain plan in new reply
            • 3. Re: subquery tuning...
              khallas301
              Explain Plan:
              
                                       Rows     Bytes     cost     Time
              SELECT STATEMENT               
                SORT ORDER BY                    20     30000     3500  00:00:45
                 NESTED LOOPS OUTER               20     30000     3490  00:00:45          
                  NESTED LOOPS                20      30000     3355  00:00:45     
                   NESTED LOOPS               15000     5000k     20    00:00:01     
                    SORT UNIQUE
                     TABLE ACCES BY INEX ROWID
                      INEX RANGE SCAN
                     TABLE ACCES BY INDEX ROWID
                      INDEX RANGE SCAN
              • 4. Re: subquery tuning...
                riedelme
                khallas301 wrote:
                SELECT
                     abc.id, def.username, def.lastname, abc.blob, xyz.time
                FROM
                     abc, (1 mil row)
                     def, (7 mil row)
                     xyz  (400k row)
                where
                     abc.id = def.id
                     and abc.blah in ('op','qr')
                     and def.id in (select id from def where otherid = :1)
                     and abc.dt > trunc(sysdate - :2)
                     and def.id = xyz.id
                order by
                     def.id, abc.ity, abc.co_id
                     
                Explain Plan:

                                         Rows Bytes cost Time
                SELECT STATEMENT               
                SORT ORDER BY                    20     30000     3500 00:00:45
                NESTED LOOPS OUTER               20     30000     3490 00:00:45          
                NESTED LOOPS                20      30000     3355 00:00:45     
                NESTED LOOPS               15000     5000k     20 00:00:01     
                SORT UNIQUE
                TABLE ACCES BY INEX ROWID
                INEX RANGE SCAN
                TABLE ACCES BY INDEX ROWID
                INDEX RANGE SCAN

                tried to have explain plan paste properly but not happening
                A properly indexed correlated EXISTS Subquery might help performance, something like
                SELECT abc.id, def.username, def.lastname, abc.blob, xyz.time
                 FROM
                      abc, (1 mil row)
                      def, (7 mil row)
                      xyz  (400k row)
                 where
                      abc.id = def.id
                      and abc.blah in ('op','qr')
                      and def.id in (select id
                                                 from def2
                                                 where otherid = :1)
                                         and abc.dt > trunc(sysdate - :2)
                                                 and  def2.id = def.id
                                                )
                      and def.id = xyz.id
                 order by
                      def.id, abc.ity, abc.co_id
                A composite index (possibly function-based using TRUNC(sysdate) too) on def2.otherid and def2.id might help.

                Edited by: riedelme on Feb 14, 2013 1:04 PM
                • 5. Re: subquery tuning...
                  khallas301
                  thank you for your response.. I will try and let you know result..

                  do you think using some kind of hint will improve query?

                  major concern is nested loop outer which is problem.. I think...
                  • 6. Re: subquery tuning...
                    riedelme
                    khallas301 wrote:
                    thank you for your response.. I will try and let you know result..

                    do you think using some kind of hint will improve query?

                    major concern is nested loop outer which is problem.. I think...
                    Under the right circumstances hints can improve performance but they can be undpredictable when something changes like a database upgrade or even database parameter changes. It is best not to use them unless they are really, really needed (though I do use them at need)

                    Hints are used to override the optimizer which does not always make the best decisions. It works better in the long run to make sound environmental changes like indexes before resoriting to hints.

                    In your case you can check out the hints regarding subquery unnesting and merging (listed in the documentation) and possibly those affecting join order but I would check possible index usage first.

                    Edited by: riedelme on Feb 14, 2013 1:42 PM
                    • 7. Re: subquery tuning...
                      Dom Brooks
                      Explain Plan:
                       
                                               Rows     Bytes     cost     Time
                      SELECT STATEMENT               
                        SORT ORDER BY                    20     30000     3500  00:00:45
                         NESTED LOOPS OUTER               20     30000     3490  00:00:45          
                          NESTED LOOPS                20      30000     3355  00:00:45     
                           NESTED LOOPS               15000     5000k     20    00:00:01     
                            SORT UNIQUE
                             TABLE ACCES BY INEX ROWID
                              INEX RANGE SCAN
                             TABLE ACCES BY INDEX ROWID
                              INDEX RANGE SCAN
                      This is a pitiful attempt at getting the required execution plan information.
                      See HOW TO: Post a SQL statement tuning request - template posting
                      major concern is nested loop outer which is problem.. I think...
                      Why?
                      part of this query to reduce 00:00:45 to much lower.
                      If it actually takes 45 seconds then the optimizer is bang on with its estimate.

                      If the estimates are accurate - which you intimate they are - then they optimizer has been very accurate with its estimate and the choice of nester loop outer is likely to be good.

                      But, given the poor effort above, I suspect though that you've no idea actually how long this takes.

                      Why guess when you can know?
                      Read the tuning thread.
                      • 8. Re: subquery tuning...
                        khallas301
                        thanks Dom with your response...I certainly have no doubt with your comments and also I'm not sure best time to run this query that's where query tuning comes into picture, 45s to me is more when all resource and parameters there to make it fast.

                        I read many user comments/article and all saying subquery will reduce performance.... also I have tried various option to get better plan doesn't work then only came to this forum.

                        I did knew best way to ask question with tkprof and better plan details but due to practical reasons and limitation I couldn't do it.

                        riedelme, when you mentioned about def2 do you mean I give alias to def table?
                        • 9. Re: subquery tuning...
                          khallas301
                          sorry guys also noticed original sql has below outer join in the end.
                          ...
                          ...
                          ...
                               and def.id = xyz.id(+)
                          order by
                               def.id, abc.ity, abc.co_id
                          
                          am I following correct process for explain plan?
                          
                          
                          1. explain plan for 
                                 select * from employee;
                          
                          2. select * from table(dbms_xplan.display);
                          because whenever I user hint or change query for different plan I dont see change in hash value makes me wonder explain plan just not displaying me same old plan all time?

                          updated outer join
                          • 10. Re: subquery tuning...
                            khallas301
                            any idea if I can change "def.id = xyz.id(+)" to something else if that can change plan positively?
                            SELECT
                                 abc.id, def.username, def.lastname, abc.blob, xyz.time
                            FROM
                                 abc, (1 mil row)
                                 def, (7 mil row)
                                 xyz  (400k row)
                            where
                                 abc.id = def.id
                                 and abc.blah in ('op','qr')
                                 and def.id in (select id from def where otherid = :1)
                                 and abc.dt > trunc(sysdate - :2)
                                 and def.id = xyz.id(+)
                            order by
                                 def.id, abc.ity, abc.co_id
                            • 11. Re: subquery tuning...
                              Dom Brooks
                              You're asking generic questions about specific solutions to a specific problem without giving the details required - it does not tend to be the most fruitful approach.

                              The template tuning thread linked to earlier tells you how to approach the problem.
                              It's so important that I'll post it again:
                              HOW TO: Post a SQL statement tuning request - template posting

                              The approach is that you get the actual SQL execution metrics.
                              You follow the principles of [url http://www.centrexcc.com/Tuning%20by%20Cardinality%20Feedback.pdf]Tuning by Cardinality Feedback which states that:

                              "IF AN ACCESS PLAN IS NOT OPTIMAL IT IS BECAUSE THE CARDINALITY ESTIMATE FOR ONE OR MORE OF
                              THE ROW SOURCES IS GROSSLY INCORRECT."

                              and

                              "THE CBO DOES AN EXCELLENT JOB OF FINDING THE BEST ACCESS PLAN FOR A GIVEN SQL PROVIDED
                              IT IS ABLE TO ACCURATELY ESTIMATE THE CARDINALITIES OF THE ROW SOURCES IN THE PLAN"

                              So you look at the steps where the optimizer might have significantly wrong with its estimates compared to actual rowsource cardinalities and then you look at the reasons why.

                              If the optimizer has been in the right ballpark with the estimates and performance is not what you need, then you need to consider changing the physical structures which give access to this data.
                              • 12. Re: subquery tuning...
                                riedelme
                                khallas301 wrote:
                                sorry guys also noticed original sql has below outer join in the end.
                                Outer joins use extra resources and can make SQL run slower. Needing outer joins is a design issue that is not easily fixed by code.

                                On possiblity for outer joins is to rewrite the SQL with a normal inner join with a UNION to a SQL which gets the rows that do not match (the outer part). This helps performance for me maybe 20% of the time. Something like
                                select a.col1
                                  from a, b
                                 where a.key = b.key (+)
                                
                                select a.col1
                                  from a, b
                                 where a.key = b.key
                                union
                                select a.col1
                                  from a
                                 where exists(
                                    select 0
                                      from b
                                     where b.key = a.key
                                   )
                                making sure the tables are properly indexed.
                                because whenever I user hint or change query for different plan I dont see change in hash value makes me wonder explain plan just not displaying me same old plan all time?
                                Recoding SQL to change the plan is tricky because the optimizer often revises SQL before execution negating physical changes. The NO_QUERY_TRANSFORMATION hint should prevent this; on rare occasions I have seen it help perfomance.
                                • 13. Re: subquery tuning...
                                  rahulras
                                  I think, it is really important how selective is your subquery ( i.e. select id from def where otherid = :1 ).
                                  Assuming, "id" is an indexed column in all these tables, I would recommend changing your query to
                                  SELECT
                                      abc.id, def.username, t1.lastname, abc.blob, xyz.time
                                  FROM
                                      abc, (1 mil row)
                                      def t1, (7 mil row)
                                      xyz  (400k row)
                                  where
                                      abc.id = t1.id
                                      and abc.blah in ('op','qr')
                                      and exists (select t2.id from def t2 where t2.id = t1.id and t2.otherid = :1)
                                      and abc.dt > trunc(sysdate - :2)
                                      and t1.id = xyz.id
                                  order by
                                      t1.id, abc.ity, abc.co_id
                                  I have changed that IN clause to co-related subquery using EXISTS
                                  • 14. Re: subquery tuning...
                                    khallas301
                                    thanks rahul but still no luck in improving this...

                                    any idea what causing below in explain plan which increases elapsed time
                                    1. sort order by / 2. nested loop outer / 3. nested loops

                                    is it subquery or outer join so that I atleast get idea on where to start looking to fix ?
                                    0. SELECT STATEMENT============12  rows======00:00:35
                                    1. SORT ORDER BY===============12 rows======00:00:35
                                    2. ---NESTED LOOPS OUTER==========12  rows=====00:00:35
                                    3. -----NESTED LOOPS==============12  rows=====00:00:35
                                    4. --------NESTED LOOPS ============ 12K rows ====00:00:01
                                    5. ----------SORT UNIQUE=============4  rows=====00:00:01
                                    SELECT
                                         abc.id, def.username, def.lastname, abc.blob, xyz.time
                                    FROM
                                         abc, (1 mil row)
                                         def, (7 mil row)
                                         xyz  (400k row)
                                    where
                                         abc.id = def.id
                                         and abc.blah in ('op','qr')
                                         and def.id in (select id from def where otherid = :1)
                                         and abc.dt > trunc(sysdate - :2)
                                         and def.id = xyz.id(+)
                                    order by
                                         def.id, abc.ity, abc.co_id
                                    tried almost all hints but not able to reduce run time...

                                    thanks,
                                    mike

                                    Edited by: khallas301 on Feb 27, 2013 2:14 PM
                                    1 2 Previous Next