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.

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.

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.

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.

Quote:

---

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.

Quote:

---

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.

Quote:

---

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.

Quote:

---

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.

Thanks for all your thoughts and comments folks. Got this sorted now.

I just did a rebuild during this morning, noparallel, and set extent sizes to 1M - of which there is only one extent allocated following the build!

:-)

T.

Quote:

---

Originally posted by TomazZ
Rebuild will use existing index, which is faster in many situations.

---

Someone (I think slimdave) told me recently that if the index is marked unusable, then it has to be rebuilt by reading the table.

Quote:

---

Originally posted by DaPi
Someone (I think slimdave) told me recently that if the index is marked unusable, then it has to be rebuilt by reading the table.

---

To add, even if Index is usable, & if index is built online, it will scan whole table.

So, wass the point in Rebild with out online clause, and use Index scan, its any way going to hinder the TRANSACTIONs starting hence forth or the other way round is as well true.

Abhay.

Quote:

---

Originally posted by slimdave
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

---

Why go through the hassle of making an index unusable, having slow queries until you rebuild it, and rebuild it, and analyze it? Sounds like a lot of work to me. In this situation, I would just:

Code:

---

ALTER INDEX xyz REBUILD ... ONLINE;

---

if we have enterprise edition we can rebuild online otherwise use the poor man´s solution :D

At the time the indexes are rebuilt (from 0200 hrs) we have no queries accessing the system. [Usage doesn't resume until around 9 a.m.)

As the indexes are actually bitmap, I believe the option to rebuild 'online' is unavailable.

We used to have the indexes present when running the batch, in the form of b-tree indexes. Unfortunately, as the user daytime queries were so ad-hoc and trying to juggle variants of concatenated indexes to suit everyone was too much of a headache and performance remained dire overall - we elected to transform to bitmaps.

These are much smaller than the b-trees, performance gained substantially from multitudinous bitmap merges for almost everyone. The only downside unfortunately was that the overnight batch with bitmap objects in place upped the batch time from around 6 hours to 6 days or more!!!

Hence the drop and recreate strategy.

Of course, you may not have wanted to know this - but I felt like filling in the gaps in the scenario.

:-)

Right now I'm going to adopt a drop, create in parallel (to avoid long create times but with the detriment of extra space usage), followed by a rebuild noparallel (which is reasonably quick and eliminates the extra space overhead from earlier), concluding with a quick analyze!

Unless of course I'm overlooking something?

Is there a better stategy that I might be missing?

- Tony.

Quote:

---

Originally posted by marist89
Why go through the hassle of making an index unusable, having slow queries until you rebuild it, and rebuild it, and analyze it? Sounds like a lot of work to me. In this situation, I would just:

Code:

---

ALTER INDEX xyz REBUILD ... ONLINE;

---

---

Problem is that with those bitmap indexes the insert/update/delete is nightmarishly slow - it's usually better to make 'em unusable and rebuild them after the operation.

On a different note, a rebuild on a usable index will read the index, not the table, except where you use the "compute statistics" clause - that makes it read the table not the old index, i believe.

Quote:

---

Originally posted by slimdave
On a different note, a rebuild on a usable index will read the index, not the table, except where you use the "compute statistics" clause - that makes it read the table not the old index, i believe.

---

Try Online clause (b*tree), it also goes for table scan instead.

Abhay.