Query CPU time keeps increasing.

Amarendra Mohanty

Query CPU time keeps increasing.
Hi,

Db2 V8 on z/OS

We have a job that executes a static query multiple times(about 600) using
host variables. The query has 5 tables joined each having about 10 million
rows. Not very big tables. The query uses indexes on all of them. Tables
have stats updated.

Initially to run about 600 times that query took about 5 min CPU as per BMC
apptune.
Everyday the CPU time increases by 1-2 mins. Now its taking 55 mins of
CPU time to run that same SQL about 600 times(It varies between 590-650).

I am not sure why the CPU time should increase so much and so consistently
everyday. The tables are not growing very fast. They grow about 1-2 MB per
day the most. Any ideas what could be the reason?

Thanks,
Amar

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Joel Goldstein

Re: Query CPU time keeps increasing.
(in response to Amarendra Mohanty)
Access paths and rows joined, answer sets.
The amount of data you are processing.
You will probably find a direct relationship between the number of getpages and the cpu cost..

Regards,
Joel


Joel Goldstein
Responsive Systems
IBM Gold Consultant
Buffer Pool Tool for DB2, the worldwide industry standard
Performance software that works...... Predicts IO Rate !!
Predicts Group Buffer Pool performance too
www.responsivesystems.com

Buffer Pool Tool for DB2 on www.LinkedIn.com
Watch the 3-Minute Buffer Pool Tool Movie at: www.responsivesystems.com/Movie1

tel. (732) 972-1261
fax.(732) 972-9416
----- Original Message -----
From: "Amar" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Wednesday, December 02, 2009 2:40 PM
Subject: [DB2-L] Query CPU time keeps increasing.


Hi,

Db2 V8 on z/OS

We have a job that executes a static query multiple times(about 600) using
host variables. The query has 5 tables joined each having about 10 million
rows. Not very big tables. The query uses indexes on all of them. Tables
have stats updated.

Initially to run about 600 times that query took about 5 min CPU as per BMC
apptune.
Everyday the CPU time increases by 1-2 mins. Now its taking 55 mins of
CPU time to run that same SQL about 600 times(It varies between 590-650).

I am not sure why the CPU time should increase so much and so consistently
everyday. The tables are not growing very fast. They grow about 1-2 MB per
day the most. Any ideas what could be the reason?

Thanks,
Amar

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Dave Nance

Re: Query CPU time keeps increasing.
(in response to Joel Goldstein)
Amar,
   You mention that stats have been updated. You did not, however, mention if:
the table/index are in need of a reorg
the statement has been rebound(what are the bind parms, ?reopt?)
 
  You, also, mention the query is using indexes for all the tables being joined/ You do not mention if:
you have any sorts
multi-index scans
1 col match instead of 5 col match and cardinality of that col is 2
do you have a lot of stage 2 predicates
I could keep going on

To get some useful advice, you need to give us information otherwise there are just a lot of guesses that could be passed along to you and you spend weeks trying them all out.
    Failing that, the answer can be found on page 194.


David Nance
 




________________________________
From: Amar <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 2, 2009 2:40:17 PM
Subject: [DB2-L] Query CPU time keeps increasing.

Hi,

Db2 V8 on z/OS

We have a job that executes a static query multiple times(about 600) using
host variables. The query has 5 tables joined each having about 10 million
rows. Not very big tables. The query uses indexes on all of them. Tables
have stats updated.

Initially to run about 600 times that query took about 5 min CPU as per BMC
apptune.
Everyday the CPU time increases by 1-2 mins. Now its taking 55 mins of
CPU time to run that same SQL about 600 times(It varies between 590-650).

I am not sure why the CPU time should increase so much and so consistently
everyday. The tables are not growing very fast. They grow about 1-2 MB per
day the most. Any ideas what could be the reason?

Thanks,
Amar

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *  http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar!  RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L





_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Nenad Vidak

Re: Query CPU time keeps increasing.
(in response to Dave Nance)
Hello,

have you recycled your DB2 or IPL the system during that period ? If yes,
did it have any effect on CPU ?

Regards, Nenad

On Wed, 2 Dec 2009 19:40:17 +0000, Amar <[login to unmask email]> wrote:

>Hi,
>
>Db2 V8 on z/OS
>
>We have a job that executes a static query multiple times(about 600) using
>host variables. The query has 5 tables joined each having about 10 million
>rows. Not very big tables. The query uses indexes on all of them. Tables
>have stats updated.
>
>Initially to run about 600 times that query took about 5 min CPU as per BMC
>apptune.
>Everyday the CPU time increases by 1-2 mins. Now its taking 55 mins of
>CPU time to run that same SQL about 600 times(It varies between 590-650).
>
>I am not sure why the CPU time should increase so much and so consistently
>everyday. The tables are not growing very fast. They grow about 1-2 MB per
>day the most. Any ideas what could be the reason?
>
>Thanks,
>Amar
>
>_____________________________________________________________________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
>_____________________________________________________________________
>
>http://www.idug.org/events/index.html is your DB2 Events calendar! RUG
meetings,
>Webcasts, Conferences- what is going on next?
>RUG leaders- get your events on the calendar today!
>_____________________________________________________________________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Roy Boxwell

Re: Query CPU time keeps increasing.
(in response to Nenad Vidak)
I agree with Joel on this one - Looks like fragmented data causing higher
and higher getpage activity to me. Check your RTS data and do a REORG of
IXs and/or TSs



Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Siegfried Fürst, Gerhard Schubert




Amar <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
02.12.2009 20:40
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
[DB2-L] Query CPU time keeps increasing.






Hi,

Db2 V8 on z/OS

We have a job that executes a static query multiple times(about 600) using

host variables. The query has 5 tables joined each having about 10 million

rows. Not very big tables. The query uses indexes on all of them. Tables
have stats updated.

Initially to run about 600 times that query took about 5 min CPU as per
BMC
apptune.
Everyday the CPU time increases by 1-2 mins. Now its taking 55 mins of
CPU time to run that same SQL about 600 times(It varies between 590-650).

I am not sure why the CPU time should increase so much and so consistently

everyday. The tables are not growing very fast. They grow about 1-2 MB per

day the most. Any ideas what could be the reason?

Thanks,
Amar

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG
meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Jorg Lueke

Re: Query CPU time keeps increasing.
(in response to Roy Boxwell)
I remember this happening when there were joins and one table was also being
inserted into during the day. CICS would keep populating the one table and
over time the resulting joins would return more data requiring more CPU.
That scenario also suffered from unstable access paths because the table
size was not too large and what was optimal for the optimizer changed for
even 50,000 rows.

Like Joel says, look at your getpages, you should be seeing more i/o to
correspond to the extra work.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L