Re: Do Oracle DBs deteriorate over time??

Tim Auckland wrote:
>
> We are setting up a new production database using Oracle 7.3 on Unix,
> and I am trying to find out whether any regular re-structures, index
> re-builds, overflow removal, de-fragmentataion, etc. is recommended
> after a period of operation.
>
> So far, I am aware of two problems in this area:
>
> 1) tables and indexes may expand and occupy multiple extents
> 2) rows may become chained if records are expanded after initial
> creation
>
> Are there any other efficiency aspects of Oracle 7.3 databases which a
> prudent DBA would monitor, and, if a problem does occur, what steps
> can be taken to remove it?
>
> Any help would be greatly appreciated.
>
> Thanks in advance.
>
> Tim Auckland.

Tim:
We run our Oracle dbs 24x7 and any down time for reorgs counts against our availability, so we minimize or eliminate it out of necessity. If you are dilligent in sizing the database and designing the application then there is no reason you have to do periodic reorgs. Set your initial storage parameters so you can hold the entire object in one or a few extents, and leave lots of free space just in case. Monitor extent allocation and try to correct growing objects before they get too large (by adjusting the storage parameters or changing the application if necessary). To avoid chaining, set your storage parameters correctly and (if necessary) consider changing the application to prevent chaining by performing delete/insert instead of update (expensive - last resort) or padding certain fields on the initial insert if the storage parameters alone won't do it.

The only thing that has caused us to reorg over the last 5 years has been a linear growth of certain tables/indexes where space was not being reused. The amount of data doesn't increase, it just doesn't reuse old blocks once the data is deleted. Oracle still hasn't explained it, but we've minimized the impact by giving it LOTS of extra space so we only reorg maybe once a year.

You can make reorgs easier if you use synonyms and/or views to access your data indirectly. This will allow you to reorg tables transparently to the application under certain circumstances, especially tables that are not frequently updated.

BTW, this was one reason we chose Oracle over Informix (several years ago, mind you!) was the periodic reorgs that Informix required. So far Oracle has worked as advertised.

Hope this helps.

-- 
---
Allen Kirby			AT&T ITS Production Services
akirby_at_att.com			Alpharetta, GA.