1 2 Previous Next 18 Replies Latest reply: Sep 10, 2013 3:50 AM by Jonathan Lewis RSS

    SQL Tuning. (union all)

    Rangarajan

      Hi,

       

      I am working in Oracle 10g R2 /RAC - Solaris

       

      I have sql which is getting executed more than 1million times during peak hours.

       

      +++++++++++++++++++

      SQL Statement

      +++++++++++++++++++

       

      select location_id -- (,longitude, latitude)

      from ph_location pl

      where (longitude between :long1 and :long2)

      and (latitude between :lat1 and :lat2)

      and catalog_id = :catalog

      and location_id in (select location_id

      from ph_location_cats

      where category_id = :cat

      union all

      select location_id

      from ph_location_prd

      where product_id = :prod)

      and location_id not in (select location_id

      from ph_loc_prd_excluded

      where product_id = :prod);

       

      ################################

      XPLAN

      ################################

       

      SQL> SET LINESIZE 130
      SET PAGESIZE 0
      SELECT *
      FROM   TABLE(DBMS_XPLAN.DISPLAY);

      SQL>   Plan hash value: 3391784670

      --------------------------------------------------------------------------------------------------------
      | Id  | Operation                     | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT              |                        |     1 |    85 |    23   (0)| 00:00:01 |
      |*  1 |  FILTER                       |                        |       |       |            |          |
      |   2 |   NESTED LOOPS ANTI           |                        |     1 |    85 |    18   (0)| 00:00:01 |
      |*  3 |    TABLE ACCESS BY INDEX ROWID| PH_LOCATION            |     1 |    56 |    17   (0)| 00:00:01 |
      |*  4 |     INDEX RANGE SCAN          | ID_PH_LOCATION_CALO    |    96 |       |    13   (0)| 00:00:01 |
      |   5 |      UNION-ALL                |                        |       |       |            |          |
      |*  6 |       INDEX UNIQUE SCAN       | PK_PH_LOCATION_CATS    |     1 |    38 |     3   (0)| 00:00:01 |
      |*  7 |       INDEX UNIQUE SCAN       | PK_PH_LOCATION_PRD     |     1 |    30 |     2   (0)| 00:00:01 |
      |*  8 |    INDEX UNIQUE SCAN          | PK_PH_LOC_PRD_EXCLUDED |   370 | 10730 |     1   (0)| 00:00:01 |
      --------------------------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

         1 - filter(TO_NUMBER(:LAT1)<=TO_NUMBER(:LAT2) AND TO_NUMBER(:LONG1)<=TO_NUMBER(:LONG2))
         3 - filter("LONGITUDE">=TO_NUMBER(:LONG1) AND "LONGITUDE"<=TO_NUMBER(:LONG2) AND
                    "LATITUDE">=TO_NUMBER(:LAT1) AND "LATITUDE"<=TO_NUMBER(:LAT2))
         4 - access("CATALOG_ID"=:CATALOG)
             filter( EXISTS ( (SELECT /*+ */ "LOCATION_ID" FROM "PH_LOCATION_CATS" "PH_LOCATION_CATS"
                    WHERE "CATEGORY_ID"=:CAT AND "LOCATION_ID"=:B1) UNION ALL  (SELECT /*+ */ "LOCATION_ID" FROM
                    "PH_LOCATION_PRD" "PH_LOCATION_PRD" WHERE "PRODUCT_ID"=:PROD AND "LOCATION_ID"=:B2)))
         6 - access("LOCATION_ID"=:B1 AND "CATEGORY_ID"=:CAT)
         7 - access("LOCATION_ID"=:B1 AND "PRODUCT_ID"=:PROD)
         8 - access("LOCATION_ID"="LOCATION_ID" AND "PRODUCT_ID"=:PROD)

      29 rows selected.

       

      How to tune this query? Is there any way to re-write this sql without unionall? If so, How? Please help.

       

       

      thanks

      Raj


        • 1. Re: SQL Tuning. (union all)
          BluShadow

          What makes you think the query is slow?

           

          Read the two threads linked to by the FAQ: Re: 3. How to  improve the performance of my query? / My query is running slow.

          • 2. Re: SQL Tuning. (union all)
            Rangarajan


            At the outset thanks for the link, the issue was reported by developers in my team.

             

            Thanks

            Raj

            • 3. Re: SQL Tuning. (union all)
              Etbin

              guessing

               

              select location_id

                from (select pl.location_id -- (,longitude, latitude)

                             x.location_id excluded_locs

                        from (select location_id

                                from ph_location

                               where longitude between :long1 and :long2

                                 and latitude between :lat1 and :lat2

                                 and catalog_id = :catalog

                             ) pl,

                             (select location_id

                                from ph_location_cats

                               where category_id = :cat

                              union all

                              select location_id

                                from ph_location_prd

                               where product_id = :prod

                             ) y,

                             (select location_id

                                 from ph_loc_prd_excluded

                                 where product_id = :prod

                             )

                       where pl.location_id = y.location_id

                         and pl.location_id = x.location_id(+)

                     )

              where excluded_locs is null

               

              Regards

               

              Etbin

              • 4. Re: SQL Tuning. (union all)
                Ishan

                A logical guess but not sure if it's exactly same as your query. I would like Etbin and Blu to correct me here, if I am wrong:

                 

                select location_id -- (,longitude, latitude)

                from ph_location pl,ph_location_cats plc,  ph_location_prd plp, ph_loc_prd_excluded plpe

                where longitude between :long1 and :long2

                and latitude between :lat1 and :lat2

                and pl.catalog_id = :catalog

                and pl.location_id = plc.location_id

                and plc.location_id = plp.location_id -- You might want to use OUTER JOIN here

                and pl.location_id <> plpe.location_id

                and pl.product_id = :prod

                ;

                 

                Thanks,

                Ishan

                • 5. Re: SQL Tuning. (union all)
                  Manik

                  Not sure if re-writing union all will really improve your query (because it may depend ondata which we deal)..

                   

                  But if the question is just about removing union all..

                   

                  One way to write the query is as below.. But I have not tested it.. So help yourself with testing..

                   

                  -------------------

                   

                  SELECT location_id                                 

                    FROM ph_location pl,

                         (SELECT COALESCE (a.location_id, b.location_id, c.location_id)

                            FROM (SELECT location_id

                                    FROM ph_location_cats

                                   WHERE category_id = :cat) a

                                 FULL OUTER JOIN (SELECT location_id

                                                    FROM ph_location_prd

                                                   WHERE product_id = :prod) b

                                    ON a.location_id = b.location_id

                                 FULL OUTER JOIN (SELECT location_id

                                                    FROM ph_loc_prd_excluded

                                                   WHERE product_id <> :prod) c

                                    ON c.location_id = a.location_id) sett

                  WHERE     (longitude BETWEEN :long1 AND :long2)

                         AND (latitude BETWEEN :lat1 AND :lat2)

                         AND catalog_id = :catalog

                         AND pl.location_id = sett.location_id;

                   

                  -------------------

                  Cheers,

                  Manik.

                  • 6. Re: SQL Tuning. (union all)
                    Etbin

                    ..  but not sure if it's exactly same as ...

                    Im pretty sure it isn't

                    your plc.location_id = plp.location_id will get the intersection of ph_location_cats and ph_location_prd while the union is required.

                    union all might indicate the tables mentioned are disjoint, but might be also used for performance reasons not bothering if some duplicates might emerge.

                    The plan is showing indexes are used everywhere so union all should not be a problem it's just that the outer join might be faster than nested loops anti.

                     

                    Regards

                     

                     

                    Etbin

                    • 7. Re: SQL Tuning. (union all)
                      chris227

                      The less "invasive" might be an or instead of the union all

                       

                      select location_id -- (,longitude, latitude)

                      from ph_location pl

                      where (longitude between :long1 and :long2)

                      and (latitude between :lat1 and :lat2)

                      and catalog_id = :catalog

                      and (

                      location_id in (select location_id

                      from ph_location_cats

                      where category_id = :cat

                      )

                      or

                      location_id in (

                      select location_id

                      from ph_location_prd

                      where product_id = :prod

                      )

                      )

                      and location_id not in (select location_id

                      from ph_loc_prd_excluded

                      where product_id = :prod);

                      • 8. Re: SQL Tuning. (union all)
                        Martin Preiss

                        some thougths - without a particular order:

                        - NOT IN is dangerous when your resultset includes NULL values, because NOT IN (something, NULL) is NULL again. If your result does not contain NULLs then that's not a problem.

                        - the plan contains a lot of INDEX UNIQUE SCANs and that's a fast access path most of the time

                        - the plan contains a NESTED LOOPS ANTI to do the NOT IN filtering. The CBO expects the table acces on PH_LOCATION to return only one row and assumes that the NL Anti join will be efficient.

                        - there is some filtering on LONGITUDE und LATITUDE in step 3, so it seems that the index ID_PH_LOCATION_CALO does not contain these columns (since the access predicate is only CATALOG_ID). If the table access is an expensive part of the operation it could be an option to build a covering index with (catalog_id, location_id, LONGITUDE, LATITUDE) to avoid the table access at all, but of course this will have an impact on all DML operation on the table and could also influence other queries.

                        - perhaps it would be a good idea to determine the relevant location_id-s before accessing ph_location at all. If PK_PH_LOC_PRD_EXCLUDED does not contain NULLs, I think, you could use something like:

                         

                        select location_id

                          from ph_location pl

                        where (longitude between :long1 and :long2)

                           and (latitude between :lat1 and :lat2)

                           and catalog_id = :catalog

                           and location_id in ((select location_id

                                                  from ph_location_cats

                                                 where category_id = :cat

                                                 union all

                                                select location_id

                                                  from ph_location_prd

                                                 where product_id = :prod)

                                                 minus

                                                select location_id

                                                  from ph_loc_prd_excluded

                                                 where product_id = :prod

                                                )

                         

                        Perhaps you could gather the execution plan for this version and the other proposed queries, so we could get an idea of the optimizer's decisions. But without knowledge of the size of the involved sets and the work that has to be done, we are only guessing.

                         

                        Regards

                         

                        Martin Preiss

                        • 9. Re: SQL Tuning. (union all)
                          Rangarajan

                          Hi

                           

                          Thanks for your reply.

                           

                          The cost of this query is more compared to original query posted.

                           

                          {noformat}

                          SQL> explain plan for select location_id -- (,longitude, latitude)

                            2  from ph_location pl

                            3  where (longitude between :long1 and :long2)

                            4  and (latitude between :lat1 and :lat2)

                            5  and catalog_id = :catalog

                            6  and (

                            7  location_id in (select location_id

                            8  from ph_location_cats

                            9  where category_id = :cat

                          10  )

                          11  or

                          12  location_id in (

                          13  select location_id

                          from ph_location_prd

                          14   15  where product_id = :prod

                          16  )

                          17  )

                          18  and location_id not in (select location_id

                          19  from ph_loc_prd_excluded

                          20  where product_id = :prod);

                           

                           

                          Explained.

                           

                           

                          SQL> select * from table(dbms_xplan.display());

                           

                           

                          PLAN_TABLE_OUTPUT

                          --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                          Plan hash value: 2662773144

                           

                           

                          ---------------------------------------------------------------------------------------------------------

                          | Id  | Operation                      | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |

                          ---------------------------------------------------------------------------------------------------------

                          |   0 | SELECT STATEMENT               |                        |     1 |    71 |    82   (0)| 00:00:01 |

                          |*  1 |  FILTER                        |                        |       |       |            |          |

                          |*  2 |   FILTER                       |                        |       |       |            |          |

                          |   3 |    NESTED LOOPS ANTI           |                        |     1 |    71 |    82   (0)| 00:00:01 |

                          |*  4 |     TABLE ACCESS BY INDEX ROWID| PH_LOCATION            |     1 |    42 |    81   (0)| 00:00:01 |

                          |*  5 |      INDEX RANGE SCAN          | ID_PH_LOCATION_CI      |  1923 |       |    16   (0)| 00:00:01 |

                          |*  6 |     INDEX UNIQUE SCAN          | PK_PH_LOC_PRD_EXCLUDED |   370 | 10730 |     1   (0)| 00:00:01 |

                          |*  7 |   INDEX UNIQUE SCAN            | PK_PH_LOCATION_CATS    |     1 |    38 |     3   (0)| 00:00:01 |

                          |*  8 |    INDEX UNIQUE SCAN           | PK_PH_LOCATION_PRD     |     1 |    30 |     2   (0)| 00:00:01 |

                          ---------------------------------------------------------------------------------------------------------

                           

                           

                          Predicate Information (identified by operation id):

                          ---------------------------------------------------

                           

                           

                             1 - filter( EXISTS (SELECT /*+ */ 0 FROM "PH_LOCATION_CATS" "PH_LOCATION_CATS" WHERE

                                        "CATEGORY_ID"=:CAT AND "LOCATION_ID"=:B1) OR  EXISTS (SELECT /*+ */ 0 FROM "PH_LOCATION_PRD"

                                        "PH_LOCATION_PRD" WHERE "PRODUCT_ID"=:PROD AND "LOCATION_ID"=:B2))

                             2 - filter(TO_NUMBER(:LAT1)<=TO_NUMBER(:LAT2) AND TO_NUMBER(:LONG1)<=TO_NUMBER(:LONG2))

                             4 - filter("LONGITUDE">=TO_NUMBER(:LONG1) AND "LONGITUDE"<=TO_NUMBER(:LONG2) AND

                                        "LATITUDE">=TO_NUMBER(:LAT1) AND "LATITUDE"<=TO_NUMBER(:LAT2))

                             5 - access("CATALOG_ID"=:CATALOG)

                             6 - access("LOCATION_ID"="LOCATION_ID" AND "PRODUCT_ID"=:PROD)

                             7 - access("LOCATION_ID"=:B1 AND "CATEGORY_ID"=:CAT)

                             8 - access("LOCATION_ID"=:B1 AND "PRODUCT_ID"=:PROD)

                           

                           

                          29 rows selected.

                          {noformat}

                          • 10. Re: SQL Tuning. (union all)
                            Rangarajan

                            \

                            Thanks for your reply. The cost is more when we user OJ

                             

                            \

                             

                            Good day

                            raj

                            • 11. Re: SQL Tuning. (union all)
                              BluShadow

                              You cannot just rely on cost.

                               

                              As I said initially, what makes you think the query is slow?

                               

                              Just because the developers have 'reported it' doesn't mean that it is slow.  You're looking to improve a query without actually having proven what the cause of the performance issue is, or even that there is a performance issue, and that's why people are only guessing what could be improvements without being able to test it, because you are not demonstrating that there is actually an issue, or where the issue is.

                               

                              Both explain plans are showing the query expects to return the results in (under) 1 second.  Where's the problem?

                              • 12. Re: SQL Tuning. (union all)
                                Martin Preiss

                                it's almost sure that your initial plan is the one with the lowest cost: that's the reason why the CBO decided to use it. But that does not gurantee that it is also the plan that brings the fastest execution. To get an idea of the performance impact you would have to run the query with fitting bind variables and take a look at the statistics. And the first thing I would check is the number of consistent gets for each version of the query.

                                • 13. Re: SQL Tuning. (union all)
                                  Etbin
                                  Where's the problem?

                                   

                                  I have sql which is getting executed more than 1million times during peak hours.


                                  most probably part of a function called from within loops ...

                                  way too many guys around here (I don't mean this Forum) convinced a function call takes no time


                                  Regards


                                  Etbin

                                  • 14. Re: SQL Tuning. (union all)
                                    Martin Preiss

                                    maybe sometimes it's better to make such queries slower to force developement to come up with a less ugly solution ...

                                    1 2 Previous Next