1 Reply Latest reply: Mar 8, 2010 9:01 AM by user503699 RSS

    Slow query with nested loop instead of hash join after choosing index acces

    TimWong765
      The following query performs as expected (Oracle 10.2.0.4 EE on Solaris 10)
      SQL> SELECT  /*+ GATHER_PLAN_STATISTICS */
        2          count(*)
        3        FROM TRT_RESPONSE r, TRT_ACTUAL_SAMPLE p, TRT_DELIVERYOCCURRENCE co
        4        WHERE r.loadenddate IS NULL
        5          AND p.loadenddate(+) IS NULL
        6          AND co.loadenddate IS NULL
        7          AND (r.client = p.client(+) AND r.ADID = p.ADID(+) AND r.ESOCCUR_ID = p.ESOCCUR_ID(+) AND r.occurrence = p.occurrence(+))
        8          AND (r.client = co.client AND r.ESOCCUR_ID = co.ESOCCUR_ID AND r.occurrence = co.occurrence);
      
      select * from table(dbms_xplan.display_cursor(null,null, 'ALLSTATS LAST +COST'));
      
      
      
        COUNT(*)
      ----------
         5775301
      
      SQL> SQL>
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      SQL_ID  1sgbdjxjddyk7, child number 0
      -------------------------------------
      SELECT  /*+ GATHER_PLAN_STATISTICS */         count(*)       FROM TRT_RESPONSE r, TRT_ACTUAL_SAMPLE p, TRT_DELIVERYOCCURRENCE co       WHERE
      r.loadenddate IS NULL         AND p.loadenddate(+) IS NULL         AND co.loadenddate IS NULL         AND (r.client = p.client(+) AND r.ADID =
      p.ADID(+) AND r.ESOCCUR_ID = p.ESOCCUR_ID(+) AND r.occurrence = p.occurrence(+))         AND (r.client = co.client AND r.ESOCCUR_ID = co.ESOCCUR_ID
      AND r.occurrence = co.occurrence)
      
      Plan hash value: 3087256236
      
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation               | Name                     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------
      |   1 |  SORT AGGREGATE         |                          |      1 |      1 |            |      1 |00:02:47.62 |     934K|    925K|       |       |          |
      |*  2 |   HASH JOIN OUTER       |                          |      1 |    402K|  1091K (16)|   5775K|00:02:34.28 |     934K|    925K|   295M|     9M|  376M (0)|
      |*  3 |    HASH JOIN            |                          |      1 |    402K| 29907  (28)|   5775K|00:00:17.35 |   41655 |  34162 |  1999K|  1333K| 2208K (0)|
      |*  4 |     INDEX FAST FULL SCAN| X_TRT_DELIVERYOCCURRENCE |      1 |  27135 |   137  (26)|  27194 |00:00:00.01 |     223 |      0 |       |       |          |
      |*  5 |     INDEX FAST FULL SCAN| X_TRT_RESPONSE           |      1 |   5775K| 28401  (25)|   5775K|00:00:05.78 |   41432 |  34162 |       |       |          |
      |*  6 |    INDEX FAST FULL SCAN | X_TRT_ACTUAL_SAMPLE      |      1 |     80M|   604K (21)|     80M|00:01:20.97 |     892K|    891K|       |       |          |
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - access("CLIENT"="CLIENT" AND "ADID"="ADID" AND "ESOCCUR_ID"="ESOCCUR_ID" AND "OCCURRENCE"="OCCURRENCE")
         3 - access("CLIENT"="CLIENT" AND "ESOCCUR_ID"="ESOCCUR_ID" AND "OCCURRENCE"="OCCURRENCE")
         4 - filter(("LOADENDDATE" IS NULL AND "CLIENT"=SYS_CONTEXT('MY_APP_CONTEXT','client')))
         5 - filter(("LOADENDDATE" IS NULL AND "CLIENT"=SYS_CONTEXT('MY_APP_CONTEXT','client')))
         6 - filter(("LOADENDDATE" IS NULL AND "CLIENT"=SYS_CONTEXT('MY_APP_CONTEXT','client')))
      
      
      29 rows selected.
      If I replace count(*) by columns, I still get a similiar plan after adding columns in table a and co:


      SQL> explain plan for
        2  SELECT  r.client,
        3          co.projectid
        4        FROM TRT_RESPONSE r, TRT_ACTUAL_SAMPLE p, TRT_DELIVERYOCCURRENCE co
        5        WHERE r.loadenddate IS NULL
        6          AND p.loadenddate(+) IS NULL
        7          AND co.loadenddate IS NULL
        8          AND (r.client = p.client(+) AND r.ADID = p.ADID(+) AND r.ESOCCUR_ID = p.ESOCCUR_ID(+) AND r.occurrence = p.occurrence(+))
        9          AND (r.client = co.client AND r.ESOCCUR_ID = co.ESOCCUR_ID AND r.occurrence = co.occurrence);
      
      
      Explained.
      
      SQL> SQL>
      SQL> select * from table(dbms_xplan.display);
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      Plan hash value: 237071247
      
      ---------------------------------------------------------------------------------------------------------
      | Id  | Operation              | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
      ---------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT       |                        |   402K|    28M|       |  1091K (16)| 00:04:40 |
      |*  1 |  HASH JOIN OUTER       |                        |   402K|    28M|    22M|  1091K (16)| 00:04:40 |
      |*  2 |   HASH JOIN            |                        |   402K|    17M|       | 30444  (28)| 00:00:08 |
      |*  3 |    TABLE ACCESS FULL   | TRT_DELIVERYOCCURRENCE | 27135 |   582K|       |   674  (11)| 00:00:01 |
      |*  4 |    INDEX FAST FULL SCAN| X_TRT_RESPONSE         |  5775K|   132M|       | 28401  (25)| 00:00:08 |
      |*  5 |   INDEX FAST FULL SCAN | X_TRT_ACTUAL_SAMPLE    |    80M|  2084M|       |   604K (21)| 00:02:35 |
      ---------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - access("CLIENT"="CLIENT"(+) AND "ADID"="ADID"(+) AND "ESOCCUR_ID"="ESOCCUR_ID"(+) AND
                    "OCCURRENCE"="OCCURRENCE"(+))
         2 - access("CLIENT"="CLIENT" AND "ESOCCUR_ID"="ESOCCUR_ID" AND "OCCURRENCE"="OCCURRENCE")
         3 - filter("LOADENDDATE" IS NULL AND "CLIENT"=SYS_CONTEXT('MY_APP_CONTEXT','client'))
         4 - filter("LOADENDDATE" IS NULL AND "CLIENT"=SYS_CONTEXT('MY_APP_CONTEXT','client'))
         5 - filter("LOADENDDATE"(+) IS NULL AND "CLIENT"(+)=SYS_CONTEXT('MY_APP_CONTEXT','client'))
      
      22 rows selected.
      If I further add the column deliverydate from table p, the execution plan changes from a hash join to a nested loop. I stopped the query after 2 hours. Actually this is the query (with some more columns in the select clause) that is running slow. The nested loop with the index access needs to be avoided. Current object statistics are available.



      SQL> explain plan for
        2  SELECT  r.client,
        3          co.projectid,
              p.deliverydate
        4    5        FROM TRT_RESPONSE r, TRT_ACTUAL_SAMPLE p, TRT_DELIVERYOCCURRENCE co
        6        WHERE r.loadenddate IS NULL
        7          AND p.loadenddate(+) IS NULL
        8          AND co.loadenddate IS NULL
        9          AND (r.client = p.client(+) AND r.ADID = p.ADID(+) AND r.ESOCCUR_ID = p.ESOCCUR_ID(+) AND r.occurrence = p.occurrence(+))
       10          AND (r.client = co.client AND r.ESOCCUR_ID = co.ESOCCUR_ID AND r.occurrence = co.occurrence);
      
      
      select * from table(dbms_xplan.display);
      
      Explained.
      
      SQL> SQL> SQL>
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      Plan hash value: 3091807501
      
      -------------------------------------------------------------------------------------------------------
      | Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
      -------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT             |                        |   402K|    31M|  1337K  (3)| 00:05:43 |
      |   1 |  NESTED LOOPS OUTER          |                        |   402K|    31M|  1337K  (3)| 00:05:43 |
      |*  2 |   HASH JOIN                  |                        |   402K|    17M| 30444  (28)| 00:00:08 |
      |*  3 |    TABLE ACCESS FULL         | TRT_DELIVERYOCCURRENCE | 27135 |   582K|   674  (11)| 00:00:01 |
      |*  4 |    INDEX FAST FULL SCAN      | X_TRT_RESPONSE         |  5775K|   132M| 28401  (25)| 00:00:08 |
      |   5 |   TABLE ACCESS BY INDEX ROWID| TRT_ACTUAL_SAMPLE      |     1 |    35 |     4   (0)| 00:00:01 |
      |*  6 |    INDEX RANGE SCAN          | X_TRT_ACTUAL_SAMPLE    |     1 |       |     3   (0)| 00:00:01 |
      -------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - access("CLIENT"="CLIENT" AND "ESOCCUR_ID"="ESOCCUR_ID" AND "OCCURRENCE"="OCCURRENCE")
         3 - filter("LOADENDDATE" IS NULL AND "CLIENT"=SYS_CONTEXT('MY_APP_CONTEXT','client'))
         4 - filter("LOADENDDATE" IS NULL AND "CLIENT"=SYS_CONTEXT('MY_APP_CONTEXT','client'))
         6 - access("CLIENT"(+)=SYS_CONTEXT('MY_APP_CONTEXT','client') AND "ADID"="ADID"(+) AND
                    "ESOCCUR_ID"="ESOCCUR_ID"(+) AND "OCCURRENCE"="OCCURRENCE"(+) AND "LOADENDDATE"(+) IS NULL)
             filter("CLIENT"="CLIENT"(+))
      
      23 rows selected.
      A no-index hint forces the CBO again to use the hash join. I'd like to avoid the hint. Are there better possibilities (= no hints) to force the CBO to not use the nested loop?



      SQL> explain plan for
        2  SELECT  /*+ NO_INDEX(p) */ r.client,
        3          co.projectid,
        4          p.deliverydate
        5        FROM TRT_RESPONSE r, TRT_ACTUAL_SAMPLE p, TRT_DELIVERYOCCURRENCE co
        6        WHERE r.loadenddate IS NULL
        7          AND p.loadenddate(+) IS NULL
              AND co.loadenddate IS NULL
        8    9          AND (r.client = p.client(+) AND r.ADID = p.ADID(+) AND r.ESOCCUR_ID = p.ESOCCUR_ID(+) AND r.occurrence = p.occurrence(+))
       10          AND (r.client = co.client AND r.ESOCCUR_ID = co.ESOCCUR_ID AND r.occurrence = co.occurrence);
      
      
      select * from table(dbms_xplan.display);
      Explained.
      
      SQL> SQL> SQL>
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      Plan hash value: 2229640215
      
      ---------------------------------------------------------------------------------------------------------
      | Id  | Operation              | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
      ---------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT       |                        |   402K|    31M|       |  2425K (11)| 00:10:21 |
      |*  1 |  HASH JOIN OUTER       |                        |   402K|    31M|    22M|  2425K (11)| 00:10:21 |
      |*  2 |   HASH JOIN            |                        |   402K|    17M|       | 30444  (28)| 00:00:08 |
      |*  3 |    TABLE ACCESS FULL   | TRT_DELIVERYOCCURRENCE | 27135 |   582K|       |   674  (11)| 00:00:01 |
      |*  4 |    INDEX FAST FULL SCAN| X_TRT_RESPONSE         |  5775K|   132M|       | 28401  (25)| 00:00:08 |
      |*  5 |   TABLE ACCESS FULL    | TRT_ACTUAL_SAMPLE      |    80M|  2701M|       |  1852K (12)| 00:07:55 |
      ---------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - access("CLIENT"="CLIENT"(+) AND "ADID"="ADID"(+) AND "ESOCCUR_ID"="ESOCCUR_ID"(+) AND
                    "OCCURRENCE"="OCCURRENCE"(+))
         2 - access("CLIENT"="CLIENT" AND "ESOCCUR_ID"="ESOCCUR_ID" AND "OCCURRENCE"="OCCURRENCE")
         3 - filter("LOADENDDATE" IS NULL AND "CLIENT"=SYS_CONTEXT('MY_APP_CONTEXT','client'))
         4 - filter("LOADENDDATE" IS NULL AND "CLIENT"=SYS_CONTEXT('MY_APP_CONTEXT','client'))
         5 - filter("LOADENDDATE"(+) IS NULL AND "CLIENT"(+)=SYS_CONTEXT('MY_APP_CONTEXT','client'))
      
      22 rows selected.
      Object statistics are:

      SQL> SELECT table_name
           , num_rows
           , blocks
      FROM   dba_tab_statistics
      WHERE  table_name in ('TRT_RESPONSE', 'TRT_DELIVERYOCCURRENCE', 'TRT_ACTUAL_SAMPLE')
      ;
        2    3    4    5    6    7
      TABLE_NAME                       NUM_ROWS     BLOCKS
      ------------------------------ ---------- ----------
      TRT_DELIVERYOCCURRENCE              39091       1126
      TRT_ACTUAL_SAMPLE               139492195    3079472
      TRT_RESPONSE                      6362583      95393
      
      
      
      SQL> select count(*) from TRT_DELIVERYOCCURRENCE;
        COUNT(*)
      ----------
           39150
      
      
      SQL> select count(*) from TRT_ACTUAL_SAMPLE;
        COUNT(*)
      ----------
       139533896
      
      
      SQL> select count(*) from TRT_RESPONSE;
        COUNT(*)
      ----------
         6362583
      
      
      
      
      SQL> SELECT table_name
        2       , column_name
           , num_distinct
        3    4       , density
        5       , num_nulls
        6       , avg_col_len
        7       , histogram
        8       , num_buckets
        9  FROM   dba_tab_col_statistics
       10  WHERE  table_name in ('TRT_RESPONSE', 'TRT_DELIVERYOCCURRENCE', 'TRT_ACTUAL_SAMPLE')
       11  order by 1,2
       12  ;
      
      TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS AVG_COL_LEN HISTOGRAM       NUM_BUCKETS
      ------------------------------ ------------------------------ ------------ ---------- ---------- ----------- --------------- -----------
      TRT_DELIVERYOCCURRENCE         ESOCCUR_ID                             1932          1          0           5 NONE                      1
      TRT_DELIVERYOCCURRENCE         CLIENT                                    1          1          0           6 NONE                      1
      TRT_DELIVERYOCCURRENCE         LOADDATE                               1192 .001207975          0           8 HEIGHT BALANCED         254
      TRT_DELIVERYOCCURRENCE         LOADENDDATE                              30  .00004182      27135           4 FREQUENCY                30
      TRT_DELIVERYOCCURRENCE         OCCURRENCE                              522          1          0           4 NONE                      1
      TRT_ACTUAL_SAMPLE              ESOCCUR_ID                              595 .002444988          0           5 HEIGHT BALANCED         254
      TRT_ACTUAL_SAMPLE              CLIENT                                    1 3.5596E-09          0           6 FREQUENCY                 1
      TRT_ACTUAL_SAMPLE              DELIVERYDATE                           2589 .002262443     512650           8 HEIGHT BALANCED         254
      TRT_ACTUAL_SAMPLE              LOADDATE                                640 .002352941          0           8 HEIGHT BALANCED         254
      TRT_ACTUAL_SAMPLE              LOADENDDATE                             583 .002906977   80945297           5 HEIGHT BALANCED         254
      TRT_ACTUAL_SAMPLE              OCCURRENCE                              125 3.5596E-09          0           4 FREQUENCY               125
      TRT_ACTUAL_SAMPLE              ADID                                3711876 7.8837E-07          0           7 HEIGHT BALANCED         254
      TRT_RESPONSE                   ESOCCUR_ID                              988          1          0           5 NONE                      1
      TRT_RESPONSE                   CLIENT                                    1          1          0           6 NONE                      1
      TRT_RESPONSE                   LOADDATE                               1294 .002129496          0           8 HEIGHT BALANCED         254
      TRT_RESPONSE                   LOADENDDATE                             670 .001841031    5775301           2 HEIGHT BALANCED         254
      TRT_RESPONSE                   OCCURRENCE                              394          1          0           4 NONE                      1
      TRT_RESPONSE                   ADID                                3402137 5.2288E-07          0           7 HEIGHT BALANCED         254
      
      55 rows selected.
      
      
      
      SQL> SELECT table_name
        2       , index_name
        3       , blevel
        4       , leaf_blocks
           , distinct_keys
        5    6       , num_rows
        7       , clustering_factor
        8       , avg_leaf_blocks_per_key
        9       , avg_data_blocks_per_key
       10  FROM   dba_ind_statistics
       11  WHERE  table_name in ('TRT_RESPONSE', 'TRT_DELIVERYOCCURRENCE', 'TRT_ACTUAL_SAMPLE')
       12  order by 1,2
       13  ;
      
      TABLE_NAME                     INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS   NUM_ROWS CLUSTERING_FACTOR AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
      ------------------------------ ------------------------------ ---------- ----------- ------------- ---------- ----------------- ----------------------- -----------------------
      TRT_DELIVERYOCCURRENCE         XPKTRT_DELIVERYOCCURRENCE               1         228         39091      39091             38753                       1                       1
      TRT_DELIVERYOCCURRENCE         X_TRT_DELIVERYOCCURRENCE                1         187         39056      39091             38749                       1                       1
      TRT_ACTUAL_SAMPLE              XPKTRT_ACTUAL_SAMPLE                    3      907781     136715448  136715448         136527432                       1                       1
      TRT_ACTUAL_SAMPLE              X_TRT_ACTUAL_SAMPLE                     3      905535     143381320  143381320         143263921                       1                       1
      TRT_RESPONSE                   XPKTRT_RESPONSE                         2       46757       6362583    6362583           6118439                       1                       1
      TRT_RESPONSE                   X_TRT_RESPONSE                          2       40368       6353942    6362583           6118292                       1                       1
      
      6 rows selected.
        • 1. Re: Slow query with nested loop instead of hash join after choosing index a
          user503699
          TimWong765 wrote:
          If I further add the column deliverydate from table p, the execution plan changes from a hash join to a nested loop. I stopped the query after 2 hours. Actually this is the query (with some more columns in the select clause) that is running slow. The nested loop with the index access needs to be avoided. Current object statistics are available.
          The (automatic) change in execution plan is not necessarily a surprise and is an expected behaviour.
          But I would suggest you trace the query which is the actual version and do not expect to tune a "similar" query.
          So what is the actual query and its corresponding plan? Also, what is the outcome of following commands (from SQl*plus)?
          show parameter optimizer
          show parameter workarea
          show parameter pga
          But the most important question is what do you plan to do with ~5.7 million rows that this query returns ? Are you sure you want to "process" so many rows in the calling application ?

          Edited by: user503699 on Mar 8, 2010 8:31 PM