SIZING INDEXES

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

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

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 :-)

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?

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???

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]

Quote:

---

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).

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

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]

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

Eventually cobbled this together for anyone who's interested !

spool c:\sql\index_size.lst
set pagesize 1000
col segment_name format a50
select segment_name, ROUND(sum(bytes)/1024/1024, "IDX SIZE in M"
from dba_segments
where segment_type = 'INDEX'
and tablespace_name = 'INDEX_TBSP'
group by segment_name
order by 2 DESC,1 ASC
/
spool off

Cheers Again
Eddy

If yoy are confident that Index does not have corrupted block, then you can speed up the index creation process by running "ALTER INDEX ... REBUILD" command with COMPUTE STATISTICS. You do not need to run analyze command after index is rebuilt.
Also examine parallelism with NOLOGGING option on a large indexes.
If possible, turn off archive process.

First of all, you do not need to rebuild all the 650 indexes in one night. You can do it in 4 or 5 nights.

FYI for those using the script - this one is correct with respect to syntax (missing a right parens and had a comma):

select segment_name, ROUND(sum(bytes))/1024/1024 "IDX SIZE in M"
from dba_segments
where segment_type = 'INDEX'
and tablespace_name = 'INDEX_TSP'
group by segment_name
order by 2 DESC,1 ASC

Eddy, 650 indexes is a lot of rebuilding. As you were advised, why don't you rebuild within a week, not within a night? If I have to do your job, bacause of possible corrupted blocks, I would drop and rebuild but first I would turn off archivelog.

Cheers Stecal - can't understand why it dropped the parens and added the comma, as all I did was cut and paste it from the SQL buffer where it was working fine - sure it would have barfed if it didn't - ODD !

I was planning on rebuilding over a few nights - the majority of the indexes are 3M and under. But the two main offenders are 200M and 1.5GB respectively. What would be the best wat to tackle a 1.5GB index rebuild - I suspect this will be the index that exponentially ballooned out of control when the patch was added and could ne only a very small in reality. If this is the case would it be preferable to drop and the create as opposed to rebuilding in terms of speed to rebuild it anyone ?

Thanks in advance
Eddy

Hi Tim,

So u hv rebuilt all the indexes in one go ?

I a running SAPR3. DB size is 200 GB.

I also need to rebuild the indexes.

What process u hv adopted :

- HV to dropped the indexes and rebuild those

or straightway u hv rebuilded those.

Please guide me on this ?

Thanks
Vijay