Re: When stats trash your performance

On 07/12/05, Allen, Brandon <Brandon.Allen_at_oneneck.com> wrote:
>
> 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.
>
>
>

Brandon,

Thanks. So I wasn't too far off the mark, gathering stats is usually good but it can sometimes cause problems if the application was designed for RBO. Given that this app has been around a while (it's been with the organisation longer than I have (6 years on Friday of next week) and hasn't had a major upgrade of the app (I believe it was originally running against 8.0) in that time) plus it's a niche product, I suspect that it was built with RBO in mind.

As I said in my reply to John just now, there are some political/organisational issues around management of databases that I'm working to correct. I'll look into the parameters you mention. I suspect that the guy who ran the stats collection probably used the old analyze command rather than dbms_stats.

STATSPACK isn't currently installed on this database but I'm trying to get it on there.

Stephen

--
It's better to ask a silly question than to make a silly assumption.

http://stephensorablog.blogspot.com/

--
http://www.freelists.org/webpage/oracle-l