Re: Are there performancebenefits to reorganizing database-using export/import?
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Tue, 11 Nov 2008 23:09:05 +0100
Message-ID: <6nue41Fvmu0U1@mid.individual.net>
>> On 10.11.2008 21:52, zigzag..._at_yahoo.com wrote:
>>
>>> Are there any benefits to reorganize database using export/import,
>>> i.e., we will do a full export of existing database and then delete
>>> all objects from application schemas and do schema imports.
>>> I have heard different views on reorganization. Some people say it is
>>> useless, some people say it can improve performance since data will be
>>> compacted in fewer blocks.
>> Also agree with Joel: First you should determine whether physical
>> properties (block usage etc.) are actually limiting performance.
>> Assuming that this has been established, an important question is: how
>> much effort does this reorganizing take and how long does the effect
>> last? If it takes a day (during which you might have to go offline) but
>> the effect is gone after a week because of the nature of your workload,
>> it's probably not worth the effort and risk.
Date: Tue, 11 Nov 2008 23:09:05 +0100
Message-ID: <6nue41Fvmu0U1@mid.individual.net>
On 11.11.2008 14:05, zigzagdna_at_yahoo.com wrote:
> On Nov 11, 2:33 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
>> On 10.11.2008 21:52, zigzag..._at_yahoo.com wrote:
>>
>>> Are there any benefits to reorganize database using export/import,
>>> i.e., we will do a full export of existing database and then delete
>>> all objects from application schemas and do schema imports.
>>> I have heard different views on reorganization. Some people say it is
>>> useless, some people say it can improve performance since data will be
>>> compacted in fewer blocks.
>> Also agree with Joel: First you should determine whether physical
>> properties (block usage etc.) are actually limiting performance.
>> Assuming that this has been established, an important question is: how
>> much effort does this reorganizing take and how long does the effect
>> last? If it takes a day (during which you might have to go offline) but
>> the effect is gone after a week because of the nature of your workload,
>> it's probably not worth the effort and risk.
> > Since my database is 70% read and 30% write, I would think effect > will last noger if any performance improvements are ontained because > of reduced no of blocks.
Those percentages are irrelevant for my point. The important question is how many changes are there in a period of time and what is their nature? With the appropriate workload it is pretty easy to ruin a reorganized index or table pretty fast (see Richard Foote's excellent blog for examples). To determine how long this takes you need to look at the change rate and where in tables / indexes these changes will be.
Btw., some reorganizations cannot be achieved with a _simple_ export import - especially when you want to try to reduce the clustering factor of an index.
Kind regards
robert Received on Tue Nov 11 2008 - 16:09:05 CST