This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Nov 19, 2012 10:41 AM by Richard Foote Go to original post RSS
  • 15. Re: Too much Logical IOs on a locally partition index !!
    871018 Explorer
    Currently Being Moderated
    First of all, I would recommend to use partition key in the query. It will allow to determine the partition whcih requires to read so many buffers.
    Probably all of them from P201208, this partition contains many deleted entries.

    It is possible to determine blocks which are read by the SQL and do dump some of them. In order to determine the blocks you can use trace 10200, or sql trace (10046) on flushed buffer cache.

    It is possible also just rebuild the index and try the query again. Most likely the problem will disappear.

    Alexander Anokhin
    http://alexanderanokhin.wordpress.com/
  • 16. Re: Too much Logical IOs on a locally partition index !!
    872581 Newbie
    Currently Being Moderated
    Thanks for your reply.

    I did the index tree dump, and the trace file has 107309 rows.

    The result of index tree dump is too much.

    However, as you appointed, I found a lot of "rrow:0" in the middle of the trace file.

    I think Index rebuild could solve this excessive logical IOs on the index.

    I just wonder why over 20000 block io is necessary in retriving "0" row on the index scan.

    I would like to know how oracle manage b*tree index structure when update has occurred.


    I greped the total trace output file with "rrow: 0".
    The total number of "rrow: 0" is 27156.

    -- *************

    Does this mean 27,156 index leafs block is empty because of update??

    And "proc_rslt=1" predicate scanned almost of "rrow:0" leaf blocks?

          branch: 0x74ba733 122398515 (311: nrow: 144, level: 1)
             leaf: 0x74ba702 122398466 (-1: nrow: 0 rrow: 0)
             leaf: 0x74ba706 122398470 (0: nrow: 85 rrow: 0)
             leaf: 0x74ba703 122398467 (1: nrow: 85 rrow: 0)
             leaf: 0x74ba707 122398471 (2: nrow: 87 rrow: 0)
             leaf: 0x74ba704 122398468 (3: nrow: 86 rrow: 0)
             leaf: 0x74ba708 122398472 (4: nrow: 88 rrow: 0)
             leaf: 0x74ba705 122398469 (5: nrow: 87 rrow: 0)
             leaf: 0x74ba709 122398473 (6: nrow: 88 rrow: 0)
             leaf: 0x74ba70a 122398474 (7: nrow: 88 rrow: 0)
             leaf: 0x74ba70b 122398475 (8: nrow: 88 rrow: 0)
             leaf: 0x74ba70c 122398476 (9: nrow: 87 rrow: 0)
             leaf: 0x74ba70d 122398477 (10: nrow: 87 rrow: 0)
             leaf: 0x74ba70e 122398478 (11: nrow: 88 rrow: 0)
             leaf: 0x74ba70f 122398479 (12: nrow: 88 rrow: 0)
             leaf: 0x74ba710 122398480 (13: nrow: 85 rrow: 0)
             leaf: 0x74ba711 122398481 (14: nrow: 82 rrow: 0)
             leaf: 0x74ba712 122398482 (15: nrow: 87 rrow: 0)
             leaf: 0x74ba713 122398483 (16: nrow: 88 rrow: 0)
             leaf: 0x74ba716 122398486 (17: nrow: 85 rrow: 0)
             leaf: 0x74ba714 122398484 (18: nrow: 88 rrow: 0)
             leaf: 0x74ba715 122398485 (19: nrow: 87 rrow: 0)
    Thanks in advance.
    Best Regards.

    Edited by: 869578 on 2012. 11. 18 오후 11:28
  • 17. Re: Too much Logical IOs on a locally partition index !!
    Richard Foote Employee ACE
    Currently Being Moderated
    869578 wrote:
    Thanks for your reply.

    I did the index tree dump, and the trace file has 107309 rows.

    The result of index tree dump is too much.

    However, as you appointed, I found a lot of "rrow:0" in the middle of the trace file.

    I think Index rebuild could solve this excessive logical IOs on the index.

    I just wonder why over 20000 block io is necessary in retriving "0" row on the index scan.

    I would like to know how oracle manage b*tree index structure when update has occurred.


    I greped the total trace output file with "rrow: 0".
    The total number of "rrow: 0" is 27156.

    -- *************

    Does this mean 27,156 index leafs block is empty because of update??

    And "proc_rslt=1" predicate scanned almost of "rrow:0" leaf blocks?

    When an index entry is "updated", basically a delete and insert operation is performed. This is to ensure the index entries always remain in order. So after updating all the entries with a value "1", they are all logically deleted and re-inserted elsewhere within the index structure. If these updates are all performed within a single transaction, the deleted index entries space can not re-claimed within the transaction and all these entries are simply marked as deleted.

    However, the branch blocks still reference these locations as new data with a value "1" could be subsequently re-inserted. These effectively empty leaf blocks can be recycled by subsequent re-inserts but until then, they remain within the index structure and because they might contain a value "1", need to be scanned just in case by your query.

    This is a classic example of when a index rebuild (or coalesce) might indeed be beneficial as you have effectively deleted many index entries without re-inserting a similiar volume and you have queries that scan the portion of the index containing these many deleted entries.

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 18. Re: Too much Logical IOs on a locally partition index !!
    rp0428 Guru
    Currently Being Moderated
    >
    However, the branch blocks still reference these locations as new data with a value "1" could be subsequently re-inserted. These effectively empty leaf blocks can be recycled by subsequent re-inserts but until then, they remain within the index structure and because they might contain a value "1", need to be scanned just in case by your query.

    This is a classic example of when a index rebuild (or coalesce) might indeed be beneficial as you have effectively deleted many index entries without re-inserting a similiar volume and you have queries that scan the portion of the index containing these many deleted entries.
    >
    Do you have an article on your blog that discusses this 'massive delete leaving empty leaf nodes' issue? I've read most of the indexing articles but couldn't seem to find one about this. I did find this one which is pretty similar.

    http://richardfoote.wordpress.com/2008/07/08/empty-leaf-blocks-and-statistics-sense-of-doubt/

    and another one which is somewhat related
    http://richardfoote.wordpress.com/2012/01/05/curious-case-of-the-ever-increasing-index-solution-a-big-hurt/

    Also, at my request OP collected basic stats on the table and index
    exec dbms_stats.gather_table_stats(ownname=>'HOHO',tabname=>'IN_BPMDNPMTACT',estimate_percent=>10, degree=>32 , method_opt=>'FOR ALL COLUMNS SIZE 1',Granularity=>'ALL',cascade=>TRUE);
    That did not help resolve the issue.

    Would creating a histogram on the index columns have resolved this? Which index columns might have been sufficient? Leading only (PROC_RSLT) or first two (PROC_RSLT AND SEQ)?
  • 19. Re: Too much Logical IOs on a locally partition index !!
    rp0428 Guru
    Currently Being Moderated
    >
    And there is no consistent-read on this table,(I mean CR copies from undo) because it is on a development environment and there are no other active sessions in it.
    >
    Take another look at the extended plan you just posted above
             0          0          0  PARTITION RANGE ALL PARTITION: 1 6 (cr=25006 pr=0 pw=0 time=73499 us cost=33902 size=207286309 card=4410347)
             0          0          0   INDEX RANGE SCAN IN_BPMDNPMTACT_DX1 PARTITION: 1 6 (cr=25006 pr=0 pw=0 time=73483 us cost=33902 size=207286309 card=4410347)(object id 792619)
    Did you notice the 'cr=25006'? The cr is 'consistent read'.

    I believe that Richard and Alexander have pinpointed the specific cause but it would instructive to see if histogram statistics would correct the problem.

    Can you collect histograms on PROC_RSLT and see if that resolves it. If not then you might try a combined histogram on PROC_RSLT and SEQ.

    One of Richard's blogs is close to your example so you might take a look at it
    http://richardfoote.wordpress.com/2008/07/08/empty-leaf-blocks-and-statistics-sense-of-doubt/

    Another one of his is somewhat related
    http://richardfoote.wordpress.com/2012/01/05/curious-case-of-the-ever-increasing-index-solution-a-big-hurt/

    And that one links to a quiz he gave that has an example I think you will want to try
    http://richardfoote.wordpress.com/2012/01/04/curious-case-of-the-ever-increasing-index-quiz-shell-drive-the-big-car/

    That first article had this example query to show deleted leaf blocks. You might run and post a similar query to see what your indexes shows.
    SQL> select lf_rows, lf_blks, del_lf_rows from index_stats;
    
       LF_ROWS    LF_BLKS DEL_LF_ROWS
    ---------- ---------- -----------
         10000         21           0
  • 20. Re: Too much Logical IOs on a locally partition index !!
    Richard Foote Employee ACE
    Currently Being Moderated
    No, histograms would be of no benefit here. The CBO already knows there are few values of interest and is using the index accordingly. The issue here is that once it does use the index, it's using it in a section where a massive update has occurred and is trolling through effectively empty leaf blocks. An index coalesce would be the best solution unless a similar number of inserts are imminent.

    I discuss this in the blog entries you've highlighted and in the "Rebuilding the Truth" presentation.

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
1 2 Previous Next

Legend

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