distributed STC high CPU question

Michael Wickman

distributed STC high CPU question
We are using a CRM package (Siebel) on os/390 2.10 DB2 version 7.1. We are seeing a high CPU utilization when we do a distributed query and want to understand why. When we do a query thru TSO SPUFI or batch, we see the requesting user/job idle while the DB2 address spaces retrieve the results and no measurable increase in cpu utilization. But doing the same thing in the distributed environment, we see the DIST started task ( and it's enclaves) using high amounts of CPU while the query is running.

Can anyone explain what may be causing this? Is this just the nature of the beast or are there some tuning activies the DBA/DB2 Systems Programmer needs to address (I'm a MVS Sys Prog.)

Thanks.

Thanks in advance.
Mike

Roger Miller

Re: distributed STC high CPU question
(in response to Michael Wickman)
On Thu, 2 Jan 2003 10:16:36 -0600, Michael Wickman <[login to unmask email]>
wrote:

>We are using a CRM package (Siebel) on os/390 2.10 DB2 version 7.1. We
are seeing a high CPU utilization when we do a distributed query and want
to understand why. When we do a query thru TSO SPUFI or batch, we see the
requesting user/job idle while the DB2 address spaces retrieve the results
and no measurable increase in cpu utilization. But doing the same thing in
the distributed environment, we see the DIST started task ( and it's
enclaves) using high amounts of CPU while the query is running.
>
>Can anyone explain what may be causing this? Is this just the nature of
the beast or are there some tuning activies the DBA/DB2 Systems Programmer
needs to address (I'm a MVS Sys Prog.)
>
>Thanks.
>
>Thanks in advance.
>Mike
>
>

What you described is typical. In TSO and batch, we use the task and cpu
shows in that address space. For distributed access, there is no MVS task
for us to use, so we have to create one - in the DIST address space.

Then the task is generally to find the expensive SQL statements from the
dynamic statement cache monitoring, and see how to tune with statistics,
reorg, creating an index, ...

Roger Miller



Edward Long

Re: distributed STC high CPU question
(in response to Roger Miller)
While this may be Working as Designed, there have been
several PTF's with close to this fact pattern.

There is one that is a hard loop in DDF caused by
complex SQL. This would appear,on a multi-processor,
as high CPU utilization; on a UNI its a disaster(IPL).

I'd encourage you to make sure your very current on
DDF related maintenance. There is also some DB2
Connect performance related maintenance in Fixpak7.
--- Roger Miller <[login to unmask email]> wrote:
> On Thu, 2 Jan 2003 10:16:36 -0600, Michael Wickman
> <[login to unmask email]>
> wrote:
>
> >We are using a CRM package (Siebel) on os/390 2.10
> DB2 version 7.1. We
> are seeing a high CPU utilization when we do a
> distributed query and want
> to understand why. When we do a query thru TSO
> SPUFI or batch, we see the
> requesting user/job idle while the DB2 address
> spaces retrieve the results
> and no measurable increase in cpu utilization. But
> doing the same thing in
> the distributed environment, we see the DIST started
> task ( and it's
> enclaves) using high amounts of CPU while the query
> is running.
> >
> >Can anyone explain what may be causing this? Is
> this just the nature of
> the beast or are there some tuning activies the
> DBA/DB2 Systems Programmer
> needs to address (I'm a MVS Sys Prog.)
> >
> >Thanks.
> >
> >Thanks in advance.
> >Mike
> >
> >
>
> What you described is typical. In TSO and batch, we
> use the task and cpu
> shows in that address space. For distributed
> access, there is no MVS task
> for us to use, so we have to create one - in the
> DIST address space.
>
> Then the task is generally to find the expensive SQL
> statements from the
> dynamic statement cache monitoring, and see how to
> tune with statistics,
> reorg, creating an index, ...
>
> Roger Miller
>
>
> To change your subscription options or to cancel
> your subscription visit the DB2-L webpage at
> http://listserv.ylassoc.com. The owners of the list
> can be reached at
[login to unmask email]


=====
Edward Long



Mike Vaughan

Re: distributed STC high CPU question
(in response to Edward Long)
I would like to try to expand on this topic just a little. We have had a real push recently to deteremine what causes high CPU in our DB2 started tasks. I can understand why "DIST" would be high if you run alot of distributed work, but what about DBM1 and MSTR? If the CPU used by a batch job shows in that tasks address space, then what accounts for DBM1/MSTR's time? I'm assuming that an increase in application activity will correspond to an increase in DBM1/MSTR time, but what factors influence this?
In other words, if I submit a batch job that runs some SQL, what portion of the cputime will show up on the batch batch application and what does the MSTR/DBM1 pick up? My guess is this probably depends on what type of activity the application is doing, but what is the breakdown?
Looking at this another way, if I go out and look at tasks currently running on our systems, DBM1 and MSTR consistently show very high cpu usage -- what is contributing?
I have been able to pick off some pieces through testing/observation, for example DDL activity that causes dataset activity (create tablespace/index) appears to result in high DBM1 cpu and low cpu attributed to the requesting address space, but I would like to think there is a comprehensive list out there somewhere (I'd be more than happy to hear a "RTFM" response if it can just tell me WHICH "M"). Personally, the more I read about this topic the less I know (and it sounds like some of the rules are changing when the requesting application is CICS).
This is really more than just a curiosity - as we look at tuning applications I have a feeling we are not taking everything into account. For example, just knowing that a change to an application reduces it's cputime by 10 seconds may not account for a 15 second increase in DB2DBM1. You can make the same correlation between comparing IBM and OEM utilities -- if you don't know what is being consumed by DBM1, you can't make a fair overall comparison. Does anyone have a good grasp on this, or know where I can find a good breakdown?

Thanks,
Mike.
-----Original Message-----
From: Roger Miller [mailto:[login to unmask email]
Sent: Thursday, January 02, 2003 12:13 PM
To: [login to unmask email]
Subject: Re: distributed STC high CPU question


On Thu, 2 Jan 2003 10:16:36 -0600, Michael Wickman <[login to unmask email]>
wrote:

>We are using a CRM package (Siebel) on os/390 2.10 DB2 version 7.1. We
are seeing a high CPU utilization when we do a distributed query and want
to understand why. When we do a query thru TSO SPUFI or batch, we see the
requesting user/job idle while the DB2 address spaces retrieve the results
and no measurable increase in cpu utilization. But doing the same thing in
the distributed environment, we see the DIST started task ( and it's
enclaves) using high amounts of CPU while the query is running.
>
>Can anyone explain what may be causing this? Is this just the nature of
the beast or are there some tuning activies the DBA/DB2 Systems Programmer
needs to address (I'm a MVS Sys Prog.)
>
>Thanks.
>
>Thanks in advance.
>Mike
>
>

What you described is typical. In TSO and batch, we use the task and cpu
shows in that address space. For distributed access, there is no MVS task
for us to use, so we have to create one - in the DIST address space.

Then the task is generally to find the expensive SQL statements from the
dynamic statement cache monitoring, and see how to tune with statistics,
reorg, creating an index, ...

Roger Miller






Joel Goldstein

Re: distributed STC high CPU question
(in response to Mike Vaughan)
Mike,
Certain functions, such as prefetch are charged to the DBM1 address space,
while logging and any backout costs are charged to
MSTR. If your batch job does a TS scan, then the cost of all the SP I/O
goes to DBM1, as SRB time.

If you are interested, I have a chart that shows where most of the
standard function CPU charges go, and whether they are as TCB or SRB time..

Regards,
Joel



Message text written by DB2 Data Base Discussion List
> I would like to try to expand on this topic just a little. We have had
a real push recently to deteremine what causes high CPU in our DB2 started
tasks. I can understand why "DIST" would be high if you run alot of
distributed work, but what about DBM1 and MSTR? If the CPU used by a batch
job shows in that tasks address space, then what accounts for DBM1/MSTR's
time? I'm assuming that an increase in application activity will
correspond to an increase in DBM1/MSTR time, but what factors influence
this?
In other words, if I submit a batch job that runs some SQL, what portion
of the cputime will show up on the batch batch application and what does
the MSTR/DBM1 pick up? My guess is this probably depends on what type of
activity the application is doing, but what is the breakdown?
Looking at this another way, if I go out and look at tasks currently
running on our systems, DBM1 and MSTR consistently show very high cpu usage
-- what is contributing?
I have been able to pick off some pieces through testing/observation,
for example DDL activity that causes dataset activity (create
tablespace/index) appears to result in high DBM1 cpu and low cpu attributed
to the requesting address space, but I would like to think there is a
comprehensive list out there somewhere (I'd be more than happy to hear a
"RTFM" response if it can just tell me WHICH "M"). Personally, the more I
read about this topic the less I know (and it sounds like some of the rules
are changing when the requesting application is CICS).
This is really more than just a curiosity - as we look at tuning
applications I have a feeling we are not taking everything into account.
For example, just knowing that a change to an application reduces it's
cputime by 10 seconds may not account for a 15 second increase in DB2DBM1.
You can make the same correlation between comparing IBM and OEM utilities
-- if you don't know what is being consumed by DBM1, you can't make a fair
overall comparison. Does anyone have a good grasp on this, or know where I
can find a good breakdown?

Thanks,
Mike.<



Mike Vaughan

Re: distributed STC high CPU question
(in response to Joel Goldstein)
Thank you for the response Joel. I would really appreciate seeing the chart you mentioned if you could pass it along.

Thanks,
Mike.
-----Original Message-----
From: Joel Goldstein
To: [login to unmask email]
Sent: 1/7/03 3:42 PM
Subject: Re: distributed STC high CPU question

Mike,
Certain functions, such as prefetch are charged to the DBM1 address
space,
while logging and any backout costs are charged to
MSTR. If your batch job does a TS scan, then the cost of all the SP I/O
goes to DBM1, as SRB time.

If you are interested, I have a chart that shows where most of the
standard function CPU charges go, and whether they are as TCB or SRB
time..

Regards,
Joel



Message text written by DB2 Data Base Discussion List
> I would like to try to expand on this topic just a little. We have
had
a real push recently to deteremine what causes high CPU in our DB2
started
tasks. I can understand why "DIST" would be high if you run alot of
distributed work, but what about DBM1 and MSTR? If the CPU used by a
batch
job shows in that tasks address space, then what accounts for
DBM1/MSTR's
time? I'm assuming that an increase in application activity will
correspond to an increase in DBM1/MSTR time, but what factors influence
this?
In other words, if I submit a batch job that runs some SQL, what
portion
of the cputime will show up on the batch batch application and what does
the MSTR/DBM1 pick up? My guess is this probably depends on what type
of
activity the application is doing, but what is the breakdown?
Looking at this another way, if I go out and look at tasks currently
running on our systems, DBM1 and MSTR consistently show very high cpu
usage
-- what is contributing?
I have been able to pick off some pieces through testing/observation,
for example DDL activity that causes dataset activity (create
tablespace/index) appears to result in high DBM1 cpu and low cpu
attributed
to the requesting address space, but I would like to think there is a
comprehensive list out there somewhere (I'd be more than happy to hear a
"RTFM" response if it can just tell me WHICH "M"). Personally, the more
I
read about this topic the less I know (and it sounds like some of the
rules
are changing when the requesting application is CICS).
This is really more than just a curiosity - as we look at tuning
applications I have a feeling we are not taking everything into account.

For example, just knowing that a change to an application reduces it's
cputime by 10 seconds may not account for a 15 second increase in
DB2DBM1.
You can make the same correlation between comparing IBM and OEM
utilities
-- if you don't know what is being consumed by DBM1, you can't make a
fair
overall comparison. Does anyone have a good grasp on this, or know
where I
can find a good breakdown?

Thanks,
Mike.<

================

the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



Martin Packer

Re: distributed STC high CPU question
(in response to Mike Vaughan)
To add to what Joel says...

I would also expect (in extremis) virtual storage management activities -
like storage contraction - to be under DBM1 and not charged back to any
application.

But I wouldn't expect this to be visible except in the case of a severe
virtual storage crunch.

You are all cutting IFCID 225 (Virtual Storage Summary) records, aren't
you? :-)

Martin

Martin Packer, MBCS Martin Packer/UK/IBM
020-8832-5167 in the UK (+44) (MOBX 273643, Internal 7-325167, Mobile
07802-245584)



Jeff Frazier

Re: distributed STC high CPU question
(in response to Martin Packer)
Joel, i would be interested in this chart also as we are experiencing high
srb time in our DDF since applying maint. In a 24 hour period we have
increased 3 hours plus in the DDF srb time. TIA Jeff




Joel Goldstein <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/07/03 04:42 PM
Please respond to DB2 Data Base Discussion List


To: [login to unmask email]
cc:
Subject: Re: distributed STC high CPU question


Mike,
Certain functions, such as prefetch are charged to the DBM1 address space,
while logging and any backout costs are charged to
MSTR. If your batch job does a TS scan, then the cost of all the SP I/O
goes to DBM1, as SRB time.

If you are interested, I have a chart that shows where most of the
standard function CPU charges go, and whether they are as TCB or SRB
time..

Regards,
Joel



Message text written by DB2 Data Base Discussion List
> I would like to try to expand on this topic just a little. We have
had
a real push recently to deteremine what causes high CPU in our DB2 started
tasks. I can understand why "DIST" would be high if you run alot of
distributed work, but what about DBM1 and MSTR? If the CPU used by a
batch
job shows in that tasks address space, then what accounts for DBM1/MSTR's
time? I'm assuming that an increase in application activity will
correspond to an increase in DBM1/MSTR time, but what factors influence
this?
In other words, if I submit a batch job that runs some SQL, what
portion
of the cputime will show up on the batch batch application and what does
the MSTR/DBM1 pick up? My guess is this probably depends on what type of
activity the application is doing, but what is the breakdown?
Looking at this another way, if I go out and look at tasks currently
running on our systems, DBM1 and MSTR consistently show very high cpu
usage
-- what is contributing?
I have been able to pick off some pieces through testing/observation,
for example DDL activity that causes dataset activity (create
tablespace/index) appears to result in high DBM1 cpu and low cpu
attributed
to the requesting address space, but I would like to think there is a
comprehensive list out there somewhere (I'd be more than happy to hear a
"RTFM" response if it can just tell me WHICH "M"). Personally, the more I
read about this topic the less I know (and it sounds like some of the
rules
are changing when the requesting application is CICS).
This is really more than just a curiosity - as we look at tuning
applications I have a feeling we are not taking everything into account.
For example, just knowing that a change to an application reduces it's
cputime by 10 seconds may not account for a 15 second increase in DB2DBM1.
You can make the same correlation between comparing IBM and OEM utilities
-- if you don't know what is being consumed by DBM1, you can't make a fair
overall comparison. Does anyone have a good grasp on this, or know where
I
can find a good breakdown?

Thanks,
Mike.<



the reached at
[login to unmask email]

Colin M Fay

Re: distributed STC high CPU question
(in response to Jeff Frazier)
Hi Joel,

Could you be so kind as to post the chart in the archives or at
least on the discussion list. I think there would be wide interest.


Colin

-----Original Message-----
From: Joel Goldstein [mailto:[login to unmask email]
Sent: Tuesday, January 07, 2003 4:42 PM
To: [login to unmask email]
Subject: Re: distributed STC high CPU question

Mike,
Certain functions, such as prefetch are charged to the DBM1 address
space,
while logging and any backout costs are charged to
MSTR. If your batch job does a TS scan, then the cost of all the SP I/O
goes to DBM1, as SRB time.

If you are interested, I have a chart that shows where most of the
standard function CPU charges go, and whether they are as TCB or SRB
time..

Regards,
Joel



Message text written by DB2 Data Base Discussion List
> I would like to try to expand on this topic just a little. We have
had
a real push recently to deteremine what causes high CPU in our DB2
started
tasks. I can understand why "DIST" would be high if you run alot of
distributed work, but what about DBM1 and MSTR? If the CPU used by a
batch
job shows in that tasks address space, then what accounts for
DBM1/MSTR's
time? I'm assuming that an increase in application activity will
correspond to an increase in DBM1/MSTR time, but what factors influence
this?
In other words, if I submit a batch job that runs some SQL, what
portion
of the cputime will show up on the batch batch application and what does
the MSTR/DBM1 pick up? My guess is this probably depends on what type
of
activity the application is doing, but what is the breakdown?
Looking at this another way, if I go out and look at tasks currently
running on our systems, DBM1 and MSTR consistently show very high cpu
usage
-- what is contributing?
I have been able to pick off some pieces through testing/observation,
for example DDL activity that causes dataset activity (create
tablespace/index) appears to result in high DBM1 cpu and low cpu
attributed
to the requesting address space, but I would like to think there is a
comprehensive list out there somewhere (I'd be more than happy to hear a
"RTFM" response if it can just tell me WHICH "M"). Personally, the more
I
read about this topic the less I know (and it sounds like some of the
rules
are changing when the requesting application is CICS).
This is really more than just a curiosity - as we look at tuning
applications I have a feeling we are not taking everything into account.

For example, just knowing that a change to an application reduces it's
cputime by 10 seconds may not account for a 15 second increase in
DB2DBM1.
You can make the same correlation between comparing IBM and OEM
utilities
-- if you don't know what is being consumed by DBM1, you can't make a
fair
overall comparison. Does anyone have a good grasp on this, or know
where I
can find a good breakdown?

Thanks,
Mike.<

================

the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



Max Scarpa

Re: distributed STC high CPU question
(in response to Colin M Fay)
Hi All

I think that an old (?) copy of the chart is available at Joel's web sites:

http://www.responsivesystems.com/New_HTMLs/DB2_System_Metrics_Files/v3_document.htm

Clicking on 'Where are the Costs Charged...........' on the left you'll
obtain 2 slides on this argument

It worths a look.

HTH

MS



Joel Goldstein

Re: distributed STC high CPU question
(in response to Max Scarpa)
Message text written by DB2 Data Base Discussion List
>Hi All

I think that an old (?) copy of the chart is available at Joel's web sites:

http://www.responsivesystems.com/New_HTMLs/DB2_System_Metrics_Files/v3_docu
ment.htm

Clicking on 'Where are the Costs Charged...........' on the left you'll
obtain 2 slides on this argument<

Yes, that's as part of a presentation, and still valid. But I have a pdf
file that I can send, and I'll put it into the DB2L archive later today.


Regards,
Joel



Max Scarpa

Re: distributed STC high CPU question
(in response to Joel Goldstein)
Hi Joel

I didn't want to substituite for the real Joel :-), I read the papers in
your web site some times ago and I thought it might be useful to spread
the link, but I wasn't sure if the chart was good for latest version (IMHUO
it was).

I think it' s a good idea to put the .pdf file in DB2-L documents.


Best Regards
Max Scarpa



Mike Vaughan

Re: distributed STC high CPU question
(in response to Max Scarpa)
First of all I would like to thank everyone for their reply to my previous posting regarding cpu usage in the DB2 started tasks.
Now I would like to try to dive in a little deeper. Looking through Joel's chart of where cpu is charged, I see a number of things that we can impact either from application changes or from subsystem changes. Is there anyway for me to know what will have the largest impact on the DB2 tasks? For example, if I know that a high percentage of DBM1's cpu is used in open/close dataset processing, then we should likely consider increasing the number of open datasets. On the other hand, if a high percentage is spent on space management then I might not want to do this (since more open datasets could correspond to more memory management work). Similarly, if a high percentage of DBM1 is spent on list-prefetch, I might want to go back to the applications using a heavy amount of list prefetch to make sure it's justified.
My issue is that at this point I think I know what things can be done to impact the cpu usage of the DB2 started tasks, but I don't know how much impact each of those will have (or what has the most potential impact). Is there a way to tell what the biggest chunk is? We do load SMF statistics reports (as well as accounting reports), but my issue there is they only show counts and not a real indicator of overhead (I know how many dataset open/close events occured, but not what they cost). Is anyone able to provide some insight on this, or am I on a wild goose chase?

Thanks,
Mike.
-----Original Message-----
From: Vaughan, Mike
Sent: Tuesday, January 07, 2003 9:19 PM
To: [login to unmask email]
Subject: Re: distributed STC high CPU question


Thank you for the response Joel. I would really appreciate seeing the chart you mentioned if you could pass it along.

Thanks,
Mike.
-----Original Message-----
From: Joel Goldstein
To: [login to unmask email]
Sent: 1/7/03 3:42 PM
Subject: Re: distributed STC high CPU question

Mike,
Certain functions, such as prefetch are charged to the DBM1 address
space,
while logging and any backout costs are charged to
MSTR. If your batch job does a TS scan, then the cost of all the SP I/O
goes to DBM1, as SRB time.

If you are interested, I have a chart that shows where most of the
standard function CPU charges go, and whether they are as TCB or SRB
time..

Regards,
Joel



Message text written by DB2 Data Base Discussion List
> I would like to try to expand on this topic just a little. We have
had
a real push recently to deteremine what causes high CPU in our DB2
started
tasks. I can understand why "DIST" would be high if you run alot of
distributed work, but what about DBM1 and MSTR? If the CPU used by a
batch
job shows in that tasks address space, then what accounts for
DBM1/MSTR's
time? I'm assuming that an increase in application activity will
correspond to an increase in DBM1/MSTR time, but what factors influence
this?
In other words, if I submit a batch job that runs some SQL, what
portion
of the cputime will show up on the batch batch application and what does
the MSTR/DBM1 pick up? My guess is this probably depends on what type
of
activity the application is doing, but what is the breakdown?
Looking at this another way, if I go out and look at tasks currently
running on our systems, DBM1 and MSTR consistently show very high cpu
usage
-- what is contributing?
I have been able to pick off some pieces through testing/observation,
for example DDL activity that causes dataset activity (create
tablespace/index) appears to result in high DBM1 cpu and low cpu
attributed
to the requesting address space, but I would like to think there is a
comprehensive list out there somewhere (I'd be more than happy to hear a
"RTFM" response if it can just tell me WHICH "M"). Personally, the more
I
read about this topic the less I know (and it sounds like some of the
rules
are changing when the requesting application is CICS).
This is really more than just a curiosity - as we look at tuning
applications I have a feeling we are not taking everything into account.

For example, just knowing that a change to an application reduces it's
cputime by 10 seconds may not account for a 15 second increase in
DB2DBM1.
You can make the same correlation between comparing IBM and OEM
utilities
-- if you don't know what is being consumed by DBM1, you can't make a
fair
overall comparison. Does anyone have a good grasp on this, or know
where I
can find a good breakdown?

Thanks,
Mike.<

================

the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can

================




Joel Goldstein

Re: distributed STC high CPU question
(in response to Mike Vaughan)
Mike,
A few comments inline to your questions below.

Regards,
Joel

Message text written by DB2 Data Base Discussion List
> First of all I would like to thank everyone for their reply to my
previous posting regarding cpu usage in the DB2 started tasks.
Now I would like to try to dive in a little deeper. Looking through
Joel's chart of where cpu is charged, I see a number of things that we can
impact either from application changes or from subsystem changes.

Is there anyway for me to know what will have the largest impact on the DB2
tasks?
++++ Somewhat.

For example, if I know that a high percentage of DBM1's cpu is used in
open/close dataset processing, then we should likely consider increasing
the number of open datasets.
++++ This will definately reduce CPU consumption, but will require a bit
more memory for all the control blocks. It's all a question of magnitude.
If your looking
at a couple hundred, don't knock yourself out. If it's thousands,
could be some good gain.

On the other hand, if a high percentage is spent on space management then I
might not want to do this (since more open datasets could correspond to
more memory management work).
++++ Space management (depending on which) is usually extend & format...
that will use the noticeable cpu cycles

Similarly, if a high percentage of DBM1 is spent on list-prefetch, I might
want to go back to the applications using a heavy amount of list prefetch
to make sure it's justified.
++++ Absolutely!!! The application tuning is where you will the largest
CPU paybacks, and the greatest reduction of application elapsed times.
Some companies have saved millions per year from fixing a few
(or sometimes even one) application performance problem.

I know how many dataset open/close events occured, but not what they cost).
Is anyone able to provide some insight on this, or am I on a wild goose
chase?
++++ I used to know these numbers, but I'm sure they have changed in the
last couple of years. I believe Akira had some of these numbers in one of
his presentations last year or the year before (IDUG or Tech,
or both).

++++ Keep in mind that you probably have 95% of your overall total system
CPU cost charged to the applications, and showing in the C2 CPU time.
The application tuning will have the greatest reduction effect on
your overall CPU busy rate.
Had one client site a few years ago that fixed ONE application
performance problem and got back 18% of the whole machine !!
Certainly an extreme case, but a real one.