1 2 Previous Next 15 Replies Latest reply: Oct 10, 2012 1:12 PM by Iordan Iotzov RSS

    Wrong estimation of sql on partitioned table

    Yasu
      Version: 11.1.0.7

      Below sql takes 192 seconds to execute. After comparing A-rows and E-rows found that range scan on index HIT_IE7 estimation is way too far from reality. This index is on a range-partition table on partition_key column.

      Even giving cardinality hint did not help Optimizer to estimate appropriately on HIT table while performing range scan in HIT_IE7.

      sql:
      select  /*+ gather_plan_statistics */           h.PRT_DT AS workDate,
                                      ah.W_STS_T AS initialStatus,
                                      ws.TYPE AS statusType,
                                      whr.C_ID AS contactId,
                                      whr.RESP_NM AS userName,
                                      count(0) AS counter,
                                      MIN (h.opposition_dt - TRUNC (SYSDATE)) AS daysToOpposition
      from                    a_hit ah
      inner join              hit h on ah.a_hit_ID=h.HIT_ID
      and                     ah.partition_key = h.partition_key
      and                     ah.partition_key = :N1
      and                     h.partition_key = :N2
      inner join              w_hit_p whr on whr.PARTITION_KEY=h.PARTITION_KEY
      and                     whr.partition_key = :N3
      and                     whr.HIT_ID=h.HIT_ID
      and                     whr.MBR_ID= :N4
      left outer join W_STATUS ws on ah.ID = ws.W_STS_ID
      inner join              s_file sf on h.s_file_id=sf.s_file_id
      where                   ah.W_STS_T=1
      and 14 = 14
      and                     sf.loaded_fl='Y'
      and                     H.PRT_DT between to_date('10/18/11','MM/DD/YY') and to_date('02/17/12','MM/DD/YY')
      and                     a_hit_ID not in
                                              (select         a_hit_ID
                                              from            H_HITS hh, H_RULE hr
                                              where           hr.H_RID = hh.H_RID
                                              and             hr.C_ID = whr.C_ID)
      and                     a_hit_ID not in
                                              (select         a_hit_ID
                                              from            T_HIT th
                                              where           th.STS in ('T', 'D')
                                              and             th.C_ID = whr.C_ID)
      group by                h.PRT_DT,
                                      ah.W_STS_T,
                                      ws.TYPE,whr.C_ID,
                                      whr.RESP_NM
      /
      Execution plan pulled just after execution of the sql using : select * from table(dbms_xplan.display_cursor(null,null,'typical allstats last'));
      Plan hash value: 1584243699
      
      ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      ---------------
      
      | Id  | Operation                                   | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1M
      em | Used-Mem |
      
      ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      ---------------
      
      |   0 | SELECT STATEMENT                            |                 |      1 |        |       | 27760 (100)|          |       |       |   1417 |00:03:12.13 |     139K|  26354 |       |
         |          |
      
      |   1 |  HASH GROUP BY                              |                 |      1 |   3886 |   576K| 27760   (1)| 00:05:34 |       |       |   1417 |00:03:12.13 |     139K|  26354 |   771K|   7
      71K| 1241K (0)|
      
      |*  2 |   FILTER                                    |                 |      1 |        |       |            |          |       |       |  23207 |00:03:18.58 |     139K|  26354 |       |
         |          |
      
      |   3 |    NESTED LOOPS                             |                 |      1 |        |       |            |          |       |       |  23207 |00:03:18.58 |     139K|  26354 |       |
         |          |
      
      |   4 |     NESTED LOOPS                            |                 |      1 |   3886 |   576K| 27759   (1)| 00:05:34 |       |       |  23207 |00:03:15.52 |     116K|  26351 |       |
         |          |
      
      |*  5 |      HASH JOIN OUTER                        |                 |      1 |   3882 |   534K| 19992   (1)| 00:04:00 |       |       |  23207 |00:03:09.37 |   70401 |  26149 |  2541K|  10
      99K| 4305K (0)|
      
      |*  6 |       HASH JOIN RIGHT ANTI                  |                 |      1 |   3882 |   466K| 19932   (1)| 00:04:00 |       |       |  23207 |00:03:06.60 |   69985 |  26149 |  1511K|  15
      11K| 1971K (0)|
      
      |*  7 |        TABLE ACCESS FULL                    | T_HIT           |      1 |  15750 |   261K|    26   (4)| 00:00:01 |       |       |  16808 |00:00:00.01 |     170 |      0 |       |
         |          |
      
      |   8 |        NESTED LOOPS ANTI                    |                 |      1 |   3892 |   402K| 19906   (1)| 00:03:59 |       |       |  23207 |00:03:06.60 |   69815 |  26149 |       |
         |          |
      
      |*  9 |         HASH JOIN                           |                 |      1 |   4072 |   389K|  3611   (2)| 00:00:44 |       |       |  23207 |00:03:06.60 |   69207 |  26149 |  2645K|  10
      85K| 3349K (0)|
      
      |* 10 |          HASH JOIN                          |                 |      1 |   4072 |   282K|  2076   (2)| 00:00:25 |       |       |  23461 |00:02:51.59 |   58901 |  21748 |  2147K|  11
      85K| 3309K (0)|
      
      |  11 |           PARTITION RANGE SINGLE            |                 |      1 |   4072 |   131K|    40   (0)| 00:00:01 |   KEY |   KEY |  23461 |00:03:15.89 |   46810 |  15021 |       |
         |          |
      
      |  12 |            TABLE ACCESS BY LOCAL INDEX ROWID| HIT             |      1 |   4072 |   131K|    40   (0)| 00:00:01 |   KEY |   KEY |  23461 |00:03:15.89 |   46810 |  15021 |       |
         |          |
      
      |* 13 |             INDEX RANGE SCAN                | HIT_IE7         |      1 |      1 |       |    21   (0)| 00:00:01 |   KEY |   KEY |  23461 |00:00:00.05 |     104 |    103 |       |
         |          |
      
      |  14 |           PARTITION RANGE SINGLE            |                 |      1 |    337K|    12M|  2033   (2)| 00:00:25 |   KEY |   KEY |    333K|00:00:08.99 |   12091 |   6727 |       |
         |          |
      
      |* 15 |            TABLE ACCESS FULL                | W_HIT_P         |      1 |    337K|    12M|  2033   (2)| 00:00:25 |   KEY |   KEY |    333K|00:00:08.99 |   12091 |   6727 |       |
         |          |
      
      |  16 |          PARTITION RANGE SINGLE             |                 |      1 |    397K|    10M|  1533   (2)| 00:00:19 |   KEY |   KEY |    323K|00:00:11.98 |   10306 |   4401 |       |
         |          |
      
      |* 17 |           TABLE ACCESS FULL                 | A_HIT           |      1 |    397K|    10M|  1533   (2)| 00:00:19 |   KEY |   KEY |    323K|00:00:11.98 |   10306 |   4401 |       |
         |          |
      
      |  18 |         VIEW PUSHED PREDICATE               | VW_SQ_1         |  23207 |      1 |     8 |     4   (0)| 00:00:01 |       |       |      0 |00:00:00.10 |     608 |      0 |       |
         |          |
      
      |  19 |          NESTED LOOPS                       |                 |  23207 |        |       |            |          |       |       |      0 |00:00:00.10 |     608 |      0 |       |
         |          |
      
      |  20 |           NESTED LOOPS                      |                 |  23207 |      1 |    26 |     4   (0)| 00:00:01 |       |       |      0 |00:00:00.07 |     608 |      0 |       |
         |          |
      
      |  21 |            TABLE ACCESS BY INDEX ROWID      | H_HITS          |  23207 |      1 |    14 |     3   (0)| 00:00:01 |       |       |      0 |00:00:00.07 |     608 |      0 |       |
         |          |
      
      |* 22 |             INDEX RANGE SCAN                | H_HITS_IF1      |  23207 |      1 |       |     1   (0)| 00:00:01 |       |       |      0 |00:00:00.05 |     608 |      0 |       |
         |          |
      
      |* 23 |            INDEX UNIQUE SCAN                | H_RULE_PK       |      0 |      1 |       |     0   (0)|          |       |       |      0 |00:00:00.01 |       0 |      0 |       |
         |          |
      
      |* 24 |           TABLE ACCESS BY INDEX ROWID       | H_RULE          |      0 |      1 |    12 |     1   (0)| 00:00:01 |       |       |      0 |00:00:00.01 |       0 |      0 |       |
         |          |
      
      |  25 |       TABLE ACCESS FULL                     | W_STATUS        |      1 |  36020 |   633K|    59   (2)| 00:00:01 |       |       |  35329 |00:00:00.01 |     416 |      0 |       |
         |          |
      
      |* 26 |      INDEX UNIQUE SCAN                      | S_FILE_PK       |  23207 |      1 |       |     1   (0)| 00:00:01 |       |       |  23207 |00:00:02.54 |   46073 |    202 |       |
         |          |
      
      |* 27 |     TABLE ACCESS BY INDEX ROWID             | S_FILE          |  23207 |      1 |    11 |     2   (0)| 00:00:01 |       |       |  23207 |00:00:00.14 |   23209 |      3 |       |
         |          |
      
      ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      ---------------
      
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - filter((TO_DATE('10/18/11','MM/DD/YY')<=TO_DATE('02/17/12','MM/DD/YY') AND :N3=:N1 AND :N2=:N1 AND :N2=:N3))
         5 - access("AH"."W_STS_ID"="WS"."W_STS_ID")
         6 - access("TH"."C_ID"="WHR"."C_ID" AND "AH"."A_HIT_ID"="A_HIT_ID")
         7 - filter(("TH"."STS"='D' OR "TH"."STS"='T'))
         9 - access("AH"."PARTITION_KEY"="H"."PARTITION_KEY" AND "AH"."A_HIT_ID"="H"."HIT_ID")
        10 - access("WHR"."PARTITION_KEY"="H"."PARTITION_KEY" AND "WHR"."HIT_ID"="H"."HIT_ID")
        13 - access("H"."PARTITION_KEY"=:N1 AND "H"."PRT_DT">=TO_DATE('10/18/11','MM/DD/YY') AND "H"."PRT_DT"<=TO_DATE('02/17/12','MM/DD/YY'))
             filter(("H"."PARTITION_KEY"=:N2 AND "H"."PARTITION_KEY"=:N3))
        15 - filter(("WHR"."PARTITION_KEY"=:N3 AND "WHR"."MBR_ID"=:N4 AND "WHR"."PARTITION_KEY"=:N2 AND "WHR"."PARTITION_KEY"=:N1))
        17 - filter(("AH"."PARTITION_KEY"=:N1 AND "AH"."PARTITION_KEY"=:N2 AND "AH"."PARTITION_KEY"=:N3 AND "AH"."W_STS_T"=1))
        22 - access("A_HIT_ID"="AH"."A_HIT_ID")
        23 - access("HH"."H_RID"="HR"."H_RID")
        24 - filter("HR"."C_ID"="WHR"."C_ID")
        26 - access("H"."S_FILE_ID"="SF"."S_FILE_ID")
        27 - filter("SF"."LOADED_FL"='Y')
      Below is the Real Time monitoring report of the sql when it was executing:

      First snapshot:
      -------------------------------------------------
      | Elapsed |   Cpu   |    IO    | Buffer | Reads |
      | Time(s) | Time(s) | Waits(s) |  Gets  |       |
      -------------------------------------------------
      |     164 |    0.74 |      163 |  45670 | 14621 |
      -------------------------------------------------
      
      
      SQL Plan Monitoring Details
      =============================================================================================================================================================================
      | Id    |                   Operation                   |      Name       |  Rows   | Cost  |   Time    | Start  | Starts |   Rows   | Memory | Activity  | Activity Detail |
      |       |                                               |                 | (Estim) |       | Active(s) | Active |        | (Actual) |        | (percent) |   (sample #)    |
      =============================================================================================================================================================================
      |     0 | SELECT STATEMENT                              |                 |         | 27760 |           |        |      1 |          |        |           |                 |
      |     1 |   HASH GROUP BY                               |                 |    3886 | 27760 |           |        |      1 |          |        |           |                 |
      |     2 |    FILTER                                     |                 |         |       |           |        |      1 |          |        |           |                 |
      |     3 |     NESTED LOOPS                              |                 |         |       |           |        |      1 |          |        |           |                 |
      |     4 |      NESTED LOOPS                             |                 |    3886 | 27759 |           |        |      1 |          |        |           |                 |
      |     5 |       HASH JOIN OUTER                         |                 |    3882 | 19992 |           |        |      1 |          |   492K |           |                 |
      |     6 |        HASH JOIN RIGHT ANTI                   |                 |    3882 | 19932 |         1 |     +9 |      1 |        0 |  1215K |           |                 |
      |     7 |         TABLE ACCESS FULL                     | T_HIT           |   15750 |    26 |         1 |     +9 |      1 |    16808 |        |           |                 |
      |     8 |         NESTED LOOPS ANTI                     |                 |    3892 | 19906 |           |        |      1 |          |        |           |                 |
      |     9 |          HASH JOIN                            |                 |    4072 |  3611 |           |        |      1 |          |   543K |           |                 |
      | -> 10 |           HASH JOIN                           |                 |    4072 |  2076 |       157 |     +9 |      1 |        0 |  2593K |           |                 |
      | -> 11 |            PARTITION RANGE SINGLE             |                 |    4072 |    40 |       157 |     +9 |      1 |    23047 |        |           |                 |
      | -> 12 |             TABLE ACCESS BY LOCAL INDEX ROWID | HIT             |    4072 |    40 |       157 |     +9 |      1 |    23047 |        |           |                 |
      | -> 13 |              INDEX RANGE SCAN                 | HIT_IE7         |       1 |    21 |       157 |     +9 |      1 |    23047 |        |           |                 |
      |    14 |            PARTITION RANGE SINGLE             |                 |    338K |  2033 |           |        |        |          |        |           |                 |
      |    15 |             TABLE ACCESS FULL                 | W_HIT_P         |    338K |  2033 |           |        |        |          |        |           |                 |
      |    16 |           PARTITION RANGE SINGLE              |                 |    398K |  1533 |           |        |        |          |        |           |                 |
      |    17 |            TABLE ACCESS FULL                  | A_HIT           |    398K |  1533 |           |        |        |          |        |           |                 |
      |    18 |          VIEW PUSHED PREDICATE                | VW_SQ_1         |       1 |     4 |           |        |        |          |        |           |                 |
      |    19 |           NESTED LOOPS                        |                 |         |       |           |        |        |          |        |           |                 |
      |    20 |            NESTED LOOPS                       |                 |       1 |     4 |           |        |        |          |        |           |                 |
      |    21 |             TABLE ACCESS BY INDEX ROWID       | H_HITS          |       1 |     3 |           |        |        |          |        |           |                 |
      |    22 |              INDEX RANGE SCAN                 | H_HITS_IF1      |       1 |     1 |           |        |        |          |        |           |                 |
      |    23 |             INDEX UNIQUE SCAN                 | H_RULE_PK       |       1 |     0 |           |        |        |          |        |           |                 |
      |    24 |            TABLE ACCESS BY INDEX ROWID        | H_RULE          |       1 |     1 |           |        |        |          |        |           |                 |
      |    25 |        TABLE ACCESS FULL                      | W_STATUS        |   36020 |    59 |           |        |        |          |        |           |                 |
      |    26 |       INDEX UNIQUE SCAN                       | S_FILE_PK       |       1 |     1 |           |        |        |          |        |           |                 |
      |    27 |      TABLE ACCESS BY INDEX ROWID              | S_FILE          |       1 |     2 |           |        |        |          |        |           |                 |
      =============================================================================================================================================================================
      Second snaphot:
      -------------------------------------------------
      | Elapsed |   Cpu   |    IO    | Buffer | Reads |
      | Time(s) | Time(s) | Waits(s) |  Gets  |       |
      -------------------------------------------------
      |     188 |    1.50 |      186 |  66937 | 25050 |
      -------------------------------------------------
      
      
      SQL Plan Monitoring Details
      =============================================================================================================================================================================
      | Id    |                   Operation                   |      Name       |  Rows   | Cost  |   Time    | Start  | Starts |   Rows   | Memory | Activity  | Activity Detail |
      |       |                                               |                 | (Estim) |       | Active(s) | Active |        | (Actual) |        | (percent) |   (sample #)    |
      =============================================================================================================================================================================
      |     0 | SELECT STATEMENT                              |                 |         | 27760 |           |        |      1 |          |        |           |                 |
      |     1 |   HASH GROUP BY                               |                 |    3886 | 27760 |           |        |      1 |          |        |           |                 |
      |     2 |    FILTER                                     |                 |         |       |           |        |      1 |          |        |           |                 |
      |     3 |     NESTED LOOPS                              |                 |         |       |           |        |      1 |          |        |           |                 |
      |     4 |      NESTED LOOPS                             |                 |    3886 | 27759 |           |        |      1 |          |        |           |                 |
      |  -> 5 |       HASH JOIN OUTER                         |                 |    3882 | 19992 |         7 |   +181 |      1 |        0 |  2842K |           |                 |
      |  -> 6 |        HASH JOIN RIGHT ANTI                   |                 |    3882 | 19932 |       179 |     +9 |      1 |    17793 |  2018K |           |                 |
      |     7 |         TABLE ACCESS FULL                     | T_HIT           |   15750 |    26 |         1 |     +9 |      1 |    16808 |        |           |                 |
      |  -> 8 |         NESTED LOOPS ANTI                     |                 |    3892 | 19906 |         7 |   +181 |      1 |    17793 |        |           |                 |
      |  -> 9 |          HASH JOIN                            |                 |    4072 |  3611 |        19 |   +169 |      1 |    17793 |  3429K |           |                 |
      |    10 |           HASH JOIN                           |                 |    4072 |  2076 |       173 |     +9 |      1 |    23461 |        |           |                 |
      |    11 |            PARTITION RANGE SINGLE             |                 |    4072 |    40 |       161 |     +9 |      1 |    23461 |        |           |                 |
      |    12 |             TABLE ACCESS BY LOCAL INDEX ROWID | HIT             |    4072 |    40 |       161 |     +9 |      1 |    23461 |        |           |                 |
      |    13 |              INDEX RANGE SCAN                 | HIT_IE7         |       1 |    21 |       161 |     +9 |      1 |    23461 |        |           |                 |
      |    14 |            PARTITION RANGE SINGLE             |                 |    338K |  2033 |        13 |   +169 |      1 |     333K |        |           |                 |
      |    15 |             TABLE ACCESS FULL                 | W_HIT_P         |    338K |  2033 |        13 |   +169 |      1 |     333K |        |           |                 |
      | -> 16 |           PARTITION RANGE SINGLE              |                 |    398K |  1533 |         7 |   +181 |      1 |     250K |        |           |                 |
      | -> 17 |            TABLE ACCESS FULL                  | A_HIT           |    398K |  1533 |         7 |   +181 |      1 |     250K |        |           |                 |
      |    18 |          VIEW PUSHED PREDICATE                | VW_SQ_1         |       1 |     4 |           |        |  17793 |          |        |           |                 |
      |    19 |           NESTED LOOPS                        |                 |         |       |           |        |  17793 |          |        |           |                 |
      |    20 |            NESTED LOOPS                       |                 |       1 |     4 |           |        |  17793 |          |        |           |                 |
      |    21 |             TABLE ACCESS BY INDEX ROWID       | H_HITS          |       1 |     3 |           |        |  17793 |          |        |           |                 |
      |    22 |              INDEX RANGE SCAN                 | H_HITS_IF1      |       1 |     1 |           |        |  17793 |          |        |           |                 |
      |    23 |             INDEX UNIQUE SCAN                 | H_RULE_PK       |       1 |     0 |           |        |        |          |        |           |                 |
      |    24 |            TABLE ACCESS BY INDEX ROWID        | H_RULE          |       1 |     1 |           |        |        |          |        |           |                 |
      |    25 |        TABLE ACCESS FULL                      | W_STATUS        |   36020 |    59 |           |        |        |          |        |           |                 |
      |    26 |       INDEX UNIQUE SCAN                       | S_FILE_PK       |       1 |     1 |           |        |        |          |        |           |                 |
      |    27 |      TABLE ACCESS BY INDEX ROWID              | S_FILE          |       1 |     2 |           |        |        |          |        |           |                 |
      =============================================================================================================================================================================
      Last snapshot
      ---------------------------------------------------------
      | Elapsed |   Cpu   |    IO    | Fetch | Buffer | Reads |
      | Time(s) | Time(s) | Waits(s) | Calls |  Gets  |       |
      ---------------------------------------------------------
      |     192 |    1.73 |      191 |    96 |   140K | 26354 |
      ---------------------------------------------------------
      
      
      SQL Plan Monitoring Details
      ==========================================================================================================================================================================
      | Id |                   Operation                   |      Name       |  Rows   | Cost  |   Time    | Start  | Starts |   Rows   | Memory | Activity  | Activity Detail |
      |    |                                               |                 | (Estim) |       | Active(s) | Active |        | (Actual) | (Max)  | (percent) |   (sample #)    |
      ==========================================================================================================================================================================
      |  0 | SELECT STATEMENT                              |                 |         | 27760 |         5 |   +192 |      1 |     1417 |        |           |                 |
      |  1 |   HASH GROUP BY                               |                 |    3886 | 27760 |         5 |   +192 |      1 |     1417 |  1271K |           |                 |
      |  2 |    FILTER                                     |                 |         |       |         1 |   +192 |      1 |    23207 |        |           |                 |
      |  3 |     NESTED LOOPS                              |                 |         |       |         1 |   +192 |      1 |    23207 |        |           |                 |
      |  4 |      NESTED LOOPS                             |                 |    3886 | 27759 |         1 |   +192 |      1 |    23207 |        |           |                 |
      |  5 |       HASH JOIN OUTER                         |                 |    3882 | 19992 |        12 |   +181 |      1 |    23207 |  4408K |           |                 |
      |  6 |        HASH JOIN RIGHT ANTI                   |                 |    3882 | 19932 |       184 |     +9 |      1 |    23207 |  2018K |           |                 |
      |  7 |         TABLE ACCESS FULL                     | T_HIT           |   15750 |    26 |         1 |     +9 |      1 |    16808 |        |           |                 |
      |  8 |         NESTED LOOPS ANTI                     |                 |    3892 | 19906 |        12 |   +181 |      1 |    23207 |        |           |                 |
      |  9 |          HASH JOIN                            |                 |    4072 |  3611 |        24 |   +169 |      1 |    23207 |  3429K |           |                 |
      | 10 |           HASH JOIN                           |                 |    4072 |  2076 |       173 |     +9 |      1 |    23461 |  3388K |           |                 |
      | 11 |            PARTITION RANGE SINGLE             |                 |    4072 |    40 |       161 |     +9 |      1 |    23461 |        |           |                 |
      | 12 |             TABLE ACCESS BY LOCAL INDEX ROWID | HIT             |    4072 |    40 |       161 |     +9 |      1 |    23461 |        |           |                 |
      | 13 |              INDEX RANGE SCAN                 | HIT_IE7         |       1 |    21 |       161 |     +9 |      1 |    23461 |        |           |                 |
      | 14 |            PARTITION RANGE SINGLE             |                 |    338K |  2033 |        13 |   +169 |      1 |     333K |        |           |                 |
      | 15 |             TABLE ACCESS FULL                 | W_HIT_P         |    338K |  2033 |        13 |   +169 |      1 |     333K |        |           |                 |
      | 16 |           PARTITION RANGE SINGLE              |                 |    398K |  1533 |        12 |   +181 |      1 |     324K |        |           |                 |
      | 17 |            TABLE ACCESS FULL                  | A_HIT           |    398K |  1533 |        12 |   +181 |      1 |     324K |        |           |                 |
      | 18 |          VIEW PUSHED PREDICATE                | VW_SQ_1         |       1 |     4 |           |        |  23207 |          |        |           |                 |
      | 19 |           NESTED LOOPS                        |                 |         |       |           |        |  23207 |          |        |           |                 |
      | 20 |            NESTED LOOPS                       |                 |       1 |     4 |           |        |  23207 |          |        |           |                 |
      | 21 |             TABLE ACCESS BY INDEX ROWID       | H_HITS          |       1 |     3 |           |        |  23207 |          |        |           |                 |
      | 22 |              INDEX RANGE SCAN                 | H_HITS_IF1      |       1 |     1 |           |        |  23207 |          |        |           |                 |
      | 23 |             INDEX UNIQUE SCAN                 | H_RULE_PK       |       1 |     0 |           |        |        |          |        |           |                 |
      | 24 |            TABLE ACCESS BY INDEX ROWID        | H_RULE          |       1 |     1 |           |        |        |          |        |           |                 |
      | 25 |        TABLE ACCESS FULL                      | W_STATUS        |   36020 |    59 |         1 |   +192 |      1 |    35329 |        |           |                 |
      | 26 |       INDEX UNIQUE SCAN                       | S_FILE_PK       |       1 |     1 |         5 |   +192 |  23207 |    23207 |        |           |                 |
      | 27 |      TABLE ACCESS BY INDEX ROWID              | S_FILE          |       1 |     2 |         1 |   +192 |  23207 |    23207 |        |           |                 |
      ==========================================================================================================================================================================
      HIT_IE7 index is created on three column of HIT table (PRT_DT,PARTITION_KEY,GROUP_ID).

      Would be helpful if anyone can provide some clues for tuning this sql.
        • 1. Re: Wrong estimation of sql on partitioned table
          damorgan
          Where's that cardinality hint you referenced?

          What is the point of this?
          and 14 = 14
          • 2. Re: Wrong estimation of sql on partitioned table
            Yasu
            Sorry i have not mentioned any details of cardinality hint execution plan since post was lengthy and was necessary as there was no change in plan.

            where 14=14 has been mentioned to capture the sql when executed from Application.
            • 3. Re: Wrong estimation of sql on partitioned table
              damorgan
              Without seeing the cardinality hint ... the fact that you stuck one in there that didn't help doesn't give anyone trying to help you any information upon which to base what we might write.

              Two other items.

              1. Make sure stats collection is current and corresponds with what is actually in the table. That means verify what you see in DBA_TABLES, DBA_INDEXES, and DBA_TAB_COLS.
              2. Drop 14=14 and label your SQL as follows:
              SELECT /* this is my query to pull end-user query quality information */ column_name
              FROM dba_tab_cols
              WHERE table_name = 'DUAL';
              • 4. Re: Wrong estimation of sql on partitioned table
                Nikolay Savvinov
                Hi,

                how can you expect the optimizer to correctly estimate the number of rows, when your predicate is like this:
                inner join              hit h on ah.a_hit_ID=h.HIT_ID
                and                     ah.partition_key = h.partition_key
                and                     ah.partition_key = :N1
                and                     h.partition_key = :N2
                inner join              w_hit_p whr on whr.PARTITION_KEY=h.PARTITION_KEY
                and                     whr.partition_key = :N3
                and                     whr.HIT_ID=h.HIT_ID
                i.e. using transitive closure:
                h.partition_key = :N1 and h.partition_key = :N2 and h.partition_key = :N3
                The optimizer has no way of knowing :N1, :N2 and :N3, and it has no reason to guess they are same (and if they aren't, then you won't get any rows).

                Best regards,
                Nikolay
                • 5. Re: Wrong estimation of sql on partitioned table
                  Mohamed Houri
                  Hi Yasser,

                  Your query is taking 192 seconds returning 1417 rows. The first operation triggered by the CBO in this case is
                  ---------------------------------------------------------------------------------------------------------------------------------------------------------------
                  | Id  | Operation                                   | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   |
                  ---------------------------------------------------------------------------------------------------------------------------------------------------------------
                  |  12 |            TABLE ACCESS BY LOCAL INDEX ROWID| HIT             |      1 |   4072 |   131K|    40   (0)| 00:00:01 |   KEY |   KEY |  23461 |00:03:15.89 |
                  |* 13 |             INDEX RANGE SCAN                | HIT_IE7         |      1 |      1 |       |    21   (0)| 00:00:01 |   KEY |   KEY |  23461 |00:00:00.05 |
                  with the corresponding predicate part on the index HIT_IE7
                   13 - access("H"."PARTITION_KEY"=:N1 AND "H"."PRT_DT">=TO_DATE('10/18/11','MM/DD/YY') AND "H"."PRT_DT"<=TO_DATE('02/17/12','MM/DD/YY'))
                         filter(("H"."PARTITION_KEY"=:N2 AND "H"."PARTITION_KEY"=:N3))
                  What can you spot from this information?

                  You can spot that the sole access to the table HIT via Local index rowid is consuming almost all the time required by the total query execution time. If something has to be tuned it will certainly be at this step of the query.
                  In addition the predicate part is showing both an access and a filter operation on the index HIT_IE7. This is a clear symptom of non precise index. You said that the index HIT_IE7 is defined as follows (PRT_DT,PARTITION_KEY,GROUP_ID) is this index locally partitioned? if so is this index prefixed or non-prefixed. it means is the PRT_DT the first column of the index?

                  Is it worth accessing 23461 records via an index range scan instead of a FULL TABLE SCAN ? Did you spot that for operation 12 the CBO is estimating a number of records to be generated of 4072which is far away from the Actual generated number of records (23461).
                  As it has been mentioned by Nicolay, the thing that the CBO is not aware about the value of the bind variables :N1, :N2 and :N3 applied against the same partition_key column, might conduct the CBO to forsee a different plan in contrast to the case where litteral are used to check against the partition key.

                  What will you got if you hint the query to use a FULL TABLE SCAN on HIT table /*+ full(hit) */

                  Another remark in passing. Your query looks like
                  inner join              hit h on ah.a_hit_ID=h.HIT_ID
                  and                     ah.partition_key = h.partition_key
                  and                     ah.partition_key = :N1
                  and                     h.partition_key = :N2
                  inner join              w_hit_p whr on whr.PARTITION_KEY=h.PARTITION_KEY
                  and                     whr.partition_key = :N3
                  and                     whr.HIT_ID=h.HIT_ID
                  and                     whr.MBR_ID= :N4
                  left outer join W_STATUS ws on ah.ID = ws.W_STS_ID
                  inner join              s_file sf on h.s_file_id=sf.s_file_id
                  If I was in your place I would have write this query as follows
                  inner join              hit h on ah.a_hit_ID=h.HIT_ID
                  and                     ah.partition_key = h.partition_key
                  and                     ah.partition_key = :N1  -- I would have questioned this clause
                  and                     h.partition_key = :N2
                  inner join              w_hit_p whr on whr.PARTITION_KEY=h.PARTITION_KEY
                  and                     whr.partition_key = :N3  -- I would have questioned this clause
                  and                     whr.HIT_ID=h.HIT_ID
                  and                     whr.MBR_ID= :N4
                  inner join              s_file sf on h.s_file_id=sf.s_file_id
                  left outer join W_STATUS ws on ah.ID = ws.W_STS_ID
                  And I would have delayed the left outer join after all inner joins because sometimes this style of ANSI join writing could introduce bugs

                  http://jonathanlewis.wordpress.com/?s=ANSI+JOIN

                  Best Regards

                  Mohamed Houri
                  www.hourim.wordpress.com
                  • 6. Re: Wrong estimation of sql on partitioned table
                    Yasu
                    After removing "where 14=14" and commenting out the unwanted predicates, ran below sql to find out the "Subquery Block Name" by using +alias for dbms_xplan.display_cursor so that i can use it while providing cardinality hint.
                    select  /*+  cardinality(SEL$4061C6BF 23461) gather_plan_statistics */ 
                    ....
                    ......
                    --and                   ah.partition_key = :N1 ===>Commented predicate
                    --and                   whr.partition_key = :N3 ===>Commented predicate
                    Query Block Name / Object Alias (identified by operation id):
                    -------------------------------------------------------------
                    
                       1 - SEL$4061C6BF
                      11 - SEL$4061C6BF / H@SEL$1
                      12 - SEL$4061C6BF / H@SEL$1
                      14 - SEL$4061C6BF / WHR@SEL$2
                      16 - SEL$4061C6BF / AH@SEL$1
                      17 - SEL$E6128FB6 / VW_SQ_1@SEL$801ADB77
                      18 - SEL$E6128FB6
                      20 - SEL$E6128FB6 / HH@SEL$7
                      21 - SEL$E6128FB6 / HH@SEL$7
                      22 - SEL$E6128FB6 / HR@SEL$7
                      23 - SEL$E6128FB6 / HR@SEL$7
                      24 - SEL$4061C6BF / TH@SEL$8
                      25 - SEL$4061C6BF / WS@SEL$3
                      26 - SEL$4061C6BF / SF@SEL$5
                      27 - SEL$4061C6BF / SF@SEL$5
                    Below is the execution plan, where cardinality hint has not impacted. Also this occurence execution time has reduced because of absence in Physical I/O(Checked in Real Time Sql monitoring report where Reads are zero). But our requirement is to reduce the execution time for the first time of execution with new bind variable values. If i execute this same sql after some days it takes the same amount of time ~200 seconds.
                    Plan hash value: 962074250
                    
                    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    ---------------
                    
                    | Id  | Operation                                   | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1M
                    em | Used-Mem |
                    
                    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    ---------------
                    
                    |   0 | SELECT STATEMENT                            |                 |      1 |        |       | 13754 (100)|          |       |       |   1417 |00:00:00.84 |     139K|      1 |       |
                       |          |
                    
                    |   1 |  HASH GROUP BY                              |                 |      1 |   1331 |   191K| 13754   (1)| 00:02:46 |       |       |   1417 |00:00:00.84 |     139K|      1 |   771K|   7
                    71K| 1265K (0)|
                    
                    |*  2 |   FILTER                                    |                 |      1 |        |       |            |          |       |       |  23207 |00:00:00.67 |     139K|      1 |       |
                       |          |
                    
                    |   3 |    NESTED LOOPS                             |                 |      1 |        |       |            |          |       |       |  23207 |00:00:00.67 |     139K|      1 |       |
                       |          |
                    
                    |   4 |     NESTED LOOPS                            |                 |      1 |   1331 |   191K| 13753   (1)| 00:02:46 |       |       |  23207 |00:00:00.67 |     115K|      1 |       |
                       |          |
                    
                    |*  5 |      HASH JOIN OUTER                        |                 |      1 |   1330 |   176K| 11092   (1)| 00:02:14 |       |       |  23207 |00:00:00.67 |   69413 |      1 |  2541K|  10
                    99K| 3343K (0)|
                    
                    |*  6 |       HASH JOIN ANTI                        |                 |      1 |   1330 |   153K| 11033   (1)| 00:02:13 |       |       |  23207 |00:00:00.65 |   69001 |      1 |  2694K|  10
                    78K| 2966K (0)|
                    
                    |   7 |        NESTED LOOPS ANTI                    |                 |      1 |   1344 |   132K| 11007   (1)| 00:02:13 |       |       |  23207 |00:00:00.38 |   68830 |      1 |       |
                       |          |
                    
                    |*  8 |         HASH JOIN                           |                 |      1 |   1614 |   146K|  4548   (1)| 00:00:55 |       |       |  23207 |00:00:00.38 |   68613 |      1 |  2645K|  10
                    85K| 2959K (0)|
                    
                    |*  9 |          HASH JOIN                          |                 |      1 |   1614 |   105K|  3157   (1)| 00:00:38 |       |       |  23461 |00:00:00.17 |   58909 |      1 |  2147K|  11
                    85K| 3006K (0)|
                    
                    |  10 |           PARTITION RANGE SINGLE            |                 |      1 |   1614 | 54876 |  1314   (0)| 00:00:16 |   KEY |   KEY |  23461 |00:00:00.01 |   46810 |      1 |       |
                       |          |
                    
                    |  11 |            TABLE ACCESS BY LOCAL INDEX ROWID| HIT             |      1 |   1614 | 54876 |  1314   (0)| 00:00:16 |   KEY |   KEY |  23461 |00:00:00.01 |   46810 |      1 |       |
                       |          |
                    
                    |* 12 |             INDEX RANGE SCAN                | HIT_IE7         |      1 |     30 |       |    10   (0)| 00:00:01 |   KEY |   KEY |  23461 |00:00:00.01 |     104 |      0 |       |
                       |          |
                    
                    |  13 |           PARTITION RANGE SINGLE            |                 |      1 |    109K|  3519K|  1841   (2)| 00:00:23 |   KEY |   KEY |    333K|00:00:00.01 |   12099 |      0 |       |
                       |          |
                    
                    |* 14 |            TABLE ACCESS FULL                | W_HIT_P      |      1 |    109K|  3519K|  1841   (2)| 00:00:23 |   KEY |   KEY |    333K|00:00:00.01 |   12099 |      0 |       |
                       |          |
                    
                    |  15 |          PARTITION RANGE SINGLE             |                 |      1 |    128K|  3254K|  1390   (2)| 00:00:17 |   KEY |   KEY |    324K|00:00:00.01 |    9704 |      0 |       |
                       |          |
                    
                    |* 16 |           TABLE ACCESS FULL                 | A_HIT          |      1 |    128K|  3254K|  1390   (2)| 00:00:17 |   KEY |   KEY |    324K|00:00:00.01 |    9704 |      0 |       |
                       |          |
                    
                    |  17 |         VIEW PUSHED PREDICATE               | VW_SQ_1         |  23207 |      1 |     8 |     4   (0)| 00:00:01 |       |       |      0 |00:00:00.05 |     217 |      0 |       |
                       |          |
                    
                    |  18 |          NESTED LOOPS                       |                 |  23207 |        |       |            |          |       |       |      0 |00:00:00.05 |     217 |      0 |       |
                       |          |
                    
                    |  19 |           NESTED LOOPS                      |                 |  23207 |      1 |    26 |     4   (0)| 00:00:01 |       |       |      0 |00:00:00.05 |     217 |      0 |       |
                       |          |
                    
                    |  20 |            TABLE ACCESS BY INDEX ROWID      | H_HITS        |  23207 |      1 |    14 |     3   (0)| 00:00:01 |       |       |      0 |00:00:00.05 |     217 |      0 |       |
                       |          |
                    
                    |* 21 |             INDEX RANGE SCAN                | H_HITS_IF1 |  23207 |      1 |       |     1   (0)| 00:00:01 |       |       |      0 |00:00:00.02 |     217 |      0 |       |
                       |          |
                    
                    |* 22 |            INDEX UNIQUE SCAN                | H_RULE_PK    |      0 |      1 |       |     0   (0)|          |       |       |      0 |00:00:00.01 |       0 |      0 |       |
                       |          |
                    
                    |* 23 |           TABLE ACCESS BY INDEX ROWID       | H_RULE       |      0 |      1 |    12 |     1   (0)| 00:00:01 |       |       |      0 |00:00:00.01 |       0 |      0 |       |
                       |          |
                    
                    |* 24 |        TABLE ACCESS FULL                    | T_HIT     |      1 |  15750 |   261K|    26   (4)| 00:00:01 |       |       |  17045 |00:00:00.01 |     171 |      0 |       |
                       |          |
                    
                    |  25 |       TABLE ACCESS FULL                     | W_STATUS    |      1 |  36020 |   633K|    59   (2)| 00:00:01 |       |       |  35351 |00:00:00.01 |     412 |      0 |       |
                       |          |
                    
                    |* 26 |      INDEX UNIQUE SCAN                      | S_FILE_PK  |  23207 |      1 |       |     1   (0)| 00:00:01 |       |       |  23207 |00:00:00.10 |   46416 |      0 |       |
                       |          |
                    
                    |* 27 |     TABLE ACCESS BY INDEX ROWID             | S_FILE     |  23207 |      1 |    11 |     2   (0)| 00:00:01 |       |       |  23207 |00:00:00.05 |   23209 |      0 |       |
                       |          |
                    
                    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    ---------------
                    
                    
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                    
                       2 - filter(TO_DATE(:N5,'MM/DD/YY')<=TO_DATE(:N6,'MM/DD/YY'))
                       5 - access("AH"."W_STS_ID"="WS"."W_STS_ID")
                       6 - access("TH"."C_ID"="WHR"."C_ID" AND "AH"."A_HIT_ID"="A_HIT_ID")
                       8 - access("AH"."PARTITION_KEY"="H"."PARTITION_KEY" AND "AH"."A_HIT_ID"="H"."HIT_ID")
                       9 - access("WHR"."PARTITION_KEY"="H"."PARTITION_KEY" AND "WHR"."HIT_ID"="H"."HIT_ID")
                      12 - access("H"."PARTITION_KEY"=:N2 AND "H"."PRT_DT">=TO_DATE(:N5,'MM/DD/YY') AND "H"."PRT_DT"<=TO_DATE(:N6,'MM/DD/YY'))
                      14 - filter(("WHR"."MBR_ID"=:N4 AND "WHR"."PARTITION_KEY"=:N2))
                      16 - filter(("AH"."PARTITION_KEY"=:N2 AND "AH"."WAT_STS_INIT"=1))
                      21 - access("A_HIT_ID"="AH"."A_HIT_ID")
                      22 - access("HH"."H_RULE_ID"="HR"."H_RULE_ID")
                      23 - filter("HR"."C_ID"="WHR"."CO_ID")
                      24 - filter(("TH"."STS"='D' OR "TH"."STS"='T'))
                      26 - access("H"."S_FILE_ID"="SF"."S_FILE_ID")
                      27 - filter("SF"."LOADED_FL"='Y')
                    At line 12 estimation is still 30 where as in reality its 23461 and taking 90% of the execution time due to this step.
                    • 7. Re: Wrong estimation of sql on partitioned table
                      Nikolay Savvinov
                      Hi,

                      probably the optimizer peeked binds which provided very strong selectivity. You can lock in the desired plan manually: first, replace literals with typical values of parameters. Hopefully this should be enough for the optimizer to come up with a better plan. If not, use hints. Once you got a plan with acceptable performance, generate an outline for it and use it. An outline can be generated using "+outline" option in the dbms_xplan.display_cursor format. If you want to, instead of generating an outline or a profile you can simply paste it into the query, it should work.

                      Best regards,
                      Nikolay
                      • 8. Re: Wrong estimation of sql on partitioned table
                        Yasu
                        Thanks for providing such a detailed analysis on this issue.

                        I commented the unwanted predicates to avoid Transitive Closure and also rewrote the sql to avoid ANSI joins. But again its the same execution plan with some minor enhancement as shown below.
                        select      /*+ gather_plan_statistics */
                        ...
                        .......
                        where                   ah.ARUBA_HIT_ID=h.HIT_ID
                        and                     ah.partition_key = h.partition_key
                        and                     whr.PARTITION_KEY=h.PARTITION_KEY
                        and                     ah.WTCH_STS_ID = ws.WTCH_STS_ID(+)
                        and                     h.source_file_id=sf.source_file_id
                        ...
                        --and                     ah.partition_key = :N1
                        --and                     whr.partition_key = :N3
                        ....
                        Below is the execution plan.
                        Plan hash value: 962074250
                        
                        ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        ---------------
                        
                        | Id  | Operation                                   | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1M
                        em | Used-Mem |
                        
                        ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        ---------------
                        
                        |   0 | SELECT STATEMENT                            |                 |      1 |        |       | 13753 (100)|          |       |       |   1417 |00:00:00.91 |     139K|     23 |       |
                           |          |
                        
                        |   1 |  HASH GROUP BY                              |                 |      1 |   1331 |   191K| 13753   (1)| 00:02:46 |       |       |   1417 |00:00:00.91 |     139K|     23 |   771K|   7
                        71K| 1259K (0)|
                        
                        |*  2 |   FILTER                                    |                 |      1 |        |       |            |          |       |       |  23207 |00:00:00.74 |     139K|     23 |       |
                           |          |
                        
                        |   3 |    NESTED LOOPS                             |                 |      1 |        |       |            |          |       |       |  23207 |00:00:00.74 |     139K|     23 |       |
                           |          |
                        
                        |   4 |     NESTED LOOPS                            |                 |      1 |   1331 |   191K| 13752   (1)| 00:02:46 |       |       |  23207 |00:00:00.74 |     115K|     23 |       |
                           |          |
                        
                        |*  5 |      HASH JOIN OUTER                        |                 |      1 |   1330 |   176K| 11091   (1)| 00:02:14 |       |       |  23207 |00:00:00.74 |   69413 |     23 |  2541K|  10
                        99K| 3334K (0)|
                        
                        |*  6 |       HASH JOIN ANTI                        |                 |      1 |   1330 |   153K| 11032   (1)| 00:02:13 |       |       |  23207 |00:00:00.72 |   69001 |     23 |  2694K|  10
                        78K| 3001K (0)|
                        
                        |   7 |        NESTED LOOPS ANTI                    |                 |      1 |   1344 |   132K| 11006   (1)| 00:02:13 |       |       |  23207 |00:00:03.50 |   68830 |     23 |       |
                           |          |
                        
                        |*  8 |         HASH JOIN                           |                 |      1 |   1614 |   146K|  4547   (1)| 00:00:55 |       |       |  23207 |00:00:03.50 |   68613 |     23 |  2645K|  10
                        85K| 3016K (0)|
                        
                        |*  9 |          HASH JOIN                          |                 |      1 |   1614 |   105K|  3157   (1)| 00:00:38 |       |       |  23461 |00:00:00.24 |   58909 |     22 |  2147K|  11
                        85K| 2942K (0)|
                        
                        |  10 |           PARTITION RANGE SINGLE            |                 |      1 |   1614 | 54876 |  1314   (0)| 00:00:16 |   KEY |   KEY |  23461 |00:00:00.01 |   46810 |     22 |       |
                           |          |
                        
                        |  11 |            TABLE ACCESS BY LOCAL INDEX ROWID| HIT             |      1 |   1614 | 54876 |  1314   (0)| 00:00:16 |   KEY |   KEY |  23461 |00:00:00.01 |   46810 |     22 |       |
                           |          |
                        
                        |* 12 |             INDEX RANGE SCAN                | HIT_IE7         |      1 |     30 |       |    10   (0)| 00:00:01 |   KEY |   KEY |  23461 |00:00:00.01 |     104 |      0 |       |
                           |          |
                        
                        |  13 |           PARTITION RANGE SINGLE            |                 |      1 |    109K|  3519K|  1841   (2)| 00:00:23 |   KEY |   KEY |    333K|00:00:00.01 |   12099 |      0 |       |
                           |          |
                        
                        |* 14 |            TABLE ACCESS FULL                | W_HIT_P    |      1 |    109K|  3519K|  1841   (2)| 00:00:23 |   KEY |   KEY |    333K|00:00:00.01 |   12099 |      0 |       |
                           |          |
                        
                        |  15 |          PARTITION RANGE SINGLE             |                 |      1 |    128K|  3254K|  1389   (2)| 00:00:17 |   KEY |   KEY |    324K|00:00:00.01 |    9704 |      1 |       |
                           |          |
                        
                        |* 16 |           TABLE ACCESS FULL                 | A_HIT       |      1 |    128K|  3254K|  1389   (2)| 00:00:17 |   KEY |   KEY |    324K|00:00:00.01 |    9704 |      1 |       |
                           |          |
                        
                        |  17 |         VIEW PUSHED PREDICATE               | VW_SQ_1         |  23207 |      1 |     8 |     4   (0)| 00:00:01 |       |       |      0 |00:00:00.07 |     217 |      0 |       |
                           |          |
                        
                        |  18 |          NESTED LOOPS                       |                 |  23207 |        |       |            |          |       |       |      0 |00:00:00.07 |     217 |      0 |       |
                           |          |
                        
                        |  19 |           NESTED LOOPS                      |                 |  23207 |      1 |    26 |     4   (0)| 00:00:01 |       |       |      0 |00:00:00.05 |     217 |      0 |       |
                           |          |
                        
                        |  20 |            TABLE ACCESS BY INDEX ROWID      | H_HITS     |  23207 |      1 |    14 |     3   (0)| 00:00:01 |       |       |      0 |00:00:00.05 |     217 |      0 |       |
                           |          |
                        
                        |* 21 |             INDEX RANGE SCAN                | H_HITS_IF1 |  23207 |      1 |       |     1   (0)| 00:00:01 |       |       |      0 |00:00:00.05 |     217 |      0 |       |
                           |          |
                        
                        |* 22 |            INDEX UNIQUE SCAN                | H_RULE_PK    |      0 |      1 |       |     0   (0)|          |       |       |      0 |00:00:00.01 |       0 |      0 |       |
                           |          |
                        
                        |* 23 |           TABLE ACCESS BY INDEX ROWID       | H_RULE       |      0 |      1 |    12 |     1   (0)| 00:00:01 |       |       |      0 |00:00:00.01 |       0 |      0 |       |
                           |          |
                        
                        |* 24 |        TABLE ACCESS FULL                    | T_HIT     |      1 |  15750 |   261K|    26   (4)| 00:00:01 |       |       |  17045 |00:00:00.01 |     171 |      0 |       |
                           |          |
                        
                        |  25 |       TABLE ACCESS FULL                     | W_STATUS    |      1 |  36020 |   633K|    58   (0)| 00:00:01 |       |       |  35358 |00:00:00.01 |     412 |      0 |       |
                           |          |
                        
                        |* 26 |      INDEX UNIQUE SCAN                      | S_FILE_PK  |  23207 |      1 |       |     1   (0)| 00:00:01 |       |       |  23207 |00:00:00.10 |   46416 |      0 |       |
                           |          |
                        
                        |* 27 |     TABLE ACCESS BY INDEX ROWID             | S_FILE     |  23207 |      1 |    11 |     2   (0)| 00:00:01 |       |       |  23207 |00:00:00.01 |   23209 |      0 |       |
                           |          |
                        
                        ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        ---------------
                        
                        
                        Predicate Information (identified by operation id):
                        ---------------------------------------------------
                        
                           2 - filter(TO_DATE(:N5,'MM/DD/YY')<=TO_DATE(:N6,'MM/DD/YY'))
                           5 - access("AH"."W_STS_ID"="WS"."W_STS_ID")
                           6 - access("TH"."C_ID"="WHR"."C_ID" AND "A_HIT_ID"="A_HIT_ID")
                           8 - access("AH"."PARTITION_KEY"="H"."PARTITION_KEY" AND "A_HIT_ID"="H"."HIT_ID")
                           9 - access("WHR"."PARTITION_KEY"="H"."PARTITION_KEY" AND "WHR"."HIT_ID"="H"."HIT_ID")
                          12 - access("H"."PARTITION_KEY"=:N2 AND "H"."PRT_DT">=TO_DATE(:N5,'MM/DD/YY') AND "H"."PRT_DT"<=TO_DATE(:N6,'MM/DD/YY'))
                          14 - filter(("WHR"."MBR_ID"=:N4 AND "WHR"."PARTITION_KEY"=:N2))
                          16 - filter(("AH"."PARTITION_KEY"=:N2 AND "AH"."W_STS_T"=1))
                          21 - access("A_HIT_ID"="A_HIT_ID")
                          22 - access("HH"."H_RULE_ID"="HR"."H_RULE_ID")
                          23 - filter("HR"."C_ID"="WHR"."C_ID")
                          24 - filter(("TH"."STS"='D' OR "TH"."STS"='T'))
                          26 - access("H"."S_FILE_ID"="SF"."S_FILE_ID")
                          27 - filter("SF"."LOADED_FL"='Y')
                        Seems to be helpful but execution time is again same if i execute it when there are no blocks cached in buffer.

                        I tried using full table scan hint on hit table, but its taking too long time to execute.
                        You said that the index HIT_IE7 is defined as follows (PRT_DT,PARTITION_KEY,GROUP_ID) is this index locally partitioned?
                        
                        SQL> select INDEX_NAME,PARTITIONING_TYPE,LOCALITY from dba_part_indexes where INDEX_NAME='HIT_IE7';
                        
                        INDEX_NAME                     PARTITIONING_TYPE            LOCALITY
                        ------------------------------ ---------------------------- ------------------------
                        HIT_IE7                        RANGE                        LOCAL
                        Yes its locally partitioned index.
                        if so is this index prefixed or non-prefixed. it means is the PRT_DT the first column of the index?
                        INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
                        ------------------------------ ------------------------------ ---------------
                        HIT_AK1                        G_S_ID                              1
                        HIT_AK2                        PARTITION_KEY                                1
                                                       HIT_ID                                       2
                        HIT_AK3                        PARTITION_KEY                                1
                                                       S_FILE_ID                               2
                        HIT_IE1                        HIT_TP                                       1
                        HIT_IE2                        SELECTED_FL                                  1
                                                       G_ID                                     2
                                                       HIT_ID                                       3
                        HIT_IE3                        T_HIT_ID                                 1
                        HIT_IE4                        PRT_DT                                       1
                        HIT_IE5                        G_ID                                     1
                                                       PRT_DT                                       2
                        HIT_IE6                        GUID                                         1
                                                       QRY_NO                                       2
                        HIT_IE7                        PARTITION_KEY                                1
                                                       PRT_DT                                       2
                                                       G_ID                                     3
                        HIT_IF2                        G_ID                                     1
                        HIT_IF3                        I_ID                                       1
                        HIT_IF4                        STS_ID                                       1
                        HIT_IF5                        S_FILE_ID                               1
                        HIT_IF6                        L_FULL_TX_ID                            1
                        HIT_PK                         HIT_ID                                       1
                        HIT_IE7 index is prefixed.
                        And I would have delayed the left outer join after all inner joins because sometimes this style of ANSI join writing could introduce bugs
                        This was really a good catch and when i delayed left outer join after all inner joins it was giving the exact execution plan when i removed all ANSI joins. But again no improvement in sql execution time and also estimation of Range scan on index HIT_IE7 is worst.

                        I think i need to consider creating a new index, could you please guide me in creating such index which should solely helpful for this sql which involved in the predicate part of the query (HIT_ID, partition_key, source_file_id, PRT_DT) columns of HIT table.

                        Thanks
                        • 9. Re: Wrong estimation of sql on partitioned table
                          Yasu
                          Same execution plan even after replacing bind variables with literals. Also estimation on HIT_IE7 index is still way too far from actual rows.
                          ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                          --------------
                          
                          | Id  | Operation                                   | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Me
                          m | Used-Mem |
                          
                          ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                          --------------
                          
                          |   0 | SELECT STATEMENT                            |                |      1 |        |       |  7359 (100)|          |       |       |   1417 |00:00:01.75 |     139K|    160 |       |
                            |          |
                          
                          |   1 |  HASH GROUP BY                              |                |      1 |   1331 |   214K|  7359   (1)| 00:01:29 |       |       |   1417 |00:00:01.75 |     139K|    160 |   771K|   77
                          1K| 1258K (0)|
                          
                          |*  2 |   FILTER                                    |                |      1 |        |       |            |          |       |       |  23207 |00:00:01.58 |     139K|    160 |       |
                            |          |
                          
                          |   3 |    NESTED LOOPS                             |                |      1 |        |       |            |          |       |       |  23207 |00:00:01.56 |     139K|    160 |       |
                            |          |
                          
                          |   4 |     NESTED LOOPS                            |                |      1 |   1331 |   214K|  7358   (1)| 00:01:29 |       |       |  23207 |00:00:01.56 |     116K|    160 |       |
                            |          |
                          
                          |*  5 |      HASH JOIN OUTER                        |                |      1 |   1330 |   200K|  4697   (1)| 00:00:57 |       |       |  23207 |00:00:01.56 |   69605 |    160 |  2541K|  109
                          9K| 3304K (0)|
                          
                          |*  6 |       HASH JOIN ANTI                        |                |      1 |   1330 |   176K|  4638   (1)| 00:00:56 |       |       |  23207 |00:00:01.54 |   69191 |    160 |  2694K|  107
                          8K| 2953K (0)|
                          
                          |*  7 |        HASH JOIN ANTI                       |                |      1 |   1344 |   156K|  4612   (1)| 00:00:56 |       |       |  23207 |00:00:01.51 |   69020 |    160 |  2694K|  107
                          8K| 2909K (0)|
                          
                          |*  8 |         HASH JOIN                           |                |      1 |   1614 |   168K|  4547   (1)| 00:00:55 |       |       |  23207 |00:00:00.43 |   68613 |      8 |  2645K|  108
                          5K| 2937K (0)|
                          
                          |*  9 |          HASH JOIN                          |                |      1 |   1614 |   129K|  3157   (1)| 00:00:38 |       |       |  23461 |00:00:00.22 |   58909 |      8 |  2147K|  118
                          5K| 2964K (0)|
                          
                          |  10 |           PARTITION RANGE SINGLE            |                |      1 |   1614 | 74244 |  1314   (0)| 00:00:16 |    36 |    36 |  23461 |00:00:03.05 |   46810 |      8 |       |
                            |          |
                          
                          |  11 |            TABLE ACCESS BY LOCAL INDEX ROWID| HIT            |      1 |   1614 | 74244 |  1314   (0)| 00:00:16 |    36 |    36 |  23461 |00:00:03.05 |   46810 |      8 |       |
                            |          |
                          
                          |* 12 |             INDEX RANGE SCAN                | HIT_IE7        |      1 |     30 |       |    10   (0)| 00:00:01 |    36 |    36 |  23461 |00:00:00.01 |     104 |      0 |       |
                            |          |
                          
                          |  13 |           PARTITION RANGE SINGLE            |                |      1 |    109K|  3838K|  1841   (2)| 00:00:23 |    36 |    36 |    333K|00:00:00.01 |   12099 |      0 |       |
                            |          |
                          
                          |* 14 |            TABLE ACCESS FULL                | W_HIT_P   |      1 |    109K|  3838K|  1841   (2)| 00:00:23 |    36 |    36 |    333K|00:00:00.01 |   12099 |      0 |       |
                            |          |
                          
                          |  15 |          PARTITION RANGE SINGLE             |                |      1 |    128K|  3129K|  1389   (2)| 00:00:17 |    36 |    36 |    324K|00:00:00.01 |    9704 |      0 |       |
                            |          |
                          
                          |* 16 |           TABLE ACCESS FULL                 | A_HIT      |      1 |    128K|  3129K|  1389   (2)| 00:00:17 |    36 |    36 |    324K|00:00:00.01 |    9704 |      0 |       |
                            |          |
                          
                          |  17 |         VIEW                                | VW_SQ_1        |      1 |  35987 |   421K|    64   (2)| 00:00:01 |       |       |  39134 |00:00:00.29 |     407 |    152 |       |
                            |          |
                          
                          |* 18 |          HASH JOIN                          |                |      1 |  35987 |   913K|    64   (2)| 00:00:01 |       |       |  39134 |00:00:00.29 |     407 |    152 |  1155K|  115
                          5K| 1461K (0)|
                          
                          |  19 |           TABLE ACCESS FULL                 | H_RULE      |      1 |   7159 | 85908 |    27   (0)| 00:00:01 |       |       |   7270 |00:00:00.05 |     175 |     73 |       |
                            |          |
                          
                          |  20 |           TABLE ACCESS FULL                 | H_HITS    |      1 |  35987 |   492K|    36   (0)| 00:00:01 |       |       |  39134 |00:00:00.01 |     232 |     79 |       |
                            |          |
                          
                          |* 21 |        TABLE ACCESS FULL                    | T_HIT    |      1 |  15750 |   261K|    26   (4)| 00:00:01 |       |       |  17061 |00:00:00.01 |     171 |      0 |       |
                            |          |
                          
                          |  22 |       TABLE ACCESS FULL                     | W_STATUS   |      1 |  36020 |   633K|    58   (0)| 00:00:01 |       |       |  35358 |00:00:00.01 |     414 |      0 |       |
                            |          |
                          
                          |* 23 |      INDEX UNIQUE SCAN                      | S_FILE_PK |  23207 |      1 |       |     1   (0)| 00:00:01 |       |       |  23207 |00:00:00.10 |   46416 |      0 |       |
                            |          |
                          
                          |* 24 |     TABLE ACCESS BY INDEX ROWID             | S_FILE    |  23207 |      1 |    11 |     2   (0)| 00:00:01 |       |       |  23207 |00:00:00.01 |   23209 |      0 |       |
                            |          |
                          
                          ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                          --------------
                          
                          
                          Predicate Information (identified by operation id):
                          ---------------------------------------------------
                          
                             2 - filter(TO_DATE('10/18/11','MM/DD/YY')<=TO_DATE('02/17/12','MM/DD/YY'))
                             5 - access("AH"."W_STS_ID"="WS"."W_STS_ID")
                             6 - access("TH"."C_ID"="WHR"."C_ID" AND "A_HIT_ID"="_HIT_ID")
                             7 - access("A_HIT_ID"="A_HIT_ID" AND "ITEM_1"="WHR"."C_ID")
                             8 - access("AH"."PARTITION_KEY"="H"."PARTITION_KEY" AND "A_HIT_ID"="H"."HIT_ID")
                             9 - access("WHR"."PARTITION_KEY"="H"."PARTITION_KEY" AND "WHR"."HIT_ID"="H"."HIT_ID")
                            12 - access("H"."PARTITION_KEY"=15572 AND "H"."PRT_DT">=TO_DATE('10/18/11','MM/DD/YY') AND "H"."PRT_DT"<=TO_DATE('02/17/12','MM/DD/YY'))
                            14 - filter(("WHR"."MBR_ID"=10 AND "WHR"."PARTITION_KEY"=15572))
                            16 - filter(("AH"."PARTITION_KEY"=15572 AND "AH"."W_STS_T"=1))
                            18 - access("HH"."H_RULE_ID"="HR"."H_RULE_ID")
                            21 - filter(("TH"."STS"='D' OR "TH"."STS"='T'))
                            23 - access("H"."S_FILE_ID"="SF"."S_FILE_ID")
                            24 - filter("SF"."LOADED_FL"='Y')
                          Confused on one thing....from where "ITEM_1"="WHR"."C_ID" predecate has been generated when sql is not referring column ITEM_1 at all.
                          • 10. Re: Wrong estimation of sql on partitioned table
                            user1124933
                            When the CBO hits several parititions using bind variables as it is the case here (see PSTART=KEY and PSTOP=KEY)
                            ---------------------------------------------------------------------------------------------------------------------------------------------------------------
                            | Id  | Operation                                   | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   |
                            ---------------------------------------------------------------------------------------------------------------------------------------------------------------
                            |  12 |            TABLE ACCESS BY LOCAL INDEX ROWID| HIT             |      1 |   4072 |   131K|    40   (0)| 00:00:01 |   KEY |   KEY |  23461 |00:03:15.89 |
                            |* 13 |             INDEX RANGE SCAN                | HIT_IE7        |      1 |      30 |       |    21   (0)| 00:00:01 |   KEY |   KEY |  23461 |00:00:00.05 |
                            the CBO will use partition level statistics for its estimation. You need then to be sure to have up-to-date partition level statistics.

                            Have you noticed that since you have commented certain where clause there is only an access operation on your index instead of (access + filter)

                            Best regards

                            Mohamed Houri
                            www.hourim.wordpress.com
                            • 11. Re: Wrong estimation of sql on partitioned table
                              Yasu
                              But when using literals it touches only one partition 36. Is there a way to find what partitions are touched when using bind variables as PSTART and PSTOP with KEY value doesn't represent which partition ?
                              Have you noticed that since you have commented certain where clause there is only an access operation on your index instead of (access + filter)
                              Yes but it has no impact on performance. Which is why i think implementing new index may help here.

                              Before checking accurateness of partition-level statistics i would like to pre-check whether it will impact the execution plan or not. I tried using cardinality hint but it didn't help. Is there a way to help cardinality estimation ?

                              Thanks.
                              • 12. Re: Wrong estimation of sql on partitioned table
                                Mohamed Houri
                                I would suggest you to check the accuracy of your hit table (and HIE7 index) statistics simply by executing the following select statement
                                select 
                                       h.prt_dt
                                      ,h.partition_key
                                      ,h.hit-id
                                      ,h.opposition_dt       
                                       ,h.source_file_id
                                from   hit h
                                where  h.parition_key = :N1
                                and    h.prt_dt between :5 and :6
                                ;
                                Replace bind variables by litteral values you have already tested. And get the correponding execution plan including E-Rows and A-Rows

                                Best regards
                                Mohamed Houri
                                www.hourim.wordpress.com
                                • 13. Re: Wrong estimation of sql on partitioned table
                                  Yasu
                                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                  | Id  | Operation                           | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |
                                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                  |   0 | SELECT STATEMENT                    |         |      1 |        |       |  1314 (100)|          |       |       |  23461 |00:00:00.01 |   39168 |     89 |
                                  |*  1 |  FILTER                             |         |      1 |        |       |            |          |       |       |  23461 |00:00:00.01 |   39168 |     89 |
                                  |   2 |   PARTITION RANGE SINGLE            |         |      1 |   1614 | 54876 |  1314   (0)| 00:00:16 |    36 |    36 |  23461 |00:00:00.01 |   39168 |     89 |
                                  |   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| HIT     |      1 |   1614 | 54876 |  1314   (0)| 00:00:16 |    36 |    36 |  23461 |00:00:00.01 |   39168 |     89 |
                                  |*  4 |     INDEX RANGE SCAN                | HIT_IE7 |      1 |     30 |       |    10   (0)| 00:00:01 |    36 |    36 |  23461 |00:00:00.01 |     128 |     89 |
                                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                  
                                  Predicate Information (identified by operation id):
                                  ---------------------------------------------------
                                  
                                     1 - filter(TO_DATE('10/18/11','MM/DD/YY')<=TO_DATE('02/17/12','MM/DD/YY'))
                                     4 - access("PARTITION_KEY"=1557 AND "PRT_DT">=TO_DATE('10/18/11','MM/DD/YY') AND "PRT_DT"<=TO_DATE('02/17/12','MM/DD/YY'))
                                  Still estimation is wrong on HIT_IE7 index.
                                  • 14. Re: Wrong estimation of sql on partitioned table
                                    Yasu
                                    If i use dynamic sampling hint in sql at level 4 i get below execution plan.
                                    ---------------------------------------------------------------------------------------------------------------------------------------------------------
                                    | Id  | Operation                           | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
                                    ---------------------------------------------------------------------------------------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT                    |         |      1 |        |       | 21402 (100)|          |       |       |  23461 |00:00:00.01 |     545K|
                                    |*  1 |  FILTER                             |         |      1 |        |       |            |          |       |       |  23461 |00:00:00.01 |     545K|
                                    |   2 |   PARTITION RANGE SINGLE            |         |      1 |  28271 |   938K| 21402   (1)| 00:04:17 |    36 |    36 |  23461 |00:00:00.01 |     545K|
                                    |*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| HIT     |      1 |  28271 |   938K| 21402   (1)| 00:04:17 |    36 |    36 |  23461 |00:00:00.01 |     545K|
                                    |*  4 |     INDEX RANGE SCAN                | HIT_AK3 |      1 |    523 |       |    92   (0)| 00:00:02 |    36 |    36 |    333K|00:00:00.01 |    1109 |
                                    ---------------------------------------------------------------------------------------------------------------------------------------------------------
                                    
                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------
                                    
                                       1 - filter(TO_DATE('10/18/11','MM/DD/YY')<=TO_DATE('02/17/12','MM/DD/YY'))
                                       3 - filter(("PRT_DT">=TO_DATE('10/18/11','MM/DD/YY') AND "PRT_DT"<=TO_DATE('02/17/12','MM/DD/YY')))
                                       4 - access("PARTITION_KEY"=15572361)
                                    But buffers touched are very high compared to HIT_IE7 index range scan execution plan.

                                    Could you please help me in understanding why filter operation is applied at step 1 when its already been filtered at step 3 ?

                                    Edited by: Yasu on Oct 10, 2012 10:02 PM
                                    Included doubt on filter operation at step 1
                                    1 2 Previous Next