This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Feb 27, 2013 3:30 PM by khallas301 RSS

subquery tuning...

khallas301 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    OS = Linux
    DB = Oracle 11.2

    appreciate your effort in posting link...

    added explain plan in new reply
  • 3. Re: subquery tuning...
    khallas301 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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