CPU & Getpages huge increase mystery

Mark Vickers

CPU & Getpages huge increase mystery
We had a new version of a program go into production on Tuesday.
We have confirmed that NO SQL changes were made.
One SQL statement has gone nutso on us.
Elapsed time & CPU time has increased 7 fold (in 28 minutes of clock time
it consumed 19 minutes of CPU !)
Getpages per SQL call went from 9 to 107.

The table in question (2 of them and are both reasonably static)
Had < 1000 inserts and updates since August 2008.
The program was last bound in October 2008.

We have compared the access path back to an explain from June 2008 and see
no differences.
In October 2008 the cost did go from 7ms to 19ms and from 81su to 231su.
This week it went up to 29ms & 350su.
We did not see any major performance degredation in October, but now it
was really bad.

We backed out the program and got the same explain and performance
problems with the old version.

This all points to the data and statistics right ?
But this is one of our most critical called modules and is hit > 500K
times a day, so I am hoping to optimize this as much as possible.

The two table counts are OTC=9K and STD=14K rows.

The query is somewhat of a bear (self referencing the same OTC table 5
times) and has a
category B with HOST VARIABLES AND TABLE CARDINALITY reason codes.

I tried adding DSTATS - VALUES 15,5 COLCARDF1 SCAN CNSW44.CNSOTC.* and
RUNSTATS TABLESPACE CNSW44.CNSOTCS
TABLE (CNSW44.CNSOTC)
COLUMN(<LISTED EVERY COLUMN>)
UPDATE ALL HISTORY ALL
INDEX(ALL KEYCARD
FREQVAL NUMCOLS 1 COUNT 25
FREQVAL NUMCOLS 2 COUNT 25
FREQVAL NUMCOLS 3 COUNT 25
FREQVAL NUMCOLS 4 COUNT 25
FREQVAL NUMCOLS 5 COUNT 25
)
[ on both tables ]
But this did not change the explain.

I am running out of ideas now, can anyone help please ?

Thanks,
Mark Vickers.



This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete all copies
of this message.




______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Robert Catterall

Re: CPU & Getpages huge increase mystery
(in response to Mark Vickers)
I realize that fewer than 1000 inserts and updates over the past four months
is not much, but the tables are relatively small so a little bit of
insert/update activity can have a significant impact on things such as data
clustering. When were the two tablespaces (if the tables are in different
tablespaces) and associated indexes last reorganized?

Also, you might want to include the query (bear though it may be) and
pertinent access path information. Someone on the list might be able to do
something with that information.

Robert


On Fri, Jan 9, 2009 at 10:00 AM, Mark Vickers <[login to unmask email]>wrote:

>
> We had a new version of a program go into production on Tuesday.
> We have confirmed that NO SQL changes were made.
> One SQL statement has gone nutso on us.
> Elapsed time & CPU time has increased 7 fold (in 28 minutes of clock time
> it consumed 19 minutes of CPU !)
> Getpages per SQL call went from 9 to 107.
>
> The table in question (2 of them and are both reasonably static)
> Had < 1000 inserts and updates since August 2008.
> The program was last bound in October 2008.
>
> We have compared the access path back to an explain from June 2008 and see
> no differences.
> In October 2008 the cost did go from 7ms to 19ms and from 81su to 231su.
> This week it went up to 29ms & 350su.
> We did not see any major performance degredation in October, but now it was
> really bad.
>
> We backed out the program and got the same explain and performance problems
> with the old version.
>
> This all points to the data and statistics right ?
> But this is one of our most critical called modules and is hit > 500K times
> a day, so I am hoping to optimize this as much as possible.
>
> The two table counts are OTC=9K and STD=14K rows.
>
> The query is somewhat of a bear (self referencing the same OTC table 5
> times) and has a
> category B with HOST VARIABLES AND TABLE CARDINALITY reason codes.
>
> I tried adding DSTATS - VALUES 15,5 COLCARDF1 SCAN CNSW44.CNSOTC.* and
> RUNSTATS TABLESPACE CNSW44.CNSOTCS
> TABLE (CNSW44.CNSOTC)
> COLUMN(<LISTED EVERY COLUMN>)
> UPDATE ALL HISTORY ALL
> INDEX(ALL KEYCARD
> FREQVAL NUMCOLS 1 COUNT 25
> FREQVAL NUMCOLS 2 COUNT 25
> FREQVAL NUMCOLS 3 COUNT 25
> FREQVAL NUMCOLS 4 COUNT 25
> FREQVAL NUMCOLS 5 COUNT 25
> )
> [ on both tables ]
> But this did not change the explain.
>
> I am running out of ideas now, can anyone help please ?
>
> Thanks,
> Mark Vickers.
>
> *
> This e-mail (and any attachments) may contain information that is
> confidential and/or protected by law. Any review, use, distribution or
> disclosure to anyone other than the
> intended recipient(s) is strictly prohibited. If you are not the intended
> recipient, please contact the sender by reply email and delete all copies of
> this message.*
>
>
>
> ------------------------------
>
> *IDUG 2009 - Europe * 5-9 October * Rome, Italy* < http://idug.org/lseu >
>
> *IDUG.org* < http://www.idug.org > was recently updated requiring members to
> use a new password. You should have gotten an e-mail with the temporary
> password assigned to your account. Please log in and update your member
> profile. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >
>



--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

PAUL WALTERS

Re: CPU & Getpages huge increase mystery
(in response to Robert Catterall)
Is it possible you are having a rid pool failure?

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Mark Vickers
Sent: Friday, January 09, 2009 10:00 AM
To: Walters, Paul; DB2 Database Discussion list at IDUG
Subject: [DB2-L] CPU & Getpages huge increase mystery


We had a new version of a program go into production on Tuesday.
We have confirmed that NO SQL changes were made.
One SQL statement has gone nutso on us.
Elapsed time & CPU time has increased 7 fold (in 28 minutes of clock time it consumed 19 minutes of CPU !)
Getpages per SQL call went from 9 to 107.

The table in question (2 of them and are both reasonably static)
Had < 1000 inserts and updates since August 2008.
The program was last bound in October 2008.

We have compared the access path back to an explain from June 2008 and see no differences.
In October 2008 the cost did go from 7ms to 19ms and from 81su to 231su.
This week it went up to 29ms & 350su.
We did not see any major performance degredation in October, but now it was really bad.

We backed out the program and got the same explain and performance problems with the old version.

This all points to the data and statistics right ?
But this is one of our most critical called modules and is hit > 500K times a day, so I am hoping to optimize this as much as possible.

The two table counts are OTC=9K and STD=14K rows.

The query is somewhat of a bear (self referencing the same OTC table 5 times) and has a
category B with HOST VARIABLES AND TABLE CARDINALITY reason codes.

I tried adding DSTATS - VALUES 15,5 COLCARDF1 SCAN CNSW44.CNSOTC.* and
RUNSTATS TABLESPACE CNSW44.CNSOTCS
TABLE (CNSW44.CNSOTC)
COLUMN(<LISTED EVERY COLUMN>)
UPDATE ALL HISTORY ALL
INDEX(ALL KEYCARD
FREQVAL NUMCOLS 1 COUNT 25
FREQVAL NUMCOLS 2 COUNT 25
FREQVAL NUMCOLS 3 COUNT 25
FREQVAL NUMCOLS 4 COUNT 25
FREQVAL NUMCOLS 5 COUNT 25
)
[ on both tables ]
But this did not change the explain.

I am running out of ideas now, can anyone help please ?

Thanks,
Mark Vickers.

This e-mail (and any attachments) may contain information that is confidential and/or protected by law. Any review, use, distribution or disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and delete all copies of this message.





________________________________

IDUG 2009 - Europe * 5-9 October * Rome, Italy < http://idug.org/lseu >

IDUG.org <http://www.idug.org> was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here.< http://www.idug.org/component/juser/register.html >

This E-Mail has been scanned for viruses.


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Roger Hecq

Re: CPU & Getpages huge increase mystery
(in response to PAUL WALTERS)
A couple of thoughts.

Would one of the index columns be a Date or Timestamp column? If so and
if the stats are old, then the optimizer might opt for a tablespace scan
because the current date is too much higher than the HIGH2KEY for that
column. If the stats are old and the number of rows has increased, you
could also be exceeding the 25% RID list processing limit, which is
calculated using the number of rows in the catalog. Was the table
reorged before the last Runstats?

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Mark Vickers
Sent: Friday, January 09, 2009 10:00 AM
To: [login to unmask email]
Subject: [DB2-L] CPU & Getpages huge increase mystery



We had a new version of a program go into production on Tuesday.
We have confirmed that NO SQL changes were made.
One SQL statement has gone nutso on us.
Elapsed time & CPU time has increased 7 fold (in 28 minutes of clock
time it consumed 19 minutes of CPU !)
Getpages per SQL call went from 9 to 107.

The table in question (2 of them and are both reasonably static)
Had < 1000 inserts and updates since August 2008.
The program was last bound in October 2008.

We have compared the access path back to an explain from June 2008 and
see no differences.
In October 2008 the cost did go from 7ms to 19ms and from 81su to 231su.

This week it went up to 29ms & 350su.
We did not see any major performance degredation in October, but now it
was really bad.

We backed out the program and got the same explain and performance
problems with the old version.

This all points to the data and statistics right ?
But this is one of our most critical called modules and is hit > 500K
times a day, so I am hoping to optimize this as much as possible.

The two table counts are OTC=9K and STD=14K rows.

The query is somewhat of a bear (self referencing the same OTC table 5
times) and has a
category B with HOST VARIABLES AND TABLE CARDINALITY reason codes.

I tried adding DSTATS - VALUES 15,5 COLCARDF1 SCAN CNSW44.CNSOTC.* and
RUNSTATS TABLESPACE CNSW44.CNSOTCS
TABLE (CNSW44.CNSOTC)
COLUMN(<LISTED EVERY COLUMN>)
UPDATE ALL HISTORY ALL
INDEX(ALL KEYCARD
FREQVAL NUMCOLS 1 COUNT 25
FREQVAL NUMCOLS 2 COUNT 25
FREQVAL NUMCOLS 3 COUNT 25
FREQVAL NUMCOLS 4 COUNT 25
FREQVAL NUMCOLS 5 COUNT 25
)
[ on both tables ]
But this did not change the explain.

I am running out of ideas now, can anyone help please ?

Thanks,
Mark Vickers.




This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the
intended recipient, please contact the sender by reply email and delete
all copies of this message.






________________________________


IDUG 2009 - Europe * 5-9 October * Rome, Italy < http://idug.org/lseu >

IDUG.org < http://www.idug.org > was recently updated requiring members
to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >



______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Martin Kenney

Re: CPU & Getpages huge increase mystery
(in response to Roger Hecq)
This sort of sounds like a situation we ran into
a many years back, and on DB2V6.

Condensed version: Check your buffer/hiper pools, have they changed
in size.

