Willem,

I was not trying to contradict your statement. As you clarified, exceptions
needs to be handled in both cases. The choice to analyze full or at a lower
percentage totally depends on the size of the db and the horsepower of the
server.

Regards,

Shaji.


--- In psftdba@yahoogroups.com, <Willem.Leenen@...> wrote:
>
> Shaji,
>
>
>
> I don't see any difference in our opinions. If your posting was meant to
> counter mine, then one of us wasn't clear.
>
>
>
> The only exception is in the last line. You state that when using a
> sample-size for gathering of stats, a need for a baseline with
> exception handling is required. I don't see the connection, because this
> exception handling needs to be done even if you are able to gather a
> 100% of the statistics. In fact, that is what I do: I need to manually
> set a few index-stats, even with my 100% gathering strategy.
>
>
>
> Regards,
>
> Willem
>
>
>
>
>
>
>
>
>
> ________________________________
>
> From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf
> Of shajivps
> Sent: dinsdag 7 juli 2009 16:37
> To: psftdba@yahoogroups.com
> Subject: PeopleSoft DBA Forum Re: DBMS_STATS and Oracle 10g
>
>
>
>
>
>
>
>
> Willem,
>
> Decision to collect complete statistics can be made only based on the
> size of the db and how long it takes to capture the stats on a daily
> basis. I have a 3TB finance db and I cant afford to run a full stats on
> this server which has 4 X 4.2ghz IBM cores. It will take close to 24
> hours to run this (timings from prior testing).
>
> I have been using 15% for the tables and 100% for indexes for the past 8
> years. It worked well in 9i and has been working fine in our 10.2.0.3
> (psoft 9) db for the past year. I also collect stats only on objects
> that has changes + new objects (list_empty and list_stale) to keep the
> list of objects small. This small list itself takes around 2-3 hours
> everyday to analyze (partition level stats + global stats for
> partitioned objects).
>
> If the db is smaller and you can get away with 100% , then it is good.
> Otherwise, you have to establish a baseline and then handle all the
> exceptions with hints, stats override (manual setting) etc.,
>
> Regards,
>
> Shaji.
>
> --- In psftdba@yahoogroups.com <mailto:psftdba%40yahoogroups.com> ,
> <Willem.Leenen@> wrote:
> >
> > Hi Brian,
> >
> >
> >
> > Your question cannot be answered with the information provided.
> > Calculation of statistics is an optimalisation game, where the time to
> > gather statistics needs to be balanced with the quality of the
> > statistics. There are a few changes regarding statistics when moving
> to
> > Oracle 10. (For example, the table monitoring is default switched on
> in
> > 10, where in 9 it's default switched off.)
> >
> >
> >
> > For me, I like to calculate as complete statistics as possible, this
> > includes all histograms and not taking a sample size. The database is
> > small enough to afford such an approach.
> >
> >
> >
> > Alternatively, I heard Jonathan Lewis say - in one of his lectures on
> > the optimizer - that he knows someone in a DWH environment who
> disabled
> > all statistics gathering and relies purely on the Dynamic Sampling (I
> > forgot which level he was on, I think 4).
> >
> >
> >
> > You need to determine your place between these two extremes based on
> > available time / available CPU and the robustness of the execution
> > plans. An 10046 trace can be helpful in determining this robustness,
> >
> >
> >
> > Regards,
> >
> >
> >
> > Willem Leenen
> >
> > Oracle DBA
> >
> >
> >
> > ________________________________
> >
> > From: psftdba@yahoogroups.com <mailto:psftdba%40yahoogroups.com>
> [mailto:psftdba@yahoogroups.com <mailto:psftdba%40yahoogroups.com> ] On
> Behalf
> > Of Brian Thompson
> > Sent: dinsdag 7 juli 2009 11:31
> > To: psftdba@yahoogroups.com <mailto:psftdba%40yahoogroups.com>
> > Subject: PeopleSoft DBA Forum DBMS_STATS and Oracle 10g
> >
> >
> >
> >
> >
> >
> >
> >
> > Hello Everyone,
> >
> >
> >
> > We have upgraded from Oracle 9i to 10g (10.2.0.4).
> >
> > We are trying to understand the behaviour of DBMS_STATS in our new
> > environment.
> >
> >
> >
> > In particular the ESTIMATE PERCENT parameter.
> >
> > In 9i this defaulted to 100%, but in 10g it defaults to
> > AUTO_SAMPLE_SIZE.
> >
> >
> >
> > And, as we understand it, the value of AUTO_SAMPLE_SIZE is calculated
> > for
> >
> > EACH table based on information the optimizer holds about the table,
> >
> > and so the actual value for ESTIMATE PERCENT, for a table in 10g,
> >
> > could be anything from 1% to 100 % !
> >
> >
> >
> > At the moment we have set ESTIMATE_PERCENT to 100%,
> >
> > but we are wondering if this is over-kill and should we just let
> >
> > it default to AUTO_SAMPLE_SIZE ?
> >
> >
> >
> > Does anybody have any thoughts to share about this issue ?
> >
> >
> >
> > Thanks in advance,
> >
> >
> >
> > Best Regards,
> >
> > Brian Thompson
> >
> > Diodes Zetex Semiconductors Ltd.
> >
> >
> >
> > __________________________________________________________
> >
> > Diodes Incorporated
> >
> > www.zetex.com <http://www.zetex.com/ <http://www.zetex.com/> >
> > www.diodes.com <http://www.diodes.com/ <http://www.diodes.com/> >
> >
> > E-MAILS are susceptible to interference. You should not assume that
> > the contents originated from the sender or Diodes Incorporated or that
> > they
> > have been accurately reproduced from their original form.
> > Diodes Incorporated accepts no responsibility for information, errors
> or
> > omissions in
> > this e-mail nor for its use or misuse nor for any act committed or
> > omitted in connection with this communication.
> > If in doubt, please verify the authenticity with the sender.
> > _________________________________________________________
> >
> >
> >
> > ----------------------------------------------------------
> > ATTENTION:
> > The information in this electronic mail message is private and
> > confidential, and only intended for the addressee. Should you
> > receive this message by mistake, you are hereby notified that
> > any disclosure, reproduction, distribution or use of this
> > message is strictly prohibited. Please inform the sender by
> > reply transmission and delete the message without copying or
> > opening it.
> >
> > Messages and attachments are scanned for all viruses known.
> > If this message contains password-protected attachments, the
> > files have NOT been scanned for viruses by the ING mail domain.
> > Always scan attachments before opening them.
> > ----------------------------------------------------------
> >
>
>
>
> -----------------------------------------------------------------
> ATTENTION:
> The information in this electronic mail message is private and
> confidential, and only intended for the addressee. Should you
> receive this message by mistake, you are hereby notified that
> any disclosure, reproduction, distribution or use of this
> message is strictly prohibited. Please inform the sender by
> reply transmission and delete the message without copying or
> opening it.
>
> Messages and attachments are scanned for all viruses known.
> If this message contains password-protected attachments, the
> files have NOT been scanned for viruses by the ING mail domain.
> Always scan attachments before opening them.
> -----------------------------------------------------------------
>