This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Jan 3, 2013 3:19 PM by Jonathan Lewis Go to original post RSS
  • 15. Re: Differenet Explain Plan for Same Query
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    rp0428 wrote:
    >
    But My question is in index IX_RMESG For the first explain plan its used "INDEX SKIP SCAN" But in the second one "INDEX RANGE SCAN"
    >

    To me the key difference in the second query is
    |  14 |             BITMAP AND
    It is that 'BITMAP AND' that is driving the second query. And it seems to me that the reason for that is this
    |* 16 |               INDEX RANGE SCAN             | IX_SENDER         | 25070 |       |       |   894   (1)| 00:00:11 |     2 |     2 |
    It is the use of the IX_SENDER index that is causing the use of a range scan for the IX_REMSG index. Oracle may be doing the range scans of both indexes because that allows the indexes to be bitmap converted and 'ANDed' together to get the result.
    |  11 |          PARTITION RANGE SINGLE            |                   |   543 | 43983 |       |  2621   (1)| 00:00:32 |     2 |     2 |
    |* 12 |           TABLE ACCESS BY LOCAL INDEX ROWID| RMESG             |   543 | 43983 |       |  2621   (1)| 00:00:32 |     2 |     2 |
    |  13 |            BITMAP CONVERSION TO ROWIDS     |                   |       |       |       |            |          |       |       |
    |  14 |             BITMAP AND                     |                   |       |       |       |            |          |       |       |
    |  15 |              BITMAP CONVERSION FROM ROWIDS |                   |       |       |       |            |          |       |       |
    |* 16 |               INDEX RANGE SCAN             | IX_SENDER         | 25070 |       |       |   894   (1)| 00:00:11 |     2 |     2 |
    |  17 |              BITMAP CONVERSION FROM ROWIDS |                   |       |       |       |            |          |       |       |
    |  18 |               SORT ORDER BY                |                   |       |       |   408K|            |          |       |       |
    |* 19 |                INDEX RANGE SCAN            | IX_RMESG          | 25070 |       |       |  1405   (1)| 00:00:17 |     2 |     2 |
    I think you're in the right area, but have the argument for the BITMAP AND back to front.
    The index IX_RMESG starts with (mesg_crea_date_time, mesg_type)
    In the first plan we have a skip scan, predicting 451 index entries, with the predicates
      14 - access("RMESG"."MESG_CREA_DATE_TIME">=TO_DATE(' 2012-01-31 10:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
                  "RMESG"."MESG_TYPE"='950' AND "RMESG"."MESG_CREA_DATE_TIME"<=TO_DATE(' 2012-01-31 13:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    
           filter("RMESG"."MESG_TYPE"='950')
    In the second plan we have a range scan, predicting 25070 entries, with the predicates
      19 - access("RMESG"."MESG_CREA_DATE_TIME">=TO_DATE(' 2012-01-17 10:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
                  "RMESG"."MESG_TYPE"='548' AND "RMESG"."MESG_CREA_DATE_TIME"<=TO_DATE(' 2012-01-17 13:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    
           filter("RMESG"."MESG_TYPE"='548' AND "RMESG"."MESG_CREA_DATE_TIME"<=TO_DATE(' 2012-01-17 13:00:00', 'syyyy-mm-dd
                  hh24:mi:ss') AND "RMESG"."MESG_CREA_DATE_TIME">=TO_DATE(' 2012-01-17 10:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    First point - there are some errors in the stats in the first plan because 451 index entries lead to (line 13) 1,400 table rows - which is clearly wrong.

    Second point - the large number of predicted index entries in the second plan is probably why Oracle decided to bring in a second index and do a bitmap conversion to avoid excess visits to the table

    Third point - how do you get a skip scan on an index when you've supplied predicates on the first two columns ? "Everybody knows" that skip scans apply only when you haven't supplied a predicate for the first column of the index. ("Everybody" happens to be wrong.)


    My guess, based on the available plan and predicate information is that there aren't very many distinct values in the partition for mesg_crea_date_time, and the volume of data covered by the three hour ranges is very large; however mesg_type 950 is rare while mesg_type 548 is very common - so Oracle has probed the index a few times (emulating an inlist iterator on a small number of values for mesg_crea_date_time) to pick up with high precision the rowids for the 950, but it has decided that so much of the range is 548 that there's no point in trying to save resources by probing the index.

    I've got a script demonstrating the principle somewhere, and if I can't find it I'll create one, which I'll post on my blog tonight if I find the time.

    Regards
    Jonathan Lewis
  • 16. Re: Differenet Explain Plan for Same Query
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    thank you Jonathan, I will Monitor your Blog Today :)
  • 17. Re: Differenet Explain Plan for Same Query
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    CREATE INDEX SIDE.IX_NAME_FORMAT_TYPE_RMESG on RMESG(mesg_frmt_name,mesg_sub_format,mesg_type,mesg_crea_date_time ) LOCAL;
    Hi Jonathan

    indexes on a partitioned table, it is partitioned by range method i.e based on Creation date time and All select queries sent to the table use the Creation date time. I have an index on Creation date time. like the following
    CREATE INDEX ix_creationdate ON table1 (DATE_TIME) LOCAL;
    CREATE INDEX IX_CCY on table1(type,ccy) LOCAL;
    CREATE INDEX IX_NAME_FORMAT_TYPE on table1 (type,frmt_name,sub_format) LOCAL;
    do I have to add DATE_TIME to all indexes (IX_NAME_FORMAT_TYPE,IX_CCY) or not?

    That mean
    CREATE INDEX ix_creationdate ON table1 (DATE_TIME) LOCAL;
    CREATE INDEX IX_CCY on table1(type,ccy) LOCAL;
    CREATE INDEX IX_NAME_FORMAT_TYPE on table1 (type,frmt_name,sub_format) LOCAL;
    or should I include DATE_TIME within each index:
    CREATE INDEX ix_creationdate ON table1 (DATE_TIME) LOCAL;
    CREATE INDEX IX_CCY on table1(DATE_TIME,type,ccy) LOCAL;
    CREATE INDEX IX_NAME_FORMAT_TYPE on table1 (DATE_TIME,type,frmt_name,sub_format) LOCAL;
    Is this should make performance better
  • 18. Re: Differenet Explain Plan for Same Query
    rp0428 Guru
    Currently Being Moderated
    >
    thank you Jonathan, I will Monitor your Blog Today
    >
    But that is just being reactive. Did you notice these comments?
    >
    My guess, based on the available plan and predicate information is that there aren't very many distinct values in the partition for mesg_crea_date_time, and the volume of data covered by the three hour ranges is very large; however mesg_type 950 is rare while mesg_type 548 is very common - so Oracle has probed the index a few times (emulating an inlist iterator on a small number of values for mesg_crea_date_time) to pick up with high precision the rowids for the 950, but it has decided that so much of the range is 548 that there's no point in trying to save resources by probing the index.
    >
    Why not be proactive and provide the stats for the filter predicates?

    1. total rows
    2. total mesg_type 548
    3. total mesg_type 950
    4. total mesg_sender_x1 = 'SOGEFRPPXXX'
    5. total m.mesg_nature = 'FINANCIAL_MSG'
    6. total distinct mesg_crea_date_time
    7. volume of data covered by the two different three hour ranges
  • 19. Re: Differenet Explain Plan for Same Query
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Osama_mustafa wrote:
    thank you Jonathan, I will Monitor your Blog Today :)
    Now loaded: http://www.oaktable.net/content/skip-scan-2

    Regards
    Jonathan Lewis
1 2 Previous Next

Legend

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