Understanding INDEX_STATS view
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Understanding INDEX_STATS view

  1. #1
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    I am trying to figure out a query that will tell me which indexes need to be rebuilt based on how many deleted rows are present in the index. From what I have read, I need to use the INDEX_STATS view after the index as been analyzed.

    However, I am running into inconsistencies in the data being reported by INDEX_STATS. For example:
    ops$jeffh@ibdev1.us> drop table jh_index_stat
    2 /

    Table dropped.

    ops$jeffh@ibdev1.us> create table jh_index_stat (
    2 run_ts date,
    3 HEIGHT NUMBER,
    4 BLOCKS NUMBER,
    5 NAME VARCHAR2(30),
    6 PARTITION_NAME VARCHAR2(30),
    7 LF_ROWS NUMBER,
    8 LF_BLKS NUMBER,
    9 LF_ROWS_LEN NUMBER,
    10 LF_BLK_LEN NUMBER,
    11 BR_ROWS NUMBER,
    12 BR_BLKS NUMBER,
    13 BR_ROWS_LEN NUMBER,
    14 BR_BLK_LEN NUMBER,
    15 DEL_LF_ROWS NUMBER,
    16 DEL_LF_ROWS_LEN NUMBER,
    17 DISTINCT_KEYS NUMBER,
    18 MOST_REPEATED_KEY NUMBER,
    19 BTREE_SPACE NUMBER,
    20 USED_SPACE NUMBER,
    21 PCT_USED NUMBER,
    22 ROWS_PER_KEY NUMBER,
    23 BLKS_GETS_PER_ACCESS NUMBER,
    24 PRE_ROWS NUMBER,
    25 PRE_ROWS_LEN NUMBER
    26 )
    27 /

    Table created.

    ops$jeffh@ibdev1.us> drop table test1
    2 /

    Table dropped.

    ops$jeffh@ibdev1.us> create table test1 (id number(10) , data_val varchar2(60))
    2 /

    Table created.

    ops$jeffh@ibdev1.us> create unique index test1_pk on test1 (id)
    2 /

    Index created.

    ops$jeffh@ibdev1.us> insert into test1 select rownum, column_name from all_tab_columns
    2 /

    26512 rows created.

    ops$jeffh@ibdev1.us> commit;

    Commit complete.

    ops$jeffh@ibdev1.us> select min(id) min_id, max(id) max_id, count(id) count_id from test1
    2 /

    MIN_ID MAX_ID COUNT_ID
    ---------- ---------- ----------
    1 26512 26512

    ops$jeffh@ibdev1.us> analyze index test1_pk validate structure
    2 /

    Index analyzed.

    ops$jeffh@ibdev1.us> insert into jh_index_stat
    2 select sysdate,
    3 HEIGHT,
    4 BLOCKS,
    5 NAME,
    6 PARTITION_NAME,
    7 LF_ROWS,
    8 LF_BLKS,
    9 LF_ROWS_LEN,
    10 LF_BLK_LEN,
    11 BR_ROWS,
    12 BR_BLKS,
    13 BR_ROWS_LEN,
    14 BR_BLK_LEN,
    15 DEL_LF_ROWS,
    16 DEL_LF_ROWS_LEN,
    17 DISTINCT_KEYS,
    18 MOST_REPEATED_KEY,
    19 BTREE_SPACE,
    20 USED_SPACE,
    21 PCT_USED,
    22 ROWS_PER_KEY,
    23 BLKS_GETS_PER_ACCESS,
    24 PRE_ROWS,
    25 PRE_ROWS_LEN
    26 from index_stats
    27 /

    1 row created.

    ops$jeffh@ibdev1.us> commit;

    Commit complete.

    ops$jeffh@ibdev1.us> delete from test1 where data_val < 'N'
    2 /

    13450 rows deleted.

    ops$jeffh@ibdev1.us> commit;

    Commit complete.

    ops$jeffh@ibdev1.us> select min(id) min_id, max(id) max_id, count(id) count_id from test1
    2 /

    MIN_ID MAX_ID COUNT_ID
    ---------- ---------- ----------
    1 26511 13062

    ops$jeffh@ibdev1.us> analyze index test1_pk validate structure
    2 /

    Index analyzed.

    ops$jeffh@ibdev1.us> insert into jh_index_stat
    2 select sysdate,
    3 HEIGHT,
    4 BLOCKS,
    5 NAME,
    6 PARTITION_NAME,
    7 LF_ROWS,
    8 LF_BLKS,
    9 LF_ROWS_LEN,
    10 LF_BLK_LEN,
    11 BR_ROWS,
    12 BR_BLKS,
    13 BR_ROWS_LEN,
    14 BR_BLK_LEN,
    15 DEL_LF_ROWS,
    16 DEL_LF_ROWS_LEN,
    17 DISTINCT_KEYS,
    18 MOST_REPEATED_KEY,
    19 BTREE_SPACE,
    20 USED_SPACE,
    21 PCT_USED,
    22 ROWS_PER_KEY,
    23 BLKS_GETS_PER_ACCESS,
    24 PRE_ROWS,
    25 PRE_ROWS_LEN
    26 from index_stats
    27 /

    1 row created.

    ops$jeffh@ibdev1.us> commit;

    Commit complete.

    ops$jeffh@ibdev1.us> select run_ts, lf_rows, br_rows, del_lf_rows
    2 from jh_index_stat
    3 /

    RUN_TS LF_ROWS BR_ROWS DEL_LF_ROWS
    -------- ---------- ---------- -----------
    13:32:41 26512 209 0
    13:32:42 26512 209 13450


    At this point, The data looks right. However, when more data gets put into the table and subsequently deleted, things get all screwed up...
    ops$jeffh@ibdev1.us> insert into test1 select rownum+40000, column_name from all_tab_columns
    2 /

    26512 rows created.

    ops$jeffh@ibdev1.us> commit;

    Commit complete.

    ops$jeffh@ibdev1.us> select min(id) min_id, max(id) max_id, count(id) count_id from test1
    2 /

    MIN_ID MAX_ID COUNT_ID
    ---------- ---------- ----------
    1 66512 39574

    ops$jeffh@ibdev1.us> delete from test1 where data_val < 'N'
    2 /

    13450 rows deleted.

    ops$jeffh@ibdev1.us> commit;

    Commit complete.

    ops$jeffh@ibdev1.us> select min(id) min_id, max(id) max_id, count(id) count_id from test1
    2 /

    MIN_ID MAX_ID COUNT_ID
    ---------- ---------- ----------
    1 66511 26124

    ops$jeffh@ibdev1.us> analyze index test1_pk validate structure
    2 /

    Index analyzed.

    ops$jeffh@ibdev1.us> insert into jh_index_stat
    2 select sysdate,
    3 HEIGHT,
    4 BLOCKS,
    5 NAME,
    6 PARTITION_NAME,
    7 LF_ROWS,
    8 LF_BLKS,
    9 LF_ROWS_LEN,
    10 LF_BLK_LEN,
    11 BR_ROWS,
    12 BR_BLKS,
    13 BR_ROWS_LEN,
    14 BR_BLK_LEN,
    15 DEL_LF_ROWS,
    16 DEL_LF_ROWS_LEN,
    17 DISTINCT_KEYS,
    18 MOST_REPEATED_KEY,
    19 BTREE_SPACE,
    20 USED_SPACE,
    21 PCT_USED,
    22 ROWS_PER_KEY,
    23 BLKS_GETS_PER_ACCESS,
    24 PRE_ROWS,
    25 PRE_ROWS_LEN
    26 from index_stats
    27 /

    1 row created.

    ops$jeffh@ibdev1.us> commit;

    Commit complete.

    ops$jeffh@ibdev1.us> select run_ts, lf_rows, br_rows, del_lf_rows
    2 from jh_index_stat
    3 /

    RUN_TS LF_ROWS BR_ROWS DEL_LF_ROWS
    -------- ---------- ---------- -----------
    13:32:41 26512 209 0
    13:32:42 26512 209 13450
    13:32:59 43292 424 17168

    Since all the entries in this index are unique and distinct, I expected 53024 for LF_ROWS and 26900 for the DEL_LF_ROWS for the third entry. What am I misunderstanding?

    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Whoa, Jeff Hunter has asked a question - Coool!

    Well, I better make this one count, then , so here we go:

    You start with 0, add 26,512 rows, hence 26,512 leaf rows
    ...so far, so good
    You delete 13450 of them, hence 13,450 deleted leaf rows.
    ...still on track

    Now.... you add back in another 26,512 rows, thereby expecting a total of 53,024 leaf rows, *but* you already deleted a bunch, so what if the index re-uses those deleted entries?

    Assume, for example, that out of 13,450 empty leaf rows sitting around, say, oh, 9732 of them got re-used.

    That leaves 26512 - 9732 or 16780 new leaf rows getting inserted:

    26512 + 16780 = 43,292. Spiffy, eh?

    Now, as we said, 9732 of those rows got re-used. Now, we delete a total of 13,450 rows again. 9732 of those rows occupied the same space as previously-deleted rows, so we only actually deleted 13450 - 9732 or 3718 *new* rows from the index. This brings our *total* of deleted rows to 13450 + 3718, or 17168.

    Phew, gotta dot those i's when answering one for the man


    HTH,

    - Chris

  3. #3
    Join Date
    Feb 2001
    Posts
    389
    To verify what chris has explained, u can dump index tree structure befor and after the process.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by chrisrlong
    Now.... you add back in another 26,512 rows, thereby expecting a total of 53,024 leaf rows, *but* you already deleted a bunch, so what if the index re-uses those deleted entries?

    Assume, for example, that out of 13,450 empty leaf rows sitting around, say, oh, 9732 of them got re-used.
    This is the part I don't understand. Since my index entries are all unique, I would expect that the index to re-use an entry UNLESS it had the exact same key value. For example, if I inserted a row with an ID=1, and deleted it, I would not expect that index row to be used again if I inserted ID=2. It should only get re-used if I insert another row with ID=1. (According to "Oracle Performance Tuning Tips & Techniques, Richard Niemiec" at least.)

    I buy your explanation, I just don't understand why the index rows would get re-used.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    I was hoping you wouldn't go there

    Unfortunately, I do not have a solid answer for that, but I have a theory, so let's run with it.

    I just performed your same test with slightly different results. After the second set of insertions, only a few hundred 'leaf rows' were 're-used'. I then performed the same steps a couple more times with similar results. I then inserted a new set, deleted the entire set, then re-inserted the entire set again (with higher ids, of course). In that case, almost *all* the leaf rows were re-used.

    Given all that, my theory is this: I have a much bigger block size than you do - mine is 16K. (genetics - it's not your fault )

    Given that, when I delete the 'semi-random' records, it is very rare that I delete *all* the records from an entire index block. You, on the other hand (so my theory goes) have a smaller block size, so you have more blocks that have *all* their entries deleted. It would seem that deleting all the entries in the index block will put that block back on the free list within the index to be subsequently re-used on the next insert. Therefore, its corresponding 'leaf rows' also get 're-used' or subtracted from the total during that insert.

    As you pointed out, it is not possible to actually re-use a row in an index unless you are actually re-inserting the exact same value. However, once the block is empty, the block, and its accompanying count of deleted rows *can* be 're-used' in a fashion.

    What do you think?

    - Chris

  6. #6
    Join Date
    Feb 2001
    Posts
    389
    del_lf_rows or the deleted rows still in leaf blocks, if there are no rows in a leaf block, that leaf block is released and put under free list for leaf block splits.Other wise that block is not released.
    del_lf_rows does not count the rows of the leaf blocks for which the whole leaf block becomes empty.

    If u see ur stats, lf_rows and br_rows.
    lf_rows according to u should have been total leaf rows inserted (minus some br_rows) but it is not so , so total rows allocated for leaf shows some of the leaf blocks did get full empty and thus space reused.


    I hope it is clear.
    GP

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by chrisrlong
    As you pointed out, it is not possible to actually re-use a row in an index unless you are actually re-inserting the exact same value. However, once the block is empty, the block, and its accompanying count of deleted rows *can* be 're-used' in a fashion.
    True, I am working with a 2K block size (Don't laugh, I inherited it). I can agree with that theory. I didn't really think about what happens when the DB block becomes free. If the DB block becomes free and gets placed back on the free list, it looks like the corresponding data wouldn't get reported in the del_lf_rows because it's not really there anyway.

    Thanks for the help. As always, it's appreciated...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I tested this today

    cerated a test table
    inserted 100 rows with values 1 to 100

    then ran analyze validate structure, got
    BTREE_SPACE USED_SPACE PCT_USED LF_ROWS DEL_LF_ROWS
    ----------- ---------- ---------- ---------- -----------
    7996 1300 17 100 0

    deleted 50 rows
    analyze again
    BTREE_SPACE USED_SPACE PCT_USED LF_ROWS DEL_LF_ROWS
    ----------- ---------- ---------- ---------- -----------
    7996 1300 17 100 50

    inserted 51 rows from 200 to 250
    analyzed again
    BTREE_SPACE USED_SPACE PCT_USED LF_ROWS DEL_LF_ROWS
    ----------- ---------- ---------- ---------- -----------
    7996 1377 18 102 0

    Hm, how come I got 102 back instead of 101?

    My block size is 8K

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    It would be interesteing to see what you get if you "alter index xyz rebuild"
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    just rebuilt it, nothin were changed


    SQL> alter index test_pk rebuild;

    Index altered.

    SQL> analyze index test_pk validate structure;

    Index analyzed.

    SQL> select btree_space, used_space, pct_used, lf_rows, del_lf_rows
    2 from index_stats;

    BTREE_SPACE USED_SPACE PCT_USED LF_ROWS DEL_LF_ROWS
    ----------- ---------- ---------- ---------- -----------
    7996 1377 18 102 0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width