High CPU usage

Kowsalya Kumari

High CPU usage

Hi all,

 

      I'm new to CA Tools, my developers & system admin are coming very frequently telling me that DB2 is using high CPU. 

      Anyone who has worked on CA Insight & Detector can help me, how & what made the DB2 to use high CPU using the tools or is there any command or way to find it?

 

Thanks

Kummu

Jorg Lueke

RE: High CPU usage
(in response to Kowsalya Kumari)

Enable historical collection for detector and it pretty much will tell you what's using CPU in DB2 at any given interval.

Avram Friedman

RE: High CPU usage
(in response to Kowsalya Kumari)

The first thing I would find out is what the people who are reporting high CPU usage mean by that.  What are they looking at?

They usually mean the DIST address space is using high CPU, sometimes over 100% of the box.

Please confirm what THEY mean by high CPU and we will move on from there..

In Reply to Kowsalya Kumari:

Hi all,

 

      I'm new to CA Tools, my developers & system admin are coming very frequently telling me that DB2 is using high CPU. 

      Anyone who has worked on CA Insight & Detector can help me, how & what made the DB2 to use high CPU using the tools or is there any command or way to find it?

 

Thanks

Kummu




Avram Friedman

IBM-sys-Prog.com

Avram Friedman

RE: High CPU usage
(in response to Avram Friedman)

Forgive me, there are actually 2 questions that need to be answered for help to be correctly focused.

  • What do they mean by HIGH CPU usage?
  • Why are you involved in the discussion at all?

    In Reply to Avram Friedman:

The first thing I would find out is what the people who are reporting high CPU usage mean by that.  What are they looking at?

They usually mean the DIST address space is using high CPU, sometimes over 100% of the box.

Please confirm what THEY mean by high CPU and we will move on from there..

In Reply to Kowsalya Kumari:

Hi all,

 

      I'm new to CA Tools, my developers & system admin are coming very frequently telling me that DB2 is using high CPU. 

      Anyone who has worked on CA Insight & Detector can help me, how & what made the DB2 to use high CPU using the tools or is there any command or way to find it?

 

Thanks

Kummu




Avram Friedman

IBM-sys-Prog.com




Avram Friedman

IBM-sys-Prog.com

Georg Peter

AW: High CPU usage
(in response to Avram Friedman)
Dear Kowsalya Kumari.

This is an extract from our company DETECTOR Planname Summary Display:

PLANNAME COMMIT ABORT SQL TIMEPCT CPUPCT INDB2_TIME INDB2_CPU
-------- -------- ------ --------- ------- ------- ------------ ------------
_ PLXXCIC3 129627 1644 4870579 68.91% 78.82% 02:53:07.410 10:44.096229
_ PLXXCIC2 35211 407 1108152 13.35% 7.80% 33:32.267692 01:03.809827
_ PLYYCIC3 10990 3 3771593 2.75% 6.26% 06:55.352423 00:51.228717
_ PLBSOWA 474 2 1469354 9.03% 2.84% 22:42.563918 00:23.255317
_ DISTSERV 0 0 32410 3.69% 1.95% 09:17.457279 00:15.975004
_ PLDTCIC2 3693 1 1121347 1.18% 1.92% 02:57.903483 00:15.748930
_ E170020 4061 0 85705 .24% .23% 00:37.202725 00:01.941677
_ PLCKIRPZ 715 4 10277 .10% .04% 00:16.077483 00:00.367501
_ DSNTEP71 0 0 68 .03% .02% 00:05.232426 00:00.197010

And if you have Detector you should be able to chose in this example the "bad" plan named PLXXCIC3 because this plan is using 78.82% CPUPCT.

Go deeper to a program level and after that a statement level - you will see what is going on, explain it and fix it ;-)

HTH.

With kind regards - mit freundlichen Gruessen - meilleures salutations,
G e o r g H . P e t e r
-------------------------------------------------------------------
D a t e n z e n t r a l e B a d e n - W u e r t t e m b e r g
E-Government, Database Systems, Product Support
Krailenshaldenstrasse 44
Ground Floor Room 018
70469 Stuttgart, Germany, Europe
e:mail [login to unmask email]<mailto:[login to unmask email]>
Phone 0049-711-8108-27271
PC-Fax 004971189696071
Internet (only in german language):http://www.dzbw.dehttp://www.dzbw.de
----------------------------------------------------------------------
Working together for better solutions


