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.

Just wanted to add, application has not lots of deletes to cleanup data in past few months. Received on Mon Nov 10 2008 - 14:56:57 CST