Re: Are there performancebenefits to reorganizing database-using export/import?
- From: zigzagdna@xxxxxxxxx
- Date: Mon, 10 Nov 2008 14:58:57 -0800 (PST)
On Nov 10, 5:07 pm, joel garry <joel-ga...@xxxxxxxx> wrote:
On Nov 10, 12:52 pm, zigzag...@xxxxxxxxx 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.
.
- References:
- Prev by Date: Re: Why Oracle does not allow rollback of DDL statements?
- Next by Date: Re: Are there performancebenefits to reorganizing database-using export/import?
- Previous by thread: Re: Are there performancebenefits to reorganizing database-using export/import?
- Next by thread: Re: Are there performancebenefits to reorganizing database-using export/import?
- Index(es):
Relevant Pages
|
