14 Replies Latest reply: Sep 13, 2013 6:59 AM by user10274093 RSS

    different Explain Plans

    user10274093

      Hi,

      in 11.2.0.3 the same query on two different DBs (on the same server) have different Explain Plans (differnet number of rows returned 9690K vs 14M) :

       

      DBDEV :

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

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

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

      |   0 | INSERT STATEMENT         |                  |  9690K|  4482M|   344K  (1)| 01:08:49 |

      |   1 |  LOAD TABLE CONVENTIONAL | PS_PROJ_RES_TA14 |       |       |            |          |

      |*  2 |   TABLE ACCESS FULL      | PS_PROJ_RESOURCE |  9690K|  4482M|   344K  (1)| 01:08:49 |

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

       

      DBTST

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

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

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

      |   0 | INSERT STATEMENT         |                  |    14M|  6534M|   344K  (1)| 01:08:50 |

      |   1 |  LOAD TABLE CONVENTIONAL | PS_PROJ_RES_TA14 |       |       |            |          |

      |*  2 |   TABLE ACCESS FULL      | PS_PROJ_RESOURCE |    14M|  6534M|   344K  (1)| 01:08:50 |

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

       

      The optimizer parameters are the same :

       

      NAME                                 TYPE        VALUE

      optimizer_capture_sql_plan_baselines  boolean     FALSE

        optimizer_dynamic_sampling           integer     2

       

      optimizer_features_enable            string      11.2.0.3

        optimizer_index_caching              integer     0

        optimizer_index_cost_adj             integer     100

        optimizer_mode                       string      ALL_ROWS

        optimizer_secure_view_merging        boolean     TRUE

        optimizer_use_invisible_indexes      boolean     FALSE

        optimizer_use_pending_statistics     boolean    FALSE

        optimizer_use_sql_plan_baselines     boolean    TRUE

       

      And the number of rows :

       

      In DBTST

      select count(*) from ps_proj_resource

        COUNT(*)

        --------

        18072893

        

      In DBDEV

        COUNT(*)

      --------

        18070581

       

      Thanks for explanation and ideas.