Re: Oracle Database Maintenance

In article <392a2483$0$209_at_nntp1.ba.best.com>,   "Mike Adams" <mikea_at_best.com> wrote:
> With Microsoft SQL Server 7.0, a DBA creates maintenance plans that
 run
> weekly or monthly to reindex, and defrag the database. What is
 the "Best
> Practice" on an oracle database? Is a weekly or Monthly process
> recommended. And what would be included in that process.
>
> Thanks!!!!
> Mike Adams
>

The answer would depend on your applications and your space management scheme. If you separate your objects by extent sizes and have decent estimates of their space needs then little to no maintenance for space management will be needed since a having objects in a reasonable number of properly sized extents is not a performance problem.

Example all objects, tables and indexes, are classified as either small, medium, or large. The initial and next extent allocations are set equal with a pctincrease of 0 for all objects. Pick extent sizes based on the object size estimates, in this example 512K, 10M, and 100M. Now you create the appropriate number of tablespaces to support each size for data and index. Build and load your tables and you have greatly reduced your need to drop and recreate objects to keep from running out of space.

Now, you may still need to run re-orgs particularly for indexes that are definded on updated/modified columns and columns on sequential keys where deletes on the base table take place since this kind of activity can easily result in the index containing many blocks with only a few entries in them. A rebuild is required to reclaim this space.

The time between rebuilds depends on the activity and how much free space you have available to take extents from, and the availability of maintenance windows.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.