Long ugly story:
DB2 systems programmer inadvertently removed all of our hiper pools
in the morning, about 30 minutes later realized his mistake, and put
them back. During the 30 minutes of no hiper pools our biggest COBOL
DB2/batch
program was compiled/bound for a minor COBOL change when the
hiper pools were gone (all SQLS remained the same) as Murphy's law would
have it.
Big batch (16 jobs calling same program) program runs at midnight,
usually finishes at 2:00. First time it runs, it finishes at 05:45, it
has a 06:00 SLA so nobody notices. The next 29 cycles it finishes
between
05:30 and 05:50, still nobody notices.
Fist of the month comes along and jobs finally miss the SLA,
everybody notices.
DBA (my lucky day) gets paged to investigate. Notices that program was
changed on the 1st of the previous month, after the batch cycle. All the

explain details are identical (access path, columns used, order of
columns,
procms, procsu, sorts, ......)
DBA notifies COBOL programmer to see what change they made. They only
changed
the number of lines per page for the report.
DBA talks to the systems programmer about any recent changes, there were
none.
An hour later systems programmer remembers the hiper pool thing.
DBA rebinds the program, batch cycle finishes at 02:00 next run, life is
good.

I still do not understand why binding the program without the hiper
pools, but
running with them messed things up (Joel?????), but they sure did.



______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Philip Gunning

Re: CPU & Getpages huge increase mystery
(in response to Martin Kenney)
Mark, if all things are equal as you indicate, then I'd look at predicates
and filtering involved to see if any of that has changed, has all the
characteristics of an access path change for the bad.make sure the type of
stats run are the same as before, I see you have already made some changes
there, that could cloud the issue. Also make sure the time is not being
spent in some other part of this module. PG



From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Mark Vickers
Sent: Friday, January 09, 2009 10:00 AM
To: [login to unmask email]
Subject: [DB2-L] CPU & Getpages huge increase mystery




We had a new version of a program go into production on Tuesday.
We have confirmed that NO SQL changes were made.
One SQL statement has gone nutso on us.
Elapsed time & CPU time has increased 7 fold (in 28 minutes of clock time it
consumed 19 minutes of CPU !)
Getpages per SQL call went from 9 to 107.

The table in question (2 of them and are both reasonably static)
Had < 1000 inserts and updates since August 2008.
The program was last bound in October 2008.

We have compared the access path back to an explain from June 2008 and see
no differences.
In October 2008 the cost did go from 7ms to 19ms and from 81su to 231su.
This week it went up to 29ms & 350su.
We did not see any major performance degredation in October, but now it was
really bad.

We backed out the program and got the same explain and performance problems
with the old version.

This all points to the data and statistics right ?
But this is one of our most critical called modules and is hit > 500K times
a day, so I am hoping to optimize this as much as possible.

The two table counts are OTC=9K and STD=14K rows.

The query is somewhat of a bear (self referencing the same OTC table 5
times) and has a
category B with HOST VARIABLES AND TABLE CARDINALITY reason codes.

I tried adding DSTATS - VALUES 15,5 COLCARDF1 SCAN CNSW44.CNSOTC.* and
RUNSTATS TABLESPACE CNSW44.CNSOTCS
TABLE (CNSW44.CNSOTC)
COLUMN(<LISTED EVERY COLUMN>)
UPDATE ALL HISTORY ALL
INDEX(ALL KEYCARD
FREQVAL NUMCOLS 1 COUNT 25
FREQVAL NUMCOLS 2 COUNT 25
FREQVAL NUMCOLS 3 COUNT 25
FREQVAL NUMCOLS 4 COUNT 25
FREQVAL NUMCOLS 5 COUNT 25
)
[ on both tables ]
But this did not change the explain.

I am running out of ideas now, can anyone help please ?

Thanks,
Mark Vickers.


This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete all copies of
this message.





_____

< http://idug.org/lseu > IDUG 2009 - Europe * 5-9 October * Rome, Italy

< http://www.idug.org > IDUG.org was recently updated requiring members to
use a new password. You should have gotten an e-mail with the temporary
password assigned to your account. Please log in and update your member
profile. If you are not already an IDUG.org member, please register here.
< http://www.idug.org/component/juser/register.html >



______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Mark Vickers

Re: CPU & Getpages huge increase mystery
(in response to Philip Gunning)
I just want to thank all of you for your replies and I am working thru
them now.

I was pulled away for a couple of days and now back to this problem.

I'll try to respond to all the questions and suggestions ASAP.

Thanks,
Mark.



This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete all copies
of this message.



______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Mark Vickers

Re: CPU & Getpages huge increase mystery
(in response to Mark Vickers)
Phil Gunning <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/09/2009 04:24 PM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] CPU & Getpages huge increase mystery






Mark, if all things are equal as you indicate, then I’d look at predicates
and filtering involved to see if any of that has changed, has all the
characteristics of an access path change for the bad…make sure the type of
stats run are the same as before, I see you have already made some changes
there, that could cloud the issue. Also make sure the time is not being
spent in some other part of this module. PG

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Mark Vickers
Sent: Friday, January 09, 2009 10:00 AM
To: [login to unmask email]
Subject: [DB2-L] CPU & Getpages huge increase mystery


We had a new version of a program go into production on Tuesday.
We have confirmed that NO SQL changes were made.
One SQL statement has gone nutso on us.
Elapsed time & CPU time has increased 7 fold (in 28 minutes of clock time
it consumed 19 minutes of CPU !)
Getpages per SQL call went from 9 to 107.

The table in question (2 of them and are both reasonably static)
Had < 1000 inserts and updates since August 2008.
The program was last bound in October 2008.

We have compared the access path back to an explain from June 2008 and see
no differences.
In October 2008 the cost did go from 7ms to 19ms and from 81su to 231su.
This week it went up to 29ms & 350su.
We did not see any major performance degredation in October, but now it
was really bad.

We backed out the program and got the same explain and performance
problems with the old version.

This all points to the data and statistics right ?
But this is one of our most critical called modules and is hit > 500K
times a day, so I am hoping to optimize this as much as possible.

The two table counts are OTC=9K and STD=14K rows.

The query is somewhat of a bear (self referencing the same OTC table 5
times) and has a
category B with HOST VARIABLES AND TABLE CARDINALITY reason codes.

I tried adding DSTATS - VALUES 15,5 COLCARDF1 SCAN CNSW44.CNSOTC.* and
RUNSTATS TABLESPACE CNSW44.CNSOTCS
TABLE (CNSW44.CNSOTC)
COLUMN(<LISTED EVERY COLUMN>)
UPDATE ALL HISTORY ALL
INDEX(ALL KEYCARD
FREQVAL NUMCOLS 1 COUNT 25
FREQVAL NUMCOLS 2 COUNT 25
FREQVAL NUMCOLS 3 COUNT 25
FREQVAL NUMCOLS 4 COUNT 25
FREQVAL NUMCOLS 5 COUNT 25
)
[ on both tables ]
But this did not change the explain.

I am running out of ideas now, can anyone help please ?

Thanks,
Mark Vickers.

This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete all copies
of this message.



IDUG 2009 - Europe * 5-9 October * Rome, Italy
IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register here.


IDUG 2009 - Europe * 5-9 October * Rome, Italy
IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register here.




This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete all copies
of this message.



Mark Vickers

Re: CPU & Getpages huge increase mystery
(in response to Mark Vickers)
The query and all the stats etc was too much to compile, so I have
uploaded the Explain Report to my wife's website:

http://www.cajuncrap.com/explain/report.htm

<shameless plug> If you like Crawfish - take a look around at what she has
to offer please ! </shameless plug>

Now since you all took the time to answer me, I will try to answer all the
questions:
FYI: (we migrated production to v8 c.m. on August 18 2008 with no
problems)

Phil:
Query block 12 went from a multiple index scan with matching columns to a
single index scan with 0 (no) matching columns and that is where I think
the cost went way up.
No changes to stats being collected on Production - just trying stuff on
test.
Time - this is what this monster did yesterday:

Program........ CNPDRVB Location... Collection
ID... BATCHG Contoken... D561D74040404040
Program Type... PKGE Version....

Statement Type... STATIC
Section Number... 4 SQL +-------- Total IN-SQL Time
--------+ +- Sync I/O --+ +- Async I/O -+ Lock
Statement Number. 5314 Calls Elapsed % CPU %
Getpage Number Elapsed Waits Elapsed Wait %
-------- ------------------
------------------ -------- ------ -------- ------ -------- ------
246354 1:50:04.793 25.4 %
1:18:42.923 46.6 % 26188015 17 00.0587 22 00.2204 0.0 %
Averages: 00:00.02681 00:00.01917 106
0 00.0000 0 0.0000

I actually thought it was called from online programs too, but there is
another version for online ............
I am not even going there !

Here are the object statistics from yesterday:


Object
Statistics
STATIC
Creator Name BPool +------------- Getpage
-------------+ +------- Sync I/O ------+ +- Async I/O -+
Long Name Type BPool HRatio Total Avg Tot Time
% Avg Time Number Tot Time Avg Time Request Pages
----------------------------- ---- ------ ------ ------ ------
-------------- -------- ------- -------- -------- ------- -------
CNSW44 CNSOTC (total) 100 % 22244K 90 1:39.522
1.5% 00.0004 2 00.0227 00.0113 6 92
..
CNSW44 CNSOTC T BP18 100 % 11296 0 00.1263
0.0% 00.0000 0 00.0000 00.0000 0 0
..
CNSW44 CNSOTCX1 I BP15 100 % 22233K 90 1:39.396
1.5% 00.0004 2 00.0227 00.0113 6 92
...
DSNDB07 DSN4K01 (total) 100 % 871658 4 04.4581
0.1% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K01 BP7 100 % 5751 0 00.0307
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K02 BP7 100 % 5 0 00.0000
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K03 BP7 100 % 737015 3 03.8000
0.1% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K04 BP7 100 % 34568 0 00.1796
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K06 BP7 100 % 34584 0 00.1496
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K07 BP7 100 % 12 0 00.0001
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K09 BP7 100 % 26987 0 00.1544
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K10 BP7 100 % 5749 0 00.0251
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K12 BP7 100 % 26987 0 00.1186
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K01 (total) 100 % 601776 2 03.5645
0.1% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K01 BP7 100 % 2 0 00.0000
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K02 BP7 100 % 117395 0 00.7417
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K03 BP7 100 % 111536 0 00.8789
0.0% 00.0000 0 00.0000 00.0000 0 0


Gerald:
Bufferpool stats from yesterday - ranked by getpages - listed top 10 for
each bufferpool:
Not really sure what to make of the second object in BP15 ?
We can continue this offline as you suggested - just wanted everyone to
see all the info.

DB2: DSNP Buffer Pool: BP15 Database:
BPool +-------- Getpage -----------+
+------ Sync I/O ------+ +- Async I/O -+
Database Page Set Type BPool HRatio Number % Tot Time Avg Time
Number Tot Time Avg Time Request Pages
-------- -------- ---- ------ ------ ------------ -------- --------
------ -------- -------- ------- -------
>> CNSW44 CNSOTCX1 I BP15 100 % 22233K 93.7% 1:39.396 00.0000 2
00.0227 00.0113 6 92
+ WW44G W44GSRQM T BP15 0.0 % 1068K 4.5% 15:42.45 00.0009
189K 3:56.558 00.0013 4940 10074K
+ WW44C W44C1OIA I BP15 99.8 % 212615 0.9% 02.4673 00.0000
156 00.3057 00.0020 701 294
+ WW44C W44C2GIA I BP15 99.9 % 83366 0.4% 00.7160 00.0000
14 00.0221 00.0016 0 32
+ WW44C W44C1ITM I BP15 99.7 % 41169 0.2% 00.5850 00.0000
21 00.1327 00.0063 0 82
+ CNSW44 CNSINVX1 I BP15 81.2 % 23466 0.1% 14.5161 00.0006
4412 13.7460 00.0031 0 0
+ WW44C W44C1GIB I BP15 99.8 % 17026 0.1% 00.1675 00.0000
12 00.0618 00.0051 0 28
+ WW44C W44C2PUR I BP15 99.7 % 11728 0.0% 00.1518 00.0000 3
00.0106 00.0035 0 32
+ CNSW44 CNSIIDX3 I BP15 95.8 % 7762 0.0% 00.1533 00.0000 9
00.0606 00.0067 0 318
+ WW44C W44CSPOL T BP15 95.9 % 6009 0.0% 00.1999 00.0000
23 00.0309 00.0013 43 221

