This discussion is archived
10 Replies Latest reply: Nov 20, 2012 11:48 AM by 970021 RSS

Query performance

970021 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    According to Oracle documentation, this procedure gathers stats on indexes as well.
  • 5. Re: Query performance
    970021 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points