Von: Avram Friedman [mailto:[login to unmask email]
Gesendet: Dienstag, 11. Februar 2014 17:17
An: [login to unmask email]
Betreff: [DB2-L] - RE: High CPU usage


Forgive me, there are actually 2 questions that need to be answered for help to be correctly focused.

* What do they mean by HIGH CPU usage?
* Why are you involved in the discussion at all?

In Reply to Avram Friedman:

The first thing I would find out is what the people who are reporting high CPU usage mean by that. What are they looking at?

They usually mean the DIST address space is using high CPU, sometimes over 100% of the box.

Please confirm what THEY mean by high CPU and we will move on from there..

In Reply to Kowsalya Kumari:

Hi all,



I'm new to CA Tools, my developers & system admin are coming very frequently telling me that DB2 is using high CPU.

Anyone who has worked on CA Insight & Detector can help me, how & what made the DB2 to use high CPU using the tools or is there any command or way to find it?



Thanks

Kummu



Avram Friedman

IBM-sys-Prog.com



Avram Friedman

IBM-sys-Prog.com

-----End Original Message-----


_______________________________________________________________________________

Abonnieren Sie unseren Infobrief und erfahren Sie regelmäßig die neuesten Nachrichten über unsere Lösungen, aktuellen Projekte und Entwicklungen. Melden sie sich an mit diesem Link http://www.dzbw.de/,Lde/Start/Newsletter+Service.html
_______________________________________________________________________________

Datenzentrale Baden-Württemberg, Anstalt des öffentlichen Rechts
Krailenshaldenstr. 44, 70469 Stuttgart
Telefon (0711) 8108-20, Telefax (0711) 8108-21350
E-Mail [login to unmask email], Internet www.datenzentrale.de
Vorstand: Karl Tramer (Vors.) und Joachim Kischlat, Vorsitzender des Verwaltungsrats: Gunter Czisch
USt-Id-Nr. DE147794223
_______________________________________________________________________________



Adam Baldwin

RE: High CPU usage
(in response to Avram Friedman)

And within Avram's "What do they mean by HIGH CPU usage?" question, you will need to differentiate between DB2 CPU usage within an application - i.e. CPU usage charged against an application's TCB - and CPU usage directly attributable to one of the DB2 address spaces' SRB.

Adam

Steen Rasmussen

AW: High CPU usage
(in response to Georg Peter)
CPU usage can be caused by a myriad of things. Georg Peter already gave a hint to see what your plans/packages are using, but as Avram mentioned - there are other things to look at like the address spaces DBM1 and MSTR. Is it paging ? or I/O or bugger pool shortage or utilities or . . . . . . . .

Steen

From: Peter, Georg [mailto:[login to unmask email]
Sent: Tuesday, February 11, 2014 10:23 AM
To: [login to unmask email]
Subject: [DB2-L] - AW: RE: High CPU usage

Dear Kowsalya Kumari.

This is an extract from our company DETECTOR Planname Summary Display:

PLANNAME COMMIT ABORT SQL TIMEPCT CPUPCT INDB2_TIME INDB2_CPU
-------- -------- ------ --------- ------- ------- ------------ ------------
_ PLXXCIC3 129627 1644 4870579 68.91% 78.82% 02:53:07.410 10:44.096229
_ PLXXCIC2 35211 407 1108152 13.35% 7.80% 33:32.267692 01:03.809827
_ PLYYCIC3 10990 3 3771593 2.75% 6.26% 06:55.352423 00:51.228717
_ PLBSOWA 474 2 1469354 9.03% 2.84% 22:42.563918 00:23.255317
_ DISTSERV 0 0 32410 3.69% 1.95% 09:17.457279 00:15.975004
_ PLDTCIC2 3693 1 1121347 1.18% 1.92% 02:57.903483 00:15.748930
_ E170020 4061 0 85705 .24% .23% 00:37.202725 00:01.941677
_ PLCKIRPZ 715 4 10277 .10% .04% 00:16.077483 00:00.367501
_ DSNTEP71 0 0 68 .03% .02% 00:05.232426 00:00.197010

And if you have Detector you should be able to chose in this example the "bad" plan named PLXXCIC3 because this plan is using 78.82% CPUPCT.

Go deeper to a program level and after that a statement level - you will see what is going on, explain it and fix it ;-)

HTH.

With kind regards - mit freundlichen Gruessen - meilleures salutations,
G e o r g H . P e t e r
-------------------------------------------------------------------
D a t e n z e n t r a l e B a d e n - W u e r t t e m b e r g
E-Government, Database Systems, Product Support
Krailenshaldenstrasse 44
Ground Floor Room 018
70469 Stuttgart, Germany, Europe
e:mail [login to unmask email]<mailto:[login to unmask email]>
Phone 0049-711-8108-27271
PC-Fax 004971189696071
Internet (only in german language):http://www.dzbw.dehttp://www.dzbw.de
----------------------------------------------------------------------
Working together for better solutions


Von: Avram Friedman [mailto:[login to unmask email]
Gesendet: Dienstag, 11. Februar 2014 17:17
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: High CPU usage


Forgive me, there are actually 2 questions that need to be answered for help to be correctly focused.

* What do they mean by HIGH CPU usage?
* Why are you involved in the discussion at all?

In Reply to Avram Friedman:

The first thing I would find out is what the people who are reporting high CPU usage mean by that. What are they looking at?

They usually mean the DIST address space is using high CPU, sometimes over 100% of the box.

Please confirm what THEY mean by high CPU and we will move on from there..

In Reply to Kowsalya Kumari:

Hi all,



I'm new to CA Tools, my developers & system admin are coming very frequently telling me that DB2 is using high CPU.

Anyone who has worked on CA Insight & Detector can help me, how & what made the DB2 to use high CPU using the tools or is there any command or way to find it?



Thanks

Kummu



Avram Friedman

IBM-sys-Prog.com



Avram Friedman

IBM-sys-Prog.com

-----End Original Message-----
_______________________________________________________________________________

Abonnieren Sie unseren Infobrief und erfahren Sie regelm??ig die neuesten Nachrichten ?ber unsere L?sungen, aktuellen Projekte und Entwicklungen. Melden Sie sich an mit diesem Link http://www.dzbw.de/,Lde/Start/Newsletter+Service.html

_______________________________________________________________________________

Datenzentrale Baden-W?rttemberg, Anstalt des ?ffentlichen Rechts
Krailenshaldenstr. 44, 70469 Stuttgart
Telefon (0711) 8108-20, Telefax (0711) 8108-21350
E-Mail [login to unmask email]<mailto:[login to unmask email]>, Internet www.datenzentrale.de<http://www.datenzentrale.de>
Vorstand: Karl Tramer (Vors.) und Joachim Kischlat, Vorsitzender des Verwaltungsrats: Gunter Czisch
USt-Id-Nr. DE147794223
_______________________________________________________________________________

-----End Original Message-----

Kowsalya Kumari

RE: AW: High CPU usage
(in response to Steen Rasmussen)

Thankyou all for your reply,

 

System admini and System operations(Who are monitoring the system) sent a mail telling that, MVS Usuage has crossed 100% and db2 has reached more than 85% of it. So just trying to understand it.

 

I'm DB2 DBA and interested in DB2 System DBA. So just trying my self to figure it out. Please do help me.

 

Thanks

Kowsalya

Chris Blaicher

AW: High CPU usage
(in response to Kowsalya Kumari)
Your question has a lot of potential answers. Look up the SMF records DB2 can produce which are documented in the DB2 Administration Guide.

Using them you can figure out who is using the most CPU and a lot of other things. It maybe a poorly written SQL, or an SQL that is run a lot.

There are a hundred ways you can aggregate the data, but here are just a few.
By total CPU time for all cases of a query
By frequency called
By average CPU time per query
By total elapsed time for all cases of a query
By average elapsed time per query
By total fetches for all cases of a query
By average fetches per query
By total synchronous reads for all cases of a query
Etc, etc, etc

Just look at the top ten of each case. Work on the top 2 or 3 that bubble to the top in multiple cases. As you fix them, some other query will surface as the worst and you work on that one next.

How to fix them is a whole other discussion.

This is what I did at a previous job. Find the problem children and work with the various groups to improve overall performance and reduce resource consumption.

Chris Blaicher
Principal Software Engineer, Software Development
Syncsort Incorporated
50 Tice Boulevard, Woodcliff Lake, NJ 07677
P: 201-930-8260 | M: 512-627-3803
E: [login to unmask email]

From: Kowsalya Kumari [mailto:[login to unmask email]
Sent: Wednesday, February 12, 2014 2:46 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: AW: High CPU usage


Thankyou all for your reply,



System admini and System operations(Who are monitoring the system) sent a mail telling that, MVS Usuage has crossed 100% and db2 has reached more than 85% of it. So just trying to understand it.



I'm DB2 DBA and interested in DB2 System DBA. So just trying my self to figure it out. Please do help me.



Thanks

Kowsalya

-----End Original Message-----

________________________________



ATTENTION: -----

The information contained in this message (including any files transmitted with this message) may contain proprietary, trade secret or other confidential and/or legally privileged information. Any pricing information contained in this message or in any files transmitted with this message is always confidential and cannot be shared with any third parties without prior written approval from Syncsort. This message is intended to be read only by the individual or entity to whom it is addressed or by their designee. If the reader of this message is not the intended recipient, you are on notice that any use, disclosure, copying or distribution of this message, in any form, is strictly prohibited. If you have received this message in error, please immediately notify the sender and/or Syncsort and destroy all copies of this message in your possession, custody or control.

Kowsalya Kumari

RE: AW: High CPU usage
(in response to Chris Blaicher)

Thanks Chris, Where can I find the SMF records?

 

 

Chris Blaicher

AW: High CPU usage
(in response to Kowsalya Kumari)
Talk to your System Programmers.

Chris Blaicher
Principal Software Engineer, Software Development
Syncsort Incorporated
50 Tice Boulevard, Woodcliff Lake, NJ 07677
P: 201-930-8260 | M: 512-627-3803
E: [login to unmask email]

From: Kowsalya Kumari [mailto:[login to unmask email]
Sent: Wednesday, February 12, 2014 9:11 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: AW: High CPU usage


Thanks Chris, Where can I find the SMF records?





-----End Original Message-----

________________________________



ATTENTION: -----

The information contained in this message (including any files transmitted with this message) may contain proprietary, trade secret or other confidential and/or legally privileged information. Any pricing information contained in this message or in any files transmitted with this message is always confidential and cannot be shared with any third parties without prior written approval from Syncsort. This message is intended to be read only by the individual or entity to whom it is addressed or by their designee. If the reader of this message is not the intended recipient, you are on notice that any use, disclosure, copying or distribution of this message, in any form, is strictly prohibited. If you have received this message in error, please immediately notify the sender and/or Syncsort and destroy all copies of this message in your possession, custody or control.

Kowsalya Kumari

RE: AW: High CPU usage
(in response to Chris Blaicher)

Ok, let me check.

 

Thanks

Kowsalya 

Avram Friedman

RE: AW: High CPU usage
(in response to Kowsalya Kumari)

Thank you for explaing the relationships and your interest in this subject.

As I now understanding it oppertations is reporting high DB2 system CPU, most likly observed with SDSF or some MVS, not DB2 monitor.
In addition I am guessing you were copied on a notice that went to a large group of people.

First of all congratulations on taking action on this request.  Seeking advice from IDUG is very comendable.

Performance can be viewed through many diffrent sets of eyes. for example

Application or thread level
System or what DB2 calls statistics level
Internals or what DB2 calls monitoring

The problem you seem to be dealing with appers to be a systems or statistics issue.  Overall CPU is too high.

Part of the difficulty in tuning DB2 (and CICS for that matter) is classic general views of of what is normal do not apply.
The DB2 DIST address space can frequently be using more than 100% of the CPU and often causes alarm by system level people.  It uses high CPU because it uses OS multi tasking.  A task or set of tasks can drive a single CPU to 100%.  A set of tasks can drive each CPU in the plex to 100% resulting in total CPU for DIST being over 100%

There are some best practices for designing and monitoring DIST applications.  I suggest the REDBOOK  DB2 stored procedures from the call and beyond as a good starting place.



In Reply to Kowsalya Kumari:

Thankyou all for your reply,

 

System admini and System operations(Who are monitoring the system) sent a mail telling that, MVS Usuage has crossed 100% and db2 has reached more than 85% of it. So just trying to understand it.

 

I'm DB2 DBA and interested in DB2 System DBA. So just trying my self to figure it out. Please do help me.

 

Thanks

Kowsalya




Avram Friedman

IBM-sys-Prog.com

Georg Peter

AW: -I suggest the REDBOOK DB2 stored procedures from the call and beyond as a good starting place.
(in response to Avram Friedman)
Avram, this is my thinking too.

It is not only DB2, the other CPU-driven "things" in an IBM-world (e.g. CICS) should be another point to notice.

And your advice to Kumari, please read the REDBOOK "DB2 stored procedures from the call and beyond" is a very good starting place ***smile***

With kind regards - mit freundlichen Gruessen - meilleures salutations,
G e o r g H . P e t e r
-------------------------------------------------------------------
D a t e n z e n t r a l e B a d e n - W u e r t t e m b e r g
E-Government, Database Systems, Product Support
Krailenshaldenstrasse 44
Ground Floor Room 018
70469 Stuttgart, Germany, Europe
e:mail [login to unmask email]<mailto:[login to unmask email]>
Phone 0049-711-8108-27271
PC-Fax 004971189696071
Internet (only in german language):http://www.dzbw.dehttp://www.dzbw.de
----------------------------------------------------------------------
Working together for better solutions


Von: Avram Friedman [mailto:[login to unmask email]
Gesendet: Mittwoch, 12. Februar 2014 17:08
An: [login to unmask email]
Betreff: [DB2-L] - RE: AW: High CPU usage


Thank you for explaing the relationships and your interest in this subject.

As I now understanding it oppertations is reporting high DB2 system CPU, most likly observed with SDSF or some MVS, not DB2 monitor.
In addition I am guessing you were copied on a notice that went to a large group of people.

First of all congratulations on taking action on this request. Seeking advice from IDUG is very comendable.

Performance can be viewed through many diffrent sets of eyes. for example

Application or thread level
System or what DB2 calls statistics level
Internals or what DB2 calls monitoring

The problem you seem to be dealing with appers to be a systems or statistics issue. Overall CPU is too high.

Part of the difficulty in tuning DB2 (and CICS for that matter) is classic general views of of what is normal do not apply.
The DB2 DIST address space can frequently be using more than 100% of the CPU and often causes alarm by system level people. It uses high CPU because it uses OS multi tasking. A task or set of tasks can drive a single CPU to 100%. A set of tasks can drive each CPU in the plex to 100% resulting in total CPU for DIST being over 100%

There are some best practices for designing and monitoring DIST applications. I suggest the REDBOOK DB2 stored procedures from the call and beyond as a good starting place.


In Reply to Kowsalya Kumari:

Thankyou all for your reply,



System admini and System operations(Who are monitoring the system) sent a mail telling that, MVS Usuage has crossed 100% and db2 has reached more than 85% of it. So just trying to understand it.



I'm DB2 DBA and interested in DB2 System DBA. So just trying my self to figure it out. Please do help me.



Thanks

Kowsalya



Avram Friedman

IBM-sys-Prog.com

-----End Original Message-----


_______________________________________________________________________________

Abonnieren Sie unseren Infobrief und erfahren Sie regelmäßig die neuesten Nachrichten über unsere Lösungen, aktuellen Projekte und Entwicklungen. Melden sie sich an mit diesem Link http://www.dzbw.de/,Lde/Start/Newsletter+Service.html
_______________________________________________________________________________

Datenzentrale Baden-Württemberg, Anstalt des öffentlichen Rechts
Krailenshaldenstr. 44, 70469 Stuttgart
Telefon (0711) 8108-20, Telefax (0711) 8108-21350
E-Mail [login to unmask email], Internet www.datenzentrale.de
Vorstand: Karl Tramer (Vors.) und Joachim Kischlat, Vorsitzender des Verwaltungsrats: Gunter Czisch
USt-Id-Nr. DE147794223
_______________________________________________________________________________



Kowsalya Kumari

RE: AW: -I suggest the REDBOOK DB2 stored procedures from the call and beyond as a good starting place.
(in response to Georg Peter)

Thanks Avram/Georg.

 

Thanks

Kowsalya

Tim Hare

AW: High CPU usage
(in response to Kowsalya Kumari)
Do not forget, in your analysis, that CPU usage itself is not bad (you're
paying for the CPU, you do want to use it).

What is desired, from a z/OS and DB2 tuning sense, is to drive the CPU as
high as you need to, _while_ keeping response time (or batch elapsed time)
for your most important applications within the range you need them to be.
If your response time is fine, then don't worry about the CPU usage unless
you are having to pay for it in some sort of chargeback situation.





Tim Hare

Hare Systems Support







From: Kowsalya Kumari [mailto:[login to unmask email]
Sent: Wednesday, February 12, 2014 2:46 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: AW: High CPU usage



Thankyou all for your reply,



System admini and System operations(Who are monitoring the system) sent a
mail telling that, MVS Usuage has crossed 100% and db2 has reached more than
85% of it. So just trying to understand it.



I'm DB2 DBA and interested in DB2 System DBA. So just trying my self to
figure it out. Please do help me.



Thanks

Kowsalya



-----End Original Message-----

Christine Grant

RE: High CPU usage
(in response to Kowsalya Kumari)

svchost.exe (netsvcs) is also a cause of high CPU usage. YOu can try some of these solutions I have found online:

    Clear Event Viewer Logs
    Download and install all available Windows updates
    Scan your computer for viruses and malware
    Find and Disable the service that causes the “svchost” high CPU usage problem

For detailed solution, Visit: http://errorcodespro.com/fix-svchost-exe-netsvcs-high-cpu-memory-usage/

James Karter

RE: High CPU usage
(in response to Christine Grant)

clear the DB logs, keep trying to clear logs in a regular basis if you are using high uses App or those app running multiple threads at a time.

Regards.

microsoftofficesupport.org