|
RE: PeopleSoft DBA Forum Digest Number 1233
Hi SP,
Thank you for the hints. I am surprised to
hear that the optimizer_dynamic_sampling should be set to zero for the PPLsoft
database. I am on Oracle 9, and in the red paper for Ora 9 there was no
mentioning of setting this parameter. I put this sampling parameter on 2 on my
database because of the following incident I once solved:
A process was traditionally taking an unusual
long time (many hours). This process used a temporary table ( TAO# ) for
temporal data storage. After investigation it appeared that the process filled
this temporary table, but did NOT calculate statistics after this filling. So
oracle used the available statistics on this temporary table. And these
statistics were gathered at a time that the table was empty, so the optimizer
was told that the table was empty, where in fact if was pretty big after the
filling. This caused the optimizer to choose a wrong execution plan, causing
performance havoc.
So the process ran with the wrong
statistics information. After this process updated the statistics after the
filling of the TAO table, the process ran less than 20 minutes.
I got worried if there would be more of
such cases, so I deleted the statistics of all temp tables and put a dynamic
sampling on 2, so that statistics would be calculated, even if the developer
forgot about this statistics-gathering setting.
Oracle 10 has already a default setting of
this parameter of 2 ( if I remember correctly). I wonder how it could be helpful
for the optimizer to put this parameter back to 0, it’s doesn’t
make sense to me. Thanks, I will check this advice.
About your remark of ESTIMATE_PERCENT=>15,
yes I agree that gathering about 15 % should be sufficient. Even 5% can give
surprisingly good statistics in my tests. There are exceptions to this rule,
where there are better results when you have more statistics. ( where MORE
statistics is MOSTLY the same as BETTER statistics). Because I could afford it,
I calculated 100% of the statistics of most of the tables, so I don’t
have to check with each performance incident if a 100% gathering makes a
difference. Call it laziness J. I noticed some plan instability with some reports and processes,
and I think that the 100% gathering just made the plans more stable – but
I have no figures to support that opinion yet.
So I agree that 15% is a good starting
point, and deviate from that when your tests tell you to do so.
Regards,
Willem
From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of SP
Sent: woensdag 8 juli 2009 20:18
To: psftdba@yahoogroups.com
Subject: Re: PeopleSoft DBA Forum
Digest Number 1233
There is a red paper on PeopleSoft site which talks
"PeopleSoft Enterprise Performance on Oracle 10g Database"
please read it ....
BTW per this paper optimizer_dynamic_sampling
should be ZERO for PeopleSoft applications....
For me (Pelase do not shoot the messenger)
ESTIMATE_PERCENT=>15 gave required performence - I also did comparision by
setting this value 100% - same results....
Cross verified with Oracle they suggested any value
between 5% to 15 % is good.
--- On Wed, 7/8/09, psftdba@yahoogroups.com
<psftdba@yahoogroups.com>
wrote:
From: psftdba@yahoogroups.com
<psftdba@yahoogroups.com>
Subject: PeopleSoft DBA Forum Digest Number 1233
To: psftdba@yahoogroups.com
Date: Wednesday, July 8, 2009, 10:35 AM
Messages
In This Digest (1 Message)
Message
1a.
Tue Jul 7, 2009
8:33 am (PDT)
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.
> ------------ --------- --------- --------- --------- --------- -
>
Create New Topic | Visit Your Group on the Web
|
-----------------------------------------------------------------
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.
-----------------------------------------------------------------
|
<Willem.Leenen@...>
Willem.Leenen@...
 Send Email
|
Having problems with message search?
