13 Replies Latest reply: Feb 25, 2013 6:45 PM by 992408 RSS

    CBO generating different plans for the same data in similar Environments

    992408
      Hi All



      I have been trying to compare an SQL from 2 different but similar environments build of the same hardware specs .The issue I am facing is environment A, the query executes in less than 2 minutes with plan mostly showing full table scans and hash join whereas in environment B(problematic), it times out after 2 hours with an error of unable to extend table space . The statistics are up to date in both environments for both tables and indexes . System parameters are exactly similar(default oracle for except for multiblock_read_count ).


      Both Environment have same db parameter for db_file_multiblock_read_count(16), optimizer(refer below),hash_area_size (131072),pga_aggregate_target(1G),db_block_size(8192) etc . SREADTIM, MREADTIM, CPUSPEED, MBRC are all null in aux_stats in both environment because workload was never collected i believe.



      Attached is details about the SQL with table stats, SQL and index stats my main concern is CBO generating different plans for the similar data and statistics and same hardware and software specs. Is there any thing else I should consider .I generally see environment B being very slow and always plans tend to nested loops and index scan whereas what we really need is a sensible FTS in many cases. One of the very surprising thing is METER_CONFIG_HEADER below which has just 80 blocks of data is being asked for index scan.


      show parameter optimizer

      optimizer_dynamic_sampling integer 2

      optimizer_features_enable string 10.2.0.4

      optimizer_index_caching integer 0

      optimizer_index_cost_adj integer 100

      optimizer_mode string ALL_ROWS

      optimizer_secure_view_merging boolean TRUE



      **Environment**



      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

      PL/SQL Release 10.2.0.4.0 - Production

      CORE     10.2.0.4.0     Production

      TNS for Solaris: Version 10.2.0.4.0 - Production

      NLSRTL Version 10.2.0.4.0 - Production





      Note: : There are slight difference in the no of records in the attached sheet.However, I wanted to tell that i have tested with exact same data and was getting similar results but I couldn't retain the data untill collecting the details in the attachment




      TEST1     COMPARE TABLE LEVE STATS  used by CBO          


      ENVIRONMENT A
      TABLE_NAME     NUM_ROWS     BLOCKS     LAST_ANALYZED
      ASSET     3607425     167760     5/02/2013 22:11
      METER_CONFIG_HEADER     3658     80     5/01/2013 0:07
      METER_CONFIG_ITEM     32310     496     5/01/2013 0:07
      NMI     1899024     33557     18/02/2013 10:55
      REGISTER     4830153     101504     18/02/2013 9:57
      SDP_LOGICAL_ASSET     1607456     19137     18/02/2013 15:48
      SDP_LOGICAL_REGISTER     5110781     78691     18/02/2013 9:56
      SERVICE_DELIVERY_POINT     1425890     42468     18/02/2013 13:54
      ENVIRONMENT B
      TABLE_NAME     NUM_ROWS     BLOCKS     LAST_ANALYZED
      ASSET     4133939     198570     16/02/2013 10:02
      METER_CONFIG_HEADER     3779     80     16/02/2013 10:55
      METER_CONFIG_ITEM     33720     510     16/02/2013 10:55
      NMI     1969000     33113     16/02/2013 10:58
      REGISTER     5837874     120104     16/02/2013 11:05
      SDP_LOGICAL_ASSET     1788152     22325     16/02/2013 11:06
      SDP_LOGICAL_REGISTER     6101934     91088     16/02/2013 11:07
      SERVICE_DELIVERY_POINT     1447589     43804     16/02/2013 11:11
      TEST ITEM 2     COMPARE INDEX STATS  used by CBO          

      ENVIRONMENT A
      TABLE_NAME     INDEX_NAME     UNIQUENESS     BLEVEL     LEAF_BLOCKS     DISTINCT_KEYS     AVG_LEAF_BLOCKS_PER_KEY     AVG_DATA_BLOCKS_PER_KEY     CLUSTERING_FACTOR     NUM_ROWS
      ASSET     IDX_AST_DEVICE_CATEGORY_SK     NONUNIQUE     2     9878     67     147     12982     869801     3553095
      ASSET     IDX_A_SAPINTLOGDEV_SK     NONUNIQUE     2     7291     2747     2     639     1755977     3597916
      ASSET     SYS_C00102592     UNIQUE     2     12488     3733831     1     1     3726639     3733831
      METER_CONFIG_HEADER     SYS_C0092052     UNIQUE     1     12     3670     1     1     3590     3670
      METER_CONFIG_ITEM     SYS_C0092074     UNIQUE     1     104     32310     1     1     32132     32310
      NMI     IDX_NMI_ID     NONUNIQUE     2     6298     844853     1     2     1964769     1965029
      NMI     IDX_NMI_ID_NK     NONUNIQUE     2     6701     1923072     1     1     1922831     1923084
      NMI     IDX_NMI_STATS     NONUNIQUE     1     106     4     26     52     211     211
      REGISTER     REG_EFFECTIVE_DTM     NONUNIQUE     2     12498     795     15     2899     2304831     4711808
      REGISTER     SYS_C00102653     UNIQUE     2     16942     5065660     1     1     5056855     5065660
      SDP_LOGICAL_ASSET     IDX_SLA_SAPINTLOGDEV_SK     NONUNIQUE     2     3667     1607968     1     1     1607689     1607982
      SDP_LOGICAL_ASSET     IDX_SLA_SDP_SK     NONUNIQUE     2     3811     668727     1     2     1606204     1607982
      SDP_LOGICAL_ASSET     SYS_C00102665     UNIQUE     2     5116     1529606     1     1     1528136     1529606
      SDP_LOGICAL_REGISTER     SYS_C00102677     UNIQUE     2     17370     5193638     1     1     5193623     5193638
      SERVICE_DELIVERY_POINT     IDX_SDP_NMI_SK     NONUNIQUE     2     4406     676523     1     2     1423247     1425890
      SERVICE_DELIVERY_POINT     IDX_SDP_SAP_INT_NMI_SK     NONUNIQUE     2     7374     676523     1     2     1458238     1461108
      SERVICE_DELIVERY_POINT     SYS_C00102687     UNIQUE     2     4737     1416207     1     1     1415022     1416207
      ENVIRONMENT B
      TABLE_NAME     INDEX_NAME     UNIQUENESS     BLEVEL     LEAF_BLOCKS     DISTINCT_KEYS     AVG_LEAF_BLOCKS_PER_KEY     AVG_DATA_BLOCKS_PER_KEY     CLUSTERING_FACTOR     NUM_ROWS
      ASSET     IDX_AST_DEVICE_CATEGORY_SK     NONUNIQUE     2     8606     121     71     16428     1987833     4162257
      ASSET     IDX_A_SAPINTLOGDEV_SK     NONUNIQUE     2     8432     1780146     1     1     2048170     4162257
      ASSET     SYS_C00116157     UNIQUE     2     13597     4162263     1     1     4158759     4162263
      METER_CONFIG_HEADER     SYS_C00116570     UNIQUE     1     12     3779     1     1     3734     3779
      METER_CONFIG_ITEM     SYS_C00116592     UNIQUE     1     107     33720     1     1     33459     33720
      NMI     IDX_NMI_ID     NONUNIQUE     2     6319     683370     1     2     1970460     1971313
      NMI     IDX_NMI_ID_NK     NONUNIQUE     2     6597     1971293     1     1     1970771     1971313
      NMI     IDX_NMI_STATS     NONUNIQUE     1     98     48     2     4     196     196
      REGISTER     REG_EFFECTIVE_DTM     NONUNIQUE     2     15615     1273     12     2109     2685924     5886582
      REGISTER     SYS_C00116748     UNIQUE     2     19533     5886582     1     1     5845565     5886582
      SDP_LOGICAL_ASSET     IDX_SLA_SAPINTLOGDEV_SK     NONUNIQUE     2     4111     1795084     1     1     1758441     1795130
      SDP_LOGICAL_ASSET     IDX_SLA_SDP_SK     NONUNIQUE     2     4003     674249     1     2     1787987     1795130
      SDP_LOGICAL_ASSET     SYS_C004520     UNIQUE     2     5864     1795130     1     1     1782147     1795130
      SDP_LOGICAL_REGISTER     SYS_C004539     UNIQUE     2     20413     6152850     1     1     6073059     6152850
      SERVICE_DELIVERY_POINT     IDX_SDP_NMI_SK     NONUNIQUE     2     3227     660649     1     2     1422572     1447803
      SERVICE_DELIVERY_POINT     IDX_SDP_SAP_INT_NMI_SK     NONUNIQUE     2     6399     646257     1     2     1346948     1349993
      SERVICE_DELIVERY_POINT     SYS_C00128706     UNIQUE     2     4643     1447946     1     1     1442796     1447946
      TEST ITEM 3     COMPARE PLANS     

      ENVIRONMENT A
      Plan hash value: 4109575732                                             
                                                    
      ------------------------------------------------------------------------------------------------------------------                                             
      | Id  | Operation                       | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                             
      ------------------------------------------------------------------------------------------------------------------                                             
      |   0 | SELECT STATEMENT                |                        |    13 |  2067 |       |   135K  (2)| 00:27:05 |                                             
      |   1 |  HASH UNIQUE                    |                        |    13 |  2067 |       |   135K  (2)| 00:27:05 |                                             
      |*  2 |   HASH JOIN                     |                        |    13 |  2067 |       |   135K  (2)| 00:27:05 |                                             
      |*  3 |    HASH JOIN                    |                        |     6 |   900 |       |   135K  (2)| 00:27:04 |                                             
      |*  4 |     HASH JOIN ANTI              |                        |     1 |   137 |       |   135K  (2)| 00:27:03 |                                             
      |*  5 |      TABLE ACCESS BY INDEX ROWID| NMI                    |     1 |    22 |       |     5   (0)| 00:00:01 |                                             
      |   6 |       NESTED LOOPS              |                        |     1 |   131 |       | 95137   (2)| 00:19:02 |                                             
      |*  7 |        HASH JOIN                |                        |     1 |   109 |       | 95132   (2)| 00:19:02 |                                             
      |*  8 |         TABLE ACCESS FULL       | ASSET                  | 36074 |  1021K|       | 38553   (2)| 00:07:43 |                                             
      |*  9 |         HASH JOIN               |                        | 90361 |  7059K|  4040K| 56578   (2)| 00:11:19 |                                             
      |* 10 |          HASH JOIN              |                        | 52977 |  3414K|  2248K| 50654   (2)| 00:10:08 |                                             
      |* 11 |           HASH JOIN             |                        | 39674 |  1782K|       | 40101   (2)| 00:08:02 |                                             
      |* 12 |            TABLE ACCESS FULL    | REGISTER               | 39439 |  1232K|       | 22584   (2)| 00:04:32 |                                             
      |* 13 |            TABLE ACCESS FULL    | SDP_LOGICAL_REGISTER   |  4206K|    56M|       | 17490   (2)| 00:03:30 |                                             
      |* 14 |           TABLE ACCESS FULL     | SERVICE_DELIVERY_POINT |   675K|    12M|       |  9412   (2)| 00:01:53 |                                             
      |* 15 |          TABLE ACCESS FULL      | SDP_LOGICAL_ASSET      |  1178K|    15M|       |  4262   (2)| 00:00:52 |                                             
      |* 16 |        INDEX RANGE SCAN         | IDX_NMI_ID_NK          |     2 |       |       |     2   (0)| 00:00:01 |                                             
      |  17 |      VIEW                       |                        | 39674 |   232K|       | 40101   (2)| 00:08:02 |                                             
      |* 18 |       HASH JOIN                 |                        | 39674 |  1046K|       | 40101   (2)| 00:08:02 |                                             
      |* 19 |        TABLE ACCESS FULL        | REGISTER               | 39439 |   500K|       | 22584   (2)| 00:04:32 |                                             
      |* 20 |        TABLE ACCESS FULL        | SDP_LOGICAL_REGISTER   |  4206K|    56M|       | 17490   (2)| 00:03:30 |                                             
      |* 21 |     TABLE ACCESS FULL           | METER_CONFIG_HEADER    |  3658 | 47554 |       |    19   (0)| 00:00:01 |                                             
      |* 22 |    TABLE ACCESS FULL            | METER_CONFIG_ITEM      |  7590 | 68310 |       |   112   (2)| 00:00:02 |                                             
      ------------------------------------------------------------------------------------------------------------------                                             
                                                    
      Predicate Information (identified by operation id):                                             
      ---------------------------------------------------                                             
                                                    
         2 - access("METER_CONFIG_HEADER_SK"="METER_CONFIG_HEADER_SK")                                             
         3 - access("NETWORK_TARIFF_CD"="NETWORK_TARIFF_CD")                                             
         4 - access("SERVICE_DELIVERY_POINT_SK"="TMP"."SERVICE_DELIVERY_POINT_SK")                                             
         5 - filter("ROW_CURRENT_IND"='Y' AND ("NMI_STATUS_CD"='A' OR "NMI_STATUS_CD"='D'))                                             
         7 - access("ASSET_CD"="EQUIP_CD" AND "SAP_INT_LOG_DEVICE_SK"="SAP_INT_LOG_DEVICE_SK")                                             
         8 - filter("ROW_CURRENT_IND"='Y')                                             
         9 - access("SERVICE_DELIVERY_POINT_SK"="SERVICE_DELIVERY_POINT_SK")                                             
        10 - access("SERVICE_DELIVERY_POINT_SK"="SERVICE_DELIVERY_POINT_SK")                                             
        11 - access("SAP_INT_LOGICAL_REGISTER_SK"="SAP_INT_LOGICAL_REGISTER_SK")                                             
        12 - filter("REGISTER_TYPE_CD"='C' AND (SUBSTR("REGISTER_ID_CD",1,1)='4' OR                                              
                    SUBSTR("REGISTER_ID_CD",1,1)='5' OR SUBSTR("REGISTER_ID_CD",1,1)='6') AND "ROW_CURRENT_IND"='Y')                                             
        13 - filter("ROW_CURRENT_IND"='Y')                                             
        14 - filter("ROW_CURRENT_IND"='Y')                                             
        15 - filter("ROW_CURRENT_IND"='Y')                                             
        16 - access("NMI_SK"="NMI_SK")                                             
        18 - access("SAP_INT_LOGICAL_REGISTER_SK"="SAP_INT_LOGICAL_REGISTER_SK")                                             
        19 - filter("REGISTER_TYPE_CD"='C' AND (SUBSTR("REGISTER_ID_CD",1,1)='1' OR                                              
                    SUBSTR("REGISTER_ID_CD",1,1)='2' OR SUBSTR("REGISTER_ID_CD",1,1)='3') AND "ROW_CURRENT_IND"='Y')                                             
        20 - filter("ROW_CURRENT_IND"='Y')                                             
        21 - filter("ROW_CURRENT_IND"='Y')                                             
        22 - filter("ROW_CURRENT_IND"='Y' AND "CONROL_REGISTER"='X')                                             
      ENVIRONMENT B

      Plan hash value: 2826260434                                   
                                          
      ---------------------------------------------------------------------------------------------------------------                                   
      | Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |                                   
      ---------------------------------------------------------------------------------------------------------------                                   
      |   0 | SELECT STATEMENT                     |                        |     1 |   181 |   103K  (2)| 00:20:47 |                                   
      |   1 |  HASH UNIQUE                         |                        |     1 |   181 |   103K  (2)| 00:20:47 |                                   
      |*  2 |   HASH JOIN ANTI                     |                        |     1 |   181 |   103K  (2)| 00:20:47 |                                   
      |*  3 |    HASH JOIN                         |                        |     1 |   176 | 56855   (2)| 00:11:23 |                                   
      |*  4 |     HASH JOIN                        |                        |     1 |   163 | 36577   (2)| 00:07:19 |                                   
      |*  5 |      TABLE ACCESS BY INDEX ROWID     | ASSET                  |     1 |    44 |     4   (0)| 00:00:01 |                                   
      |   6 |       NESTED LOOPS                   |                        |     1 |   131 |  9834   (2)| 00:01:59 |                                   
      |   7 |        NESTED LOOPS                  |                        |     1 |    87 |  9830   (2)| 00:01:58 |                                   
      |   8 |         NESTED LOOPS                 |                        |     1 |    74 |  9825   (2)| 00:01:58 |                                   
      |*  9 |          HASH JOIN                   |                        |     1 |    52 |  9820   (2)| 00:01:58 |                                   
      |* 10 |           TABLE ACCESS BY INDEX ROWID| METER_CONFIG_HEADER    |     1 |    14 |     1   (0)| 00:00:01 |                                   
      |  11 |            NESTED LOOPS              |                        |     1 |    33 |   116   (2)| 00:00:02 |                                   
      |* 12 |             TABLE ACCESS FULL        | METER_CONFIG_ITEM      |     1 |    19 |   115   (2)| 00:00:02 |                                   
      |* 13 |             INDEX RANGE SCAN         | SYS_C00116570          |     1 |       |     1   (0)| 00:00:01 |                                   
      |* 14 |           TABLE ACCESS FULL          | SERVICE_DELIVERY_POINT |   723K|    13M|  9699   (2)| 00:01:57 |                                   
      |* 15 |          TABLE ACCESS BY INDEX ROWID | NMI                    |     1 |    22 |     5   (0)| 00:00:01 |                                   
      |* 16 |           INDEX RANGE SCAN           | IDX_NMI_ID_NK          |     2 |       |     2   (0)| 00:00:01 |                                   
      |* 17 |         TABLE ACCESS BY INDEX ROWID  | SDP_LOGICAL_ASSET      |     1 |    13 |     5   (0)| 00:00:01 |                                   
      |* 18 |          INDEX RANGE SCAN            | IDX_SLA_SDP_SK         |     2 |       |     2   (0)| 00:00:01 |                                   
      |* 19 |        INDEX RANGE SCAN              | IDX_A_SAPINTLOGDEV_SK  |     2 |       |     2   (0)| 00:00:01 |                                   
      |* 20 |      TABLE ACCESS FULL               | REGISTER               | 76113 |  2378K| 26743   (2)| 00:05:21 |                                   
      |* 21 |     TABLE ACCESS FULL                | SDP_LOGICAL_REGISTER   |  5095K|    63M| 20245   (2)| 00:04:03 |                                   
      |  22 |    VIEW                              |                        | 90889 |   443K| 47021   (2)| 00:09:25 |                                   
      |* 23 |     HASH JOIN                        |                        | 90889 |  2307K| 47021   (2)| 00:09:25 |                                   
      |* 24 |      TABLE ACCESS FULL               | REGISTER               | 76113 |   966K| 26743   (2)| 00:05:21 |                                   
      |* 25 |      TABLE ACCESS FULL               | SDP_LOGICAL_REGISTER   |  5095K|    63M| 20245   (2)| 00:04:03 |                                   
      ---------------------------------------------------------------------------------------------------------------                                   
                                          
      Predicate Information (identified by operation id):                                   
      ---------------------------------------------------                                   
                                          
         2 - access("SERVICE_DELIVERY_POINT_SK"="TMP"."SERVICE_DELIVERY_POINT_SK")                                   
         3 - access("SERVICE_DELIVERY_POINT_SK"="SERVICE_DELIVERY_POINT_SK" AND                                    
                    "SAP_INT_LOGICAL_REGISTER_SK"="SAP_INT_LOGICAL_REGISTER_SK")                                   
         4 - access("ASSET_CD"="EQUIP_CD")                                   
         5 - filter("ROW_CURRENT_IND"='Y')                                   
         9 - access("NETWORK_TARIFF_CD"="NETWORK_TARIFF_CD")                                   
        10 - filter("ROW_CURRENT_IND"='Y')                                   
        12 - filter("ROW_CURRENT_IND"='Y' AND "CONROL_REGISTER"='X')                                   
        13 - access("METER_CONFIG_HEADER_SK"="METER_CONFIG_HEADER_SK")                                   
        14 - filter("ROW_CURRENT_IND"='Y')                                   
        15 - filter("ROW_CURRENT_IND"='Y' AND ("NMI_STATUS_CD"='A' OR "NMI_STATUS_CD"='D'))                                   
        16 - access("NMI_SK"="NMI_SK")                                   
        17 - filter("ROW_CURRENT_IND"='Y')                                   
        18 - access("SERVICE_DELIVERY_POINT_SK"="SERVICE_DELIVERY_POINT_SK")                                   
        19 - access("SAP_INT_LOG_DEVICE_SK"="SAP_INT_LOG_DEVICE_SK")                                   
        20 - filter((SUBSTR("REGISTER_ID_CD",1,1)='4' OR SUBSTR("REGISTER_ID_CD",1,1)='5' OR                                    
                    SUBSTR("REGISTER_ID_CD",1,1)='6') AND "REGISTER_TYPE_CD"='C' AND "ROW_CURRENT_IND"='Y')                                   
        21 - filter("ROW_CURRENT_IND"='Y')                                   
        23 - access("SAP_INT_LOGICAL_REGISTER_SK"="SAP_INT_LOGICAL_REGISTER_SK")                                   
        24 - filter((SUBSTR("REGISTER_ID_CD",1,1)='1' OR SUBSTR("REGISTER_ID_CD",1,1)='2' OR                                    
                    SUBSTR("REGISTER_ID_CD",1,1)='3') AND "REGISTER_TYPE_CD"='C' AND "ROW_CURRENT_IND"='Y')                                   
        25 - filter("ROW_CURRENT_IND"='Y')
      Edited by: abhilash173 on Feb 24, 2013 9:16 PM

      Edited by: abhilash173 on Feb 24, 2013 9:18 PM