Re: Using an import as a defrag

On Jan 22, 12:38 pm, howard <pkowa..._at_gmail.com> wrote:
> Hello,
>
> I was talking with fellow DBA that told me that I should export my
> schema, drop it, and re-import it.
>
> The reason was that it would act as a defragment and would reorganize
> my extents and therefore I would gain better performance.
>
> I have a small environment; the db is only 100GB. I would only retain
> about 10GB in recovered space if I did this in production.
>
> Is this an accepted practice?
>
> In 10g, I have worked with segment advisor and the reorganize tools,
> but they seem almost useless.
>
> Thanks,
>
> Howard

Howard,

(assuming database version if 10g R1 10.1.0.2 as no version was explicitly stated for the database in question)

What performance problems is the application currently experiencing? Is the database growth causing a problem, such as backups failing to complete during their maintenance window?

If enough space would actually in fact be reclaimed to make this exercise worthwhile, why would you want to actually move the data out of the database, drop and re-create the schema and all of its underlying objects, grants, synonyms for such?

If enterprise edition is available, search for the use of the dbmsredefinition  package.
If on standard edition, re-creating a table (and its indexes) will cause downtime (although less downtime than the export/import route). As you mention that this is a production database, you might want to benchmark this re-org in your test database and schedule it accordingly during downtime windows.

Under some circumstances such a re-org might actually be warranted. In some cases one might want to re-order the data according to the most important data access pattern so that the crucial index(es) clustering factor is minimized. While you're at it, you might want to consider re-ordering index columns and leveraging (index key) compression.

Search for some papers written by Richard Foote that touch on this subject.

The paper by Juan Loiza entitled "how to stop defragmenting and start living" isn't returned in a search via google. That paper was typically a good start. Are you using locally managed tablespaces with uniform extent sizes, or is this database still using dictionarymanaged  tablespaces?

-bdbafh Received on Tue Jan 22 2008 - 12:11:02 CST