Re: Are there performancebenefits to reorganizing database-using export/import?

On Nov 10, 3:52 pm, zigzag..._at_yahoo.com wrote:
> I am using Oracle 9.2.0.5 on HP UNIX 11i. Since it is a production
> system, I cannot change version of Oracle any time soon, so please do
> not suggest that.
>
> I have a production database using Oracle 9.2.0.5, which has been
> running for last 3 years since it was upgraded from 8.1.7. At that
> time we had done full export of 8.1.7 database and then created 9.2.
> instance and then imported all the application schemas.
>
> Load on our database has been increasing and there are constant
> pressures from management to improve performance. We have looked at
> indexes many times, have lots of memory for SGA and have tuned various
> init.ora parameters. We utlize three pools buffer, keep and recycle.
> Being a third party packages, we cannot rewrite queries. Avg CPU
> Utilization (6 CPU"s) is 30%, memeory utilization is 60% (we have 24
> GB memeory on the server), so I do not see we are taxing any
> resources, i..e, adding more CPU's and memory should not help.
>
> Application is a mix of OLTP and reporting, it is definitely more read
> than write.
>
> 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. We will
> run the dbms_Stats again to recomputed statistics. Of course, we will
> test all of that in a test environment before making change sin
> production.
>
> 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.
> Appreciate your feedback.

Why would you want to take the data out of the database? If in fact a re-org would help (say to move data from dictionarymanaged  tablespaces with a range of extent sizes to a locally-managed tablespace with uniform extent sizes) you need not move the data out of the database to do this.

One could create such a tablespace, and during a maintenance window, move the table to the new tablespace and subsequently rebuild the indexes. In standard edition, this is an offline operation. In enterprise edition (of some versions) this can be performed online. In newer editions of the Oracle database server software, the package dbms_redefinition is available.

While you are at it, you might consider key compression of concatenated indexes.
This can reduce the space used by indexes, but you might need to re- order the columns in the index in order to gain the space reduction that you're after.

Then again, if your IO is truly 70% read and 30% write, you might just be best off with reducing the number of indexes to reduce the amount of writes from inserts, updates and deletes. Perhaps the default response to a performance problem is to "add yet another index" and now you have dozens of indexes on your main fact tables. That would certainly increase the percent of writes on DML.

If you were to do a full database export/import, why not into a newer version of the Oracle database server software? Certainly there have been improvements in the cost based optimizer since 9.2.0.5 - why not give 10.2.0.4 a try (in testing)?

-bdbafh Received on Tue Nov 11 2008 - 09:04:50 CST