Having problems with message search?
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
>
> 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
> Of Brian Thompson
> Sent: dinsdag 7 juli 2009 11:31
> To: psftdba@yahoogroups
> 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_
>
>
>
> And, as we understand it, the value of AUTO_SAMPLE_
> 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_
>
>
>
> 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.
> www.diodes.com <http://www.diodes.
>
> 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. -----------------------------------------------------------------
Send Email
