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

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. Received on Mon Nov 10 2008 - 14:52:09 CST