13 Replies Latest reply: Jan 27, 2013 7:52 PM by Hemant K Chitale RSS

    Index skip scan 11.2.0.2

    961833
      I have two identical database. (Same hardware,same workload,same schema,same sqls)
      select count(*), bean   from tab where status='ENABLED' and max_retry_count > 0 
      and retry_count < max_retry_count and time > sysdate - 3    and time < sysdate - 1/24    group by bean;
      Execution plan DB1
      -----------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
      -----------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                     |                      |       |       |   123 (100)|          |       |       |
      |   1 |  SORT GROUP BY                       |                      |     1 |    12 |   123   (1)| 00:00:01 |       |       |
      |*  2 |   FILTER                             |                      |       |       |            |          |       |       |
      |   3 |    PARTITION RANGE ALL               |                      |     1 |    12 |   122   (0)| 00:00:01 |     1 |    22 |
      |*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| TAB                  |     1 |    12 |   122   (0)| 00:00:01 |     1 |    22 |
      |*  5 |      INDEX SKIP SCAN                 | IND_TAB              |    59 |       |    82   (0)| 00:00:01 |     1 |    22 |
      -----------------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - filter(SYSDATE@!-3<SYSDATE@!-.0416666666666666666666666666666666666667)
         4 - filter((MAX_RETRY_COUNT>0 AND RETRY_COUNT<MAX_RETRY_COUNT))
         5 - access(STATUS='ENABLED' AND TIME>SYSDATE@!-3 AND
                    TIME<SYSDATE@!-.0416666666666666666666666666666666666667)
             filter((STATUS='ENABLED' AND TIME<SYSDATE@!-.0416666666666666666666666666666666666667 AND
                    TIME>SYSDATE@!-3))
      
      Index stats db1
      INDEX_NAME   COLUMN_NAME   COLUMN_POSITION   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
      --------------------------------------------------------- --------------- ---------- -
      IND_TAB      BEAN                1       1894          1308              1398
                   STATUS              2       1894          1308              1398
                   TIME                3       1894          1308              1398
      
      Index column stats db1
      COLUMN_NAME   NUM_DISTINCT  NUM_NULLS LAST_ANALYZED        HISTOGRAM               DENSITY AVG_COL_LEN
      ------------- ------------ ---------- -------------------- --------------- --------------- -----------
      STATUS                   5  147334927 25.Jan.13/05:09:18   FREQUENCY           .0003326680           2
      TIME                  1360  147334964 25.Jan.13/05:09:18   HEIGHT BALANCED     .0008352757           2
      
      Histogram on STATUS in db1
      Enter value for column_name: STATUS
      
      ENDPOINT_NUMBER FREQUENCY HEX_VAL                             CHR(TO_NUMBER(SUBS ENDPOINT_ACTUAL_VALUE
      --------------- --------- ----------------------------------- ------------------ --------------------------------------------------
                  102       102  44495341424C2C9E089E1D78C00000     DISABL
                 1106      1004  454E41424C45609F58C98BE0000000     ENABLE
                 1110         4  4552524F51FFE9FC0A43E132000000     ERROQ
                 1147        37  51554152414E62602989E3C0800000     QUARAN
                 1503       356  534944454C4935C988005088C00000     SIDELI
      
      Table stats in db1
      TABLE_NAME   PAR   NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZED,'D SAMPLE_SIZE
      ------------ --- ---------- ---------- ------------------------ -----------
      TAB          YES  147338902    2837814 25-jan-13:05:21:02         147338902
      Execution plan DB2
      ---------------------------------------------------------------------------------------------------------
      | Id  | Operation             | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
      ---------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT      |                 |       |       |   920K(100)|          |       |       |
      |   1 |  SORT GROUP BY        |                 |     1 |    12 |   920K  (3)| 01:01:24 |       |       |
      |*  2 |   FILTER              |                 |       |       |            |          |       |       |
      |   3 |    PARTITION RANGE ALL|                 |     1 |    12 |   920K  (3)| 01:01:24 |     1 |    22 |
      |*  4 |     TABLE ACCESS FULL | TAB             |     1 |    12 |   920K  (3)| 01:01:24 |     1 |    22 |
      ---------------------------------------------------------------------------------------------------------
      Predicate Information (identified by operation id):
      ---------------------------------------------------
         2 - filter(SYSDATE@!-3<SYSDATE@!-.0416666666666666666666666666666666666667)
         4 - filter((STATUS='ENABLED' AND TIME>SYSDATE@!-3 AND TIME<SYSDATE@!-.0416666666666666666666666666666666666667 AND
                    RETRY_COUNT<MAX_RETRY_COUNT AND MAX_RETRY_COUNT>0))
      
      Index stats db2
      INDEX_NAME  COLUMN_NAME  COLUMN_POSITION   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
      ----------- ------------ --------------- ---------- ------------- -----------------
      IND_TAB     BEAN               1       3469          1333              1659
                  STATUS             2       3469          1333              1659
                  TIME               3       3469          1333              1659
      
      Index col stats db2
      COLUMN_NAME     NUM_DISTINCT  NUM_NULLS LAST_ANALYZED      HISTOGRAM               DENSITY AVG_COL_LEN
      --------------- ------------ ---------- ------------------ --------------- --------------- -----------
      STATUS                     5  147194991 25.Jan.13/05:00:51 FREQUENCY           .0001097936           2
      TIME                    1330  147195016 25.Jan.13/05:00:51 HEIGHT BALANCED     .0009951184           2
      
      Histogram on STATUS in db2
      Enter value for column_name: STATUS
      
      ENDPOINT_NUMBER FREQUENCY HEX_VAL                             CHR(TO_NUMBER(SUBS ENDPOINT_ACTUAL_VALUE
      --------------- --------- ----------------------------------- ------------------ --------------------------------------------------
                   95        95  44495341424C2C9E089E1D78C00000     DISABL
                 4155      4060  454E41424C45609F58C98BE0000000     ENABLE
                 4181        26  4552524F51FFE9FC0A43E132000000     ERROQ
                 4206        25  51554152414E62602989E3C0800000     QUARAN
                 4554       348  534944454C4935C988005088C00000     SIDELI
      
      Table stats in db2
      TABLE_NAME  PAR   NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZED,'D SAMPLE_SIZE
      ----------- --- ---------- ---------- ------------------------ -----------
      TAB         YES  147197392    2800986 25-jan-13:05:13:40         147197392
      Can anyone help me understand what is causing FTS in DB2 ?

      Edited by: 958830 on Jan 24, 2013 11:55 PM

      Edited by: 958830 on Jan 24, 2013 11:57 PM
        • 1. Re: Index skip scan 11.2.0.2
          asahide
          (Same hardware,same workload,same schema,same sqls)
          How about STATISTICS data (on table and index) and initial parameter? Same?

          Regards,
          • 2. Re: Index skip scan 11.2.0.2
            dataseven
            hi,

            could you have different oracle version or optimizer_index_caching and optimizer_index_cost_adj parameter is different between databases?

            regards,

            Edited by: dataseven on 24.Oca.2013 23:41
            • 3. Re: Index skip scan 11.2.0.2
              Nikolay Savvinov
              Hi,

              1) check if the index exists on the second environment, and is in a usable state
              2) if it does, then do the following:
              - on the database where the query is performing well, generate explain plan with the outline option: explain plan for <query text>; select * from table(dbms_xplan.display(null, null, 'outline'));
              - copy the text of the outline, and paste it into the query on the other environment, placing it directly after the opening SELECT keyword
              - post the resulting plan here

              Best regards,
              Nikolay
              • 4. Re: Index skip scan 11.2.0.2
                961833
                I have listed statistical difference. Parameters are all same in both dbs.
                (_optimizer_skip_scan_enabled= true as well in both db)
                • 5. Re: Index skip scan 11.2.0.2
                  961833
                  1) check if the index exists on the second environment, and is in a usable state

                  Index exists and it is in usable status.

                  2) if it does, then do the following:
                  - on the database where the query is performing well, generate explain plan with the outline option: explain plan for <query text>; select * from table(dbms_xplan.display(null, null, 'outline'));
                  - copy the text of the outline, and paste it into the query on the other environment, placing it directly after the opening SELECT keyword
                  - post the resulting plan here

                  I have put profile in db2 (copied hints from db1) and with profile in place, CBO is using Index skip scan in db2. However, i would like to know the cause as i know such temporary fixes !
                  (I put below hint in sql-profile)
                  INDEX_SS(@SEL$1 TAB@SEL$1 (TAB.BEAN TAB.STATUS TAB.TIME))

                  Edited by: 958830 on Jan 24, 2013 11:50 PM
                  • 6. Re: Index skip scan 11.2.0.2
                    Hemant K Chitale
                    Generate an Event 10053 trace in each environment. The trace should tell you whether it evaluated the Index Access path in the second database and, if so, why it rejected this.


                    Hemant K Chitale
                    • 7. Re: Index skip scan 11.2.0.2
                      Nikolay Savvinov
                      Hi,

                      >

                      [...]
                      2) if it does, then do the following:
                      - on the database where the query is performing well, generate explain plan with the outline option: explain plan for <query text>; select * from table(dbms_xplan.display(null, null, 'outline'));
                      - copy the text of the outline, and paste it into the query on the other environment, placing it directly after the opening SELECT keyword
                      - post the resulting plan here

                      I have put profile in db2 (copied hints from db1) and with profile in place, CBO is using Index skip scan in db2. However, i would like to know the cause as i know such temporary fixes !
                      I've asked you to post the resulting plan, not the outline itself. I'm not suggesting you to use the outline as a fix, the idea was to use it to see the costs. I expect that the cost will be much higher on db2 than on db1 (otherwise the skip scan would have showed up naturally on db2), and that the exact figures will give us a hint as to why the costs are so different.

                      Best regards,
                      Nikolay
                      • 8. Re: Index skip scan 11.2.0.2
                        961833
                        db1
                        SELECT COUNT(*) FROM TAB WHERE STATUS='ENABLED';
                          COUNT(*)
                        ----------
                              2101
                        db2
                        SELECT COUNT(*) FROM TAB WHERE STATUS='ENABLED';
                          COUNT(*)
                        ----------
                              2331
                        
                        I believe there is some oddity in the way Oracle captures histograms.
                        In DB2 even though count is 2331, histograms is showing it almost double (4060).  However, i am bit reluctant to consider histograms as culprit here. I may be wrong though !
                        • 9. Re: Index skip scan 11.2.0.2
                          961833
                          -----------------------------------------------------------------------------------------------------------------------------
                          | Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
                          -----------------------------------------------------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT                     |                      |       |       |   239 (100)|          |       |       |
                          |   1 |  SORT GROUP BY                       |                      |     1 |    12 |   239   (1)| 00:00:01 |       |       |
                          |*  2 |   FILTER                             |                      |       |       |            |          |       |       |
                          |   3 |    PARTITION RANGE ALL               |                      |     1 |    12 |   238   (0)| 00:00:01 |     1 |    22 |
                          |*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| TAB                  |     1 |    12 |   238   (0)| 00:00:01 |     1 |    22 |
                          |*  5 |      INDEX SKIP SCAN                 | IND_TAB              |   143 |       |   116   (0)| 00:00:01 |     1 |    22 |
                          -----------------------------------------------------------------------------------------------------------------------------
                          Predicate Information (identified by operation id):
                          ---------------------------------------------------
                             2 - filter(SYSDATE@!-3<SYSDATE@!-.0416666666666666666666666666666666666667)
                             4 - filter(("RETRY_COUNT"<"MAX_RETRY_COUNT" AND "MAX_RETRY_COUNT">0))
                             5 - access("STATUS"='ENABLED' AND "TIME">SYSDATE@!-3 AND
                                        "TIME"<SYSDATE@!-.0416666666666666666666666666666666666667)
                                 filter(("STATUS"='ENABLED' AND "TIME">SYSDATE@!-3 AND
                                        "TIME"<SYSDATE@!-.0416666666666666666666666666666666666667))
                          Note
                          -----
                             - SQL profile PROFILE_2rkg00dy4uyq4 used for this statement
                          Edited by: 958830 on Jan 25, 2013 12:08 AM
                          • 10. Re: Index skip scan 11.2.0.2
                            Nikolay Savvinov
                            Hi,

                            it looks like instead of copy-pasting the outline as a hint, you created and applied a SQL profile -- that may have affected the cost estimates displayed in the plan. Otherwise, I cannot explain why the optimizer would knowingly pick a much more expensive plan.

                            Best regards,
                            Nikolay
                            • 11. Re: Index skip scan 11.2.0.2
                              Jonathan Lewis
                              958830 wrote:
                              I have two identical database. (Same hardware,same workload,same schema,same sqls)
                              The first step in understanding the problem is to make db1 follow the db2 plan, and vice versa. This may show you where the optimizer is going wrong in one system or the other.
                              In this case the SQL is so simple (apparently) that all it should take is a /*+ full(tab) */ hint in db1 and an /*+ index_ss(tab ind_tab) */ in db2. (Get rid of the profile first)

                              Having said that, I note that the statistics look pretty similar, and that the index looks fairly extreme in that it has only a couple of thousand entries on a table with 140M rows - all three columns are null for almost all rows - so from a human perspective it's almost certain that ANY way of using the index to get to the non-null table is bound to be more efficient that doing a full tablescan.

                              In DB2, the "worst case cost" of doing a skip scan should be no larger than "index leaf blocks + index num_rows" (i.e. total number of blocks you'd have to read from the index plus worst posisbly number of blocks you'd have to read from the table) - so clearly the optimizer has gone wrong and we have to figure out why.

                              The most significant thing I can see is that the stats on db2 are much further from being self-consistent. In particular your index has 3,469 non-null entries, but the histogram says that one of the columns (status) has 4,554 non-null entries. I can imagine two possibilities (a) this inconsistency found a bug in the Oracle code path, (b) there is a sanity check in the code path which caused Oracle to (e.g.) ignore the index because its statistics were not trust-worthy.

                              Update: it would be useful to see the column stats for the BEAN column.

                              Regards
                              Jonathan Lewis

                              Edited by: Jonathan Lewis on Jan 25, 2013 9:15 AM
                              • 12. Re: Index skip scan 11.2.0.2
                                961833
                                Nice catch on histogram oddity. I did not pay attention there. I learn something new today.
                                DB-1
                                COLUMN_NAME   NUM_DISTINCT  NUM_NULLS LAST_ANALYZED      HISTOGRAM               DENSITY AVG_COL_LEN
                                ------------- ------------ ---------- ------------------ --------------- --------------- -----------
                                BEAN            13            147334964 25.Jan.13/05:09:18 FREQUENCY           .0003401361           2
                                
                                db2
                                COLUMN_NAME   NUM_DISTINCT  NUM_NULLS LAST_ANALYZED      HISTOGRAM               DENSITY AVG_COL_LEN
                                ------------- ------------ ---------- ------------------ --------------- --------------- -----------
                                BEAN            11            147195016 25.Jan.13/05:00:51 FREQUENCY           .0001032418           2
                                Nightly dbms_stats kicked in and it seems new stats have restored original skip scan in db2.
                                • 13. Re: Index skip scan 11.2.0.2
                                  Hemant K Chitale
                                  Nightly dbms_stats kicked in and it seems new stats have restored original skip scan in db2
                                  That's the danger / risk with the auto jobs. Sampling may not be adequate for certain tables / columns. You could write your own gather_stats job specifically for this table so that you can control the sampling.


                                  Hemant K Chitale