DB2: DSNP Buffer Pool: BP18 Database:
BPool +-------- Getpage -----------+
+------ Sync I/O ------+ +- Async I/O -+
Database Page Set Type BPool HRatio Number % Tot Time Avg Time
Number Tot Time Avg Time Request Pages
-------- -------- ---- ------ ------ ------------ -------- --------
------ -------- -------- ------- -------
+ WW44C W44C4BOL I BP18 100 % 241401 65.5% 01.0077 00.0000 5
00.0033 00.0007 0 0
+ WW44C W44C6BOL I BP18 100 % 34465 9.4% 00.1548 00.0000 4
00.0058 00.0014 0 0
+ WW44G W44GSCPT T BP18 99.8 % 29509 8.0% 00.3865 00.0000
11 00.0588 00.0053 4 58
+ WW44N W44N1BBI I BP18 31.3 % 14787 4.0% 11.5780 00.0008
3892 10.2893 00.0026 0 6269
>> CNSW44 CNSOTCS T BP18 100 % 11296 3.1% 00.1263 00.0000 0
00.0000 00.0000 0 0
+ WW44N W44N1RPM I BP18 99.7 % 10447 2.8% 00.0846 00.0000 6
00.0140 00.0023 0 27
+ WW44C W44C3POL I BP18 99.6 % 7640 2.1% 00.1317 00.0000
34 00.0696 00.0020 0 0
+ WW44C W44C1PDL I BP18 99.9 % 6600 1.8% 00.1114 00.0000 4
00.0364 00.0091 0 0
+ WW44G W44G1RPM I BP18 97.5 % 5955 1.6% 01.2068 00.0002
150 01.0478 00.0070 0 0
+ WW44C W44C2OUT I BP18 98.9 % 2562 0.7% 00.1037 00.0000
28 00.0784 00.0028 0 0


Kenney:
Bufferpools have not changed - if they did, I am the only one who would
have done so.
But on further thought here, someone could have added other objects that
use the same bufferpools, so I am going to isolate the tables and test
that, thanks.
FYI: (we migrated production to v8 c.m. on August 18 2008 with no
problems)

Roger / Paul / Robert:
There are dates and timestamps - stats were collected Jan 1 2009, so they
were OK.
Checked for rid pool failures and none found - table only has 9,000 rows !
The first thing one of the application DBA's did was to reorg/runstats and
that did not help -
I never got a chance to query to see if it really did need a reorg ?

Fred:
The only report I can scrape up for the old explain is this
BindImpactExpert comparison :

ImpactExpert for DB2 z/OS ----- Access Paths ------- LINE 00000001 COL 001
080
Command ===> Scroll ===>
PAGE
DB2:
DSNL
Primary cmd: END, D(etails on/off), C(atalog data)

Collection . BATCHG Timestamp. . 2008070211570000
Package. . . CNPDRVB Contoken . . 184D70920346F963
StmtNo . . . 5330 Bindtime . . 2008-06-03-12.18.45.626181



Access path before REBIND -------------| Access path with REBIND
--------------

TABLE QB PN AC MA ME IX PR | TABLE QB PN AC MA ME
IX PR
INDEX TY CO TH ON FT | INDEX TY CO TH
ON FT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - -
DSNWFQB(09) 1 1 R 0 0 N S | DSNWFQB(09) 1 1 R 0 0
N S
2 1 0 0 | 2 1 0 0

CNSOTC 4 1 I 3 0 Y | CNSOTC 4 1 I 3 0
Y
CNSOTCX1 | CNSOTCX1
CNSOTC 4 2 I 5 1 Y | CNSOTC 4 2 I 5 1
Y
CNSOTCX1 | CNSOTCX1
5 1 0 0 | 5 1 0 0

DSNWFQB(05) 9 1 R 0 0 N S | DSNWFQB(05) 9 1 R 0 0
N S
CNSOTC 10 1 I 2 0 Y | CNSOTC 10 1 I 2 0
Y
CNSOTCX1 | CNSOTCX1
CNSSTD 10 2 I 3 1 N | CNSSTD 10 2 I 3 1
N
CNSSTDX1 | CNSSTDX1
CNSOTC 10 3 M 0 1 N L | CNSOTC 10 3 M 0 1
N L
CNSOTC 10 3 MX 2 1 Y | CNSOTC 10 3 MX 2 1
Y
CNSOTCX1 | CNSOTCX1
CNSOTC 10 3 MX 2 1 Y | CNSOTC 10 3 MX 2 1
Y
CNSOTCX1 | CNSOTCX1
CNSOTC 10 3 MU 0 1 N | CNSOTC 10 3 MU 0 1
N
DSNWFQB(02) 11 1 R 0 0 N S | DSNWFQB(02) 11 1 R 0 0
N S
CNSOTC 12 1 M 0 0 N L | CNSOTC 12 1 M 0 0
N L
CNSOTC 12 1 MX 2 0 Y | CNSOTC 12 1 MX 2 0
Y
CNSOTCX1 | CNSOTCX1
CNSOTC 12 1 MX 2 0 Y | CNSOTC 12 1 MX 2 0
Y
CNSOTCX1 | CNSOTCX1
CNSOTC 12 1 MU 0 0 N | CNSOTC 12 1 MU 0 0
N
|
Milliseconds: 7 | Milliseconds: 19
Serviceunits: 81 | Serviceunits: 231


This morning I used Visual Explain's Statistics Advisor and it suggested
the following (which I ran and got no change on access path)
RUNSTATS TABLESPACE CNSW44.CNSSTDS
TABLE(CNSW44.CNSSTD)
COLUMN(CPY_CD)
COLGROUP(CPY_CD,STR_NBR_DSH) FREQVAL COUNT 10
COLGROUP(STR_NBR_DSH) FREQVAL COUNT 10
SORTDEVT SYSDA SORTNUM 4
SHRLEVEL CHANGE REPORT YES
RUNSTATS TABLESPACE CNSW44.CNSOTCS
TABLE(CNSW44.CNSOTC)
COLUMN(CPY_CD)
COLGROUP(CPY_CD,STR_NBR_MST) FREQVAL COUNT 1
COLGROUP(STR_NBR_MST) FREQVAL COUNT 10
COLGROUP(ACV_FL) FREQVAL COUNT 1
COLGROUP(INL_EXL_CD) FREQVAL COUNT 10
COLGROUP(ACV_FL,AD_GRP,CPY_CD,FAC_WHS,INL_EXL_CD,
ITM_NBR_6,STR_NBR_MST)
COLGROUP(ACV_FL,CPY_CD,INL_EXL_CD,STR_NBR_MST)
COLGROUP(FAC_WHS) FREQVAL COUNT 10
COLGROUP(ACV_FL,CPY_CD,FAC_WHS,INL_EXL_CD,ITM_NBR_6,
STR_NBR_MST)
COLGROUP(AD_GRP) FREQVAL COUNT 10
SORTDEVT SYSDA SORTNUM 4
INDEX(CNSW44.CNSOTCX1 KEYCARD)
SHRLEVEL CHANGE REPORT YES

So, while you are spending the rest of the day trying to wade through all
of this,
I am thinking about throwing some differnt indexes at the problem using
some of the combinations from the COLGROUPS.

Don't go away - I'll be right back ...................
Thanks again.




This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete all copies
of this message.



______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Fred Edgar

Re: CPU & Getpages huge increase mystery
(in response to Mark Vickers)
Mark,

At first blush, 106 average getpages and 19 milliseconds of CPU doesn't look too bad
considering the SQL and access path. Do you have numbers from before?

Fred

Mark Vickers <[login to unmask email]> wrote:

The query and all the stats etc was too much to compile, so I have uploaded the Explain Report to my wife's website:

http://www.cajuncrap.com/explain/report.htm

<shameless plug> If you like Crawfish - take a look around at what she has to offer please ! </shameless plug>

Now since you all took the time to answer me, I will try to answer all the questions:
FYI: (we migrated production to v8 c.m. on August 18 2008 with no problems)

Phil:
Query block 12 went from a multiple index scan with matching columns to a single index scan with 0 (no) matching columns and that is where I think the cost went way up.
No changes to stats being collected on Production - just trying stuff on test.
Time - this is what this monster did yesterday:

Program........ CNPDRVB Location... Collection ID... BATCHG Contoken... D561D74040404040
Program Type... PKGE Version....

Statement Type... STATIC
Section Number... 4 SQL +-------- Total IN-SQL Time --------+ +- Sync I/O --+ +- Async I/O -+ Lock
Statement Number. 5314 Calls Elapsed % CPU % Getpage Number Elapsed Waits Elapsed Wait %
-------- ------------------ ------------------ -------- ------ -------- ------ -------- ------
246354 1:50:04.793 25.4 % 1:18:42.923 46.6 % 26188015 17 00.0587 22 00.2204 0.0 %
Averages: 00:00.02681 00:00.01917 106 0 00.0000 0 0.0000

I actually thought it was called from online programs too, but there is another version for online ............
I am not even going there !

Here are the object statistics from yesterday:


Object Statistics
STATIC
Creator Name BPool +------------- Getpage -------------+ +------- Sync I/O ------+ +- Async I/O -+
Long Name Type BPool HRatio Total Avg Tot Time % Avg Time Number Tot Time Avg Time Request Pages
----------------------------- ---- ------ ------ ------ ------ -------------- -------- ------- -------- -------- ------- -------
CNSW44 CNSOTC (total) 100 % 22244K 90 1:39.522 1.5% 00.0004 2 00.0227 00.0113 6 92
..
CNSW44 CNSOTC T BP18 100 % 11296 0 00.1263 0.0% 00.0000 0 00.0000 00.0000 0 0
..
CNSW44 CNSOTCX1 I BP15 100 % 22233K 90 1:39.396 1.5% 00.0004 2 00.0227 00.0113 6 92
...
DSNDB07 DSN4K01 (total) 100 % 871658 4 04.4581 0.1% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K01 BP7 100 % 5751 0 00.0307 0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K02 BP7 100 % 5 0 00.0000 0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K03 BP7 100 % 737015 3 03.8000 0.1% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K04 BP7 100 % 34568 0 00.1796 0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K06 BP7 100 % 34584 0 00.1496 0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K07 BP7 100 % 12 0 00.0001 0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K09 BP7 100 % 26987 0 00.1544 0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K10 BP7 100 % 5749 0 00.0251 0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K12 BP7 100 % 26987 0 00.1186 0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K01 (total) 100 % 601776 2 03.5645 0.1% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K01 BP7 100 % 2 0 00.0000 0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K02 BP7 100 % 117395 0 00.7417 0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K03 BP7 100 % 111536 0 00.8789 0.0% 00.0000 0 00.0000 00.0000 0 0


