RE: stats document links.
- From: Herring Dave - dherri <Dave.Herring@xxxxxxxxxx>
- To: "Joel.Patterson@xxxxxxxxxxx" <Joel.Patterson@xxxxxxxxxxx>, "dmann99@xxxxxxxxx" <dmann99@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 30 Mar 2011 15:17:00 +0000
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@xxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Joel.Patterson@xxxxxxxxxxx
Sent: Wednesday, March 23, 2011 12:04 PM
To: dmann99@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of David Mann
Sent: Wednesday, March 23, 2011 11:50 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: stats document links.
> From: <Joel.Patterson@xxxxxxxxxxx>
> 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
