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, <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@yahoogroups.com] On Behalf
> Of Brian Thompson
> Sent: dinsdag 7 juli 2009 11:31
> To: psftdba@yahoogroups.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/>
> 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.
> -----------------------------------------------------------------
>