This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Sep 10, 2013 1:50 AM by Jonathan Lewis Go to original post RSS
  • 15. Re: SQL Tuning. (union all)
    Rangarajan Newbie
    Currently Being Moderated

    Hi

    Thanks for giving lights.

    SQL> explain plan for select location_id

    from ph_location_prd

    where product_id = :prod

      ;

     

     

    Explained.

     

     

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

    Plan hash value: 3216691719

     

     

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

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

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

    |   0 | SELECT STATEMENT  |                 |   190 |  5700 |    84   (2)| 00:00:02 |

    |*  1 |  TABLE ACCESS FULL| PH_LOCATION_PRD |   190 |  5700 |    84   (2)| 00:00:02 |

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

     

     

    Predicate Information (identified by operation id):

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

     

     

       1 - filter("PRODUCT_ID"=:PROD)

     

     

    13 rows selected.

     

    SQL> explain plan for select location_id

    from ph_location_cats

    where category_id = :cat  2    3

      4  ;

     

     

    Explained.

     

     

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

    Plan hash value: 3353164478

     

     

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

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

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

    |   0 | SELECT STATEMENT            |                        |  2854 |   105K|  2528   (1)| 00:00:31 |

    |   1 |  TABLE ACCESS BY INDEX ROWID| PH_LOCATION_CATS       |  2854 |   105K|  2528   (1)| 00:00:31 |

    |*  2 |   INDEX RANGE SCAN          | ID_PH_LOCATION_CATS_CI |  2854 |       |    31   (0)| 00:00:01 |

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

     

     

    Predicate Information (identified by operation id):

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

     

     

       2 - access("CATEGORY_ID"=:CAT)

     

     

    14 rows selected.

    The repose time of query on table ph_location_cats takes more time here.

     

    We have indics on the table

    SQL> desc ph_location_cats

    Name                                                                    Null?    Type

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

    LOCATION_ID                                                             NOT NULL VARCHAR2(128 CHAR)

    CATEGORY_ID                                                             NOT NULL VARCHAR2(128 CHAR)

     

    IndexUniqueposition
    ID_PH_LOCATION_CATS_CIN1
    PK_PH_LOCATION_CATSY1
    PK_PH_LOCATION_CATSY2

     

    ph_location_prd response time is ok.


    Thanks

    Raj


  • 16. Re: SQL Tuning. (union all)
    Etbin Guru
    Currently Being Moderated

    Some five or six years ago a possible hypothesis seemed to be they were doing it on purpose to come out with great improvements some time later, but in spite of moving to Exadata, it hasn't happened yet (until now).

     

    Regards

     

    Etbin

  • 17. Re: SQL Tuning. (union all)
    jihuyao Journeyer
    Currently Being Moderated

    Result cache in 11g would be better solution to reduce the overhead work by caching each result set or some super sets.

     

    Other than that, there may be some small things to change, assuming those tables are lookup like and relatively static, such as using keep buffer instead of default buffer and adding flag columns in the same table instead of looking up other tables (eliminate the semi or anti semi joins in where clauses).  Those flag column values can be assigned Y/N by trigger and even partitioned if necessary.

     

    These logical/physical design change may be too little and too late comparing to the application design, but 0.001s reduction added up million times is still significant.

  • 18. Re: SQL Tuning. (union all)
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated


    You can't tune this query without knowing the application and data - it's already doing things as efficiently as possible.

    Your first target is to find out why it happens 1,000,000 times per hour and reduce that number.

    If the number can't be reduce then you need to work out which conditions are going to short-circuit the query as early as possible.

    If it's already doing the best possible you may be able to rewrite the query using the "double join" ( double trouble | Oracle Scratchpad ) technique to limit your table visits to a minimum.

     

    Your plan works as follows:

     

    --------------------------------------------------------------------------------------------------------
    | 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)

     

     

    LIne 4 does an index range scan based on catalog_id

     

    Your "IN .. UNION ALL" had been converted to an "EXISTS .. UNION ... " which is called for every index entry found by line 4

     

    Lines 6 and 7 are operating as efficiently as they can, and line 7 isn't called if line 6 returns a row. You might get some benefit here if you make sure that the subquery most likely to return a row appears first in the plan.

     

    Any index entry that survives the existence test results in a visit to the PH_LOCATION table where the longitude and latitude are checked (which suggests that perhaps you should be using Oracle Spatial) before the NOT IN subquery is executed - but this has been transformed into NOT EXISTS and then to an ANTI-JOIN, which operates very efficiently.

     

    Two points with the NOT IN: first, you could check the order of execution of the IN and NOT IN subqueries to see which one of them eliminates most of the data for a catalog_id earliest; secondly both subqueries could operate against the index range scan: if you stopped Oracle from visiting the table BEFORE running the second subquery you might to less work. Possibly a no_unnest hint, perhaps with a push_pred hint would do the trick.  If you can't get the two subqueries to run against the index, you can rewrite the query referencing the ph_location table twice, using a no_merge view to do the index range scan and two subqueries, then a join by rowed to pick up and check any surviving table rows.

     

    Regards

    Jonathan Lewis

1 2 Previous Next

Legend

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