Inline STATISTICS cost figures

Michael Ebert

Inline STATISTICS cost figures
Hi List,

I've just done some measurements/comparisons on using REORG followed by
RUNSTATS vs. REORG...STATISTICS (inline Stats). The benchmarks were big
REORG...DISCARD jobs (not OLR) used in the maintenance of a history/backup
table. The biggest job discarded 18M rows from the TS and reloaded 86M
rows. The TS is compressed. 3 big indexes. DB2 V7.1, zOS 1.4.

Here are just the results/conclusions:

1. Inline STATS use about 15% _more_ CPU time (this is a pretty stable
figure).
2. Inline STATS use the same or _higher_ elapsed times (elapsed times can
vary a lot, however the machine was not at 100%).
3. The UTUT phase is about 55% longer (elapsed times - same remark).
4. In case the utility fails, you lose more resources spent.
5. In case the utility fails, you get Catalog inconsistencies: because
statistics that have already been updated in the Catalog won't be rolled
back (this is most important if you use OLR, which may fail in the LOGAPPLY
phase, leaving you with the old TS but the new Statistics)
6. If you're still on V6: you need to have lots of PTFs dealing with Inline
Stats issues (the same probably applies to old V7 maintenance levels)

In short, don't use Inline Stats.

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

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dave Nance

Re: Inline STATISTICS cost figures
(in response to Michael Ebert)
I can't say that I agree with you there. We use inline stats quite
extensively and did some benchmarks a few years ago and they were
cheaper. You mention that your reorg takes longer, but are you comparing
apples to apples here? The time you should be comparing to is the time
of your reorg plus the time of your standalone stats. We are V7 and stay
pretty current on maintenance, but we did do our benchmarks when we were
still on V6.

Dave Nance
First Health Services, Corp.
(804)527-6841


>>> [login to unmask email] 1/12/05 10:21:08 AM >>>
Hi List,

I've just done some measurements/comparisons on using REORG followed
by
RUNSTATS vs. REORG...STATISTICS (inline Stats). The benchmarks were
big
REORG...DISCARD jobs (not OLR) used in the maintenance of a
history/backup
table. The biggest job discarded 18M rows from the TS and reloaded 86M
rows. The TS is compressed. 3 big indexes. DB2 V7.1, zOS 1.4.

Here are just the results/conclusions:

1. Inline STATS use about 15% _more_ CPU time (this is a pretty
stable
figure).
2. Inline STATS use the same or _higher_ elapsed times (elapsed times
can
vary a lot, however the machine was not at 100%).
3. The UTUT phase is about 55% longer (elapsed times - same remark).
4. In case the utility fails, you lose more resources spent.
5. In case the utility fails, you get Catalog inconsistencies: because
statistics that have already been updated in the Catalog won't be
rolled
back (this is most important if you use OLR, which may fail in the
LOGAPPLY
phase, leaving you with the old TS but the new Statistics)
6. If you're still on V6: you need to have lots of PTFs dealing with
Inline
Stats issues (the same probably applies to old V7 maintenance levels)

In short, don't use Inline Stats.

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

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm



"MMS <fhmail.firsthealth.com>" made the following annotations.
------------------------------------------------------------------------------
This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.
=====

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Lynne Flatley

Re: Inline STATISTICS cost figures
(in response to Dave Nance)
We had the same problem about a month or so ago in which the OLR with
in-line stats failed to complete (the tablespace being reorged was being
heavily used and the job just couldn't find the window to get the drains
done) and the stats on one index were funky afterwards. We too are on
V7. We haven't noticed the additional CPU time expense that you've
noted...but we weren't looking for it.

Lynne Flatley
Liberty Mutual Insurance Co.
Personal Market Systems - DBA
Portsmouth, NH 03802-7201
( 603-245-9334

"You cannot strengthen the weak by weakening the strong. You cannot help
the wage-earner by pulling down the wage-payer. You cannot help the poor
by destroying the rich. You cannot help men permanently by doing for
them what they could and should do for themselves." - Abraham Lincoln


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Michael Ebert
Sent: Wednesday, January 12, 2005 10:21 AM
To: [login to unmask email]
Subject: [DB2-L] Inline STATISTICS cost figures


Hi List,

I've just done some measurements/comparisons on using REORG followed by
RUNSTATS vs. REORG...STATISTICS (inline Stats). The benchmarks were big
REORG...DISCARD jobs (not OLR) used in the maintenance of a
history/backup table. The biggest job discarded 18M rows from the TS and
reloaded 86M rows. The TS is compressed. 3 big indexes. DB2 V7.1, zOS
1.4.

Here are just the results/conclusions:

1. Inline STATS use about 15% _more_ CPU time (this is a pretty stable
figure). 2. Inline STATS use the same or _higher_ elapsed times (elapsed
times can vary a lot, however the machine was not at 100%). 3. The UTUT
phase is about 55% longer (elapsed times - same remark). 4. In case the
utility fails, you lose more resources spent. 5. In case the utility
fails, you get Catalog inconsistencies: because statistics that have
already been updated in the Catalog won't be rolled back (this is most
important if you use OLR, which may fail in the LOGAPPLY phase, leaving
you with the old TS but the new Statistics) 6. If you're still on V6:
you need to have lots of PTFs dealing with Inline Stats issues (the same
probably applies to old V7 maintenance levels)

