RE: When stats trash your performance

A problem I've run into before is people running the old ANALYZE statements without specifying a sample size. This will default to a sample size of only 1064 rows (regardless of tables size)! If that is the case, you would certainly get better results by doing a compute, or at least estimate with >= 5% sample size, regardless of whether it is done with dbms_stats or analyze. Dbms_stats is generally preferred, and highly recommended by Oracle, but known to be buggy so you have to be careful although I believe most of the bugs were worked out by 9.2.0.6.    

-----Original Message-----

From: stephen booth [mailto:stephenbooth.uk_at_gmail.com] Sent: Wednesday, December 07, 2005 2:40 PM To: Allen, Brandon
Cc: oracle-l_at_freelists.org
Subject: Re: When stats trash your performance

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

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.

--

http://www.freelists.org/webpage/oracle-l Received on Wed Dec 07 2005 - 15:56:39 CST