Reducing Index Builds Effect on Production

[ocp8]

Hello..

I have 2 Test and 2 Production databases on a F740 Netapps filer and experience SEVERE performance degredation in production whenever I build indexes a certain 12 million row 7 gig table I have in test. We are going to have to build the indexes again on the test database next week and I was wondering if there was a way I could possibly reduce the impact of this process on the production database? I know the i/o costs are terrible and this is the consequence of having test databases on the production system. We don't have anywhere else to put them and my boss is well aware of this issue. The only thing I can think of is to create the indexes over the weekend when there are no users on the system. I just didn't know if anyone might have any ideas on how I could do it during production hours. We do not have the partitioning option and this is a canned application so I do not have direct access to developers. Any help or ideas would be greatly appreciated!!

Thank you!

[jmodic]

Can you specify your Oracle release? Can you post the command you are using for rebuilding index? Can you let us know about your RAM, number of CPUs, SGA size, your SORT_AREA_SIZE setting.... ?

Are you using NOLOGGING during rebuild?

[marist89]

Do the TEST and PROD databases share the same filer?

[ocp8]

My configuration is pretty bad:

1 CPU
210 MHZ
1 GIG RAM

My sga looks like this:

Total System Global Area 230641056 bytes
Fixed Size 38984 bytes
Variable Size 107312472 bytes
Database Buffers 122880000 bytes
Redo Buffers 409600 bytes

My sort parameters look like this:

sort_area_retained_size 409600
sort_area_size 409600
sort_direct_writes AUTO
sort_read_fac 5
sort_spacemap_size 512
sort_write_buffer_size 32768
sort_write_buffers 2

The index create script looks like this:

CREATE INDEX AR00200TI5 ON
AR00200T(SOURCE_SS_ID, SOURCE_REFERENCE, SOURCE_SPEC1, SOURCE_SPEC2, SOURCE_SPEC3)
UNRECOVERABLE parallel (degree 8) tablespace appindex200t;

Thank you for your help!!

[stmontgo]

please post your version oracle, it makes a big difference

[pando]

get rid of parallel, you only have one CPU that makes you process worse

[ocp8]

All the databases are on the Filer!! Much to my disagreement!!

[ocp8]

The database is 8.1.7.

[ocp8]

I have been corrected:

2 processors
120 MHZ each

Thanks again for all the suggestions!!

[jmodic]

Are you creating a new index or are you rebuilding existing index (eg. DROP INDEX + CREATE INDEX)?

If you are only rebuilding it and if the index is not in UNUSABLE state, you will save a huge amount of time if you would rather use ALTER INDEX REBUILD instead of DROP INDEX + CREATE INDEX.

If you are creating a brand new index, then your biggest problems are perhaps disk sorts, because your sort area is rather tinny for such large number of rows. I don't know what are the sizes of your other three databases SGA's and if your system is allready swapping a lot, but in any case increasing the SORT_AREA_SIZE and SORT_AREA_REATINED_SIZE for the session that is performing index buildto let's say 5 Mb will certainly make a noticable difference in time required, yet it will probably not make any noticable differenc to the system swapping.