10 Replies Latest reply: Nov 20, 2012 1:48 PM by 970021 RSS

    Query performance

    970021
      The below query takes about 16 seconds to run and I'm wondering if I can make it run under 3 seconds. Below is the execution plan as well as statistics. Please advise what kinds of things I can test with.
      Stats are up to date and unnecessary columns/outer joins have been removed.
      Version: Version: 11.2.0.2
      variable strid number
      variable vvdid number
       exec :strid := 7435
       exec :vvdid := 7790      
      SELECT TableTR0_.GD_ID          ,
        TableTR0_.V_ID             ,
        TableBR1_.SAMPLE_ID               ,
        TableBR1_.V_ID             ,
        TableEA2_.SAMPLE_ID               ,
        TableEA2_.V_ID             ,
        TablePQ3_.dt_id            ,
        TablePQ3_.V_ID             ,
        TableBCex4_.GD_ID               ,
        TableBCex4_.V_ID             ,
        TableMMs5_.GD_ID               ,
        TableMMs5_.V_ID             ,
        TableOBar6_.HEADER_ID              ,
        TableOBar6_.SEQUENCE               ,
        TableOBar6_.GD_ID               ,
        TableOBar6_.V_ID             ,
        TableXCeq7_.HEADER_ID              ,
        TableXCeq7_.SEQUENCE               ,
        TableXCeq7_.GD_ID               ,
        TableXCeq7_.V_ID             ,
        TableADopti8_.took_ID           ,
        packageent13_.EFFECTIVE_DT          ,
        TableYT9_.OOD_CD                 ,
        TableYT9_.GD_ID               ,
        TableYT9_.V_ID             ,
        OTEaleq10_.OOD_CD                ,
        OTEaleq10_.GD_ID              ,
        OTEaleq10_.V_ID            ,
        TableADopti11_.took_ID          ,
        packageent12_.EFFECTIVE_DT          ,
        packageent12_.package_ID            ,
        TableTR0_.ALL_WD                 ,
        TableTR0_.VEH_SOURCE             ,
        TableBR1_.AVAILABILITY           ,
        TableBR1_.dt_id            ,
        TableBR1_.EFFECTIVE_DT           ,
        TableBR1_.HIST_SAMPLE_ID          ,
        TableBR1_.MODEL_COMMENT          ,
        TableBR1_.MODEL_NM               ,
        TableBR1_.MODEL_YR               ,
        TableBR1_.SUB_dt_id        ,
        TableBCex4_.ACTV_FLG               ,
        TableOBar6_.CATEGORY_LIST          ,
        TableOBar6_.TableXC               ,
        TableOBar6_.GD_ID               ,
        TableOBar6_.V_ID             ,
        TableOBar6_.HEADER_ID              ,
        TableOBar6_.SEQUENCE               ,
        TableOBar6_.GD_ID               ,
        TableOBar6_.V_ID             ,
        TableXCeq7_.took_ID           ,
        TableYT9_.AVAILABILITY           ,
        TableYT9_.CATEGORY_LIST          ,
        TableYT9_.EXT_DESC               ,
        TableYT9_.HEADER_ID              ,
        TableYT9_.OPT_KIND_ID            ,
        TableYT9_.OPT_DESC               ,
        TableYT9_.SEQUENCE               ,
        TableYT9_.PON                    ,
        TableYT9_.PRICE_NOTES            ,
        TableYT9_.SUPPORTED_LOGIC        ,
        TableYT9_.TYP_FILTER             ,
        TableYT9_.UNSUPPORTED_LOGIC      ,
        TableYT9_.GD_ID               ,
        TableYT9_.V_ID             ,
        TableYT9_.OOD_CD                 ,
        TableYT9_.GD_ID               ,
        TableYT9_.V_ID             
      FROM YUG_STYLE TableTR0_
      INNER JOIN YUG_MODEL TableBR1_
      ON TableTR0_.SAMPLE_ID   =TableBR1_.SAMPLE_ID
      AND TableTR0_.V_ID=TableBR1_.V_ID
      LEFT OUTER JOIN HG_YUG_MODEL_EXTN TableEA2_
      ON TableBR1_.SAMPLE_ID   =TableEA2_.SAMPLE_ID
      AND TableBR1_.V_ID=TableEA2_.V_ID
      INNER JOIN YUG_DIVISION TablePQ3_
      ON TableBR1_.dt_id=TablePQ3_.dt_id
      AND TableBR1_.V_ID=TablePQ3_.V_ID
      INNER JOIN HG_YUG_STYLE_EXTN TableBCex4_
      ON TableTR0_.GD_ID   =TableBCex4_.GD_ID
      AND TableTR0_.V_ID=TableBCex4_.V_ID
      LEFT OUTER JOIN HG_SVF_ATTRBTS TableMMs5_
      ON TableTR0_.GD_ID   =TableMMs5_.GD_ID
      AND TableTR0_.V_ID=TableMMs5_.V_ID
      INNER JOIN YUG_TableXC TableOBar6_
      ON TableTR0_.GD_ID   =TableOBar6_.GD_ID
      AND TableTR0_.V_ID=TableOBar6_.V_ID
      INNER JOIN HG_TableXC_EQPMNT TableXCeq7_
      ON TableOBar6_.HEADER_ID  =TableXCeq7_.HEADER_ID
      AND TableOBar6_.SEQUENCE  =TableXCeq7_.SEQUENCE
      AND TableOBar6_.GD_ID  =TableXCeq7_.GD_ID
      AND TableOBar6_.V_ID=TableXCeq7_.V_ID
      INNER JOIN HG_TableAD_OTE_DESC TableADopti8_
      ON TableXCeq7_.took_ID=TableADopti8_.took_ID
      LEFT OUTER JOIN PUGI_package packageent13_
      ON TableADopti8_.took_ID=packageent13_.package_ID
      INNER JOIN YUG_OTES TableYT9_
      ON TableTR0_.GD_ID   =TableYT9_.GD_ID
      AND TableTR0_.V_ID=TableYT9_.V_ID
      INNER JOIN HG_OTE_EQPMNT OTEaleq10_
      ON TableYT9_.OOD_CD     =OTEaleq10_.OOD_CD
      AND TableYT9_.GD_ID  =OTEaleq10_.GD_ID
      AND TableYT9_.V_ID=OTEaleq10_.V_ID
      INNER JOIN HG_TableAD_OTE_DESC TableADopti11_
      ON OTEaleq10_.took_ID=TableADopti11_.took_ID
      LEFT OUTER JOIN PUGI_package packageent12_
      on TableADopti11_.took_ID =packageent12_.package_id
      WHERE (TableTR0_.GD_ID in(:strid))
      AND TableTR0_.V_ID   =TableBR1_.V_ID
      and TableTR0_.V_ID   =TablePQ3_.V_ID
      and TableTR0_.V_ID   =:vvdid;
      Here is the result of the autotrace output
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 1875615696
      
      -------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                                | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
      -------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                         |                              |     1 |  1424 |   108   (0)| 00:00:02 |
      |   1 |  NESTED LOOPS OUTER                      |                              |     1 |  1424 |   108   (0)| 00:00:02 |
      |   2 |   NESTED LOOPS                           |                              |     1 |  1401 |   106   (0)| 00:00:02 |
      |   3 |    NESTED LOOPS                          |                              |     1 |  1200 |   104   (0)| 00:00:02 |
      |   4 |     MERGE JOIN CARTESIAN                 |                              |     1 |  1124 |   102   (0)| 00:00:02 |
      |   5 |      NESTED LOOPS OUTER                  |                              |     1 |  1103 |    54   (0)| 00:00:01 |
      |   6 |       NESTED LOOPS                       |                              |     1 |  1080 |    52   (0)| 00:00:01 |
      |   7 |        NESTED LOOPS                      |                              |     1 |   879 |    50   (0)| 00:00:01 |
      |   8 |         NESTED LOOPS                     |                              |    11 |  7040 |    28   (0)| 00:00:01 |
      |   9 |          NESTED LOOPS                    |                              |     1 |   617 |     6   (0)| 00:00:01 |
      |  10 |           NESTED LOOPS OUTER             |                              |     1 |   598 |     5   (0)| 00:00:01 |
      |  11 |            NESTED LOOPS                  |                              |     1 |   420 |     5   (0)| 00:00:01 |
      |  12 |             NESTED LOOPS OUTER           |                              |     1 |   364 |     4   (0)| 00:00:01 |
      |  13 |              NESTED LOOPS                |                              |     1 |   311 |     3   (0)| 00:00:01 |
      |  14 |               TABLE ACCESS BY INDEX ROWID| HG_YUG_STYLE_EXTN            |     1 |    58 |     2   (0)| 00:00:01 |
      |* 15 |                INDEX UNIQUE SCAN         | HG_YUG_STYLE_EXTN_UK1        |     1 |       |     1   (0)| 00:00:01 |
      |  16 |               TABLE ACCESS BY INDEX ROWID| YUG_STYLE                    |     1 |   253 |     1   (0)| 00:00:01 |
      |* 17 |                INDEX RANGE SCAN          | PK_YUG_GD_ID              |     1 |       |     0   (0)| 00:00:01 |
      |  18 |              TABLE ACCESS BY INDEX ROWID | HG_SVF_ATTRBTS               |     1 |    53 |     1   (0)| 00:00:01 |
      |* 19 |               INDEX UNIQUE SCAN          | PK_HG_SVF_ATTRBTS            |     1 |       |     0   (0)| 00:00:01 |
      |  20 |             TABLE ACCESS BY INDEX ROWID  | YUG_MODEL                    |     1 |    56 |     1   (0)| 00:00:01 |
      |* 21 |              INDEX RANGE SCAN            | PK_YUG_MODEL                 |     1 |       |     0   (0)| 00:00:01 |
      |  22 |            TABLE ACCESS BY INDEX ROWID   | HG_YUG_MODEL_EXTN            |     1 |   178 |     0   (0)| 00:00:01 |
      |* 23 |             INDEX UNIQUE SCAN            | PK_HG_YUG_MODEL_EXTN         |     1 |       |     0   (0)| 00:00:01 |
      |  24 |           TABLE ACCESS BY INDEX ROWID    | YUG_DIVISION                 |     1 |    19 |     1   (0)| 00:00:01 |
      |* 25 |            INDEX RANGE SCAN              | PK_YUG_DIVISION              |     1 |       |     0   (0)| 00:00:01 |
      |  26 |          TABLE ACCESS BY INDEX ROWID     | HG_OTE_EQPMNT             |    19 |   437 |    22   (0)| 00:00:01 |
      |* 27 |           INDEX RANGE SCAN               | PK_HG_OTE_EQPMNT          |    19 |       |     2   (0)| 00:00:01 |
      |  28 |         TABLE ACCESS BY INDEX ROWID      | YUG_OTES                  |     1 |   239 |     2   (0)| 00:00:01 |
      |* 29 |          INDEX UNIQUE SCAN               | YUG_OTES_PK               |     1 |       |     1   (0)| 00:00:01 |
      |  30 |        TABLE ACCESS BY INDEX ROWID       | HG_TableAD_OTE_DESC        |     1 |   201 |     2   (0)| 00:00:01 |
      |* 31 |         INDEX RANGE SCAN                 | HG_TableAD_OTE_DESC_INDEX1 |     1 |       |     1   (0)| 00:00:01 |
      |  32 |       TABLE ACCESS BY INDEX ROWID        | PUGI_package               |     1 |    23 |     2   (0)| 00:00:01 |
      |* 33 |        INDEX RANGE SCAN                  | PK_PUGI_package            |     1 |       |     1   (0)| 00:00:01 |
      |  34 |      BUFFER SORT                         |                              |    26 |   546 |   100   (0)| 00:00:02 |
      |* 35 |       TABLE ACCESS BY INDEX ROWID        | HG_TableXC_EQPMNT           |    26 |   546 |    48   (0)| 00:00:01 |
      |* 36 |        INDEX RANGE SCAN                  | HG_TableXC_XREF_INDEX1      |    53 |       |     2   (0)| 00:00:01 |
      |  37 |     TABLE ACCESS BY INDEX ROWID          | YUG_TableXC                 |     1 |    76 |     2   (0)| 00:00:01 |
      |* 38 |      INDEX RANGE SCAN                    | PK_YUG_TableXC              |     1 |       |     1   (0)| 00:00:01 |
      |  39 |    TABLE ACCESS BY INDEX ROWID           | HG_TableAD_OTE_DESC        |     1 |   201 |     2   (0)| 00:00:01 |
      |* 40 |     INDEX RANGE SCAN                     | HG_TableAD_OTE_DESC_INDEX1 |     1 |       |     1   (0)| 00:00:01 |
      |  41 |   TABLE ACCESS BY INDEX ROWID            | PUGI_package               |     1 |    23 |     2   (0)| 00:00:01 |
      |* 42 |    INDEX RANGE SCAN                      | PK_PUGI_package            |     1 |       |     1   (0)| 00:00:01 |
      -------------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
        15 - access("TableBCEX4_"."GD_ID"=TO_NUMBER(:strid) AND
                    "TableBCEX4_"."V_ID"=TO_NUMBER(:vvdid))
        17 - access("TableTR0_"."GD_ID"=TO_NUMBER(:strid) AND
                    "TableTR0_"."V_ID"=TO_NUMBER(:vvdid))
        19 - access("TableMMS5_"."V_ID"(+)=TO_NUMBER(:vvdid) AND
                    "TableMMS5_"."GD_ID"(+)=TO_NUMBER(:strid))
        21 - access("TableTR0_"."SAMPLE_ID"="TableBR1_"."SAMPLE_ID" AND
                    "TableTR0_"."V_ID"="TableBR1_"."V_ID")
             filter("TableBR1_"."V_ID"=TO_NUMBER(:vvdid))
        23 - access("TableBR1_"."SAMPLE_ID"="TableEA2_"."SAMPLE_ID"(+) AND
                    "TableEA2_"."V_ID"(+)=TO_NUMBER(:vvdid))
        25 - access("TableBR1_"."dt_id"="TablePQ3_"."dt_id" AND
                    "TableBR1_"."V_ID"="TablePQ3_"."V_ID")
             filter("TablePQ3_"."V_ID"=TO_NUMBER(:vvdid))
        27 - access("OTEALEQ10_"."GD_ID"=TO_NUMBER(:strid) AND
                    "OTEALEQ10_"."V_ID"=TO_NUMBER(:vvdid))
             filter("OTEALEQ10_"."V_ID"=TO_NUMBER(:vvdid))
        29 - access("TableYT9_"."GD_ID"=TO_NUMBER(:strid) AND "TableYT9_"."OOD_CD"="OTEALEQ10_"."OOD_CD"
                    AND "TableYT9_"."V_ID"=TO_NUMBER(:vvdid))
        31 - access("OTEALEQ10_"."took_ID"="TableADOPTI11_"."took_ID")
        33 - access("TableADOPTI11_"."took_ID"="packageENT12_"."package_ID"(+))
        35 - filter("TableXCEQ7_"."V_ID"=TO_NUMBER(:vvdid))
        36 - access("TableXCEQ7_"."GD_ID"=TO_NUMBER(:strid))
        38 - access("TableOBAR6_"."SEQUENCE"="TableXCEQ7_"."SEQUENCE" AND
                    "TableOBAR6_"."GD_ID"=TO_NUMBER(:strid) AND "TableOBAR6_"."HEADER_ID"="TableXCEQ7_"."HEADER_ID" AND
                    "TableOBAR6_"."V_ID"=TO_NUMBER(:vvdid))
        40 - access("TableXCEQ7_"."took_ID"="TableADOPTI8_"."took_ID")
        42 - access("TableADOPTI8_"."took_ID"="packageENT13_"."package_ID"(+))
      
      
      Statistics
      ----------------------------------------------------------
                0  recursive calls
                0  db block gets
            20526  consistent gets
                0  physical reads
                0  redo size
           920942  bytes sent via SQL*Net to client
            14002  bytes received via SQL*Net from client
              228  SQL*Net roundtrips to/from client
                1  sorts (memory)
                0  sorts (disk)
             3400  rows processed
      Edited by: 967018 on Nov 19, 2012 9:50 AM
        • 1. Re: Query performance
          Dom Brooks
          See [url https://forums.oracle.com/forums/thread.jspa?threadID=863295]How to post a sql tuning request

          Estimate of rows returned = 1
          0  SELECT STATEMENT    1  1424  108 (0) 00:00:02  
          Actual rows returned:
          3400 rows processed
          Accurate estimates are essential to optimal execution.

          Particularly when stats/estimates say 0 or 1 rows then you get particularly poor choices
          A MEREG JOIN CARTSIAN, for example, can be particularly inefficient if the estimates are significantly out.
          4  MERGE JOIN CARTESIAN    1  1124  102 (0) 00:00:02  
          ...
          34  BUFFER SORT    26  546  100 (0) 00:00:02  
          • 2. Re: Query performance
            970021
            I ran DBMS_STATS.GATHER_TABLE_STATS on all tables involved. Wouldn't that provide accurate stats?

            Here is the additional information. I can't run tkprof because I don't have access to the files right now.
            SQL> show parameter optimizer
            
            NAME                                 TYPE                             VALUE
            ------------------------------------ -------------------------------- ------------------------------
            optimizer_capture_sql_plan_baselines boolean                          FALSE
            optimizer_dynamic_sampling           integer                          2
            optimizer_features_enable            string                           11.2.0.2
            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
            SQL> show parameter db_file_multi
            
            NAME                                 TYPE                             VALUE
            ------------------------------------ -------------------------------- ------------------------------
            db_file_multiblock_read_count        integer                          98
            SQL> show parameter db_block_size
            
            NAME                                 TYPE                             VALUE
            ------------------------------------ -------------------------------- ------------------------------
            db_block_size                        integer                          8192
            SQL> show parameter cursor_sharing
            
            NAME                                 TYPE                             VALUE
            ------------------------------------ -------------------------------- ------------------------------
            cursor_sharing                       string                           FORCE
            Here is the explain plan:
            SQL> select * from table(dbms_xplan.display);
            
            PLAN_TABLE_OUTPUT
            --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            Plan hash value: 1392349618
            
            -------------------------------------------------------------------------------------------------------------------------
            | Id  | Operation                                | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
            -------------------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT                         |                              |     8 | 11392 |   179   (1)| 00:00:03 |
            |   1 |  NESTED LOOPS OUTER                      |                              |     8 | 11392 |   179   (1)| 00:00:03 |
            |   2 |   NESTED LOOPS                           |                              |     8 | 11208 |   170   (1)| 00:00:03 |
            |   3 |    NESTED LOOPS                          |                              |     8 |  9600 |   154   (1)| 00:00:02 |
            |   4 |     MERGE JOIN CARTESIAN                 |                              |     8 |  8992 |   138   (1)| 00:00:02 |
            |   5 |      NESTED LOOPS                        |                              |     1 |  1103 |    90   (2)| 00:00:02 |
            |   6 |       NESTED LOOPS OUTER                 |                              |     1 |  1084 |    89   (2)| 00:00:02 |
            |   7 |        NESTED LOOPS                      |                              |     1 |   906 |    89   (2)| 00:00:02 |
            |   8 |         NESTED LOOPS OUTER               |                              |     1 |   850 |    88   (2)| 00:00:02 |
            |   9 |          NESTED LOOPS                    |                              |     1 |   827 |    86   (2)| 00:00:02 |
            |* 10 |           HASH JOIN                      |                              |     1 |   626 |    84   (2)| 00:00:02 |
            |  11 |            TABLE ACCESS BY INDEX ROWID   | HG_OTE_EQPMNT             |    33 |   759 |    37   (0)| 00:00:01 |
            |* 12 |             INDEX RANGE SCAN             | PK_HG_OTE_EQPMNT          |    33 |       |     3   (0)| 00:00:01 |
            |  13 |            NESTED LOOPS                  |                              |    43 | 25929 |    46   (0)| 00:00:01 |
            |  14 |             NESTED LOOPS OUTER           |                              |     1 |   364 |     4   (0)| 00:00:01 |
            |  15 |              NESTED LOOPS                |                              |     1 |   311 |     3   (0)| 00:00:01 |
            |  16 |               TABLE ACCESS BY INDEX ROWID| HG_YUG_STYLE_EXTN            |     1 |    58 |     2   (0)| 00:00:01 |
            |* 17 |                INDEX UNIQUE SCAN         | HG_YUG_STYLE_EXTN_UK1        |     1 |       |     1   (0)| 00:00:01 |
            |  18 |               TABLE ACCESS BY INDEX ROWID| YUG_STYLE                    |     1 |   253 |     1   (0)| 00:00:01 |
            |* 19 |                INDEX RANGE SCAN          | PK_YUG_GD_ID              |     1 |       |     0   (0)| 00:00:01 |
            |  20 |              TABLE ACCESS BY INDEX ROWID | HG_SVF_ATTRBTS               |     1 |    53 |     1   (0)| 00:00:01 |
            |* 21 |               INDEX UNIQUE SCAN          | PK_HG_SVF_ATTRBTS            |     1 |       |     0   (0)| 00:00:01 |
            |  22 |             TABLE ACCESS BY INDEX ROWID  | YUG_OPTIONS                  |    43 | 10277 |    42   (0)| 00:00:01 |
            |* 23 |              INDEX RANGE SCAN            | YUG_OPTIONS_PK               |    43 |       |     2   (0)| 00:00:01 |
            |  24 |           TABLE ACCESS BY INDEX ROWID    | HG_TableAD_OTE_DESC        |     1 |   201 |     2   (0)| 00:00:01 |
            |* 25 |            INDEX RANGE SCAN              | HG_TableAD_OTE_DESC_INDEX1 |     1 |       |     1   (0)| 00:00:01 |
            |  26 |          TABLE ACCESS BY INDEX ROWID     | PUGI_PACKAGE               |     1 |    23 |     2   (0)| 00:00:01 |
            |* 27 |           INDEX RANGE SCAN               | PK_PUGI_PACKAGE            |     1 |       |     1   (0)| 00:00:01 |
            |  28 |         TABLE ACCESS BY INDEX ROWID      | YUG_MODEL                    |     1 |    56 |     1   (0)| 00:00:01 |
            |* 29 |          INDEX RANGE SCAN                | PK_YUG_MODEL                 |     1 |       |     0   (0)| 00:00:01 |
            |  30 |        TABLE ACCESS BY INDEX ROWID       | HG_YUG_MODEL_EXTN            |     1 |   178 |     0   (0)| 00:00:01 |
            |* 31 |         INDEX UNIQUE SCAN                | PK_HG_YUG_MODEL_EXTN         |     1 |       |     0   (0)| 00:00:01 |
            |  32 |       TABLE ACCESS BY INDEX ROWID        | YUG_DIVISION                 |     1 |    19 |     1   (0)| 00:00:01 |
            |* 33 |        INDEX RANGE SCAN                  | PK_YUG_DIVISION              |     1 |       |     0   (0)| 00:00:01 |
            |  34 |      BUFFER SORT                         |                              |    49 |  1029 |   137   (1)| 00:00:02 |
            |* 35 |       TABLE ACCESS BY INDEX ROWID        | HG_TableXC_EQPMNT           |    49 |  1029 |    48   (0)| 00:00:01 |
            |* 36 |        INDEX RANGE SCAN                  | HG_TableXC_XREF_INDEX1      |    53 |       |     2   (0)| 00:00:01 |
            |  37 |     TABLE ACCESS BY INDEX ROWID          | YUG_TableXC                 |     1 |    76 |     2   (0)| 00:00:01 |
            |* 38 |      INDEX RANGE SCAN                    | PK_YUG_TableXC              |     1 |       |     1   (0)| 00:00:01 |
            |  39 |    TABLE ACCESS BY INDEX ROWID           | HG_TableAD_OTE_DESC        |     1 |   201 |     2   (0)| 00:00:01 |
            |* 40 |     INDEX RANGE SCAN                     | HG_TableAD_OTE_DESC_INDEX1 |     1 |       |     1   (0)| 00:00:01 |
            |  41 |   TABLE ACCESS BY INDEX ROWID            | PUGI_PACKAGE               |     1 |    23 |     2   (0)| 00:00:01 |
            |* 42 |    INDEX RANGE SCAN                      | PK_PUGI_PACKAGE            |     1 |       |     1   (0)| 00:00:01 |
            -------------------------------------------------------------------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
              10 - access("TableYT9_"."OOD_CD"="OPTIONALEQ10_"."OOD_CD" AND
                          "TableYT9_"."GD_ID"="OPTIONALEQ10_"."GD_ID" AND
                          "TableYT9_"."V_ID"="OPTIONALEQ10_"."V_ID")
              12 - access("OPTIONALEQ10_"."GD_ID"=7435 AND "OPTIONALEQ10_"."V_ID"=7790)
            
            PLAN_TABLE_OUTPUT
            --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                   filter("OPTIONALEQ10_"."V_ID"=7790)
              17 - access("TableBCEX4_"."GD_ID"=7435 AND "TableBCEX4_"."V_ID"=7790)
              19 - access("TableTR0_"."GD_ID"=7435 AND "TableTR0_"."V_ID"=7790)
              21 - access("TableMMS5_"."V_ID"(+)=7790 AND "TableMMS5_"."GD_ID"(+)=7435)
              23 - access("TableYT9_"."GD_ID"=7435 AND "TableYT9_"."V_ID"=7790)
                   filter("TableYT9_"."V_ID"=7790)
              25 - access("OPTIONALEQ10_"."TOOK_ID"="TableADOPTI11_"."TOOK_ID")
              27 - access("TableADOPTI11_"."TOOK_ID"="PACKAGEENT12_"."PACKAGE_ID"(+))
              29 - access("TableTR0_"."SMPLE_ID"="TableBR1_"."SMPLE_ID" AND
                          "TableTR0_"."V_ID"="TableBR1_"."V_ID")
                   filter("TableBR1_"."V_ID"=7790)
              31 - access("TableBR1_"."SMPLE_ID"="TableEA2_"."SMPLE_ID"(+) AND "TableEA2_"."V_ID"(+)=7790)
              33 - access("TableBR1_"."DT_ID"="TablePQ3_"."DT_ID" AND
                          "TableBR1_"."V_ID"="TablePQ3_"."V_ID")
                   filter("TablePQ3_"."V_ID"=7790)
              35 - filter("TableXCEQ7_"."V_ID"=7790)
              36 - access("TableXCEQ7_"."GD_ID"=7435)
              38 - access("TableOBAR6_"."SEQUENCE"="TableXCEQ7_"."SEQUENCE" AND "TableOBAR6_"."GD_ID"=7435 AND
                          "TableOBAR6_"."HEADER_ID"="TableXCEQ7_"."HEADER_ID" AND "TableOBAR6_"."V_ID"=7790)
              40 - access("TableXCEQ7_"."TOOK_ID"="TableADOPTI8_"."TOOK_ID")
              42 - access("TableADOPTI8_"."TOOK_ID"="PACKAGEENT13_"."PACKAGE_ID"(+))
            
            78 rows selected.
            
            Elapsed: 00:00:00.32
            • 3. Re: Query performance
              sb92075
              967018 wrote:
              I ran DBMS_STATS.GATHER_TABLE_STATS on all tables involved. Wouldn't that provide accurate stats?
              what about statistics on INDEXES?
              • 4. Re: Query performance
                970021
                According to Oracle documentation, this procedure gathers stats on indexes as well.
                • 5. Re: Query performance
                  970021
                  Isn't the rows column in the plan representing the cardinality and not the actual # of rows. So basically the rows = 1 means all 3400 records are unique?

                  All tables have a column V_ID which represents the version. So for example in the clause below, table TableBCex4_ has a PK of GD_ID and V_ID, to include the version of GD_ID.
                  ON TableTR0_.GD_ID   =TableBCex4_.GD_ID
                  AND TableTR0_.V_ID=TableBCex4_.V_ID
                  • 6. Re: Query performance
                    Nikolay Savvinov
                    Hi
                    967018 wrote:
                    I ran DBMS_STATS.GATHER_TABLE_STATS on all tables involved. Wouldn't that provide accurate stats?
                    In general, no. Optimizer stats tell how many rows are in a table or a partition, and some basic things about column data (min and max column values, number of NULLs etc.). I.e. with accurate optimizer stats you can count on accurate cardinality estimates if there are no predicates or the predicates are simple and uncorrelated. I suspect that the latter condition in your case is not true. E.g. when estimating the selectivity of the join below

                    INNER JOIN HG_OTE_EQPMNT OTEaleq10_
                    ON TableYT9_.OOD_CD =OTEaleq10_.OOD_CD
                    AND TableYT9_.GD_ID =OTEaleq10_.GD_ID
                    AND TableYT9_.V_ID=OTEaleq10_.V_ID

                    the optimizer assumes that columns OOD_CD, GD_ID and V_ID are not correlated with each other. If this assumption is wrong, then cardinality will be grossly underestimated, leading to bad join methods and/or order.

                    >
                    Here is the additional information. I can't run tkprof because I don't have access to the files right now.
                    there are other tools -- SQL real-time monitor (requires diagnostic pack license), statistics_level = all + dbms_xplan(...'allstats last')

                    Best regards,
                    Nikolay
                    • 7. Re: Query performance
                      970021
                      INNER JOIN HG_OTE_EQPMNT OTEaleq10_
                      ON TableYT9_.OOD_CD =OTEaleq10_.OOD_CD
                      AND TableYT9_.GD_ID =OTEaleq10_.GD_ID
                      AND TableYT9_.V_ID=OTEaleq10_.V_ID
                      
                      the optimizer assumes that columns OOD_CD, GD_ID and V_ID are not correlated with each other. If this assumption is wrong, then cardinality will be grossly underestimated, leading to bad join methods and/or order.
                      I'm not sure what you mean by correlated, but it's a 1 to many relationship between GD_ID and OOD_CD. V_ID only has two values in all these tables and for this query, I'm using V_ID = 7790. I suppose GD_ID and OOD_CD is correlated then? If so, how can I tell the optimizer they are correlated?
                      • 8. Re: Query performance
                        Nikolay Savvinov
                        Hi

                        >
                        ...
                        I'm not sure what you mean by correlated, but it's a 1 to many relationship between GD_ID and OOD_CD.
                        Correlation means that quantities depend on each other. For example, 1/12 of all people have birthday in May and 1/2 of all people are male. Therefore, 1/24 of people are males born in May: gender and birthday are not correlated, therefore we can multiply the fractions. On the other hand, 1/5 of all people are Chinese and 1/5 of all people speak Mandarin, but people who both are Chinese and speak Mandarin are not 1/25, but 1/5 of the population. Since language and country of origin do correlate, we cannot multiply the fractions.

                        This becomes very important when the optimizer estimates selectivities. E.g. it can calculate what fraction of rows satisfies col1 = A and col2 = B, but what would be the fraction of rows for col1 = A AND col2 = B? The optimizer assumes that the columns are not correlated (because there is no good way for it to measure all possible correlations), but often this assumption is wrong, leading to bad cardinality estimates, and as a consequence, bad plans.

                        Best regards,
                        Nikolay
                        • 9. Re: Query performance
                          Dom Brooks
                          Isn't the rows column in the plan representing the cardinality and not the actual # of rows. So basically the rows = 1 means all 3400 records are unique?
                          Yes/No.
                          It is cardinality. But cardinality is the number of rows estimated to be returned from that rowsource.
                          So, "1" means 1 row is estimated to be returned from that rowsource
                          Depending on that operation, it may be one row estimated per start - e.g. the inner rowsource (not the outer/driving rowsource) of a NESTED LOOP.

                          https://blogs.oracle.com/optimizer/entry/how_do_i_know_if
                          • 10. Re: Query performance
                            970021
                            I followed the instructions and added a
                            /*+ GATHER_PLAN_STATISTICS */
                            in the SQL statement. Then I ran
                            select * from table (DBMS_XPLAN.DISPLAY_CURSOR (FORMAT =>'ALLSTATS LAST'));
                            and here is the output I got:
                            SQL_ID  7pak1qxfs3t56, child number 0
                            -------------------------------------
                            select * from table (DBMS_XPLAN.DISPLAY_CURSOR (FORMAT =>:"SYS_B_0"))
                            
                            Plan hash value: 3713220770
                            
                            ---------------------------------------------------------------------
                            | Id  | Operation                         | Name           | E-Rows |
                            ---------------------------------------------------------------------
                            |   0 | SELECT STATEMENT                  |                |        |
                            |   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |   8168 |
                            ---------------------------------------------------------------------
                            
                            Note
                            -----
                               - Warning: basic plan statistics not available. These are only collected when:
                                   * hint 'gather_plan_statistics' is used for the statement or
                                   * parameter 'statistics_level' is set to 'ALL', at session or system level
                            I ran this in SQL Developer since it would take me forever to run it in SQL Plus. It returns 3400 records in SQL Plus and it would more than an hour to display the results. Is there a different way of doing this in SQL Plus?