DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Index Blocks/Extent Usage

  1. #1
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78

    Index Blocks/Extent Usage

    I have an overnight batch schedule during which I drop bitmap indexes, then recreate them again afterwards.

    I recently did some analysis and found some of the index statistics as follows:

    E.g.

    Typical_Index_BM

    Size in bytes from dba_segments = 70 MB.
    Size in bytes (unused above HWM) from dbms_space.unused_space = 0 MB
    Size in bytes (on freelist) from dbms_space.free_blocks = 69 MB

    It would appear to me that 70 megs of blocks get used at some point during the index build (shown by the amount from dba_segments), and the fact that 0 megs appear above the HWM from dbms_space.unused_space.)

    How is it that 69 MB of these used blocks then become available on the freelist for the segment?

    Why didn't the index, in it's 'build-phase', just use 1 MB instead?

    [ The index does build in parallel (degree 10), into a dictionary managed tablespace whose default storage clauses are set to 10 MB initial/next extents. ]


    - Tony.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Parallel degree 10 will use 10 times the disk space than a regular index build. Besides, degree 10 for a 1M index is overkill, IMHO.
    Jeff Hunter

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    I belive u messed some where.
    Analyze the index and see the used blocks.

    BTW, Jeff as you said prallel would eat 10 times disk space, so for 1M index the total sapce allocated is 70M with 7 extents?

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    First, the INITIAL is 10M. Second, I suspect something is limiting Oracle to 7 parallel processes instead of the intended 10. However, with a 1M index, it probably is created in about .42 seconds so it would be really hard to tell.
    Jeff Hunter

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    On a side issue, a more robust process would be to make the index unusable and set skip_unusable_indexes = true, then rebuild them after the batch process completes
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    Hmmm.

    I believe this index was somewhat bigger before, as it has been converted from a standard b-tree to bitmap at some point in time.

    The rebuild process for indexes gets actioned from within a Korn shell script which passes in the same build parameters for each index. Some of them are quite sizeable - hence the degree 10 parallelism.


    I'll evaluate some of the timings on these smaller indexes and maybe run them noparallel instead.

    :-)

    Many thanks.

    Regarding the 'unusable' state for indexes. I'm a little unsure of what the benefit on that would be. Could I be enlightened?

    1. How would one manually 'set' an index to unusable state? {I've only ever effected that during table moves before . . . .}

    2. What exactly IS the benefit on a rebuild, as opposed to a build from scratch?


    - Tony.

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by AJW_ID01
    1. How would one manually 'set' an index to unusable state? {I've only ever effected that during table moves before . . . .}

    2. What exactly IS the benefit on a rebuild, as opposed to a build from scratch?
    - Tony.
    1) Alter Index idx_nm unusable;

    2) nothing.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Originally posted by AJW_ID01
    2. What exactly IS the benefit on a rebuild, as opposed to a build from scratch?
    Rebuild will use existing index, which is faster in many situations.
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  9. #9
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by TomazZ
    Rebuild will use existing index, which is faster in many situations.
    Right, rebuild will be faster as it need not go to the table and do a full table scan, it will use the existing index.

    But if the table has been moved then its same whether u build from a scratch or do a rebuild as it has to hit the table any way.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  10. #10
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Originally posted by adewri
    But if the table has been moved then its same whether u build from a scratch or do a rebuild as it has to hit the table any way.
    Yes, or if there has been heavy DML on table, it can also be faster using table.
    I think he is going for a regular overnight index rebuild, so it's neither of these two.
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

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