In short, don't use Inline Stats.

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

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Patrick Steurs

Re: Inline STATISTICS cost figures
(in response to Lynne Flatley)
Hi,



I did also some benchmarks on db2 v6 ( We are now on db2 v7 and z/os1.4
and did not rerun these jobs )

I ran a image-copy + reorg + runstats on a tables of .97GB with 3
indexes as 3 seperate jobs

I ran the same job as reorg + sortkeys + inline image-copy and inline
stats and we gained 55% in elpased time, but cpu-time went nearly 10%
up.



greetings



Patrick Steurs

DBA at National Bank of Belgium





-----Original Message-----
From: David Nance [mailto:[login to unmask email]
Sent: woensdag 12 januari 2005 16:37
To: [login to unmask email]
Subject: Re: [DB2-L] Inline STATISTICS cost figures



I can't say that I agree with you there. We use inline stats quite
extensively and did some benchmarks a few years ago and they were
cheaper. You mention that your reorg takes longer, but are you comparing
apples to apples here? The time you should be comparing to is the time
of your reorg plus the time of your standalone stats. We are V7 and stay
pretty current on maintenance, but we did do our benchmarks when we were
still on V6.



Dave Nance
First Health Services, Corp.
(804)527-6841


>>> [login to unmask email] 1/12/05 10:21:08 AM >>>
Hi List,

I've just done some measurements/comparisons on using REORG followed by
RUNSTATS vs. REORG...STATISTICS (inline Stats). The benchmarks were big
REORG...DISCARD jobs (not OLR) used in the maintenance of a
history/backup
table. The biggest job discarded 18M rows from the TS and reloaded 86M
rows. The TS is compressed. 3 big indexes. DB2 V7.1, zOS 1.4.

Here are just the results/conclusions:

1. Inline STATS use about 15% _more_ CPU time (this is a pretty stable
figure).
2. Inline STATS use the same or _higher_ elapsed times (elapsed times
can
vary a lot, however the machine was not at 100%).
3. The UTUT phase is about 55% longer (elapsed times - same remark).
4. In case the utility fails, you lose more resources spent.
5. In case the utility fails, you get Catalog inconsistencies: because
statistics that have already been updated in the Catalog won't be rolled
back (this is most important if you use OLR, which may fail in the
LOGAPPLY
phase, leaving you with the old TS but the new Statistics)
6. If you're still on V6: you need to have lots of PTFs dealing with
Inline
Stats issues (the same probably applies to old V7 maintenance levels)

In short, don't use Inline Stats.

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

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. < http://www.idugdb2-l.org./ > The IDUG List
Admins can be reached at [login to unmask email]
< http://www.idugdb2-l.org./ > Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

"MMS <fhmail.firsthealth.com>" made the following annotations.
------------------------------------------------------------------------
------
This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.

======


------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm



-----------------------------------------
Visit our website! http://www.nbb.be "DISCLAIMER: The content of this
e-mail message should not be construed as binding on the part of the
National Bank of Belgium (NBB) unless otherwise and previously stated. The
opinions expressed in this message are solely those of the author and do
not necessarily reflect NBB viewpoints, particularly when the content of
this message, or part thereof, is private by nature or does not fall within
the professional scope of its author."


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Ebert

Re: Inline STATISTICS cost figures
(in response to Patrick Steurs)
Yes, of course I compare REORG+RUNSTATS figures vs. REORG...STATISTICS
figures.

I don't claim that these figures are correct in all cases, just in the
significant examples I selected. However, I don't see how you can have more
than tiny improvements from inline stats even in optimal cases - both REORG
and RUNSTATS are CPU-intensive and the cost for the additional I/O is small
(both CPU and elapsed time). Even then, you have all the other issues I
mentioned.

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





From: David Nance <[login to unmask email]>@IDUGDB2-L.ORG on 12-01-2005 10:37
EST

Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>



To: [login to unmask email]


cc:






Subjec Re: [DB2-L] Inline STATISTICS cost
t: figures








I can't say that I agree with you there. We use inline stats quite
extensively and did some benchmarks a few years ago and they were cheaper.
You mention that your reorg takes longer, but are you comparing apples to
apples here? The time you should be comparing to is the time of your reorg
plus the time of your standalone stats. We are V7 and stay pretty current
on maintenance, but we did do our benchmarks when we were still on V6.

Dave Nance
First Health Services, Corp.
(804)527-6841


>>> [login to unmask email] 1/12/05 10:21:08 AM >>>
Hi List,

I've just done some measurements/comparisons on using REORG followed by
RUNSTATS vs. REORG...STATISTICS (inline Stats). The benchmarks were big
REORG...DISCARD jobs (not OLR) used in the maintenance of a history/backup
table. The biggest job discarded 18M rows from the TS and reloaded 86M
rows. The TS is compressed. 3 big indexes. DB2 V7.1, zOS 1.4.

Here are just the results/conclusions:

1. Inline STATS use about 15% _more_ CPU time (this is a pretty stable
figure).
2. Inline STATS use the same or _higher_ elapsed times (elapsed times can
vary a lot, however the machine was not at 100%).
3. The UTUT phase is about 55% longer (elapsed times - same remark).
4. In case the utility fails, you lose more resources spent.
5. In case the utility fails, you get Catalog inconsistencies: because
statistics that have already been updated in the Catalog won't be rolled
back (this is most important if you use OLR, which may fail in the LOGAPPLY
phase, leaving you with the old TS but the new Statistics)
6. If you're still on V6: you need to have lots of PTFs dealing with Inline
Stats issues (the same probably applies to old V7 maintenance levels)

In short, don't use Inline Stats.

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

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Ebert

Re: Inline STATISTICS cost figures
(in response to Michael Ebert)
Well, that is not a valid example - you change too many things in one go,
so you cannot directly tell which caused what.

Also, you probably mean REORG+IC+RUNSTATS vs. REORG SORTKEYS Inline IC
Inline Stats (i.e. standalone IC after the REORG, not before). In that
case, you would get the elapsed time improvement both from the SORTKEYS
(resulting in parallel index build) and the inline IC which, as standalone,
would simply add elapsed time (IC is I/O-bound). The Inline Stats would not
result in an elapsed time change but might be the cause for the CPU
increase (together with the SORTKEYS).

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




From: Steurs Patrick <[login to unmask email]>@IDUGDB2-L.ORG on 12-01-2005
16:45

Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>



To: [login to unmask email]


cc:






Subjec Re: [DB2-L] Inline STATISTICS cost
t: figures








Hi,

I did also some benchmarks on db2 v6 ( We are now on db2 v7 and z/os1.4
and did not rerun these jobs )
I ran a image-copy + reorg + runstats on a tables of .97GB with 3 indexes
as 3 seperate jobs
I ran the same job as reorg + sortkeys + inline image-copy and inline stats
and we gained 55% in elpased time, but cpu-time went nearly 10% up.

greetings

Patrick Steurs
DBA at National Bank of Belgium

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ray Janes

Re: Inline STATISTICS cost figures
(in response to Michael Ebert)
It's been a few years, but here is my recollection. I ran online reorg with
and with out inline stats. I then ran stand alone stats. Adding together
the OLR without stats plus the stand alone stats yielded high CPU and wall
clock time compared to OLR with inline stats. Meaning, if you are going to
do stats anyway, do them inline.
Yes, early on there were 'issues' with inline stats. But I think those are
all addressed by IBM.

-----Original Message-----
From: Michael Ebert [mailto:[login to unmask email]
Sent: Wednesday, January 12, 2005 10:00 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Inline STATISTICS cost figures


Well, that is not a valid example - you change too many things in one go,
so you cannot directly tell which caused what.

Also, you probably mean REORG+IC+RUNSTATS vs. REORG SORTKEYS Inline IC
Inline Stats (i.e. standalone IC after the REORG, not before). In that
case, you would get the elapsed time improvement both from the SORTKEYS
(resulting in parallel index build) and the inline IC which, as standalone,
would simply add elapsed time (IC is I/O-bound). The Inline Stats would not
result in an elapsed time change but might be the cause for the CPU
increase (together with the SORTKEYS).

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




From: Steurs Patrick <[login to unmask email]>@IDUGDB2-L.ORG on 12-01-2005
16:45

Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>



To: [login to unmask email]


cc:






Subjec Re: [DB2-L] Inline STATISTICS cost
t: figures








Hi,

I did also some benchmarks on db2 v6 ( We are now on db2 v7 and z/os1.4
and did not rerun these jobs )
I ran a image-copy + reorg + runstats on a tables of .97GB with 3 indexes
as 3 seperate jobs
I ran the same job as reorg + sortkeys + inline image-copy and inline stats
and we gained 55% in elpased time, but cpu-time went nearly 10% up.

greetings

Patrick Steurs
DBA at National Bank of Belgium

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm