When stats trash your performance
- From: stephen booth <stephenbooth.uk@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 7 Dec 2005 18:59:01 +0000
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.
Other related posts:
