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

On Nov 10, 5:07 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Nov 10, 12: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.
>
> It's possible (likely even, in some situations) that data will be
> spread across more blocks, if you don't watch out for pctfree.  Also,
> be sure and watch what exp/imp does with initial extents, read the
> compress parameter docs carefully.
>
> Have you gone through the method-R methodology?  I think that is
> pretty good for determining what's wrong on a system that is at least
> in the ballpark tuning-wise.
>
> Now, you should have a pretty good idea which tables and indices are
> at issue.  As a general rule, I would advise only messing with data
> you can show is problematical.  But I can certainly understand the
> desire to just have everything clean and new with a simple exp/imp, if
> your testing shows it helps, hey, why not, I've had it work for me.
> Well, one why not is a possible aging of the physical disk blocks,
> they may not be so clean after multiple add/delete cycles, so you
> don't want to make a habit of it.
>
> One argument against even specific table reorgs is that there may be
> an effect of things being fast for a while, then slowing down again.
>
> Sometimes, you just need more I/O horsepower.  Sometimes, you just
> need to look and be sure the I/O is doing what it is supposed to be.
> Sometimes you just need to offload the reporting to not interfere with
> OLTP.  Sometimes, you just need to stop running a report no one uses.
>
> So what is your I/O?
>
> At any rate, see this:  http://jonathanlewis.wordpress.com/2008/05/14/index-efficiency/
>
> jg
> --
> @home.com is bogus.
> What's not in your database?http://catless.ncl.ac.uk/Risks/25.44.html#subj7- Hide quoted text -
>
> - Show quoted text -

Thanks a lot. Received on Mon Nov 10 2008 - 16:58:57 CST