Gerald:
Bufferpool stats from yesterday - ranked by getpages - listed top 10 for each bufferpool:
Not really sure what to make of the second object in BP15 ?
We can continue this offline as you suggested - just wanted everyone to see all the info.

DB2: DSNP Buffer Pool: BP15 Database:
BPool +-------- Getpage -----------+ +------ Sync I/O ------+ +- Async I/O -+
Database Page Set Type BPool HRatio Number % Tot Time Avg Time Number Tot Time Avg Time Request Pages
-------- -------- ---- ------ ------ ------------ -------- -------- ------ -------- -------- ------- -------
>> CNSW44 CNSOTCX1 I BP15 100 % 22233K 93.7% 1:39.396 00.0000 2 00.0227 00.0113 6 92
+ WW44G W44GSRQM T BP15 0.0 % 1068K 4.5% 15:42.45 00.0009 189K 3:56.558 00.0013 4940 10074K
+ WW44C W44C1OIA I BP15 99.8 % 212615 0.9% 02.4673 00.0000 156 00.3057 00.0020 701 294
+ WW44C W44C2GIA I BP15 99.9 % 83366 0.4% 00.7160 00.0000 14 00.0221 00.0016 0 32
+ WW44C W44C1ITM I BP15 99.7 % 41169 0.2% 00.5850 00.0000 21 00.1327 00.0063 0 82
+ CNSW44 CNSINVX1 I BP15 81.2 % 23466 0.1% 14.5161 00.0006 4412 13.7460 00.0031 0 0
+ WW44C W44C1GIB I BP15 99.8 % 17026 0.1% 00.1675 00.0000 12 00.0618 00.0051 0 28
+ WW44C W44C2PUR I BP15 99.7 % 11728 0.0% 00.1518 00.0000 3 00.0106 00.0035 0 32
+ CNSW44 CNSIIDX3 I BP15 95.8 % 7762 0.0% 00.1533 00.0000 9 00.0606 00.0067 0 318
+ WW44C W44CSPOL T BP15 95.9 % 6009 0.0% 00.1999 00.0000 23 00.0309 00.0013 43 221

DB2: DSNP Buffer Pool: BP18 Database:
BPool +-------- Getpage -----------+ +------ Sync I/O ------+ +- Async I/O -+
Database Page Set Type BPool HRatio Number % Tot Time Avg Time Number Tot Time Avg Time Request Pages
-------- -------- ---- ------ ------ ------------ -------- -------- ------ -------- -------- ------- -------
+ WW44C W44C4BOL I BP18 100 % 241401 65.5% 01.0077 00.0000 5 00.0033 00.0007 0 0
+ WW44C W44C6BOL I BP18 100 % 34465 9.4% 00.1548 00.0000 4 00.0058 00.0014 0 0
+ WW44G W44GSCPT T BP18 99.8 % 29509 8.0% 00.3865 00.0000 11 00.0588 00.0053 4 58
+ WW44N W44N1BBI I BP18 31.3 % 14787 4.0% 11.5780 00.0008 3892 10.2893 00.0026 0 6269
>> CNSW44 CNSOTCS T BP18 100 % 11296 3.1% 00.1263 00.0000 0 00.0000 00.0000 0 0
+ WW44N W44N1RPM I BP18 99.7 % 10447 2.8% 00.0846 00.0000 6 00.0140 00.0023 0 27
+ WW44C W44C3POL I BP18 99.6 % 7640 2.1% 00.1317 00.0000 34 00.0696 00.0020 0 0
+ WW44C W44C1PDL I BP18 99.9 % 6600 1.8% 00.1114 00.0000 4 00.0364 00.0091 0 0
+ WW44G W44G1RPM I BP18 97.5 % 5955 1.6% 01.2068 00.0002 150 01.0478 00.0070 0 0
+ WW44C W44C2OUT I BP18 98.9 % 2562 0.7% 00.1037 00.0000 28 00.0784 00.0028 0 0


Kenney:
Bufferpools have not changed - if they did, I am the only one who would have done so.
But on further thought here, someone could have added other objects that use the same bufferpools, so I am going to isolate the tables and test that, thanks.
FYI: (we migrated production to v8 c.m. on August 18 2008 with no problems)

Roger / Paul / Robert:
There are dates and timestamps - stats were collected Jan 1 2009, so they were OK.
Checked for rid pool failures and none found - table only has 9,000 rows !
The first thing one of the application DBA's did was to reorg/runstats and that did not help -
I never got a chance to query to see if it really did need a reorg ?

Fred:
The only report I can scrape up for the old explain is this BindImpactExpert comparison :

ImpactExpert for DB2 z/OS ----- Access Paths ------- LINE 00000001 COL 001 080
Command ===> Scroll ===> PAGE
DB2: DSNL
Primary cmd: END, D(etails on/off), C(atalog data)

Collection . BATCHG Timestamp. . 2008070211570000
Package. . . CNPDRVB Contoken . . 184D70920346F963
StmtNo . . . 5330 Bindtime . . 2008-06-03-12.18.45.626181


Access path before REBIND -------------| Access path with REBIND --------------

TABLE QB PN AC MA ME IX PR | TABLE QB PN AC MA ME IX PR
INDEX TY CO TH ON FT | INDEX TY CO TH ON FT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
DSNWFQB(09) 1 1 R 0 0 N S | DSNWFQB(09) 1 1 R 0 0 N S
2 1 0 0 | 2 1 0 0
CNSOTC 4 1 I 3 0 Y | CNSOTC 4 1 I 3 0 Y
CNSOTCX1 | CNSOTCX1
CNSOTC 4 2 I 5 1 Y | CNSOTC 4 2 I 5 1 Y
CNSOTCX1 | CNSOTCX1
5 1 0 0 | 5 1 0 0
DSNWFQB(05) 9 1 R 0 0 N S | DSNWFQB(05) 9 1 R 0 0 N S
CNSOTC 10 1 I 2 0 Y | CNSOTC 10 1 I 2 0 Y
CNSOTCX1 | CNSOTCX1
CNSSTD 10 2 I 3 1 N | CNSSTD 10 2 I 3 1 N
CNSSTDX1 | CNSSTDX1
CNSOTC 10 3 M 0 1 N L | CNSOTC 10 3 M 0 1 N L
CNSOTC 10 3 MX 2 1 Y | CNSOTC 10 3 MX 2 1 Y
CNSOTCX1 | CNSOTCX1
CNSOTC 10 3 MX 2 1 Y | CNSOTC 10 3 MX 2 1 Y
CNSOTCX1 | CNSOTCX1
CNSOTC 10 3 MU 0 1 N | CNSOTC 10 3 MU 0 1 N
DSNWFQB(02) 11 1 R 0 0 N S | DSNWFQB(02) 11 1 R 0 0 N S
CNSOTC 12 1 M 0 0 N L | CNSOTC 12 1 M 0 0 N L
CNSOTC 12 1 MX 2 0 Y | CNSOTC 12 1 MX 2 0 Y
CNSOTCX1 | CNSOTCX1
CNSOTC 12 1 MX 2 0 Y | CNSOTC 12 1 MX 2 0 Y
CNSOTCX1 | CNSOTCX1
CNSOTC 12 1 MU 0 0 N | CNSOTC 12 1 MU 0 0 N
|
Milliseconds: 7 | Milliseconds: 19
Serviceunits: 81 | Serviceunits: 231


This morning I used Visual Explain's Statistics Advisor and it suggested the following (which I ran and got no change on access path)
RUNSTATS TABLESPACE CNSW44.CNSSTDS
TABLE(CNSW44.CNSSTD)
COLUMN(CPY_CD)
COLGROUP(CPY_CD,STR_NBR_DSH) FREQVAL COUNT 10
COLGROUP(STR_NBR_DSH) FREQVAL COUNT 10
SORTDEVT SYSDA SORTNUM 4
SHRLEVEL CHANGE REPORT YES
RUNSTATS TABLESPACE CNSW44.CNSOTCS
TABLE(CNSW44.CNSOTC)
COLUMN(CPY_CD)
COLGROUP(CPY_CD,STR_NBR_MST) FREQVAL COUNT 1
COLGROUP(STR_NBR_MST) FREQVAL COUNT 10
COLGROUP(ACV_FL) FREQVAL COUNT 1
COLGROUP(INL_EXL_CD) FREQVAL COUNT 10
COLGROUP(ACV_FL,AD_GRP,CPY_CD,FAC_WHS,INL_EXL_CD,
ITM_NBR_6,STR_NBR_MST)
COLGROUP(ACV_FL,CPY_CD,INL_EXL_CD,STR_NBR_MST)
COLGROUP(FAC_WHS) FREQVAL COUNT 10
COLGROUP(ACV_FL,CPY_CD,FAC_WHS,INL_EXL_CD,ITM_NBR_6,
STR_NBR_MST)
COLGROUP(AD_GRP) FREQVAL COUNT 10
SORTDEVT SYSDA SORTNUM 4
INDEX(CNSW44.CNSOTCX1 KEYCARD)
SHRLEVEL CHANGE REPORT YES

So, while you are spending the rest of the day trying to wade through all of this,
I am thinking about throwing some differnt indexes at the problem using some of the combinations from the COLGROUPS.

Don't go away - I'll be right back ...................
Thanks again.



This e-mail (and any attachments) may contain information that is confidential and/or protected by law. Any review, use, distribution or disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and delete all copies of this message.


---------------------------------
IDUG 2009 - Europe * 5-9 October * Rome, Italy
IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here.



______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Mark Vickers

Re: CPU & Getpages huge increase mystery
(in response to Fred Edgar)
Hi Fred,
There was a lot of data to look at, so I am sure I just confused everyone.

The current explain is at 29 ms & 480 su and running at 109 getpages.

The previous acceptable run was at 19 ms & 106 getpages.

