7 Replies Latest reply: Dec 22, 2010 7:48 AM by 704978 RSS

    Query tuning

    704978
      Hi Everyone,

      I have query like below (example) where it gives merge join cartesian.

      SELECT X.TIME_PERIOD EXPOSURE_PERIOD,Y.TIME_PERIOD EVALUATION_PERIOD,BUSINESS_UNIT,
      TO_NUMBER(LOB_VALUE) BUSINESS_UNIT_LOB_ID_FIN,0 CALC_VALUE

      FROM ACTUARIAL_REF_DATA.TIME_PERIOD_HIERARCHY X,ACTUARIAL_REF_DATA.TIME_PERIOD_HIERARCHY Y,ANALYSIS_BUSINESS_UNITS, ANALYSIS_LOBS

      WHERE X.TIME_PERIOD BETWEEN TO_NUMBER('200001') AND TO_NUMBER('200212')
      AND Y.TIME_PERIOD BETWEEN TO_NUMBER('200001') AND TO_NUMBER('200212')
      AND BUSINESS_UNIT = '33011'

      I found that Merge join cartesian is not good in the DB. I have used /*+ORDERED*/  hint but the explain plan is the same and time difference is the almost same.

      Can you please ;et me know the best method to use.

      Regards,
      Sundeep
        • 1. Re: Query tuning
          Shaan_dmp
          I would suggests first try with oracle provided tools sql tuning and access options from OEM...
          • 2. Re: Query tuning
            SatishKandi
            kangula wrote:
            Hi Everyone,

            I have query like below (example) where it gives merge join cartesian.

            SELECT X.TIME_PERIOD EXPOSURE_PERIOD,Y.TIME_PERIOD EVALUATION_PERIOD,BUSINESS_UNIT,
            TO_NUMBER(LOB_VALUE) BUSINESS_UNIT_LOB_ID_FIN,0 CALC_VALUE

            FROM ACTUARIAL_REF_DATA.TIME_PERIOD_HIERARCHY X,ACTUARIAL_REF_DATA.TIME_PERIOD_HIERARCHY Y,ANALYSIS_BUSINESS_UNITS, ANALYSIS_LOBS

            WHERE X.TIME_PERIOD BETWEEN TO_NUMBER('200001') AND TO_NUMBER('200212')
            AND Y.TIME_PERIOD BETWEEN TO_NUMBER('200001') AND TO_NUMBER('200212')
            AND BUSINESS_UNIT = '33011'
            MERGE JOIN CARTESIAN access plan is a result of missing join predicates - which in your case happen to be the joins between all these tables. What you have is individual table conditions but no joins.
            I found that Merge join cartesian is not good in the DB. I have used /*+ORDERED*/  hint but the explain plan is the same and time difference is the almost same.
            This hint, for sure, is not going to help here.
            • 3. Re: Query tuning
              Charles Hooper
              Sundeep,

              This is your query:
              SELECT
                X.TIME_PERIOD EXPOSURE_PERIOD,
                Y.TIME_PERIOD EVALUATION_PERIOD,
                BUSINESS_UNIT,
                TO_NUMBER(LOB_VALUE) BUSINESS_UNIT_LOB_ID_FIN,
                0 CALC_VALUE 
              FROM
                ACTUARIAL_REF_DATA.TIME_PERIOD_HIERARCHY X,
                ACTUARIAL_REF_DATA.TIME_PERIOD_HIERARCHY Y,
                ANALYSIS_BUSINESS_UNITS,
                ANALYSIS_LOBS
              WHERE
                X.TIME_PERIOD BETWEEN TO_NUMBER('200001') AND TO_NUMBER('200212')
                AND Y.TIME_PERIOD BETWEEN TO_NUMBER('200001') AND TO_NUMBER('200212')
                AND BUSINESS_UNIT = '33011'
              You are asking Oracle to retrieve data from 4 tables, and you have applied restrictions on the rows to be returned from at least 2 of the tables, possibly 3 tables. The reason for the Cartesian join is that you did not tell Oracle how the tables were related to each other. Usually, there will be one (two, three, four, etc.) columns that the tables will have in common. You need to list these relationships in the WHERE clause.

              Also, why are you specifying TO_NUMBER('200001'), and not just 200001?

              Charles Hooper
              Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
              http://hoopercharles.wordpress.com/
              IT Manager/Oracle DBA
              K&M Machine-Fabricating, Inc.
              • 4. Re: Query tuning
                704978
                Hi Charles,

                Many thanks for your reply. I have all the 4 tables used in the where condition.

                FROM ACTUARIAL_REF_DATA.TIME_PERIOD_HIERARCHY X,
                ACTUARIAL_REF_DATA.TIME_PERIOD_HIERARCHY Y,
                ANALYSIS_BUSINESS_UNITS,
                ANALYSIS_LOBS


                WHERE X.TIME_PERIOD BETWEEN TO_NUMBER('200001') AND TO_NUMBER('200212')
                AND Y.TIME_PERIOD BETWEEN TO_NUMBER('200001') AND TO_NUMBER('200212')
                AND BUSINESS_UNIT = '33011' AND LOB_VALUE = TO_NUMBER('2920')

                The reason we used TO_NUMBER as the column needs to convert date as below.

                BETWEEN TO_NUMBER (SYS_CONTEXT('RHS_CONTEXT','EXP_DATE_START'))

                Regards,
                Sundeep
                • 5. Re: Query tuning
                  Thierry H.
                  Hi Sundeep,

                  What Charles means is that you did not link any of the tables in your where clause. So, try the following:

                  WHERE
                  X.TIME_PERIOD BETWEEN TO_NUMBER('200001') AND TO_NUMBER('200212')
                  AND Y.TIME_PERIOD = X.TIME_PERIOD
                  AND BUSINESS_UNIT = '33011'


                  Then X is linked to Y !!

                  Regards,
                  Thierry
                  • 6. Re: Query tuning
                    Charles Hooper
                    Hi Sundeep,

                    Thierry has pointed you in the right direction. Here is a very quick demonstration, I will create 3 tables, each with 2 rows:
                    CREATE TABLE T1 (
                      COL1 NUMBER,
                      COL2 NUMBER);
                     
                    CREATE TABLE T2 (
                      COL1 NUMBER,
                      COL2 NUMBER);
                     
                    CREATE TABLE T3 (
                      COL1 NUMBER,
                      COL2 NUMBER);
                     
                    INSERT INTO T1 VALUES(1, 100);
                    INSERT INTO T1 VALUES(2, 101);
                     
                    INSERT INTO T2 VALUES(1, 1000);
                    INSERT INTO T2 VALUES(2, 1001);
                     
                    INSERT INTO T3 VALUES(1000, 2000);
                    INSERT INTO T3 VALUES(1001, 2001);
                    Now, let's assume that I want to retrieve the rows from the tables, so I try something like this (after turning on AUTOTRACE to see the execution plan):
                    SET AUTOTRACE ON
                     
                    SELECT
                      *
                    FROM
                      T1,
                      T2,
                      T3
                    WHERE
                      T1.COL2 BETWEEN 100 AND 101
                      AND T2.COL2 BETWEEN 1000 AND 1001
                      AND T3.COL2 BETWEEN 2000 AND 2001;
                    In the above, notice that I have restrictions specified for each of the tables, but I have not specified how the tables are related to one another. The query as written returned 8 rows:
                          COL1       COL2       COL1       COL2       COL1       COL2
                    ---------- ---------- ---------- ---------- ---------- ----------
                             1        100          1       1000       1000       2000
                             1        100          1       1000       1001       2001
                             1        100          2       1001       1000       2000
                             1        100          2       1001       1001       2001
                             2        101          1       1000       1000       2000
                             2        101          1       1000       1001       2001
                             2        101          2       1001       1000       2000
                             2        101          2       1001       1001       2001
                     
                    8 rows selected.
                    And the execution plan looked like this:
                    Execution Plan
                    ----------------------------------------------------------
                    Plan hash value: 3820470131
                     
                    ------------------------------------------------------------------------------
                    | Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                    ------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT      |      |     8 |   624 |     9   (0)| 00:00:01 |
                    |   1 |  MERGE JOIN CARTESIAN |      |     8 |   624 |     9   (0)| 00:00:01 |
                    |   2 |   MERGE JOIN CARTESIAN|      |     4 |   208 |     5   (0)| 00:00:01 |
                    |*  3 |    TABLE ACCESS FULL  | T1   |     2 |    52 |     2   (0)| 00:00:01 |
                    |   4 |    BUFFER SORT        |      |     2 |    52 |     3   (0)| 00:00:01 |
                    |*  5 |     TABLE ACCESS FULL | T2   |     2 |    52 |     2   (0)| 00:00:01 |
                    |   6 |   BUFFER SORT         |      |     2 |    52 |     8   (0)| 00:00:01 |
                    |*  7 |    TABLE ACCESS FULL  | T3   |     2 |    52 |     1   (0)| 00:00:01 |
                    ------------------------------------------------------------------------------
                     
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                       3 - filter("T1"."COL2">=100 AND "T1"."COL2"<=101)
                       5 - filter("T2"."COL2">=1000 AND "T2"."COL2"<=1001)
                       7 - filter("T3"."COL2">=2000 AND "T3"."COL2"<=2001)
                     
                    Note
                    -----
                       - dynamic sampling used for this statement
                    Notice in the execution plan that there are two MERGE JOIN CARTESIAN operations - that basically states that if there are 2 rows matching the restrictions on the first table, and 2 rows matching the restriction on the second and third tables, 2 * 2 * 2 = 8 rows will be produced. If each of the restrictions on the tables produced 1 row, or a Cartesian join appears between a table row source with a single row and another table with more than one row, that is not an indication of a problem.

                    Now, let's fix the SQL statement to indicate how the tables are related:
                    SELECT
                      *
                    FROM
                      T1,
                      T2,
                      T3
                    WHERE
                      T1.COL2 BETWEEN 100 AND 101
                      AND T2.COL2 BETWEEN 1000 AND 1001
                      AND T3.COL2 BETWEEN 2000 AND 2001
                      AND T1.COL1 = T2.COL1
                      AND T2.COL2 = T3.COL1;
                    The above change results in two rows:
                          COL1       COL2       COL1       COL2       COL1       COL2
                    ---------- ---------- ---------- ---------- ---------- ----------
                             1        100          1       1000       1000       2000
                             2        101          2       1001       1001       2001
                    The execution plan produced looks like this:
                    Execution Plan
                    ----------------------------------------------------------
                    Plan hash value: 261998084
                     
                    ----------------------------------------------------------------------------
                    | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                    ----------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT    |      |     2 |   156 |     7  (15)| 00:00:01 |
                    |*  1 |  HASH JOIN          |      |     2 |   156 |     7  (15)| 00:00:01 |
                    |*  2 |   HASH JOIN         |      |     2 |   104 |     5  (20)| 00:00:01 |
                    |*  3 |    TABLE ACCESS FULL| T1   |     2 |    52 |     2   (0)| 00:00:01 |
                    |*  4 |    TABLE ACCESS FULL| T2   |     2 |    52 |     2   (0)| 00:00:01 |
                    |*  5 |   TABLE ACCESS FULL | T3   |     2 |    52 |     2   (0)| 00:00:01 |
                    ----------------------------------------------------------------------------
                     
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                       1 - access("T2"."COL2"="T3"."COL1")
                       2 - access("T1"."COL1"="T2"."COL1")
                       3 - filter("T1"."COL2">=100 AND "T1"."COL2"<=101)
                       4 - filter("T2"."COL2">=1000 AND "T2"."COL2"<=1001)
                       5 - filter("T3"."COL2">=2000 AND "T3"."COL2"<=2001 AND
                                  "T3"."COL1">=1000 AND "T3"."COL1"<=1001)
                     
                    Note
                    -----
                       - dynamic sampling used for this statement
                    -----------------------

                    If a column is defined as a DATE column, it is much better to treat it as a DATE column in WHERE clauses.

                    If you want to do a little reading, take a look at a couple of articles that I wrote that will probably be helpful for you:
                    http://hoopercharles.wordpress.com/2010/01/24/sql-basics-working-with-erp-data/
                    http://hoopercharles.wordpress.com/2010/01/06/date-datatype-or-number-data-type-which-should-be-used/

                    Charles Hooper
                    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                    http://hoopercharles.wordpress.com/
                    IT Manager/Oracle DBA
                    K&M Machine-Fabricating, Inc.
                    • 7. Re: Query tuning
                      704978
                      Hi Charles,

                      Thanks for the Example and I am clear with the issue. I have changed the query adding joining condition and i can see performance improvement in execution time.

                      Will execute the entire SQL ad check if this would be helpful.

                      Just to add, Nice articles in "http://hoopercharles.wordpress.com"

                      Regards,
                      Sundeep K