RE: stats document links.
Date: Wed, 30 Mar 2011 15:17:00 +0000
Message-ID: <BD475CE0B3EE894DA0CAB36CE2F7DEB4019AF777_at_LITIGMBCRP02.Corp.Acxiom.net>
One more thing I'd like to add is to consider the impact of collecting stats, especially if the results don't change (I fall in line with what Wolfgang Breitling preaches). Let's say you have a table that's updated frequently yet the distribution of values on indexed columns don't change much. Or that regardless of the changes, you want the same, key queries using the same xplans. Then gathering statistics would NOT be what you want, because new stats could convince the optimizer to do something you don't want it to do. Obviously this is in an OLTP-type env where there aren't a lot of adhoc queries.
Also note that gathering stats invalidates xplans in the shared pool for the associated tables. If key queries use a lot of binds and you're under 10g, gathering stats could INCREASE poor xplans, as the process forces new plans to be generated and with bind variable peeking you've got more of a chance to get poor xplans generated.
I'm not advocating to never gather stats. I'm just suggesting you review why you want to gather stats and to consider what you expect to happen when changes happen.
DAVID HERRING
DBA
Acxiom Corporation
EML dave.herring_at_acxiom.com TEL 630.944.4762 MBL 630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA WWW.ACXIOM.COM The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joel.Patterson_at_crowley.com
Sent: Wednesday, March 23, 2011 12:04 PM
To: dmann99_at_gmail.com; oracle-l_at_freelists.org
Subject: RE: stats document links.
Thanks everyone.
Someone also sent Relation between Table Monitoring and STATISTICS_LEVEL parameter in 10g [ID 252597.1] which I pulled enough out of to satisfy.
The other links and suggestions are also appreciated.
Joel Patterson
Database Administrator
904 727-2546
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Mann
Sent: Wednesday, March 23, 2011 11:50 AM
To: oracle-l_at_freelists.org
Subject: Re: stats document links.
> From: <Joel.Patterson_at_crowley.com>
> Date: Tue, 22 Mar 2011 13:18:23 -0400
> Subject: stats document links.
>
> I use default stats collection method on 10.2.0.4 database. I get questioned (via email) about someones attention being drawn to the fact that many tables have not had stats collected in months, and some have had them recently.
> Does anyone off hand have some nice links I an send these people, I believe this last on is a project manager type.
I get this too... Usually consultants brought into check app performance and looking for a finger to point. Managers are CCed, momentum builds for blaming the DBAs. Good times.
After educating them a little on the how/when/why of stats I usually show consultants and customers what is going on with key tables via the [DBA_|ALL_|USER_]TAB_MODIFICATIONS view.
Often the case is explaining that activity != modifications and that stats are stale once they don't properly describe the data they are supposed to represent, not when they are are generated X days ago.
If they are convinced statistics are the issue then there are ways to dig deeper and see if inaccurate statistics are causing an issue with executing the query, 10053 trace, and running the query with GATHER_PLAN_STATISTICS hint are good tools.
-- Dave Mann www.brainio.us www.ba6.us - Database Stuff - http://www.ba6.us/rss.xml -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 30 2011 - 10:17:00 CDT