12 Replies Latest reply: Jan 4, 2012 8:34 AM by TimWong765 RSS

    Bad execution plan in 11.2.0.2 compared to 10.2.0.4 with dynamic sampling

    TimWong765
      We upgraded an application from 10.2.0.4 to 11.2.0.2. Some queries use dynamic sampling (Table IDENT_TRANS_HASH does not have statistics - intentionally)

      10.2.0.4 produced the following (good) plan
      --------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
      | Id  | Operation                        | Name               | Rows  | Bytes | Cost  | Time      |  TQ  |IN-OUT|PQ Distrib | Pstart| Pstop |
      --------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
      | 0   | SELECT STATEMENT                 |                    |       |       | 3412K |           |      |      |           |       |       |
      | 1   |  PX COORDINATOR                  |                    |       |       |       |           |      |      |           |       |       |
      | 2   |   PX SEND QC (RANDOM)            | :TQ10003           | 1085K |  191M | 3412K |  03:12:12 |:Q1003| P->S |QC (RANDOM)|       |       |
      | 3   |    HASH JOIN BUFFERED            |                    | 1085K |  191M | 3412K |  03:12:12 |:Q1003| PCWP |           |       |       |
      | 4   |     BUFFER SORT                  |                    |       |       |       |           |:Q1003| PCWC |           |       |       |
      | 5   |      PX RECEIVE                  |                    |     1 |    32 |    10 |  00:00:01 |:Q1003| PCWP |           |       |       |
      | 6   |       PX SEND BROADCAST          | :TQ10000           |     1 |    32 |    10 |  00:00:01 |      | S->P |BROADCAST  |       |       |
      | 7   |        VIEW                      |                    |     1 |    32 |    10 |  00:00:01 |      |      |           |       |       |
      | 8   |         HASH UNIQUE              |                    |     1 |    73 |    10 |  00:00:01 |      |      |           |       |       |
      | 9   |          HASH JOIN               |                    |     1 |    73 |     5 |  00:00:01 |      |      |           |       |       |
      | 10  |           PARTITION RANGE SINGLE |                    |     1 |    69 |     2 |  00:00:01 |      |      |           | 6     | 6     |
      | 11  |            TABLE ACCESS FULL     | TRANS_IDENT_RULESET|     1 |    69 |     2 |  00:00:01 |      |      |           | 6     | 6     |
      | 12  |           PARTITION RANGE ALL    |                    |     1 |     4 |     2 |  00:00:01 |      |      |           | 1     | 9     |
      | 13  |            INDEX FAST FULL SCAN  | XPKTRANS_JOB       |     1 |     4 |     2 |  00:00:01 |      |      |           | 1     | 9     |
      | 14  |             SORT AGGREGATE       |                    |     1 |    12 |       |           |      |      |           |       |       |
      | 15  |              PARTITION RANGE ALL |                    |     2 |    24 |     4 |  00:00:01 |      |      |           | 1     | 9     |
      | 16  |               TABLE ACCESS FULL  | TRANS_JOB          |     2 |    24 |     4 |  00:00:01 |      |      |           | 1     | 9     |
      | 17  |     HASH JOIN                    |                    | 1085K |  157M | 3412K |  03:12:12 |:Q1003| PCWP |           |       |       |
      | 18  |      PX RECEIVE                  |                    |   21M | 1569M | 1706K |  02:36:06 |:Q1003| PCWP |           |       |       |
      | 19  |       PX SEND HASH               | :TQ10001           |   21M | 1569M | 1706K |  02:36:06 |:Q1001| P->P |HASH       |       |       |
      | 20  |        PX BLOCK ITERATOR         |                    |   21M | 1569M | 1706K |  02:36:06 |:Q1001| PCWC |           | 1     | 32    |
      | 21  |         TABLE ACCESS FULL        | IDENT_TRANS_HASH   |   21M | 1569M | 1706K |  02:36:06 |:Q1001| PCWP |           | 161   | 192   |
      | 22  |      PX RECEIVE                  |                    |   21M | 1569M | 1706K |  02:36:06 |:Q1003| PCWP |           |       |       |
      | 23  |       PX SEND HASH               | :TQ10002           |   21M | 1569M | 1706K |  02:36:06 |:Q1002| P->P |HASH       |       |       |
      | 24  |        PX BLOCK ITERATOR         |                    |   21M | 1569M | 1706K |  02:36:06 |:Q1002| PCWC |           | 1     | 32    |
      | 25  |         TABLE ACCESS FULL        | IDENT_TRANS_HASH   |   21M | 1569M | 1706K |  02:36:06 |:Q1002| PCWP |           | 161   | 192   |
      --------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
      Predicate Information:
      ----------------------
      3 - access("S"."CLIENT"="SCOPE"."CLIENT" AND "S"."IDENTTYPEID"="SCOPE"."IDENTTYPEID" AND "T"."CLIENT"="SCOPE"."CLIENT" AND "T"."IDENTTYPEID"="SCOPE"."IDENTTYPEID")
      9 - access("JOBID"="J"."JOBID")
      11 - filter(("IDENTDOMAIN"='CONTACT' AND "SCOPE"='DYNAMIC' AND INTERNAL_FUNCTION("TYPE") AND "CLIENT"='IT_XX'))
      13 - filter("JOBID"=)
      16 - filter("LOADENDDATE" IS NULL)
      17 - access("T"."IDENTHASH"="S"."IDENTHASH")
      17 - filter("T"."HEADERID">"S"."HEADERID")
      21 - access(:Z>=:Z AND :Z<=:Z)
      21 - filter("S"."CLIENT"='IT_XX')
      25 - access(:Z>=:Z AND :Z<=:Z)
      25 - filter("T"."CLIENT"='IT_XX')
      while 11.2.0.2 shows a bad plan
      ----------------------------------------------------------+-----------------------------------+---------------+
      | Id  | Operation                    | Name               | Rows  | Bytes | Cost  | Time      | Pstart| Pstop |
      ----------------------------------------------------------+-----------------------------------+---------------+
      | 0   | SELECT STATEMENT             |                    |       |       | 8650K |           |       |       |
      | 1   |  NESTED LOOPS                |                    |     2 |   914 | 8650K |  34:27:49 |       |       |
      | 2   |   HASH JOIN                  |                    |   15K | 6080K | 6573K |  26:11:33 |       |       |
      | 3   |    PARTITION RANGE ALL       |                    |   366 |   71K |    23 |  00:00:01 | 1     | 12    |
      | 4   |     PARTITION HASH ALL       |                    |   366 |   71K |    23 |  00:00:01 | 1     | 32    |
      | 5   |      TABLE ACCESS FULL       | IDENT_TRANS_HASH   |   366 |   71K |    23 |  00:00:01 | 1     | 384   |
      | 6   |    PARTITION RANGE SINGLE    |                    |   17M | 3459M | 6573K |  26:11:32 | 8     | 8     |
      | 7   |     PARTITION HASH ALL       |                    |   17M | 3459M | 6573K |  26:11:32 | 1     | 32    |
      | 8   |      TABLE ACCESS FULL       | IDENT_TRANS_HASH   |   17M | 3459M | 6573K |  26:11:32 | 225   | 256   |
      | 9   |   VIEW PUSHED PREDICATE      |                    |     1 |    57 |   137 |  00:00:02 |       |       |
      | 10  |    SORT UNIQUE               |                    |     2 |   116 |   137 |  00:00:02 |       |       |
      | 11  |     FILTER                   |                    |       |       |       |           |       |       |
      | 12  |      HASH JOIN               |                    |     2 |   116 |     9 |  00:00:01 |       |       |
      | 13  |       PARTITION RANGE ALL    |                    |     1 |     5 |     4 |  00:00:01 | 1     | 12    |
      | 14  |        INDEX FAST FULL SCAN  | XPKTRANS_JOB       |     1 |     5 |     4 |  00:00:01 | 1     | 12    |
      | 15  |         SORT AGGREGATE       |                    |     1 |    13 |       |           |       |       |
      | 16  |          PARTITION RANGE ALL |                    |     1 |    13 |   127 |  00:00:02 | 1     | 12    |
      | 17  |           TABLE ACCESS FULL  | TRANS_JOB          |     1 |    13 |   127 |  00:00:02 | 1     | 12    |
      | 18  |       PARTITION RANGE SINGLE |                    |    38 |  2014 |     4 |  00:00:01 | KEY   | KEY   |
      | 19  |        TABLE ACCESS FULL     | TRANS_IDENT_RULESET|    38 |  2014 |     4 |  00:00:01 | KEY   | KEY   |
      ----------------------------------------------------------+-----------------------------------+---------------+
      Predicate Information:
      ----------------------
      2 - access("T"."IDENTHASH"="S"."IDENTHASH")
      2 - filter("T"."HEADERID">"S"."HEADERID")
      8 - filter("T"."CLIENT"='IT_XX')
      9 - filter(("S"."IDENTTYPEID"="SCOPE"."IDENTTYPEID" AND "T"."IDENTTYPEID"="SCOPE"."IDENTTYPEID"))
      11 - filter("T"."CLIENT"="S"."CLIENT")
      12 - access("JOBID"="J"."JOBID")
      14 - filter("JOBID"=)
      17 - filter("LOADENDDATE" IS NULL)
      19 - filter(("IDENTDOMAIN"='CONTACT' AND "SCOPE"='DYNAMIC' AND INTERNAL_FUNCTION("TYPE") AND "CLIENT"="T"."CLIENT"))
      The query is
        SELECT 
        S.CLIENT,
        S.HEADERID,
        S.IDENTTYPEID,
        T.HEADERID as IDENTHEADER,
        S.UNMATCH,
        T.UNMATCH,
        SCOPE.TYPE,
        S.IDENTHASH 
      FROM   
        (SELECT --+no_merge 
           DISTINCT CLIENT, IDENTTYPEID, TYPE
         FROM transfer0.V_TRANS_IDENT_RULESET
         WHERE IDENTDOMAIN='CONTACT'
           AND SCOPE='DYNAMIC'
           AND TYPE in ('IDENT', 'REJECT')) SCOPE,
        identification0.IDENT_TRANS_HASH S,
        identification0.IDENT_TRANS_HASH T
      WHERE S.CLIENT=SCOPE.CLIENT
        AND S.IDENTTYPEID=SCOPE.IDENTTYPEID
        AND T.CLIENT=SCOPE.CLIENT
        AND T.IDENTTYPEID=SCOPE.IDENTTYPEID
        AND T.HEADERID>S.HEADERID
        AND T.IDENTHASH=S.IDENTHASH
        and t.client = 'IT_XX';
      Strangely, a hint
      SELECT --+ dynamic_sampling(S,5) dynamic_sampling(T,5)
      ... etc
      changes the plan to a good one while
      alter session set optimizer_dynamic_sampling=5;
      does not improve the plan.

      What can cause the optimier to choose the bad execution plan in 11.2.0.2?
        • 1. Re: Bad execution plan in 11.2.0.2 compared to 10.2.0.4 with dynamic sampling
          TimWong765
          I uploaded 10053 traces
          http://dl.dropbox.com/u/55386528/10053_10_2_0_4.trc
          http://dl.dropbox.com/u/55386528/10053_11_2_0_2.trc
          The dynamic sampling section for 11.2.0.2 looks ok but the join order seams to go wrong.
          • 2. Re: Bad execution plan in 11.2.0.2 compared to 10.2.0.4 with dynamic sampling
            rp0428
            First of all CONGRATULATIONS to you for actually having a prior execution plan to compare a new one to! Far too often those plans are not kept for comparison.

            See Jonathan Lewis's response (from 2006 no less!) at this askTom link: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:30752265185078

            Copied here for convenience:
            Looks like the poster has a table which has got statistics, so the 'alter session' to level 2 has 
            no effect - and level 2 is the 10g default anyway.  The query would only be sampled (assuming the 
            table had proper stats) if the parameter setting was 4 - forcing a sample because of the potential 
            for dependent predicates.
            
            Setting the hint to level 2 in the statement - whether query level or table level - forces a sample 
            whether or not it's needed.  
            and this block from another link should answer your first question: http://blogs.oracle.com/optimizer/entry/dynamic_sampling_and_its_impact_on_the_optimizer
            From Oracle Database 11g Release 2 onwards the optimizer will automatically decide if dynamic sampling will be useful and what dynamic sampling level will be used for SQL statements executed in parallel. This decision is based on size of the tables in the statement and the complexity of the predicates. However, if the OPTIMIZER_DYNAMIC_SAMPLING parameter is explicitly set to a non-default value, then that specified value will be honored. You can tell if dynamic sampling kicks in by looks in the note section of the execution plan.
            I personally try to insist on NEVER relying on default settings for any parameter since a default value is subject to change between releases, even incremental ones. Also, as with all things Oracle, you can usually never force Oracle to do anything but can only HINT and SUGGEST.
            • 3. Re: Bad execution plan in 11.2.0.2 compared to 10.2.0.4 with dynamic sampling
              Charles Hooper
              TimWong765 wrote:
              I uploaded 10053 traces
              http://dl.dropbox.com/u/55386528/10053_10_2_0_4.trc
              http://dl.dropbox.com/u/55386528/10053_11_2_0_2.trc
              The dynamic sampling section for 11.2.0.2 looks ok but the join order seams to go wrong.
              I looked briefly at the 10053 trace files and I see a couple of notable differences.
              In the 10.2.0.4 trace file, the following parameters are shown as changed from the default:
                PARAMETERS WITH ALTERED VALUES
                ******************************
                optimizer_features_enable           = 10.2.0.3
                _pga_max_size                       = 2097140 KB
                star_transformation_enabled         = true
              Don't be too concerned with the pgamax_size parameter at this point, it is likely autoset to that value based on the PGA_AGGREGATE_TARGET parameter. So, star transformation is enabled, and the optimizer was instructed to behave similar to the 10.2.0.3 optimizer.

              Now take a look at the parameters from the same section on 11.2.0.2:
              _pga_max_size                       = 2097152 KB
              _smm_px_max_size                    = 5931008 KB
              parallel_query_mode                 = disabled
              star_transformation_enabled         = true
              query_rewrite_enabled               = false
              _optimizer_enable_extended_stats    = false
              optimizer_capture_sql_plan_baselines = true
              db_file_multiblock_read_count       = 16
              parallel_degree_limit               = 4
              parallel_max_degree                 = 4
              _parallel_cluster_cache_policy      = adaptive
              Why are those parameters changed from the default values?

              The end result is that the query is being rewritten by the optimizer differently for 10.2.0.4 and 11.2.0.2.
              The 10.2.0.4 rewritten query:
              SELECT
                "S"."CLIENT" "CLIENT",
                "S"."HEADERID" "HEADERID",
                "S"."IDENTTYPEID" "IDENTTYPEID",
                "T"."HEADERID" "IDENTHEADER",
                "S"."UNMATCH" "UNMATCH",
                "T"."UNMATCH" "UNMATCH",
                "SCOPE"."TYPE" "TYPE",
                "S"."IDENTHASH" "IDENTHASH"
              FROM
                (SELECT /*+ NO_MERGE */ DISTINCT
                   "TRANS_IDENT_RULESET"."CLIENT" "CLIENT",
                   "TRANS_IDENT_RULESET"."IDENTTYPEID" "IDENTTYPEID",
                   "TRANS_IDENT_RULESET"."TYPE" "TYPE"
                 FROM
                   TRANSFER0."TRANS_JOB" "J",
                   TRANSFER0."TRANS_IDENT_RULESET" "TRANS_IDENT_RULESET"
                 WHERE
                   "TRANS_IDENT_RULESET"."JOBID"="J"."JOBID"
                   AND "J"."JOBID"= (SELECT
                                       MAX("TRANS_JOB"."JOBID") "JOBID"
                                     FROM
                                       TRANSFER0."TRANS_JOB" "TRANS_JOB"
                                     WHERE
                                       "TRANS_JOB"."LOADENDDATE" IS NULL)
                   AND "TRANS_IDENT_RULESET"."IDENTDOMAIN"='CONTACT'
                   AND "TRANS_IDENT_RULESET"."SCOPE"='DYNAMIC'
                   AND ("TRANS_IDENT_RULESET"."TYPE"='IDENT'
                        OR "TRANS_IDENT_RULESET"."TYPE"='REJECT')
                   AND "TRANS_IDENT_RULESET"."CLIENT"='IT_XX') "SCOPE",
                "IDENTIFICATION0"."IDENT_TRANS_HASH" "S",
                "IDENTIFICATION0"."IDENT_TRANS_HASH" "T"
              WHERE
                "T"."IDENTTYPEID"="SCOPE"."IDENTTYPEID"
                AND "T"."CLIENT"="SCOPE"."CLIENT"           /* Moved to inline view in 11.2.0.2 */
                AND "S"."IDENTTYPEID"="SCOPE"."IDENTTYPEID"
                AND "S"."CLIENT"="SCOPE"."CLIENT"           /* Not in 11.2.0.2 query, created "transitive predicate generation" */
                AND "S"."CLIENT"='IT_XX'                    /* Not in 11.2.0.2 query, created "transitive predicate generation" */
                AND "T"."HEADERID">"S"."HEADERID"
                AND "T"."IDENTHASH"="S"."IDENTHASH"
                AND "T"."CLIENT"='IT_XX'
              The 11.2.0.2 rewritten query:
              SELECT
                "S"."CLIENT" "CLIENT",
                "S"."HEADERID" "HEADERID",
                "S"."IDENTTYPEID" "IDENTTYPEID",
                "T"."HEADERID" "IDENTHEADER",
                "S"."UNMATCH" "UNMATCH",
                "T"."UNMATCH" "UNMATCH",
                "SCOPE"."TYPE" "TYPE",
                "S"."IDENTHASH" "IDENTHASH"
              FROM
                (SELECT /*+ NO_MERGE */ DISTINCT
                   "TRANS_IDENT_RULESET"."CLIENT" "CLIENT",
                   "TRANS_IDENT_RULESET"."IDENTTYPEID" "IDENTTYPEID",
                   "TRANS_IDENT_RULESET"."TYPE" "TYPE"
                 FROM
                   TRANSFER0."TRANS_JOB" "J",
                   TRANSFER0."TRANS_IDENT_RULESET" "TRANS_IDENT_RULESET"
                 WHERE
                   "TRANS_IDENT_RULESET"."JOBID"="J"."JOBID"
                   AND "J"."JOBID"= (SELECT
                                       MAX("TRANS_JOB"."JOBID") "JOBID"
                                     FROM
                                       TRANSFER0."TRANS_JOB" "TRANS_JOB"
                                     WHERE
                                       "TRANS_JOB"."LOADENDDATE" IS NULL)
                   AND "TRANS_IDENT_RULESET"."IDENTDOMAIN"='CONTACT'
                   AND "TRANS_IDENT_RULESET"."SCOPE"='DYNAMIC'
                   AND ("TRANS_IDENT_RULESET"."TYPE"='IDENT'
                        OR "TRANS_IDENT_RULESET"."TYPE"='REJECT')
                   AND "TRANS_IDENT_RULESET"."CLIENT"="T"."CLIENT"
                   AND "T"."CLIENT"="S"."CLIENT") "SCOPE",    /* Created by JPPD:   Pushing predicate "S"."CLIENT"="SCOPE"."CLIENT" */
                "IDENTIFICATION0"."IDENT_TRANS_HASH" "S",
                "IDENTIFICATION0"."IDENT_TRANS_HASH" "T"
              WHERE
                "T"."IDENTTYPEID"="SCOPE"."IDENTTYPEID"
                AND "S"."IDENTTYPEID"="SCOPE"."IDENTTYPEID"
                AND "T"."HEADERID">"S"."HEADERID"
                AND "T"."IDENTHASH"="S"."IDENTHASH"
                AND "T"."CLIENT"='IT_XX'
              You might try an experiment. At the session level, set the OPTIMIZER_FEATURES_ENABLE parameter to 10.2.0.3, and execute the transformed 10.2.0.4 version of the query on 11.2.0.2. Does the execution plan appear to be the same as it was on 10.2.0.4?

              Charles Hooper
              http://hoopercharles.wordpress.com/
              IT Manager/Oracle DBA
              K&M Machine-Fabricating, Inc.
              • 4. Re: Bad execution plan in 11.2.0.2 compared to 10.2.0.4 with dynamic sampling
                TimWong765
                many thanks for your replies.

                optimizer_features_enable was set to 10.2.0.3 in the DB with version 10.2.0.4 as we there were many statements that were slow after the upgrade to 10.2.0.4. We never succeeded to set optimizer_features_enable = 10.2.0.4 in DB version 10.2.0.4. But now at least the parameter is set to 11.2.0.2 after the latest upgrade to 11gR2.
                Damn! If I now set
                alter session set optimizer_features_enable = '10.2.0.3';
                in the DB with version 11.2.0.2 results in the expected(=good) plan. (Looks like a never ending story to get rid of this parameter setting :( )
                --------------------------------------------------------------------------------------------------------------------------
                | Id  | Operation                  | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
                --------------------------------------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT           |                     |   906K|   412M|       |    13M (97)| 54:06:30 |       |       |
                |*  1 |  HASH JOIN                 |                     |   906K|   412M|       |    13M (97)| 54:06:30 |       |       |
                |   2 |   VIEW                     |                     |     1 |    77 |       |   135   (2)| 00:00:02 |       |       |
                |   3 |    HASH UNIQUE             |                     |     1 |   179 |       |   135   (2)| 00:00:02 |       |       |
                |*  4 |     HASH JOIN              |                     |     1 |   179 |       |     7  (15)| 00:00:01 |       |       |
                |   5 |      PARTITION RANGE SINGLE|                     |     1 |   174 |       |     2   (0)| 00:00:01 |     8 |     8 |
                |*  6 |       TABLE ACCESS FULL    | TRANS_IDENT_RULESET |     1 |   174 |       |     2   (0)| 00:00:01 |     8 |     8 |
                |   7 |      PARTITION RANGE ALL   |                     |     1 |     5 |       |     4   (0)| 00:00:01 |     1 |    12 |
                |*  8 |       INDEX FAST FULL SCAN | XPKTRANS_JOB        |     1 |     5 |       |     4   (0)| 00:00:01 |     1 |    12 |
                |   9 |        SORT AGGREGATE      |                     |     1 |    13 |       |            |          |       |       |
                |  10 |         PARTITION RANGE ALL|                     |     1 |    13 |       |   127   (0)| 00:00:02 |     1 |    12 |
                |* 11 |          TABLE ACCESS FULL | TRANS_JOB           |     1 |    13 |       |   127   (0)| 00:00:02 |     1 |    12 |
                |* 12 |   HASH JOIN                |                     |   906K|   345M|  3666M|    13M (97)| 54:06:28 |       |       |
                |  13 |    PARTITION RANGE SINGLE  |                     |    18M|  3458M|       |  6800K (99)| 26:26:42 |     8 |     8 |
                |  14 |     PARTITION HASH ALL     |                     |    18M|  3458M|       |  6800K (99)| 26:26:42 |     1 |    32 |
                |* 15 |      TABLE ACCESS FULL     | IDENT_TRANS_HASH    |    18M|  3458M|       |  6800K (99)| 26:26:42 |   225 |   256 |
                |  16 |    PARTITION RANGE SINGLE  |                     |    18M|  3458M|       |  6800K (99)| 26:26:42 |     8 |     8 |
                |  17 |     PARTITION HASH ALL     |                     |    18M|  3458M|       |  6800K (99)| 26:26:42 |     1 |    32 |
                |* 18 |      TABLE ACCESS FULL     | IDENT_TRANS_HASH    |    18M|  3458M|       |  6800K (99)| 26:26:42 |   225 |   256 |
                --------------------------------------------------------------------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                
                   1 - access("S"."CLIENT"="SCOPE"."CLIENT" AND "S"."IDENTTYPEID"="SCOPE"."IDENTTYPEID" AND
                              "T"."CLIENT"="SCOPE"."CLIENT" AND "T"."IDENTTYPEID"="SCOPE"."IDENTTYPEID")
                   4 - access("JOBID"="J"."JOBID")
                   6 - filter("IDENTDOMAIN"='CONTACT' AND "SCOPE"='DYNAMIC' AND ("TYPE"='IDENT' OR "TYPE"='REJECT') AND
                              "CLIENT"='IT_XX')
                   8 - filter("JOBID"= (SELECT MAX("JOBID") FROM TRANSFER0."TRANS_JOB" "TRANS_JOB" WHERE "LOADENDDATE" IS NULL))
                  11 - filter("LOADENDDATE" IS NULL)
                  12 - access("T"."IDENTHASH"="S"."IDENTHASH")
                       filter("T"."HEADERID">"S"."HEADERID")
                  15 - filter("S"."CLIENT"='IT_XX')
                  18 - filter("T"."CLIENT"='IT_XX')
                
                Note
                -----
                   - dynamic sampling used for this statement (level=2)
                .


                Non-default Parameters in the 11.2.0.2 DB were set
                optimizerenable_extended_stats = false (requested by Oracle due to a bug)
                optimizer_capture_sql_plan_baselines = true (to get baselines in order to get more stable execution plans)
                db_file_multiblock_read_count = 16 (DWH application with many table scans. Several batch scripts already set this value to 16)
                parallel_XXX were set to limit parallelism
                query_rewrite_enabled = false (no matViews to benefit from query rewrite)
                star_transformation_enabled = true (some fact tables with bitmap indexes)

                I also played around with some of these parameters now and set star_transformation_enabled=false - turns out to produce at least a better plan, too (10.2.0.4 had set this parameter to true though).
                -----------------------------------------------------------------------------------------------------------------------------
                | Id  | Operation                     | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
                -----------------------------------------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT              |                     |  2671K|  1215M|       |    13M (98)| 05:58:36 |       |       |
                |*  1 |  HASH JOIN                    |                     |  2671K|  1215M|   294M|    13M (98)| 05:58:36 |       |       |
                |   2 |   PART JOIN FILTER CREATE     | :BF0000             |  1066K|   281M|       |  6800K (99)| 02:57:06 |       |       |
                |*  3 |    HASH JOIN                  |                     |  1066K|   281M|       |  6800K (99)| 02:57:06 |       |       |
                |   4 |     PART JOIN FILTER CREATE   | :BF0001             |     1 |    77 |       |   135   (2)| 00:00:01 |       |       |
                |   5 |      VIEW                     |                     |     1 |    77 |       |   135   (2)| 00:00:01 |       |       |
                |   6 |       HASH UNIQUE             |                     |     1 |   179 |       |   135   (2)| 00:00:01 |       |       |
                |*  7 |        HASH JOIN              |                     |     1 |   179 |       |     7  (15)| 00:00:01 |       |       |
                |   8 |         PARTITION RANGE SINGLE|                     |     1 |   174 |       |     2   (0)| 00:00:01 |     8 |     8 |
                |*  9 |          TABLE ACCESS FULL    | TRANS_IDENT_RULESET |     1 |   174 |       |     2   (0)| 00:00:01 |     8 |     8 |
                |  10 |         PARTITION RANGE ALL   |                     |     1 |     5 |       |     4   (0)| 00:00:01 |     1 |    12 |
                |* 11 |          INDEX FAST FULL SCAN | XPKTRANS_JOB        |     1 |     5 |       |     4   (0)| 00:00:01 |     1 |    12 |
                |  12 |           SORT AGGREGATE      |                     |     1 |    13 |       |            |          |       |       |
                |  13 |            PARTITION RANGE ALL|                     |     1 |    13 |       |   127   (0)| 00:00:01 |     1 |    12 |
                |* 14 |             TABLE ACCESS FULL | TRANS_JOB           |     1 |    13 |       |   127   (0)| 00:00:01 |     1 |    12 |
                |  15 |     PARTITION RANGE SINGLE    |                     |    18M|  3458M|       |  6800K (99)| 02:57:06 |KEY(AP)|KEY(AP)|
                |  16 |      PARTITION HASH ALL       |                     |    18M|  3458M|       |  6800K (99)| 02:57:06 |     1 |    32 |
                |* 17 |       TABLE ACCESS FULL       | IDENT_TRANS_HASH    |    18M|  3458M|       |  6800K (99)| 02:57:06 |   KEY |   KEY |
                |  18 |   PARTITION RANGE SINGLE      |                     |    18M|  3458M|       |  6800K (99)| 02:57:06 |KEY(AP)|KEY(AP)|
                |  19 |    PARTITION HASH ALL         |                     |    18M|  3458M|       |  6800K (99)| 02:57:06 |     1 |    32 |
                |* 20 |     TABLE ACCESS FULL         | IDENT_TRANS_HASH    |    18M|  3458M|       |  6800K (99)| 02:57:06 |   KEY |   KEY |
                -----------------------------------------------------------------------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                
                   1 - access("T"."CLIENT"="SCOPE"."CLIENT" AND "T"."IDENTTYPEID"="SCOPE"."IDENTTYPEID" AND
                              "T"."IDENTHASH"="S"."IDENTHASH")
                       filter("T"."HEADERID">"S"."HEADERID")
                   3 - access("S"."CLIENT"="SCOPE"."CLIENT" AND "S"."IDENTTYPEID"="SCOPE"."IDENTTYPEID")
                   7 - access("JOBID"="J"."JOBID")
                   9 - filter("IDENTDOMAIN"='CONTACT' AND "SCOPE"='DYNAMIC' AND ("TYPE"='IDENT' OR "TYPE"='REJECT') AND
                              "CLIENT"='IT_XX')
                  11 - filter("JOBID"= (SELECT MAX("JOBID") FROM TRANSFER0."TRANS_JOB" "TRANS_JOB" WHERE "LOADENDDATE" IS NULL))
                  14 - filter("LOADENDDATE" IS NULL)
                  17 - filter("S"."CLIENT"='IT_XX')
                  20 - filter("T"."CLIENT"='IT_XX')
                
                Note
                -----
                   - dynamic sampling used for this statement (level=2)
                • 5. Re: Bad execution plan in 11.2.0.2 compared to 10.2.0.4 with dynamic sampling
                  damorgan
                  Hints are not hints or suggestions ... they are commands that must be obeyed if valid. The operative phrase is "if valid" as the Oracle documentation on hints is wholly inadequate.

                  But before recommending or using hints ... I advise everyone to read this
                  http://jonathanlewis.wordpress.com/2008/05/02/rules-for-hinting/
                  and then read it again. And again. And again. Especially bullet point #1.
                  • 6. Re: Bad execution plan in 11.2.0.2 compared to 10.2.0.4 with dynamic sampling
                    Randolf Geist
                    TimWong765 wrote:
                    We upgraded an application from 10.2.0.4 to 11.2.0.2. Some queries use dynamic sampling (Table IDENT_TRANS_HASH does not have statistics - intentionally)

                    What can cause the optimier to choose the bad execution plan in 11.2.0.2?
                    That's a pretty interesting case.

                    It should not be left unmentioned that you've changed a lot of things in course of the upgrade: You've changed the block size from 8K to 16K and you've particularly disabled parallel query. The latter means that you're more or less comparing apples to oranges in terms of costing: The 10.2.0.3/4 plan uses parallel query.

                    Disabling parallel query together with the fact that 11.2.0.2 evaluates a join predicate push down (JPPD) transformation that 10.2.0.3 does not might already be sufficient to arrive at the bad plan.

                    The main issue is that the dynamic sampling generates a bad cardinality estimate, but this dynamic sampling issue in turn is caused by the fact that you only get the partition pruning working on IDENT_TRANS_HASH S due to the transitive closure of

                    S.CLIENT=SCOPE.CLIENT
                    T.CLIENT=SCOPE.CLIENT
                    t.client = 'IT_XX'
                    => S.CLIENT = T.CLIENT
                    => s.client = 'IT_XX'

                    But this transitive closure is lost due to the JPPD transformation, and therefore the dynamic sampling has to sample all 384 subpartitions rather than 32 hash subpartitions of a single range partition. This result of this dynamic sampling causes the bad cardinality estimate and allows the optimizer to cost the bad plan lower than the good plans. I'm not sure if this is a problem with the dynamic sampling code - I would have expected to get a better estimate with a higher sampling setting.

                    So here are some thoughts for further experiments:

                    - What execution plan do you get if you enable parallel query on 11.2.0.2?
                    - What happens if you add the S.CLIENT = T.CLIENT join explicitly?
                    - What happens if you add s.client = 'IT_XX' explicitly?

                    Open questions:
                    - What do you see for the dynamic sampling in the 10053 trace if you set the OPTIMIZER_DYNAMIC_SAMPLING to 5 or when using the explicit dynamic handling table level hints as described in your post?
                    - Why does star_transformation_enabled = false influence the JPPD transformation (because the cost of the resulting plan is higher than the cost of the JPPD plan)?

                    Hope this helps,
                    Randolf
                    • 7. Re: Bad execution plan in 11.2.0.2 compared to 10.2.0.4 with dynamic sampling
                      603349
                      TimWong765 wrote:
                      If I now set
                      alter session set optimizer_features_enable = '10.2.0.3';
                      in the DB with version 11.2.0.2 results in the expected(=good) plan. (Looks like a never ending story to get rid of this parameter setting :( )
                      In order to make debugging easier and eliminate some variables, can you get optimizer traces from the 11.2.0.2 db for OFE=10.2.0.3 and OFE=11.2.0.2 and post links to them (this does assume you get the same/good plan with OFE=10.2.0.3 in the 11.2.0.2 db)?

                      --
                      Regards,
                      Greg Rahn | blog | twitter | linkedin
                      • 8. Re: Bad execution plan in 11.2.0.2 compared to 10.2.0.4 with dynamic sampling
                        Jonathan Lewis
                        Randolf Geist wrote:

                        It should not be left unmentioned that you've changed a lot of things in course of the upgrade: You've changed the block size from 8K to 16K and you've particularly disabled parallel query. The latter means that you're more or less comparing apples to oranges in terms of costing: The 10.2.0.3/4 plan uses parallel query.
                        It also looks as if the range partitioning has been changed - and that's not going to make it any easier to unravel the problem.


                        Regards
                        Jonathan Lewis
                        http://jonathanlewis.wordpress.com
                        Author: <b><em>Oracle Core</em></b>
                        • 9. Re: Bad execution plan in 11.2.0.2 compared to 10.2.0.4 with dynamic sampling
                          Jonathan Lewis
                          TimWong765 wrote:


                          Strangely, a hint
                          SELECT --+ dynamic_sampling(S,5) dynamic_sampling(T,5)
                          ... etc
                          changes the plan to a good one while
                          alter session set optimizer_dynamic_sampling=5;
                          does not improve the plan.
                          To address just this observation:
                          -- the dynamic_sampling table-based hint at level will tell Oracle to sample (by default) 32 * 2<sup>5</sup> blocks.
                          -- the optimizer_dynamic_sampling parameter at level 5 will tell Oracle to sample (by default) 64 blocks for any table that matches the rules.

                          Regards
                          Jonathan Lewis
                          http://jonathanlewis.wordpress.com
                          Author: <b><em>Oracle Core</em></b>
                          • 10. Re: Bad execution plan in 11.2.0.2 compared to 10.2.0.4 with dynamic sampling
                            TimWong765
                            Many thanks for your help.

                            If I enable parallel query, the plan changes and removes the nested loop. The query comes back immediately (while the bad one runs for hours without result)
                            ---------------------------------------------------------------------------------------------------------------------------------------------------------------
                            | Id  | Operation                                  | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
                            ---------------------------------------------------------------------------------------------------------------------------------------------------------------
                            |   0 | SELECT STATEMENT                           |                     |  2671K|  1215M|  1888K (99)| 00:49:12 |       |       |        |      |            |
                            |   1 |  PX COORDINATOR                            |                     |       |       |            |          |       |       |        |      |            |
                            |   2 |   PX SEND QC (RANDOM)                      | :TQ20005            |  2671K|  1215M|  1888K (99)| 00:49:12 |       |       |  Q2,05 | P->S | QC (RAND)  |
                            |*  3 |    HASH JOIN                               |                     |  2671K|  1215M|  1888K (99)| 00:49:12 |       |       |  Q2,05 | PCWP |            |
                            |   4 |     PART JOIN FILTER CREATE                | :BF0000             |  1066K|   281M|   944K (99)| 00:24:36 |       |       |  Q2,05 | PCWP |            |
                            |   5 |      PX RECEIVE                            |                     |  1066K|   281M|   944K (99)| 00:24:36 |       |       |  Q2,05 | PCWP |            |
                            |   6 |       PX SEND BROADCAST LOCAL              | :TQ20004            |  1066K|   281M|   944K (99)| 00:24:36 |       |       |  Q2,04 | P->P | BCST LOCAL |
                            |*  7 |        HASH JOIN                           |                     |  1066K|   281M|   944K (99)| 00:24:36 |       |       |  Q2,04 | PCWP |            |
                            |   8 |         PART JOIN FILTER CREATE            | :BF0002             |     1 |    77 |    28   (4)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                            |   9 |          PX RECEIVE                        |                     |     1 |    77 |    28   (4)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                            |  10 |           PX SEND PARTITION (KEY)          | :TQ20003            |     1 |    77 |    28   (4)| 00:00:01 |       |       |  Q2,03 | P->P | PART (KEY) |
                            |  11 |            VIEW                            |                     |     1 |    77 |    28   (4)| 00:00:01 |       |       |  Q2,03 | PCWP |            |
                            |  12 |             HASH UNIQUE                    |                     |     1 |   179 |    28   (4)| 00:00:01 |       |       |  Q2,03 | PCWP |            |
                            |  13 |              PX RECEIVE                    |                     |     1 |   179 |    28   (4)| 00:00:01 |       |       |  Q2,03 | PCWP |            |
                            |  14 |               PX SEND HASH                 | :TQ20002            |     1 |   179 |    28   (4)| 00:00:01 |       |       |  Q2,02 | P->P | HASH       |
                            |  15 |                HASH UNIQUE                 |                     |     1 |   179 |    28   (4)| 00:00:01 |       |       |  Q2,02 | PCWP |            |
                            |* 16 |                 HASH JOIN                  |                     |     1 |   179 |     4  (25)| 00:00:01 |       |       |  Q2,02 | PCWP |            |
                            |  17 |                  PX RECEIVE                |                     |     1 |   174 |     2   (0)| 00:00:01 |       |       |  Q2,02 | PCWP |            |
                            |  18 |                   PX SEND HASH             | :TQ20000            |     1 |   174 |     2   (0)| 00:00:01 |       |       |  Q2,00 | P->P | HASH       |
                            |  19 |                    PX BLOCK ITERATOR       |                     |     1 |   174 |     2   (0)| 00:00:01 |     8 |     8 |  Q2,00 | PCWC |            |
                            |* 20 |                     TABLE ACCESS FULL      | TRANS_IDENT_RULESET |     1 |   174 |     2   (0)| 00:00:01 |     8 |     8 |  Q2,00 | PCWP |            |
                            |  21 |                  PX RECEIVE                |                     |     1 |     5 |     1   (0)| 00:00:01 |       |       |  Q2,02 | PCWP |            |
                            |  22 |                   PX SEND HASH             | :TQ20001            |     1 |     5 |     1   (0)| 00:00:01 |       |       |  Q2,01 | P->P | HASH       |
                            |  23 |                    PX PARTITION RANGE ALL  |                     |     1 |     5 |     1   (0)| 00:00:01 |     1 |    12 |  Q2,01 | PCWC |            |
                            |* 24 |                     INDEX SKIP SCAN        | XPKTRANS_JOB        |     1 |     5 |     1   (0)| 00:00:01 |     1 |    12 |  Q2,01 | PCWP |            |
                            |  25 |                      SORT AGGREGATE        |                     |     1 |    13 |            |          |       |       |  Q2,01 | PCWP |            |
                            |  26 |                       PX COORDINATOR       |                     |       |       |            |          |       |       |        |      |            |
                            |  27 |                        PX SEND QC (RANDOM) | :TQ10000            |     1 |    13 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
                            |  28 |                         SORT AGGREGATE     |                     |     1 |    13 |            |          |       |       |  Q1,00 | PCWP |            |
                            |  29 |                          PX BLOCK ITERATOR |                     |     1 |    13 |    24   (0)| 00:00:01 |     1 |    12 |  Q1,00 | PCWC |            |
                            |* 30 |                           TABLE ACCESS FULL| TRANS_JOB           |     1 |    13 |    24   (0)| 00:00:01 |     1 |    12 |  Q1,00 | PCWP |            |
                            |  31 |         PX PARTITION RANGE SINGLE          |                     |    18M|  3458M|   944K (99)| 00:24:36 |KEY(AP)|KEY(AP)|  Q2,04 | PCWC |            |
                            |  32 |          PX PARTITION HASH ALL             |                     |    18M|  3458M|   944K (99)| 00:24:36 |     1 |    32 |  Q2,04 | PCWC |            |
                            |* 33 |           TABLE ACCESS FULL                | IDENT_TRANS_HASH    |    18M|  3458M|   944K (99)| 00:24:36 |   KEY |   KEY |  Q2,04 | PCWP |            |
                            |  34 |     PX BLOCK ITERATOR                      |                     |    18M|  3458M|   944K (99)| 00:24:36 |     1 |    32 |  Q2,05 | PCWC |            |
                            |* 35 |      TABLE ACCESS FULL                     | IDENT_TRANS_HASH    |    18M|  3458M|   944K (99)| 00:24:36 |   KEY |   KEY |  Q2,05 | PCWP |            |
                            ---------------------------------------------------------------------------------------------------------------------------------------------------------------
                            
                            Predicate Information (identified by operation id):
                            ---------------------------------------------------
                            
                               3 - access("T"."CLIENT"="SCOPE"."CLIENT" AND "T"."IDENTTYPEID"="SCOPE"."IDENTTYPEID" AND "T"."IDENTHASH"="S"."IDENTHASH")
                                   filter("T"."HEADERID">"S"."HEADERID")
                               7 - access("S"."CLIENT"="SCOPE"."CLIENT" AND "S"."IDENTTYPEID"="SCOPE"."IDENTTYPEID")
                              16 - access("JOBID"="J"."JOBID")
                              20 - filter("IDENTDOMAIN"='CONTACT' AND "SCOPE"='DYNAMIC' AND ("TYPE"='IDENT' OR "TYPE"='REJECT') AND "CLIENT"='IT_XX')
                              24 - access("JOBID"= (SELECT MAX(SYS_OP_CSR(SYS_OP_MSR(MAX("JOBID")),0)) FROM TRANSFER0."TRANS_JOB" "TRANS_JOB" WHERE "LOADENDDATE" IS NULL))
                                   filter("JOBID"= (SELECT MAX(SYS_OP_CSR(SYS_OP_MSR(MAX("JOBID")),0)) FROM TRANSFER0."TRANS_JOB" "TRANS_JOB" WHERE "LOADENDDATE" IS NULL))
                              30 - filter("LOADENDDATE" IS NULL)
                              33 - filter("S"."CLIENT"='IT_XX')
                              35 - filter("T"."CLIENT"='IT_XX' AND SYS_OP_BLOOM_FILTER(:BF0001,"T"."IDENTTYPEID","T"."CLIENT","T"."IDENTHASH"))
                            
                            Note
                            -----
                               - dynamic sampling used for this statement (level=2)
                            
                            60 rows selected.
                            If I add and S.CLIENT = T.CLIENT (similiar for s.client = 'IT_XX'), the plan also changes
                            -----------------------------------------------------------------------------------------------------------------------------
                            | Id  | Operation                     | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
                            -----------------------------------------------------------------------------------------------------------------------------
                            |   0 | SELECT STATEMENT              |                     |  2671K|  1215M|       |    13M (98)| 05:58:36 |       |       |
                            |*  1 |  HASH JOIN                    |                     |  2671K|  1215M|   294M|    13M (98)| 05:58:36 |       |       |
                            |   2 |   PART JOIN FILTER CREATE     | :BF0000             |  1066K|   281M|       |  6800K (99)| 02:57:06 |       |       |
                            |*  3 |    HASH JOIN                  |                     |  1066K|   281M|       |  6800K (99)| 02:57:06 |       |       |
                            |   4 |     PART JOIN FILTER CREATE   | :BF0001             |     1 |    77 |       |   135   (2)| 00:00:01 |       |       |
                            |   5 |      VIEW                     |                     |     1 |    77 |       |   135   (2)| 00:00:01 |       |       |
                            |   6 |       HASH UNIQUE             |                     |     1 |   179 |       |   135   (2)| 00:00:01 |       |       |
                            |*  7 |        HASH JOIN              |                     |     1 |   179 |       |     7  (15)| 00:00:01 |       |       |
                            |   8 |         PARTITION RANGE SINGLE|                     |     1 |   174 |       |     2   (0)| 00:00:01 |     8 |     8 |
                            |*  9 |          TABLE ACCESS FULL    | TRANS_IDENT_RULESET |     1 |   174 |       |     2   (0)| 00:00:01 |     8 |     8 |
                            |  10 |         PARTITION RANGE ALL   |                     |     1 |     5 |       |     4   (0)| 00:00:01 |     1 |    12 |
                            |* 11 |          INDEX FAST FULL SCAN | XPKTRANS_JOB        |     1 |     5 |       |     4   (0)| 00:00:01 |     1 |    12 |
                            |  12 |           SORT AGGREGATE      |                     |     1 |    13 |       |            |          |       |       |
                            |  13 |            PARTITION RANGE ALL|                     |     1 |    13 |       |   127   (0)| 00:00:01 |     1 |    12 |
                            |* 14 |             TABLE ACCESS FULL | TRANS_JOB           |     1 |    13 |       |   127   (0)| 00:00:01 |     1 |    12 |
                            |  15 |     PARTITION RANGE SINGLE    |                     |    18M|  3458M|       |  6800K (99)| 02:57:06 |KEY(AP)|KEY(AP)|
                            |  16 |      PARTITION HASH ALL       |                     |    18M|  3458M|       |  6800K (99)| 02:57:06 |     1 |    32 |
                            |* 17 |       TABLE ACCESS FULL       | IDENT_TRANS_HASH    |    18M|  3458M|       |  6800K (99)| 02:57:06 |   KEY |   KEY |
                            |  18 |   PARTITION RANGE SINGLE      |                     |    18M|  3458M|       |  6800K (99)| 02:57:06 |KEY(AP)|KEY(AP)|
                            |  19 |    PARTITION HASH ALL         |                     |    18M|  3458M|       |  6800K (99)| 02:57:06 |     1 |    32 |
                            |* 20 |     TABLE ACCESS FULL         | IDENT_TRANS_HASH    |    18M|  3458M|       |  6800K (99)| 02:57:06 |   KEY |   KEY |
                            -----------------------------------------------------------------------------------------------------------------------------
                            
                            Predicate Information (identified by operation id):
                            ---------------------------------------------------
                            
                               1 - access("T"."CLIENT"="SCOPE"."CLIENT" AND "T"."IDENTTYPEID"="SCOPE"."IDENTTYPEID" AND
                                          "T"."IDENTHASH"="S"."IDENTHASH" AND "S"."CLIENT"="T"."CLIENT")
                                   filter("T"."HEADERID">"S"."HEADERID")
                               3 - access("S"."CLIENT"="SCOPE"."CLIENT" AND "S"."IDENTTYPEID"="SCOPE"."IDENTTYPEID")
                               7 - access("JOBID"="J"."JOBID")
                               9 - filter("IDENTDOMAIN"='CONTACT' AND "SCOPE"='DYNAMIC' AND ("TYPE"='IDENT' OR "TYPE"='REJECT') AND
                                          "CLIENT"='IT_XX')
                              11 - filter("JOBID"= (SELECT MAX("JOBID") FROM TRANSFER0."TRANS_JOB" "TRANS_JOB" WHERE "LOADENDDATE" IS NULL))
                              14 - filter("LOADENDDATE" IS NULL)
                              17 - filter("S"."CLIENT"='IT_XX')
                              20 - filter("T"."CLIENT"='IT_XX')
                            
                            Note
                            -----
                               - dynamic sampling used for this statement (level=2)
                            
                            46 rows selected.
                            10053 traces
                            The original settings from production
                            http://dl.dropbox.com/u/55386528/10053_11_2_0_2.trc (11.2.0.2 as configured in prod with OFE = 11.2.0.2) Bad plan
                            http://dl.dropbox.com/u/55386528/10053_10_2_0_4.trc (10.2.0.4 DB configured in old prod = uses OFE 10.2.0.3) Good plan

                            Test settings:
                            http://dl.dropbox.com/u/55386528/10053_11_2_0_2_withOFE10203.trc (11.2.0.2 with "alter session set optimizer_features_enable = '10.2.0.3';") Fast plan
                            http://dl.dropbox.com/u/55386528/10053_11_2_0_4_withParallelQuery.trc (11.2.0.2 with "alter session enable parallel query;") Fast plan
                            http://dl.dropbox.com/u/55386528/10053_11_2_0_2_starTransformationEnabled_false.trc (11.2.0.2 with "alter session set star_transformation_enabled = false;") Fast plan
                            http://dl.dropbox.com/u/55386528/10053_11_2_0_2_samplingNoHint.trc (11.2.0.2 with "alter session set optimizer_dynamic_sampling=5;") Bad plan
                            http://dl.dropbox.com/u/55386528/10053_11_2_0_2_samplingWithHint.trc (11.2.0.2 with Hint "--+ dynamic_sampling(S,5) dynamic_sampling(T,5)") Fast plan
                            • 11. Re: Bad execution plan in 11.2.0.2 compared to 10.2.0.4 with dynamic sampling
                              Randolf Geist
                              TimWong765 wrote:
                              If I enable parallel query, the plan changes and removes the nested loop. The query comes back immediately (while the bad one runs for hours without result)

                              If I add and S.CLIENT = T.CLIENT (similiar for s.client = 'IT_XX'), the plan also changes
                              Just a couple of additional thoughts:

                              - The bad plan is indeed caused by a combination of your data distribution and a bug in the dynamic sampling code which is only showing up due to the lost transitive closure. There is a difference how dynamic sampling works when confronted with multiple partitions: If the number of partitions to scan is greater than the number of blocks to sample then it uses a UNION ALL query on different partitions - in the other case it's the default query form that gets used.

                              And in this special case (num_partitions_to_sample > num_blocks_to_sample) there is a simple bug that seems to be fixed in 11.2.0.3: Instead of using the total number of blocks of all table partitions it uses the number of blocks of the partitions selected for sampling as the number of blocks for costing. This explains why the cost estimate for one instance of IDENT_TRANS_HASH is so low in the 11.2.0.2 bad plan posted. This is a bug.

                              The low cardinality estimate is caused by the low sample size and the obviously almost empty (or even not materialized due to deferred segment creation?) selected subpartitions for sampling.

                              - This also explains why the table level dynamic sampling hint changes the plan: Thanks to Jonathan we know now that the same level 5 leads to a higher number of blocks to be sampled in comparison to the OPTIMIZER_DYNAMIC_SAMPLING setting. This higher number of blocks leads to the default dynamic sampling code (num_blocks_to_sample > num_partitions_to_sample) and therefore to a correct costing (this code path uses the correct number of blocks) and better cardinality estimate. The JPPD transformation gets rejected in this case due to the extreme cost of the NL join.

                              - I wouldn't constrain the 11g version to a db_file_multiblock_read_count of 16. It probably doesn't make a lot of difference but if you leave the parameter unset (is it left unset in your 10.2.0.4 database?) it will use usually a much larger I/O size for multi-block reads than your current setting of 256K (default is 1MB in recent versions depending on your session and buffer_cache size setting), but a quite conservative value for costing (8) if you don't have a MBRC defined in your WORKLOAD system statistics.

                              - Why have you disabled parallel query in 11g? Given the amount of data to process you probably want to consider using parallel query, in particular since you seem to have used it in 10.2.0.4.

                              Hope this helps,
                              Randolf
                              • 12. Re: Bad execution plan in 11.2.0.2 compared to 10.2.0.4 with dynamic sampling
                                TimWong765
                                Thanks again for your help & explanations.

                                Parallel query: Parallel query is disabled for some users/schemas. It should not have been disabled for this user/schema. This is a mistake. Actually I configured the DB with "statement queuing" and used your article http://oracle-randolf.blogspot.com/2011/07/cost-is-time-next-generation.html as I also hit the bug with unreasonable high values during calibration (= setting calibration values manually as mentioned in OracleSupportWeb). Integration testing worked well. As soon as the system went live in 11/2011 too many statements suffered from a slow/wrong plan (undiscovered during integration test) and the queue with waiting statements went longer and longer... A fast fix was to disable parallelism for some schemas but the problem (too many parallel statements running much longer as expected) was still there the next days. So I disabled statement queuing a few days later and went back to the 10g parallelism (Auto DOP + statement queuing may be worth another try in future as soon as the plans are stable and not not suffering from long running statement). Obviously, I missed to enable parallelism for this user/schema. The IO calibration is also the reason I did not gather workload statistics originally (compared to the old production DB) but maybe in the near future.

                                db_file_multiblock_read_count: default value was smaller but the old production system used workload statistics (see above).