-
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.
-
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
-
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"
-
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
-
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
-
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.
-
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"
-
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
-
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."
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|