Re: Using an import as a defrag

From: joel garry <joel-garry_at_home.com>
Date: Tue, 22 Jan 2008 10:47:04 -0800 (PST)
Message-ID: <c0d0f676-2585-48ab-a36e-90348a773b3c@v17g2000hsa.googlegroups.com>


On Jan 22, 9:38 am, 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.

The first part is true and the second part is generally not true as stated.

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

How exactly have you determined that?

A lot can depend on basic things like PCTFREE and PCTUSED. If you have a lot of nearly full blocks, the reorg could put extra space in blocks and increase the space used. If you are never going to insert into or update these blocks, that could be silly. If you are going to, it could be good. Wasted space may affect full index or table scanning time - do you do that a lot? Undo space may be affected if you update large portions of your db. Do you do that? (I do with certain applications upgrades, it can make a difference in how big a window I need). If you have a lot of historical data that won't be updated, it may make sense to import that with different attributes than your normal inserts and updates. You might eventually want to put those in read-only tablespaces, anyways.

Have you checked for row chaining or row migration? If you have those, there may not only be an advantage to reorg, but a necessity, as well as checking on the basics for going forward.

There are some possibilities for performance increase due to rebuilding the index, but they aren't common - see the paper bdbafh mentioned, as well as Richard Foote's wordpress blog for lots of details.

Jonathan Lewis' FAQ also has some mention of how to figure out if you need to reorg, I think (no time to check just now). There was a thread here not long ago on it, search this group for his name and mine.

Here is a primer for posting here: http://www.dbaoracle.net/readme-cdos.htm

>
> Is this an accepted practice?

For performance, no, not generally. The accepted practice is to test and see if it makes sense for certain situations which turn out to be relatively rare. I have one where I have tested and found it to be true, but even so I'm not doing it, since in the normal course of upgrades it will soon be exp/imp anyways. So in the end I will have gone 1.5 years in production with an extra 10G - it made some difference in backup times and certain critical disk usage, but nothing users would care about (except for the cold backup window, which I've finally convinced them to get rid of).

Definitely check out Tom Kyte's books for proper understanding.

>
> In 10g, I have worked with segment advisor and the reorganize tools,
> but they seem almost useless.

The help on those finally seems to be catching up with the reality. "Fixed in the next release" :-)
http://education.oracle.com/pls/web_prod-plq-dad/show_desc.redirect?dc=D16817_979533&p_org_id=9&lang=CS&source_call= Doug has a blog, maybe you should ask if he has suggestions. The traditional tools mentioned in that advert are ones you can use to make performance judgements.

jg

--
@home.com is bogus.
Netadmin:  "Oops!  I sure am glad this isn't production."
Me:  "Famous last words"
General laughter.
Received on Tue Jan 22 2008 - 12:47:04 CST

Original text of this message