RE: When stats trash your performance
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: <stephenbooth.uk@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 7 Dec 2005 12:13:06 -0700
Stephen,
It is correct that changes in stats can cause performance problems, but that is
the exception, not the norm. In > 99% of the cases, you want to have the most
accurate stats available to enable the CBO to make a well-informed decision.
It sounds like what might have happened in your case is that the application
queries were tuned for the RBO and the generation of stats enabled Oracle to
use the CBO instead, which in some cases can cause worse performance (note
again this is the exception, however if just one high-impact statement is
changed for the worse it can have an impact on the entire system). In that
case - your plan to delete the stats might work just fine. Also, maybe the
stats weren't gathered accurately enough (too small sample size) - perhaps they
should be gathered again with a higher sample size, or even computed (100%
sample). Index stats should pretty much always be computed. If you don't know
what the stats were before, then it will be hard to get back to them. You
could do a restore to another test database and see what the stats used to be.
Other things you might want to look into for the future are running statspack
snapshots at level 6 to capture explain plans for comparison before & after
updating stats, and using the stattab and statid parameters of the dbms_stats
procedures to save your old stats before updating them.
Regards,
Brandon Allen
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of stephen booth
Sent: Wednesday, December 07, 2005 11:59 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: When stats trash your performance
I've heard mention in a number of talks that you shouldn't gather stats very
often, even not at all once a system is bedded in, as it can cause your
database to start performing really badly. I don't recall any one ever saying,
or ever reading, an explanation as to why this might happen. Perhaps I'm
looking at it from the wrong perspective but it seems axiomic that the fresher
your stats the better decisions CBO is likely to make.
I've Googled and can't really find anything that seems relevant other than some
references to gathering stats invalidating existing plans so meaning Oracle has
to do a hard parse.
Can anyone point me to any documentation that might explain why this might
happen. It's not something I've really had to deal with in the past (getting
the system running and occasionally trying to tune the server to
reduce/eliminate waits before it goes live has been most of what I've been
involved in, we don't write code in house so SQL tuning hasn't really
featured). Something with pointers as to how to resolve such problems would be
very useful as well.
What has triggered this question is I've just been hit by a situation where one
of our systems was running OK (performance wasn't great but was acceptable and
wasn't noticably worse than when the system was put in) then one of the
sysadmins (not DBA) was told by our FM supplier to analyze a half dozen tables
and associated indexes that were used by a new module that's due to go live
soon and had been loaded with some historical information that was required.
He did so and, so he claims, the system started running noticably slower
shortly after. He then decided to analyze all the rest of the tables and their
indexes. Performance got worse. Four days later they notified me (for
political reasons I have no involvement with this system or any other system
that has gone live) and basically said "it's running slow, fix it.". I don't
know if there were any existing stats which got over written (I've asked, still
waiting for a response), if there weren't I'm considering just deleting all the
stats and seeing if that helps.
This system is shutdown every night for backup so the plans get wiped out
anyway.
Stephen
--
It's better to ask a silly question than to make a silly assumption.
Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do not
consent to Internet email for messages of this kind. Opinions, conclusions and
other information in this message that do not relate to the official business
of this company shall be understood as neither given nor endorsed by it.
Other related posts:
