SIZING INDEXES

[Eddy99]

I need approximate sizes of the indexes on our production system to enable me to schedule rebuilds over a series of batch overnight processes. Does anyone have a formula that will enable me to work out the size simply by knowing the INITIAL EXTENT, NEXT EXTENT, NUM OF EXTENTS and PCT INCREASE VALUES for the index or is there more to this than meets the eyes ?

Help would be very much appreciated

Thanks in Advance
Eddy

[TimHall]

The current size of you indexes can be worked out like this:

SQL> select sum(bytes)
2 from dba_segments
3 where segment_name = 'MY_INDEX_IDX';

SUM(BYTES)
----------
41943040

Cheers

[Eddy99]

I'll re phrase my question a little here.

What I was after was a script of method to run over all 650 indexes that I need to Analyse and not just each index on an ad-hoc basis.

Thanks for the info - it is helpful :-)

[julian]

Eddy, do you want to analyze those indexes or rebuild them? If the latter, then would you like to rebuild all indexes or just the ones that need to be rebuilded?

[TimHall]

I don't understand the question. First you said rebuilding your indexes. Next you said analyzing them. I don't see what the size of the index has to do with either.

If you want the size of multiple indexes you can do something liek this:

select segment_name, sum(bytes)
from dba_segments
where segment_type = 'INDEX'
and owner = 'MY_SCHEMA'
group by segment_name;

If you want to rebuild several indexes you can do something like this:

DECLARE
CURSOR c_indexes IS
SELECT owner || '.' || index_name as name
FROM dba_indexes
WHERE owner = 'MY_SCHEMA';
BEGIN
FOR cur_idx IN c_indexes LOOP
EXECUTE IMMEDIATE 'ALTER INDEX ' || cur_idx.name || ' REBUILD ONLINE;';
END LOOP;
END;
/

If you want to analyze several indexes you can do something like this:

DECLARE
CURSOR c_indexes IS
SELECT owner, index_name
FROM dba_indexes
WHERE owner = 'MY_SCHEMA';
BEGIN
FOR cur_idx IN c_indexes LOOP
DBMS_STATS.GATHER_INDEX_STATS(cur_idx.owner, cur_idx.index_name);
END LOOP;
END;
/

Perhaps you want to do a combination of these things???

[Eddy99]

I've obviously been as Clear as mud to date.

In essence I am rebuilding the indexes on the Oracle Apps system.

But before I do the rebuild I have been told I need to find out an approximate size for each of the indexes. This is so that I can estimate the time it will take to rebuild the indexes by testing some of the rebuilds against the UAT system. I have been told that we can't REBUILD ONLINE as my boss doesn't like the idea of this. Therefore we are going to rebuild them during the nightly batch jobs.

Out of interest does anyone have any real time experience of what kind of impact rebuilding the indexes online has on system performance ? Is the degredation such that this shouldn't be considered a viable option. Obviously this is relative to normal system performance.

Thanks for all the help so far

Eddy

PS:- I want to rebuild some indexes in a schema where an Oracle Patch has added some 5,000,000 records to some indexes when there should be about 5 records, hence causing capacity management problems.

[Edited by Eddy99 on 05-23-2002 at 08:50 AM]

[julian]

Originally posted by Eddy99

Out of interest does anyone have any real time experience of what kind of impact rebuilding the indexes online has on system performance ?

If you rebuild one index, performance will not suffer that much but you better rebuild them at hight when there is no DB activity. Do you drop and rebuild, or just rebuild?

I just rebuilt 5 indexes in a small production detabase (by dropping them first), nobody complained (it took me 2-3 minutes or so).

[TimHall]

Does the information above answer your question or do you need more clarification?

I'm assuming this was the answer to your question:

select segment_name, sum(bytes)
from dba_segments
where segment_type = 'INDEX'
and owner = 'MY_SCHEMA'
group by segment_name;

Cheers

[Eddy99]

Thankyou Tim that is just what I wanted !

Thankyou Julian - I am rebuilding 650 indexes on the production system so I think an overnight job seems the best bet in light of your comments. Is it a lot faster to just drop and then recreate than rebuilding - I would guess if your rebuilding an index from 5,000,000 to 5 entries it would be ? Would the rebuild option mean reading all the 5,000,000 blocks in the index to find the 5 that havn't been deleted ?

Thanks again all
Eddy

[Edited by Eddy99 on 05-23-2002 at 09:18 AM]

[TimHall]

Cool. It took some time but we got there in the end