RUNSTATS ... SAMPLE

Smike Toppins

RUNSTATS ... SAMPLE
Can anyone give me any feedback on experiences with using the SAMPLE clause
of the RUNSTATS utility?


SMike Toppins
Great-West Life
[login to unmask email]
(303) 737-5094



Michael Ebert

Re: RUNSTATS ... SAMPLE
(in response to Smike Toppins)
The CPU time saving is not what you might expect - it is not inversely
proportional with the SAMPLE parm. With SAMPLE 15, you get about 50% CPU
savings. With SAMPLE 1, the savings are around 60%, so it only makes sense
to use SAMPLE parms from 10 to maybe 25. Also, you probably shouldn't use
TABLE(ALL) SAMPLE nn with multi-table TSs that have both large and small
tables, because it might give you seriously wrong info for the small tables
(I know that BMCSTATS does; I haven't checked this for IBMs RUNSTAT).

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany



Can anyone give me any feedback on experiences with using the SAMPLE clause
of the RUNSTATS utility?


SMike Toppins
Great-West Life
[login to unmask email]
(303) 737-5094



Grant Allen

Re: RUNSTATS ... SAMPLE
(in response to Michael Ebert)
Toppins, Smike[SMTP:[login to unmask email] wrote:
> > Can anyone give me any feedback on experiences with using the SAMPLE
> clause
> > of the RUNSTATS utility?
>
Smike,

My experience on several of platforms (DB2, Oracle, SQL Server) is pretty
similar. Sampling for statistics is safe if you know you have a relatively
normal distribution, especially across key/indexed fields. If you suffer
from skew in any way on a table, consider larger sample sizes, or full
stats.

Ciao
Fuzzy
:-)

----------------------------------------------------------------------------
"Woo Hoo!" H. Simpson
----------------------------------------------------------------------------
The contents of this post are my opinions only
If swallowed seek medical advice

(Apologies for the excess signature)
This email message (and attachments) may contain information confidential to
TOWER Software. If you are not the intended recipient you cannot use,
distribute or copy the message or message attachments. If you are not the
intended recipient, please notify the sender by return email immediately and
delete all copies of the message and attachments. Opinions, conclusions and
other information in this message and attachments that do not relate to the
official business of TOWER Software, are not given or endorsed by it.



Patric Becker

Re: RUNSTATS ... SAMPLE
(in response to Grant Allen)
Hi !

What I´ve learned about using "SAMPLE" is that SAMPLE only analyzes the
first n percent of the table. It might be O.K. for "normally distributed"
data, but maybe you won´t get what you want if the sampled column is an
index-column (only sampling first n percent of an index column may result
in unpredictable access-paths).

So my opinion is : be careful using SAMPLE :-)

Regards,
Patric



Jim Ruddy

Re: RUNSTATS ... SAMPLE
(in response to Patric Becker)
IBM RUNSTATS samples the entire table not the first n percent.

Jim Ruddy
DB2 for z/OS and OS/390 Development



Isaac Yassin

Re: RUNSTATS ... SAMPLE
(in response to Jim Ruddy)
Hi,
Last time I checked SAMPLE at 25% it was nearly the same as full stats.
So - I gave up on SAMPLE ...

Isaac Yassin
[login to unmask email]
----- Original Message -----
From: <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Sunday, January 06, 2002 12:37 AM
Subject: Re: RUNSTATS ... SAMPLE


> The CPU time saving is not what you might expect - it is not inversely
> proportional with the SAMPLE parm. With SAMPLE 15, you get about 50% CPU
> savings. With SAMPLE 1, the savings are around 60%, so it only makes sense
> to use SAMPLE parms from 10 to maybe 25. Also, you probably shouldn't use
> TABLE(ALL) SAMPLE nn with multi-table TSs that have both large and small
> tables, because it might give you seriously wrong info for the small tables
> (I know that BMCSTATS does; I haven't checked this for IBMs RUNSTAT).
>
> Dr. Michael Ebert
> DB2 Database Administrator
> aMaDEUS Data Processing
> Erding / Munich, Germany
>
>
>
> Can anyone give me any feedback on experiences with using the SAMPLE clause
> of the RUNSTATS utility?
>
>
> SMike Toppins
> Great-West Life
> [login to unmask email]
> (303) 737-5094
>
>
> DB2-L webpage at http://www.ryci.com/db2-l. The
owners of the list can
>