Prior to that it was 7 ms (don't know the GP).

The SQL has not changed for both rebinds.

Problem is, it is now so CPU intensive, we have had to alter the schedule
so it can run alone during the day so we can get some CICS thruput !

We have a new version of the data and program using roll-up tables (which
I don't like) but so far in test, we have it down to 0.002 cpu secs per
SQL and it was at 0.019.

I just wish I had stats to compare to see what changed.

Thanks,
Mark.



Fred Edgar <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/13/2009 04:09 PM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] CPU & Getpages huge increase mystery






Mark,

At first blush, 106 average getpages and 19 milliseconds of CPU doesn't
look too bad
considering the SQL and access path. Do you have numbers from before?

Fred

Mark Vickers <[login to unmask email]> wrote:

The query and all the stats etc was too much to compile, so I have
uploaded the Explain Report to my wife's website:

http://www.cajuncrap.com/explain/report.htm

<shameless plug> If you like Crawfish - take a look around at what she has
to offer please ! </shameless plug>

Now since you all took the time to answer me, I will try to answer all the
questions:
FYI: (we migrated production to v8 c.m. on August 18 2008 with no
problems)

Phil:
Query block 12 went from a multiple index scan with matching columns to a
single index scan with 0 (no) matching columns and that is where I think
the cost went way up.
No changes to stats being collected on Production - just trying stuff on
test.
Time - this is what this monster did yesterday:

Program........ CNPDRVB Location... Collection
ID... BATCHG Contoken... D561D74040404040
Program Type... PKGE Version....

Statement Type... STATIC
Section Number... 4 SQL +-------- Total IN-SQL Time
--------+ +- Sync I/O --+ +- Async I/O -+ Lock
Statement Number. 5314 Calls Elapsed % CPU %
Getpage Number Elapsed Waits Elapsed Wait %
-------- ------------------
------------------ -------- ------ -------- ------ -------- ------
246354 1:50:04.793 25.4 % 1:18:42.923
46.6 % 26188015 17 00.0587 22 00.2204 0.0 %
Averages: 00:00.02681 00:00.01917
106 0 00.0000 0 0.0000

I actually thought it was called from online programs too, but there is
another version for online ............
I am not even going there !

Here are the object statistics from yesterday:


Object
Statistics
STATIC
Creator Name BPool +------------- Getpage
-------------+ +------- Sync I/O ------+ +- Async I/O -+
Long Name Type BPool HRatio Total Avg Tot Time
% Avg Time Number Tot Time Avg Time Request Pages
----------------------------- ---- ------ ------ ------ ------
-------------- -------- ------- -------- -------- ------- -------
CNSW44 CNSOTC (total) 100 % 22244K 90 1:39.522
1.5% 00.0004 2 00.0227 00.0113 6 92
..
CNSW44 CNSOTC T BP18 100 % 11296 0 00.1263
0.0% 00.0000 0 00.0000 00.0000 0 0
..
CNSW44 CNSOTCX1 I BP15 100 % 22233K 90 1:39.396
1.5% 00.0004 2 00.0227 00.0113 6 92
...
DSNDB07 DSN4K01 (total) 100 % 871658 4 04.4581
0.1% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K01 BP7 100 % 5751 0 00.0307
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K02 BP7 100 % 5 0 00.0000
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K03 BP7 100 % 737015 3 03.8000
0.1% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K04 BP7 100 % 34568 0 00.1796
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K06 BP7 100 % 34584 0 00.1496
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K07 BP7 100 % 12 0 00.0001
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K09 BP7 100 % 26987 0 00.1544
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K10 BP7 100 % 5749 0 00.0251
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K12 BP7 100 % 26987 0 00.1186
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K01 (total) 100 % 601776 2 03.5645
0.1% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K01 BP7 100 % 2 0 00.0000
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K02 BP7 100 % 117395 0 00.7417
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K03 BP7 100 % 111536 0 00.8789
0.0% 00.0000 0 00.0000 00.0000 0 0


Gerald:
Bufferpool stats from yesterday - ranked by getpages - listed top 10 for
each bufferpool:
Not really sure what to make of the second object in BP15 ?
We can continue this offline as you suggested - just wanted everyone to
see all the info.

DB2: DSNP Buffer Pool: BP15 Database:
BPool +-------- Getpage -----------+
+------ Sync I/O ------+ +- Async I/O -+
Database Page Set Type BPool HRatio Number % Tot Time Avg Time
Number Tot Time Avg Time Request Pages
-------- -------- ---- ------ ------ ------------ -------- --------
------ -------- -------- ------- -------
>> CNSW44 CNSOTCX1 I BP15 100 % 22233K 93.7% 1:39.396 00.0000 2
00.0227 00.0113 6 92
+ WW44G W44GSRQM T BP15 0.0 % 1068K 4.5% 15:42.45 00.0009
189K 3:56.558 00.0013 4940 10074K
+ WW44C W44C1OIA I BP15 99.8 % 212615 0.9% 02.4673 00.0000
156 00.3057 00.0020 701 294
+ WW44C W44C2GIA I BP15 99.9 % 83366 0.4% 00.7160 00.0000
14 00.0221 00.0016 0 32
+ WW44C W44C1ITM I BP15 99.7 % 41169 0.2% 00.5850 00.0000
21 00.1327 00.0063 0 82
+ CNSW44 CNSINVX1 I BP15 81.2 % 23466 0.1% 14.5161 00.0006
4412 13.7460 00.0031 0 0
+ WW44C W44C1GIB I BP15 99.8 % 17026 0.1% 00.1675 00.0000
12 00.0618 00.0051 0 28
+ WW44C W44C2PUR I BP15 99.7 % 11728 0.0% 00.1518 00.0000 3
00.0106 00.0035 0 32
+ CNSW44 CNSIIDX3 I BP15 95.8 % 7762 0.0% 00.1533 00.0000 9
00.0606 00.0067 0 318
+ WW44C W44CSPOL T BP15 95.9 % 6009 0.0% 00.1999 00.0000
23 00.0309 00.0013 43 221

DB2: DSNP Buffer Pool: BP18 Database:
BPool +-------- Getpage -----------+
+------ Sync I/O ------+ +- Async I/O -+
Database Page Set Type BPool HRatio Number % Tot Time Avg Time
Number Tot Time Avg Time Request Pages
-------- -------- ---- ------ ------ ------------ -------- --------
------ -------- -------- ------- -------
+ WW44C W44C4BOL I BP18 100 % 241401 65.5% 01.0077 00.0000 5
00.0033 00.0007 0 0
+ WW44C W44C6BOL I BP18 100 % 34465 9.4% 00.1548 00.0000 4
00.0058 00.0014 0 0
+ WW44G W44GSCPT T BP18 99.8 % 29509 8.0% 00.3865 00.0000
11 00.0588 00.0053 4 58
+ WW44N W44N1BBI I BP18 31.3 % 14787 4.0% 11.5780 00.0008
3892 10.2893 00.0026 0 6269
>> CNSW44 CNSOTCS T BP18 100 % 11296 3.1% 00.1263 00.0000 0
00.0000 00.0000 0 0
+ WW44N W44N1RPM I BP18 99.7 % 10447 2.8% 00.0846 00.0000 6
00.0140 00.0023 0 27
+ WW44C W44C3POL I BP18 99.6 % 7640 2.1% 00.1317 00.0000
34 00.0696 00.0020 0 0
+ WW44C W44C1PDL I BP18 99.9 % 6600 1.8% 00.1114 00.0000 4
00.0364 00.0091 0 0
+ WW44G W44G1RPM I BP18 97.5 % 5955 1.6% 01.2068 00.0002
150 01.0478 00.0070 0 0
+ WW44C W44C2OUT I BP18 98.9 % 2562 0.7% 00.1037 00.0000
28 00.0784 00.0028 0 0


Kenney:
Bufferpools have not changed - if they did, I am the only one who would
have done so.
But on further thought here, someone could have added other objects that
use the same bufferpools, so I am going to isolate the tables and test
that, thanks.
FYI: (we migrated production to v8 c.m. on August 18 2008 with no
problems)

Roger / Paul / Robert:
There are dates and timestamps - stats were collected Jan 1 2009, so they
were OK.
Checked for rid pool failures and none found - table only has 9,000 rows !

The first thing one of the application DBA's did was to reorg/runstats and
that did not help -
I never got a chance to query to see if it really did need a reorg ?

Fred:
The only report I can scrape up for the old explain is this
BindImpactExpert comparison :

ImpactExpert for DB2 z/OS ----- Access Paths ------- LINE 00000001 COL 001
080
Command ===> Scroll ===>
PAGE
DB2:
DSNL
Primary cmd: END, D(etails on/off), C(atalog data)

Collection . BATCHG Timestamp. . 2008070211570000
Package. . . CNPDRVB Contoken . . 184D70920346F963
StmtNo . . . 5330 Bindtime . . 2008-06-03-12.18.45.626181



Access path before REBIND -------------| Access path with REBIND
--------------

TABLE QB PN AC MA ME IX PR | TABLE QB PN AC MA ME
IX PR
INDEX TY CO TH ON FT | INDEX TY CO TH
ON FT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - -
DSNWFQB(09) 1 1 R 0 0 N S | DSNWFQB(09) 1 1 R 0 0
N S
2 1 0 0 | 2 1 0 0

CNSOTC 4 1 I 3 0 Y | CNSOTC 4 1 I 3 0
Y
CNSOTCX1 | CNSOTCX1
CNSOTC 4 2 I 5 1 Y | CNSOTC 4 2 I 5 1
Y
CNSOTCX1 | CNSOTCX1
5 1 0 0 | 5 1 0 0

DSNWFQB(05) 9 1 R 0 0 N S | DSNWFQB(05) 9 1 R 0 0
N S
CNSOTC 10 1 I 2 0 Y | CNSOTC 10 1 I 2 0
Y
CNSOTCX1 | CNSOTCX1
CNSSTD 10 2 I 3 1 N | CNSSTD 10 2 I 3 1
N
CNSSTDX1 | CNSSTDX1
CNSOTC 10 3 M 0 1 N L | CNSOTC 10 3 M 0 1
N L
CNSOTC 10 3 MX 2 1 Y | CNSOTC 10 3 MX 2 1
Y
CNSOTCX1 | CNSOTCX1
CNSOTC 10 3 MX 2 1 Y | CNSOTC 10 3 MX 2 1
Y
CNSOTCX1 | CNSOTCX1
CNSOTC 10 3 MU 0 1 N | CNSOTC 10 3 MU 0 1
N
DSNWFQB(02) 11 1 R 0 0 N S | DSNWFQB(02) 11 1 R 0 0
N S
CNSOTC 12 1 M 0 0 N L | CNSOTC 12 1 M 0 0
N L
CNSOTC 12 1 MX 2 0 Y | CNSOTC 12 1 MX 2 0
Y
CNSOTCX1 | CNSOTCX1
CNSOTC 12 1 MX 2 0 Y | CNSOTC 12 1 MX 2 0
Y
CNSOTCX1 | CNSOTCX1
CNSOTC 12 1 MU 0 0 N | CNSOTC 12 1 MU 0 0
N
|
Milliseconds: 7 | Milliseconds: 19
Serviceunits: 81 | Serviceunits: 231


This morning I used Visual Explain's Statistics Advisor and it suggested
the following (which I ran and got no change on access path)
RUNSTATS TABLESPACE CNSW44.CNSSTDS
TABLE(CNSW44.CNSSTD)
COLUMN(CPY_CD)
COLGROUP(CPY_CD,STR_NBR_DSH) FREQVAL COUNT 10
COLGROUP(STR_NBR_DSH) FREQVAL COUNT 10
SORTDEVT SYSDA SORTNUM 4
SHRLEVEL CHANGE REPORT YES
RUNSTATS TABLESPACE CNSW44.CNSOTCS
TABLE(CNSW44.CNSOTC)
COLUMN(CPY_CD)
COLGROUP(CPY_CD,STR_NBR_MST) FREQVAL COUNT 1
COLGROUP(STR_NBR_MST) FREQVAL COUNT 10
COLGROUP(ACV_FL) FREQVAL COUNT 1
COLGROUP(INL_EXL_CD) FREQVAL COUNT 10
COLGROUP(ACV_FL,AD_GRP,CPY_CD,FAC_WHS,INL_EXL_CD,
ITM_NBR_6,STR_NBR_MST)
COLGROUP(ACV_FL,CPY_CD,INL_EXL_CD,STR_NBR_MST)
COLGROUP(FAC_WHS) FREQVAL COUNT 10
COLGROUP(ACV_FL,CPY_CD,FAC_WHS,INL_EXL_CD,ITM_NBR_6,
STR_NBR_MST)
COLGROUP(AD_GRP) FREQVAL COUNT 10
SORTDEVT SYSDA SORTNUM 4
INDEX(CNSW44.CNSOTCX1 KEYCARD)
SHRLEVEL CHANGE REPORT YES

So, while you are spending the rest of the day trying to wade through all
of this,
I am thinking about throwing some differnt indexes at the problem using
some of the combinations from the COLGROUPS.

Don't go away - I'll be right back ...................
Thanks again.



This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete all copies
of this message.



IDUG 2009 - Europe * 5-9 October * Rome, Italy
IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register here.



IDUG 2009 - Europe * 5-9 October * Rome, Italy
IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register here.




This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete all copies
of this message.



______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Richard Humphris

Re: CPU & Getpages huge increase mystery
(in response to Mark Vickers)
Hi Mark,

Take a look at this hiper ptf from IBM (apar PK74545 / ptf UK41678) it
talks about the optimizer sometimes choosing the wrong access path when
the SQL uses the clauses:
"FETCH FIRST n ROWS ONLY"
or "OPTIMIZE FOR n ROWS".


This ptf just became available on 12/4/2008.



IBM says: Sometimes when the "FETCH FIRST n ROWS ONLY" or "OPTIMIZE FOR
n ROWS clause" is present, a suboptimal pipeline plan may be chosen due
to an internal

bug where a certain field containing an estimated cost used during query
optimization is not set. This can cause query performance problems.
PROBLEM CONCLUSION: The performance problem described above is fixed by
setting the field correctly.
Rich Humphris


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Mark Vickers
Sent: Tuesday, January 13, 2009 5:12 PM
To: [login to unmask email]
Subject: Re: [DB2-L] CPU & Getpages huge increase mystery



Hi Fred,
There was a lot of data to look at, so I am sure I just confused
everyone.

The current explain is at 29 ms & 480 su and running at 109
getpages.

The previous acceptable run was at 19 ms & 106 getpages.

Prior to that it was 7 ms (don't know the GP).

The SQL has not changed for both rebinds.

Problem is, it is now so CPU intensive, we have had to alter the
schedule so it can run alone during the day so we can get some CICS
thruput !

We have a new version of the data and program using roll-up
tables (which I don't like) but so far in test, we have it down to 0.002
cpu secs per SQL and it was at 0.019.

I just wish I had stats to compare to see what changed.

Thanks,
Mark.



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

01/13/2009 04:09 PM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc
Subject
Re: [DB2-L] CPU & Getpages huge increase mystery






Mark,

At first blush, 106 average getpages and 19 milliseconds of CPU
doesn't look too bad
considering the SQL and access path. Do you have numbers from
before?

Fred

Mark Vickers <[login to unmask email]> wrote:

The query and all the stats etc was too much to compile, so I
have uploaded the Explain Report to my wife's website:

http://www.cajuncrap.com/explain/report.htm
< http://www.cajuncrap.com/explain/report.htm >

<shameless plug> If you like Crawfish - take a look around at
what she has to offer please ! </shameless plug>

Now since you all took the time to answer me, I will try to
answer all the questions:
FYI: (we migrated production to v8 c.m. on August 18 2008 with
no problems)

Phil:
Query block 12 went from a multiple index scan with matching
columns to a single index scan with 0 (no) matching columns and that is
where I think the cost went way up.
No changes to stats being collected on Production - just trying
stuff on test.
Time - this is what this monster did yesterday:

Program........ CNPDRVB Location...
Collection ID... BATCHG Contoken... D561D74040404040
Program Type... PKGE Version....



Statement Type... STATIC

Section Number... 4 SQL +-------- Total
IN-SQL Time --------+ +- Sync I/O --+ +- Async I/O -+ Lock
Statement Number. 5314 Calls Elapsed % CPU
% Getpage Number Elapsed Waits Elapsed Wait %
-------- ------------------
------------------ -------- ------ -------- ------ -------- ------
246354 1:50:04.793 25.4 %
1:18:42.923 46.6 % 26188015 17 00.0587 22 00.2204 0.0 %
Averages: 00:00.02681
00:00.01917 106 0 00.0000 0 0.0000

I actually thought it was called from online programs too, but
there is another version for online ............
I am not even going there !

Here are the object statistics from yesterday:



Object
Statistics
STATIC

Creator Name BPool +-------------
Getpage -------------+ +------- Sync I/O ------+ +- Async I/O -+
Long Name Type BPool HRatio Total Avg
Tot Time % Avg Time Number Tot Time Avg Time Request Pages
----------------------------- ---- ------ ------ ------ ------
-------------- -------- ------- -------- -------- ------- -------
CNSW44 CNSOTC (total) 100 % 22244K 90
1:39.522 1.5% 00.0004 2 00.0227 00.0113 6 92
..

CNSW44 CNSOTC T BP18 100 % 11296 0
00.1263 0.0% 00.0000 0 00.0000 00.0000 0 0
..

CNSW44 CNSOTCX1 I BP15 100 % 22233K 90
1:39.396 1.5% 00.0004 2 00.0227 00.0113 6 92
...

DSNDB07 DSN4K01 (total) 100 % 871658 4
04.4581 0.1% 00.0000 0 00.0000 00.0000 0 0


DSNDB07 DSN4K01 BP7 100 % 5751 0
00.0307 0.0% 00.0000 0 00.0000 00.0000 0 0


DSNDB07 DSN4K02 BP7 100 % 5 0
00.0000 0.0% 00.0000 0 00.0000 00.0000 0 0


DSNDB07 DSN4K03 BP7 100 % 737015 3
03.8000 0.1% 00.0000 0 00.0000 00.0000 0 0


DSNDB07 DSN4K04 BP7 100 % 34568 0
00.1796 0.0% 00.0000 0 00.0000 00.0000 0 0


DSNDB07 DSN4K06 BP7 100 % 34584 0
00.1496 0.0% 00.0000 0 00.0000 00.0000 0 0


DSNDB07 DSN4K07 BP7 100 % 12 0
00.0001 0.0% 00.0000 0 00.0000 00.0000 0 0


DSNDB07 DSN4K09 BP7 100 % 26987 0
00.1544 0.0% 00.0000 0 00.0000 00.0000 0 0


DSNDB07 DSN4K10 BP7 100 % 5749 0
00.0251 0.0% 00.0000 0 00.0000 00.0000 0 0


DSNDB07 DSN4K12 BP7 100 % 26987 0
00.1186 0.0% 00.0000 0 00.0000 00.0000 0 0


DSNDB07 DSN4K01 (total) 100 % 601776 2
03.5645 0.1% 00.0000 0 00.0000 00.0000 0 0


DSNDB07 DSN4K01 BP7 100 % 2 0
00.0000 0.0% 00.0000 0 00.0000 00.0000 0 0


DSNDB07 DSN4K02 BP7 100 % 117395 0
00.7417 0.0% 00.0000 0 00.0000 00.0000 0 0


DSNDB07 DSN4K03 BP7 100 % 111536 0
00.8789 0.0% 00.0000 0 00.0000 00.0000 0 0



Gerald:
Bufferpool stats from yesterday - ranked by getpages - listed
top 10 for each bufferpool:
Not really sure what to make of the second object in BP15 ?
We can continue this offline as you suggested - just wanted
everyone to see all the info.

DB2: DSNP Buffer Pool: BP15 Database:

BPool +-------- Getpage
-----------+ +------ Sync I/O ------+ +- Async I/O -+
Database Page Set Type BPool HRatio Number % Tot Time Avg
Time Number Tot Time Avg Time Request Pages
-------- -------- ---- ------ ------ ------------ --------
-------- ------ -------- -------- ------- -------
>> CNSW44 CNSOTCX1 I BP15 100 % 22233K 93.7% 1:39.396
00.0000 2 00.0227 00.0113 6 92
+ WW44G W44GSRQM T BP15 0.0 % 1068K 4.5% 15:42.45
00.0009 189K 3:56.558 00.0013 4940 10074K
+ WW44C W44C1OIA I BP15 99.8 % 212615 0.9% 02.4673
00.0000 156 00.3057 00.0020 701 294
+ WW44C W44C2GIA I BP15 99.9 % 83366 0.4% 00.7160
00.0000 14 00.0221 00.0016 0 32
+ WW44C W44C1ITM I BP15 99.7 % 41169 0.2% 00.5850
00.0000 21 00.1327 00.0063 0 82
+ CNSW44 CNSINVX1 I BP15 81.2 % 23466 0.1% 14.5161
00.0006 4412 13.7460 00.0031 0 0
+ WW44C W44C1GIB I BP15 99.8 % 17026 0.1% 00.1675
00.0000 12 00.0618 00.0051 0 28
+ WW44C W44C2PUR I BP15 99.7 % 11728 0.0% 00.1518
00.0000 3 00.0106 00.0035 0 32
+ CNSW44 CNSIIDX3 I BP15 95.8 % 7762 0.0% 00.1533
00.0000 9 00.0606 00.0067 0 318
+ WW44C W44CSPOL T BP15 95.9 % 6009 0.0% 00.1999
00.0000 23 00.0309 00.0013 43 221

DB2: DSNP Buffer Pool: BP18 Database:

BPool +-------- Getpage
-----------+ +------ Sync I/O ------+ +- Async I/O -+
Database Page Set Type BPool HRatio Number % Tot Time Avg
Time Number Tot Time Avg Time Request Pages
-------- -------- ---- ------ ------ ------------ --------
-------- ------ -------- -------- ------- -------
+ WW44C W44C4BOL I BP18 100 % 241401 65.5% 01.0077
00.0000 5 00.0033 00.0007 0 0
+ WW44C W44C6BOL I BP18 100 % 34465 9.4% 00.1548
00.0000 4 00.0058 00.0014 0 0
+ WW44G W44GSCPT T BP18 99.8 % 29509 8.0% 00.3865
00.0000 11 00.0588 00.0053 4 58
+ WW44N W44N1BBI I BP18 31.3 % 14787 4.0% 11.5780
00.0008 3892 10.2893 00.0026 0 6269
>> CNSW44 CNSOTCS T BP18 100 % 11296 3.1% 00.1263
00.0000 0 00.0000 00.0000 0 0
+ WW44N W44N1RPM I BP18 99.7 % 10447 2.8% 00.0846
00.0000 6 00.0140 00.0023 0 27
+ WW44C W44C3POL I BP18 99.6 % 7640 2.1% 00.1317
00.0000 34 00.0696 00.0020 0 0
+ WW44C W44C1PDL I BP18 99.9 % 6600 1.8% 00.1114
00.0000 4 00.0364 00.0091 0 0
+ WW44G W44G1RPM I BP18 97.5 % 5955 1.6% 01.2068
00.0002 150 01.0478 00.0070 0 0
+ WW44C W44C2OUT I BP18 98.9 % 2562 0.7% 00.1037
00.0000 28 00.0784 00.0028 0 0


Kenney:
Bufferpools have not changed - if they did, I am the only one
who would have done so.
But on further thought here, someone could have added other
objects that use the same bufferpools, so I am going to isolate the
tables and test that, thanks.
FYI: (we migrated production to v8 c.m. on August 18 2008 with
no problems)

Roger / Paul / Robert:
There are dates and timestamps - stats were collected Jan 1
2009, so they were OK.
Checked for rid pool failures and none found - table only has
9,000 rows !
The first thing one of the application DBA's did was to
reorg/runstats and that did not help -
I never got a chance to query to see if it really did need a
reorg ?

Fred:
The only report I can scrape up for the old explain is this
BindImpactExpert comparison :

ImpactExpert for DB2 z/OS ----- Access Paths ------- LINE
00000001 COL 001 080
Command ===>
Scroll ===> PAGE

DB2: DSNL
Primary cmd: END, D(etails on/off), C(atalog data)



Collection . BATCHG Timestamp. . 2008070211570000

Package. . . CNPDRVB Contoken . . 184D70920346F963

StmtNo . . . 5330 Bindtime . .
2008-06-03-12.18.45.626181




Access path before REBIND -------------| Access path with REBIND
--------------


TABLE QB PN AC MA ME IX PR | TABLE QB PN
AC MA ME IX PR
INDEX TY CO TH ON FT | INDEX
TY CO TH ON FT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - -
DSNWFQB(09) 1 1 R 0 0 N S | DSNWFQB(09) 1 1
R 0 0 N S
2 1 0 0 | 2 1
0 0
CNSOTC 4 1 I 3 0 Y | CNSOTC 4 1
I 3 0 Y
CNSOTCX1 | CNSOTCX1

CNSOTC 4 2 I 5 1 Y | CNSOTC 4 2
I 5 1 Y
CNSOTCX1 | CNSOTCX1

5 1 0 0 | 5 1
0 0
DSNWFQB(05) 9 1 R 0 0 N S | DSNWFQB(05) 9 1
R 0 0 N S
CNSOTC 10 1 I 2 0 Y | CNSOTC 10 1
I 2 0 Y
CNSOTCX1 | CNSOTCX1

CNSSTD 10 2 I 3 1 N | CNSSTD 10 2
I 3 1 N
CNSSTDX1 | CNSSTDX1

CNSOTC 10 3 M 0 1 N L | CNSOTC 10 3
M 0 1 N L
CNSOTC 10 3 MX 2 1 Y | CNSOTC 10 3
MX 2 1 Y
CNSOTCX1 | CNSOTCX1

CNSOTC 10 3 MX 2 1 Y | CNSOTC 10 3
MX 2 1 Y
CNSOTCX1 | CNSOTCX1

CNSOTC 10 3 MU 0 1 N | CNSOTC 10 3
MU 0 1 N
DSNWFQB(02) 11 1 R 0 0 N S | DSNWFQB(02) 11 1
R 0 0 N S
CNSOTC 12 1 M 0 0 N L | CNSOTC 12 1
M 0 0 N L
CNSOTC 12 1 MX 2 0 Y | CNSOTC 12 1
MX 2 0 Y
CNSOTCX1 | CNSOTCX1

CNSOTC 12 1 MX 2 0 Y | CNSOTC 12 1
MX 2 0 Y
CNSOTCX1 | CNSOTCX1

CNSOTC 12 1 MU 0 0 N | CNSOTC 12 1
MU 0 0 N
|

Milliseconds: 7 | Milliseconds: 19

Serviceunits: 81 | Serviceunits: 231



This morning I used Visual Explain's Statistics Advisor and it
suggested the following (which I ran and got no change on access path)
RUNSTATS TABLESPACE CNSW44.CNSSTDS
TABLE(CNSW44.CNSSTD)
COLUMN(CPY_CD)
COLGROUP(CPY_CD,STR_NBR_DSH) FREQVAL COUNT 10
COLGROUP(STR_NBR_DSH) FREQVAL COUNT 10
SORTDEVT SYSDA SORTNUM 4
SHRLEVEL CHANGE REPORT YES
RUNSTATS TABLESPACE CNSW44.CNSOTCS
TABLE(CNSW44.CNSOTC)
COLUMN(CPY_CD)
COLGROUP(CPY_CD,STR_NBR_MST) FREQVAL COUNT 1
COLGROUP(STR_NBR_MST) FREQVAL COUNT 10
COLGROUP(ACV_FL) FREQVAL COUNT 1
COLGROUP(INL_EXL_CD) FREQVAL COUNT 10
COLGROUP(ACV_FL,AD_GRP,CPY_CD,FAC_WHS,INL_EXL_CD,
ITM_NBR_6,STR_NBR_MST)
COLGROUP(ACV_FL,CPY_CD,INL_EXL_CD,STR_NBR_MST)
COLGROUP(FAC_WHS) FREQVAL COUNT 10
COLGROUP(ACV_FL,CPY_CD,FAC_WHS,INL_EXL_CD,ITM_NBR_6,
STR_NBR_MST)
COLGROUP(AD_GRP) FREQVAL COUNT 10
SORTDEVT SYSDA SORTNUM 4
INDEX(CNSW44.CNSOTCX1 KEYCARD)
SHRLEVEL CHANGE REPORT YES

So, while you are spending the rest of the day trying to wade
through all of this,
I am thinking about throwing some differnt indexes at the
problem using some of the combinations from the COLGROUPS.

Don't go away - I'll be right back ...................
Thanks again.



This e-mail (and any attachments) may contain information that
is confidential and/or protected by law. Any review, use, distribution
or disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the
intended recipient, please contact the sender by reply email and delete
all copies of this message.



________________________________


IDUG 2009 - Europe * 5-9 October * Rome, Italy
< http://idug.org/lseu >

IDUG.org < http://www.idug.org/ > was recently updated requiring
members to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >




________________________________


IDUG 2009 - Europe * 5-9 October * Rome, Italy
< http://idug.org/lseu >

IDUG.org < http://www.idug.org/ > was recently updated requiring
members to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >





This e-mail (and any attachments) may contain information that
is confidential and/or protected by law. Any review, use, distribution
or disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the
intended recipient, please contact the sender by reply email and delete
all copies of this message.






________________________________


IDUG 2009 - Europe * 5-9 October * Rome, Italy
< http://idug.org/lseu >

IDUG.org < http://www.idug.org > was recently updated requiring
members to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >


E-MAIL CONFIDENTIALITY NOTICE: The contents of this e-mail message and any attachments are intended solely for the
addressee(s) and may contain confidential and/or legally privileged information. If you are not the
intended recipient of this message or if this message has been addressed to you in error, please
immediately alert the sender by reply e-mail and then delete this message and any attachments. If you
are not the intended recipient, you are notified that any use, dissemination, distribution, copying, or
storage of this message or any attachment is strictly prohibited.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Mark Vickers

Re: CPU & Getpages huge increase mystery
(in response to Richard Humphris)
Hi Rich,
Thanks, but Neither of those were in the original SQL, I added optimize
for 1 row, which I probably should have taken out when I posted the
problem.

However, I am not going to disregard the HIPER, and I am awaiting response
to my ticket with IBM as after HLS Technologies helped me with the
OPTHINT, we got the access path back to what we wanted and an 85% in CPU
reduction - has to be an optimizer bug and I our maintenance level is at
Nov 2007, so I am hoping for a fix for this.

Thanks again,
Mark.




"Humphris,Richard P." <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/16/2009 04:28 PM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] CPU & Getpages huge increase mystery






Hi Mark,

Take a look at this hiper ptf from IBM (apar PK74545 / ptf UK41678) it
talks about the optimizer sometimes choosing the wrong access path when
the SQL uses the clauses:
"FETCH FIRST n ROWS ONLY"
or "OPTIMIZE FOR n ROWS".

This ptf just became available on 12/4/2008.

IBM says: Sometimes when the "FETCH FIRST n ROWS ONLY" or "OPTIMIZE FOR n
ROWS clause" is present, a suboptimal pipeline plan may be chosen due to
an internal
bug where a certain field containing an estimated cost used during query
optimization is not set. This can cause query performance problems.
PROBLEM CONCLUSION: The performance problem described above is fixed by
setting the field correctly.
Rich Humphris
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Mark Vickers
Sent: Tuesday, January 13, 2009 5:12 PM
To: [login to unmask email]
Subject: Re: [DB2-L] CPU & Getpages huge increase mystery


Hi Fred,
There was a lot of data to look at, so I am sure I just confused everyone.


The current explain is at 29 ms & 480 su and running at 109 getpages.

The previous acceptable run was at 19 ms & 106 getpages.

Prior to that it was 7 ms (don't know the GP).

The SQL has not changed for both rebinds.

Problem is, it is now so CPU intensive, we have had to alter the schedule
so it can run alone during the day so we can get some CICS thruput !

We have a new version of the data and program using roll-up tables (which
I don't like) but so far in test, we have it down to 0.002 cpu secs per
SQL and it was at 0.019.

I just wish I had stats to compare to see what changed.

Thanks,
Mark.


Fred Edgar <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/13/2009 04:09 PM

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



To
[login to unmask email]
cc

Subject
Re: [DB2-L] CPU & Getpages huge increase mystery








Mark,

At first blush, 106 average getpages and 19 milliseconds of CPU doesn't
look too bad
considering the SQL and access path. Do you have numbers from before?

Fred

Mark Vickers <[login to unmask email]> wrote:

The query and all the stats etc was too much to compile, so I have
uploaded the Explain Report to my wife's website:

http://www.cajuncrap.com/explain/report.htm

<shameless plug> If you like Crawfish - take a look around at what she has
to offer please ! </shameless plug>

Now since you all took the time to answer me, I will try to answer all the
questions:
FYI: (we migrated production to v8 c.m. on August 18 2008 with no
problems)

Phil:
Query block 12 went from a multiple index scan with matching columns to a
single index scan with 0 (no) matching columns and that is where I think
the cost went way up.
No changes to stats being collected on Production - just trying stuff on
test.
Time - this is what this monster did yesterday:

Program........ CNPDRVB Location... Collection
ID... BATCHG Contoken... D561D74040404040
Program Type... PKGE Version....

Statement Type... STATIC
Section Number... 4 SQL +-------- Total IN-SQL Time
--------+ +- Sync I/O --+ +- Async I/O -+ Lock
Statement Number. 5314 Calls Elapsed % CPU %
Getpage Number Elapsed Waits Elapsed Wait %
-------- ------------------
------------------ -------- ------ -------- ------ -------- ------
246354 1:50:04.793 25.4 % 1:18:42.923
46.6 % 26188015 17 00.0587 22 00.2204 0.0 %
Averages: 00:00.02681 00:00.01917
106 0 00.0000 0 0.0000

I actually thought it was called from online programs too, but there is
another version for online ............
I am not even going there !

Here are the object statistics from yesterday:


Object Statistics

STATIC
Creator Name BPool +------------- Getpage
-------------+ +------- Sync I/O ------+ +- Async I/O -+
Long Name Type BPool HRatio Total Avg Tot Time %
Avg Time Number Tot Time Avg Time Request Pages
----------------------------- ---- ------ ------ ------ ------
-------------- -------- ------- -------- -------- ------- -------
CNSW44 CNSOTC (total) 100 % 22244K 90 1:39.522
1.5% 00.0004 2 00.0227 00.0113 6 92
..
CNSW44 CNSOTC T BP18 100 % 11296 0 00.1263
0.0% 00.0000 0 00.0000 00.0000 0 0
..
CNSW44 CNSOTCX1 I BP15 100 % 22233K 90 1:39.396
1.5% 00.0004 2 00.0227 00.0113 6 92
...
DSNDB07 DSN4K01 (total) 100 % 871658 4 04.4581
0.1% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K01 BP7 100 % 5751 0 00.0307
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K02 BP7 100 % 5 0 00.0000
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K03 BP7 100 % 737015 3 03.8000
0.1% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K04 BP7 100 % 34568 0 00.1796
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K06 BP7 100 % 34584 0 00.1496
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K07 BP7 100 % 12 0 00.0001
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K09 BP7 100 % 26987 0 00.1544
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K10 BP7 100 % 5749 0 00.0251
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K12 BP7 100 % 26987 0 00.1186
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K01 (total) 100 % 601776 2 03.5645
0.1% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K01 BP7 100 % 2 0 00.0000
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K02 BP7 100 % 117395 0 00.7417
0.0% 00.0000 0 00.0000 00.0000 0 0

DSNDB07 DSN4K03 BP7 100 % 111536 0 00.8789
0.0% 00.0000 0 00.0000 00.0000 0 0


Gerald:
Bufferpool stats from yesterday - ranked by getpages - listed top 10 for
each bufferpool:
Not really sure what to make of the second object in BP15 ?
We can continue this offline as you suggested - just wanted everyone to
see all the info.

DB2: DSNP Buffer Pool: BP15 Database:
BPool +-------- Getpage -----------+
+------ Sync I/O ------+ +- Async I/O -+
Database Page Set Type BPool HRatio Number % Tot Time Avg Time
Number Tot Time Avg Time Request Pages
-------- -------- ---- ------ ------ ------------ -------- --------
------ -------- -------- ------- -------
>> CNSW44 CNSOTCX1 I BP15 100 % 22233K 93.7% 1:39.396 00.0000 2
00.0227 00.0113 6 92
+ WW44G W44GSRQM T BP15 0.0 % 1068K 4.5% 15:42.45 00.0009
189K 3:56.558 00.0013 4940 10074K
+ WW44C W44C1OIA I BP15 99.8 % 212615 0.9% 02.4673 00.0000
156 00.3057 00.0020 701 294
+ WW44C W44C2GIA I BP15 99.9 % 83366 0.4% 00.7160 00.0000
14 00.0221 00.0016 0 32
+ WW44C W44C1ITM I BP15 99.7 % 41169 0.2% 00.5850 00.0000
21 00.1327 00.0063 0 82
+ CNSW44 CNSINVX1 I BP15 81.2 % 23466 0.1% 14.5161 00.0006
4412 13.7460 00.0031 0 0
+ WW44C W44C1GIB I BP15 99.8 % 17026 0.1% 00.1675 00.0000
12 00.0618 00.0051 0 28
+ WW44C W44C2PUR I BP15 99.7 % 11728 0.0% 00.1518 00.0000 3
00.0106 00.0035 0 32
+ CNSW44 CNSIIDX3 I BP15 95.8 % 7762 0.0% 00.1533 00.0000 9
00.0606 00.0067 0 318
+ WW44C W44CSPOL T BP15 95.9 % 6009 0.0% 00.1999 00.0000
23 00.0309 00.0013 43 221

DB2: DSNP Buffer Pool: BP18 Database:
BPool +-------- Getpage -----------+
+------ Sync I/O ------+ +- Async I/O -+
Database Page Set Type BPool HRatio Number % Tot Time Avg Time
Number Tot Time Avg Time Request Pages
-------- -------- ---- ------ ------ ------------ -------- --------
------ -------- -------- ------- -------
+ WW44C W44C4BOL I BP18 100 % 241401 65.5% 01.0077 00.0000 5
00.0033 00.0007 0 0
+ WW44C W44C6BOL I BP18 100 % 34465 9.4% 00.1548 00.0000 4
00.0058 00.0014 0 0
+ WW44G W44GSCPT T BP18 99.8 % 29509 8.0% 00.3865 00.0000
11 00.0588 00.0053 4 58
+ WW44N W44N1BBI I BP18 31.3 % 14787 4.0% 11.5780 00.0008
3892 10.2893 00.0026 0 6269
>> CNSW44 CNSOTCS T BP18 100 % 11296 3.1% 00.1263 00.0000 0
00.0000 00.0000 0 0
+ WW44N W44N1RPM I BP18 99.7 % 10447 2.8% 00.0846 00.0000 6
00.0140 00.0023 0 27
+ WW44C W44C3POL I BP18 99.6 % 7640 2.1% 00.1317 00.0000
34 00.0696 00.0020 0 0
+ WW44C W44C1PDL I BP18 99.9 % 6600 1.8% 00.1114 00.0000 4
00.0364 00.0091 0 0
+ WW44G W44G1RPM I BP18 97.5 % 5955 1.6% 01.2068 00.0002
150 01.0478 00.0070 0 0
+ WW44C W44C2OUT I BP18 98.9 % 2562 0.7% 00.1037 00.0000
28 00.0784 00.0028 0 0


Kenney:
Bufferpools have not changed - if they did, I am the only one who would
have done so.
But on further thought here, someone could have added other objects that
use the same bufferpools, so I am going to isolate the tables and test
that, thanks.
FYI: (we migrated production to v8 c.m. on August 18 2008 with no
problems)

Roger / Paul / Robert:
There are dates and timestamps - stats were collected Jan 1 2009, so they
were OK.
Checked for rid pool failures and none found - table only has 9,000 rows !

The first thing one of the application DBA's did was to reorg/runstats and
that did not help -
I never got a chance to query to see if it really did need a reorg ?

Fred:
The only report I can scrape up for the old explain is this
BindImpactExpert comparison :

ImpactExpert for DB2 z/OS ----- Access Paths ------- LINE 00000001 COL 001
080
Command ===> Scroll ===>
PAGE
DB2:
DSNL
Primary cmd: END, D(etails on/off), C(atalog data)

Collection . BATCHG Timestamp. . 2008070211570000
Package. . . CNPDRVB Contoken . . 184D70920346F963
StmtNo . . . 5330 Bindtime . . 2008-06-03-12.18.45.626181



Access path before REBIND -------------| Access path with REBIND
--------------

TABLE QB PN AC MA ME IX PR | TABLE QB PN AC MA ME
IX PR
INDEX TY CO TH ON FT | INDEX TY CO TH
ON FT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - -
DSNWFQB(09) 1 1 R 0 0 N S | DSNWFQB(09) 1 1 R 0 0
N S
2 1 0 0 | 2 1 0 0

CNSOTC 4 1 I 3 0 Y | CNSOTC 4 1 I 3 0
Y
CNSOTCX1 | CNSOTCX1
CNSOTC 4 2 I 5 1 Y | CNSOTC 4 2 I 5 1
Y
CNSOTCX1 | CNSOTCX1
5 1 0 0 | 5 1 0 0

DSNWFQB(05) 9 1 R 0 0 N S | DSNWFQB(05) 9 1 R 0 0
N S
CNSOTC 10 1 I 2 0 Y | CNSOTC 10 1 I 2 0
Y
CNSOTCX1 | CNSOTCX1
CNSSTD 10 2 I 3 1 N | CNSSTD 10 2 I 3 1
N
CNSSTDX1 | CNSSTDX1
CNSOTC 10 3 M 0 1 N L | CNSOTC 10 3 M 0 1
N L
CNSOTC 10 3 MX 2 1 Y | CNSOTC 10 3 MX 2 1
Y
CNSOTCX1 | CNSOTCX1
CNSOTC 10 3 MX 2 1 Y | CNSOTC 10 3 MX 2 1
Y
CNSOTCX1 | CNSOTCX1
CNSOTC 10 3 MU 0 1 N | CNSOTC 10 3 MU 0 1
N
DSNWFQB(02) 11 1 R 0 0 N S | DSNWFQB(02) 11 1 R 0 0
N S
CNSOTC 12 1 M 0 0 N L | CNSOTC 12 1 M 0 0
N L
CNSOTC 12 1 MX 2 0 Y | CNSOTC 12 1 MX 2 0
Y
CNSOTCX1 | CNSOTCX1
CNSOTC 12 1 MX 2 0 Y | CNSOTC 12 1 MX 2 0
Y
CNSOTCX1 | CNSOTCX1
CNSOTC 12 1 MU 0 0 N | CNSOTC 12 1 MU 0 0
N
|
Milliseconds: 7 | Milliseconds: 19
Serviceunits: 81 | Serviceunits: 231


This morning I used Visual Explain's Statistics Advisor and it suggested
the following (which I ran and got no change on access path)
RUNSTATS TABLESPACE CNSW44.CNSSTDS
TABLE(CNSW44.CNSSTD)
COLUMN(CPY_CD)
COLGROUP(CPY_CD,STR_NBR_DSH) FREQVAL COUNT 10
COLGROUP(STR_NBR_DSH) FREQVAL COUNT 10
SORTDEVT SYSDA SORTNUM 4
SHRLEVEL CHANGE REPORT YES
RUNSTATS TABLESPACE CNSW44.CNSOTCS
TABLE(CNSW44.CNSOTC)
COLUMN(CPY_CD)
COLGROUP(CPY_CD,STR_NBR_MST) FREQVAL COUNT 1
COLGROUP(STR_NBR_MST) FREQVAL COUNT 10
COLGROUP(ACV_FL) FREQVAL COUNT 1
COLGROUP(INL_EXL_CD) FREQVAL COUNT 10
COLGROUP(ACV_FL,AD_GRP,CPY_CD,FAC_WHS,INL_EXL_CD,
ITM_NBR_6,STR_NBR_MST)
COLGROUP(ACV_FL,CPY_CD,INL_EXL_CD,STR_NBR_MST)
COLGROUP(FAC_WHS) FREQVAL COUNT 10
COLGROUP(ACV_FL,CPY_CD,FAC_WHS,INL_EXL_CD,ITM_NBR_6,
STR_NBR_MST)
COLGROUP(AD_GRP) FREQVAL COUNT 10
SORTDEVT SYSDA SORTNUM 4
INDEX(CNSW44.CNSOTCX1 KEYCARD)
SHRLEVEL CHANGE REPORT YES

So, while you are spending the rest of the day trying to wade through all
of this,
I am thinking about throwing some differnt indexes at the problem using
some of the combinations from the COLGROUPS.

Don't go away - I'll be right back ...................
Thanks again.



This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete all copies
of this message.




IDUG 2009 - Europe * 5-9 October * Rome, Italy

IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register here.


IDUG 2009 - Europe * 5-9 October * Rome, Italy
IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register here.



This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete all copies
of this message.



IDUG 2009 - Europe * 5-9 October * Rome, Italy
IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register here.

E-MAIL CONFIDENTIALITY NOTICE: The contents of this e-mail message and
any attachments are intended solely for the
addressee(s) and may contain confidential and/or legally privileged
information. If you are not the
intended recipient of this message or if this message has been addressed
to you in error, please
immediately alert the sender by reply e-mail and then delete this message
and any attachments. If you
are not the intended recipient, you are notified that any use,
dissemination, distribution, copying, or
storage of this message or any attachment is strictly prohibited.


IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia
IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register here.




This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete all copies
of this message.



______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html