Bad performance - cannot pinpoint problem area

Mark Vickers

Bad performance - cannot pinpoint problem area
We have a data warehouse update job which we think is performing badly.
I will apologize in advance for the length of this, but I am trying
to answer all the obvious questions up front (like, Is the power on ?).

We have DB2 v7 running on IBM T-Rex.

The batch program is doing 1,7 million selects only using all 9 columns of
the index (there are only another 5 columns of data - I know that is
excessive, but being a data mart, some of the tables are de-normalized.
There is only one index, which is the primary key, as required by our 4GL
COBOL Application Development Tool.

The table has 7mil rows, the explain looks good, 9 matching columns on the
index scan, the table was in good shape and did not need reorg.

Here are the stats from the job (MainView):

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~
RUNTIME ANALYSIS IN DB2 IN APPL. TOTAL %IN DB2(=)
TOTAL(*)
---------------- -------- -------- -------- 0
...25...50...75..100%
ELAPSED TIME 01:02:21 46 s 01:03:07 | ===================*
|
CPU TIME 00:02:38 17 s 00:02:55 | <
|
DB2 WAIT TIME 00:55:39 | =================
|
- - - - - - ACTIVITY - - - - - - - - - - - KEY INDICATORS - - - - - -
-
TOTAL SQL..................1,723K FILTER: GETPAGE = 4,387K (> 102K)

GETPAGES...................4,387K SQL: SELECT=1,723K, FETCH= 0

SYNC READS (PRLL=00) ......1,006K I/O RSP: SYNC= 1,944 us, ASYNC= 10
ms
PREFETCH PAGES READ........6,156K LOCK SUSPENSIONS = 55

UPDATES/COMMIT................0.0

BFR HIT RATIOS:...VP= 0%,HP=100%

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~

Here is the part that concerns me:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~
- - - - - - - - ELAPSED TIME ANALYSIS (ACCTG CLASSES 2,3 ONLY) - - - - - - -
-
CATEGORY #EVENTS AVG/EVENT ELAPSED %TOTAL

---------------- ------- --------- -------- ------- 0
...25...50...75..100%
ELAPSED TIME |
|
IN DB2 01:02:21 98.77 | *******************
|
IN APPLICATION 46 s 1.22 | <
|
--TOTALS-- 01:03:07 100.00 | ********************
|
WAITS IN DB2 (LOCAL) |
|
LOCK/LATCH 535 1,226 us 656 ms 0.01 | <
|
I/O WAIT 1,006K 1,944 us 00:32:35 51.63 | **********
|
LOG WRITE I/O 0 0 us 0 us 0.00 |
|
OTHER READ I/O 135,265 10 ms 00:23:03 36.52 | *******
|
OTHER WRITE I/O 0 0 us 0 us 0.00 |
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~

There seems way too much wait time in DB2.
I/O Wait is synchronous read I/O and the OTHER READ I/O is:
Elapsed time spent in DB2 waiting for read I/O for I/O done by
another thread such as sequential prefetch, sequential detection, or
list prefetch (QWACAWTR).
Of course this implies contention, so I made sure none of the other plans
that access this table were running.

After re-running runstats to be sure, DSNACCAV did not report needing a
reorg.
In desperation we reorged the table and rebound the package yesterday and
got
exactly the same results today.

The only locks are 1,7m lock and 1,7m unlock for the isolation "CS".

I am running out of ideas, can anyone see or suggest anything please ?

thank you,
Mark Vickers
@ANICO.

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

michael bell

Re: Bad performance - cannot pinpoint problem area
(in response to Mark Vickers)
your symptoms look exactly like death by IO. The clues are
1. 4M getpages for 1.7 M Selects (if the next select was close to the
previous index key values, index lookaside would bypass most of the
getpages).
2. 1M sync IOs
3. 6M pages read by prefetch

probable causes are one or both of the sequence issues
1. the sequence of the logic that drives the selects
2. the cluster ration of the index - what does runstats show

OK, it is time for some guess work -
1. What drives the 1.7 million selects - is it an external file? Can the
process that drives the SELECT's be changed to process in the index
sequence? (sort the file, build Temp table and sort to match the index,
etc?)
2. You have a lot of prefetch activity that isn't getting any results 6M
pages prefetch for 4M get pages. You can try changing to OPTIMIZE for 1 ROWS
to turn off prefetch.
3. What is the cluster ratio of the index? If the index is mostly random (<
cluster ratio 80%) then you need to examine the next alternative.
4. How long would it take to unload, and sort the table into the index
order and process in batch read file logic?

Mike Bell
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Vickers, Mark
Sent: Thursday, December 11, 2003 10:57 AM
To: [login to unmask email]
Subject: Bad performance - cannot pinpoint problem area


We have a data warehouse update job which we think is performing badly.
I will apologize in advance for the length of this, but I am trying
to answer all the obvious questions up front (like, Is the power on ?).

We have DB2 v7 running on IBM T-Rex.

The batch program is doing 1,7 million selects only using all 9 columns of
the index (there are only another 5 columns of data - I know that is
excessive, but being a data mart, some of the tables are de-normalized.
There is only one index, which is the primary key, as required by our 4GL
COBOL Application Development Tool.

The table has 7mil rows, the explain looks good, 9 matching columns on the
index scan, the table was in good shape and did not need reorg.

Here are the stats from the job (MainView):

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~
RUNTIME ANALYSIS IN DB2 IN APPL. TOTAL %IN DB2(=)
TOTAL(*)
---------------- -------- -------- -------- 0
...25...50...75..100%
ELAPSED TIME 01:02:21 46 s 01:03:07 | ===================*
|
CPU TIME 00:02:38 17 s 00:02:55 | <
|
DB2 WAIT TIME 00:55:39 | =================
|
- - - - - - ACTIVITY - - - - - - - - - - - KEY INDICATORS - - - - - -
-
TOTAL SQL..................1,723K FILTER: GETPAGE = 4,387K (> 102K)

GETPAGES...................4,387K SQL: SELECT=1,723K, FETCH= 0

SYNC READS (PRLL=00) ......1,006K I/O RSP: SYNC= 1,944 us, ASYNC= 10
ms
PREFETCH PAGES READ........6,156K LOCK SUSPENSIONS = 55

UPDATES/COMMIT................0.0

BFR HIT RATIOS:...VP= 0%,HP=100%

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~

Here is the part that concerns me:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~
- - - - - - - - ELAPSED TIME ANALYSIS (ACCTG CLASSES 2,3 ONLY) - - - - - - -
-
CATEGORY #EVENTS AVG/EVENT ELAPSED %TOTAL

---------------- ------- --------- -------- ------- 0
...25...50...75..100%
ELAPSED TIME |
|
IN DB2 01:02:21 98.77 | *******************
|
IN APPLICATION 46 s 1.22 | <
|
--TOTALS-- 01:03:07 100.00 | ********************
|
WAITS IN DB2 (LOCAL) |
|
LOCK/LATCH 535 1,226 us 656 ms 0.01 | <
|
I/O WAIT 1,006K 1,944 us 00:32:35 51.63 | **********
|
LOG WRITE I/O 0 0 us 0 us 0.00 |
|
OTHER READ I/O 135,265 10 ms 00:23:03 36.52 | *******
|
OTHER WRITE I/O 0 0 us 0 us 0.00 |
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~

There seems way too much wait time in DB2.
I/O Wait is synchronous read I/O and the OTHER READ I/O is:
Elapsed time spent in DB2 waiting for read I/O for I/O done by
another thread such as sequential prefetch, sequential detection, or
list prefetch (QWACAWTR).
Of course this implies contention, so I made sure none of the other plans
that access this table were running.

After re-running runstats to be sure, DSNACCAV did not report needing a
reorg.
In desperation we reorged the table and rebound the package yesterday and
got
exactly the same results today.

The only locks are 1,7m lock and 1,7m unlock for the isolation "CS".

I am running out of ideas, can anyone see or suggest anything please ?

thank you,
Mark Vickers
@ANICO.

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 10/22/2003

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 10/22/2003

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

Missy Case

Re: Bad performance - cannot pinpoint problem area
(in response to michael bell)
Mark,
I would first check your bufferpools. Separate the objects into a pool or
pair of pools so you can see what kind of pool hits you're getting. Which,
if any, thresholds you're hitting. What kind of read to IO ratio you're
getting, your page residency rate on the shared pools may be getting you.
Personally, I'd put the index(es) into one pool & the tablespace into
another. Make sure the batch access is coming to DB2 in the same as the
cluster index, this will give you better prefetch page usage & overall
batch performance.

Your process is probably highly sequential, so tweak your bufferpool
thresholds to allow for that. If you're doing a lot of updating, your
hiperpool pages will be just thrashed in & out & not effectively used. If
it's only a read process, a hiperpool page is close to as good as a vpool
page (IMHOO!) Make sure your pool size is at least 1000 pages to give you
the best hit on your prefetch number of pages. Check the segsize for your
tablespace - it will affect the pages getting into your pool also. If
you're partitioned, that doesn't impact this setting. Check your %free &
Freepage, are you getting a lot of pages in that have very little data, are
you doing prefetch on your prefetch # of pages & getting 2 or 3 empty pages
for each IO? Are you datasharing & do you use GPCACHE changed vs all &
membercluster NO?

Just a few random thoughts, but hopefully a place to start.
Missy Case
FDR
701-275-6358




"Vickers, Mark"
<[login to unmask email] To: [login to unmask email]
CO.COM> cc:
Sent by: DB2 Data bcc:
Base Discussion Subject: Bad performance - cannot pinpoint problem area
List
<[login to unmask email]
ORG>


12/11/03 10:56 AM
Please respond to
DB2 Database
Discussion list
at IDUG






We have a data warehouse update job which we think is performing badly.
I will apologize in advance for the length of this, but I am trying
to answer all the obvious questions up front (like, Is the power on ?).

We have DB2 v7 running on IBM T-Rex.

The batch program is doing 1,7 million selects only using all 9 columns of
the index (there are only another 5 columns of data - I know that is
excessive, but being a data mart, some of the tables are de-normalized.
There is only one index, which is the primary key, as required by our 4GL
COBOL Application Development Tool.

The table has 7mil rows, the explain looks good, 9 matching columns on the
index scan, the table was in good shape and did not need reorg.

Here are the stats from the job (MainView):

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~
RUNTIME ANALYSIS IN DB2 IN APPL. TOTAL %IN DB2(=)
TOTAL(*)
---------------- -------- -------- -------- 0
...25...50...75..100%
ELAPSED TIME 01:02:21 46 s 01:03:07 |
===================*
|
CPU TIME 00:02:38 17 s 00:02:55 | <
|
DB2 WAIT TIME 00:55:39 | =================
|
- - - - - - ACTIVITY - - - - - - - - - - - KEY INDICATORS - - - - - -
-
TOTAL SQL..................1,723K FILTER: GETPAGE = 4,387K (> 102K)

GETPAGES...................4,387K SQL: SELECT=1,723K, FETCH= 0

SYNC READS (PRLL=00) ......1,006K I/O RSP: SYNC= 1,944 us, ASYNC= 10
ms
PREFETCH PAGES READ........6,156K LOCK SUSPENSIONS = 55

UPDATES/COMMIT................0.0

BFR HIT RATIOS:...VP= 0%,HP=100%

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~

Here is the part that concerns me:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~
- - - - - - - - ELAPSED TIME ANALYSIS (ACCTG CLASSES 2,3 ONLY) - - - - - -
-
-
CATEGORY #EVENTS AVG/EVENT ELAPSED %TOTAL

---------------- ------- --------- -------- ------- 0
...25...50...75..100%
ELAPSED TIME |
|
IN DB2 01:02:21 98.77 | *******************
|
IN APPLICATION 46 s 1.22 | <
|
--TOTALS-- 01:03:07 100.00 |
********************
|
WAITS IN DB2 (LOCAL) |
|
LOCK/LATCH 535 1,226 us 656 ms 0.01 | <
|
I/O WAIT 1,006K 1,944 us 00:32:35 51.63 | **********
|
LOG WRITE I/O 0 0 us 0 us 0.00 |
|
OTHER READ I/O 135,265 10 ms 00:23:03 36.52 | *******
|
OTHER WRITE I/O 0 0 us 0 us 0.00 |
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~

There seems way too much wait time in DB2.
I/O Wait is synchronous read I/O and the OTHER READ I/O is:
Elapsed time spent in DB2 waiting for read I/O for I/O done by
another thread such as sequential prefetch, sequential detection, or
list prefetch (QWACAWTR).
Of course this implies contention, so I made sure none of the other plans
that access this table were running.

After re-running runstats to be sure, DSNACCAV did not report needing a
reorg.
In desperation we reorged the table and rebound the package yesterday and
got
exactly the same results today.

The only locks are 1,7m lock and 1,7m unlock for the isolation "CS".

I am running out of ideas, can anyone see or suggest anything please ?

thank you,
Mark Vickers
@ANICO.

---------------------------------------------------------------------------------

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

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

Carol G

Re: Bad performance - cannot pinpoint problem area
(in response to Missy Case)
Are you sure you are getting Stats on all the columns in the index. We had a
similiar problem also. I would definitely check that. Are your bufferpools
big enough or are you overflowing the bufferpools? Is the tablespace and
index in a different bufferpool?


Regards,
Carol


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Vickers, Mark
Sent: Thursday, December 11, 2003 11:57 AM
To: [login to unmask email]
Subject: Bad performance - cannot pinpoint problem area


We have a data warehouse update job which we think is performing badly.
I will apologize in advance for the length of this, but I am trying
to answer all the obvious questions up front (like, Is the power on ?).

We have DB2 v7 running on IBM T-Rex.

The batch program is doing 1,7 million selects only using all 9 columns of
the index (there are only another 5 columns of data - I know that is
excessive, but being a data mart, some of the tables are de-normalized.
There is only one index, which is the primary key, as required by our 4GL
COBOL Application Development Tool.

The table has 7mil rows, the explain looks good, 9 matching columns on the
index scan, the table was in good shape and did not need reorg.

Here are the stats from the job (MainView):

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~
RUNTIME ANALYSIS IN DB2 IN APPL. TOTAL %IN DB2(=)
TOTAL(*)
---------------- -------- -------- -------- 0
...25...50...75..100%
ELAPSED TIME 01:02:21 46 s 01:03:07 | ===================*
|
CPU TIME 00:02:38 17 s 00:02:55 | <
|
DB2 WAIT TIME 00:55:39 | =================
|
- - - - - - ACTIVITY - - - - - - - - - - - KEY INDICATORS - - - - - -
-
TOTAL SQL..................1,723K FILTER: GETPAGE = 4,387K (> 102K)

GETPAGES...................4,387K SQL: SELECT=1,723K, FETCH= 0

SYNC READS (PRLL=00) ......1,006K I/O RSP: SYNC= 1,944 us, ASYNC= 10
ms
PREFETCH PAGES READ........6,156K LOCK SUSPENSIONS = 55

UPDATES/COMMIT................0.0

BFR HIT RATIOS:...VP= 0%,HP=100%

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~

Here is the part that concerns me:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~
- - - - - - - - ELAPSED TIME ANALYSIS (ACCTG CLASSES 2,3 ONLY) - - - - - - -
-
CATEGORY #EVENTS AVG/EVENT ELAPSED %TOTAL

---------------- ------- --------- -------- ------- 0
...25...50...75..100%
ELAPSED TIME |
|
IN DB2 01:02:21 98.77 | *******************
|
IN APPLICATION 46 s 1.22 | <
|
--TOTALS-- 01:03:07 100.00 | ********************
|
WAITS IN DB2 (LOCAL) |
|
LOCK/LATCH 535 1,226 us 656 ms 0.01 | <
|
I/O WAIT 1,006K 1,944 us 00:32:35 51.63 | **********
|
LOG WRITE I/O 0 0 us 0 us 0.00 |
|
OTHER READ I/O 135,265 10 ms 00:23:03 36.52 | *******
|
OTHER WRITE I/O 0 0 us 0 us 0.00 |
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~

There seems way too much wait time in DB2.
I/O Wait is synchronous read I/O and the OTHER READ I/O is:
Elapsed time spent in DB2 waiting for read I/O for I/O done by
another thread such as sequential prefetch, sequential detection, or
list prefetch (QWACAWTR).
Of course this implies contention, so I made sure none of the other plans
that access this table were running.

After re-running runstats to be sure, DSNACCAV did not report needing a
reorg.
In desperation we reorged the table and rebound the package yesterday and
got
exactly the same results today.

The only locks are 1,7m lock and 1,7m unlock for the isolation "CS".

I am running out of ideas, can anyone see or suggest anything please ?

thank you,
Mark Vickers
@ANICO.

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

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

Mark Vickers

Re: Bad performance - cannot pinpoint problem area
(in response to Carol G)
Bob,
1. No partitioning. Packages and Plans have degree(1).

2. Buffer Pools (Data Warehouse has their own):
POOL VP HP
ID SIZE SIZE
Data: BP6 7,760 13,700
Index: BP7 7,688 17,100

3. REOPTVAR = no

Thanks for your help.
Mark.


-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Thursday, December 11, 2003 11:28 AM
To: [login to unmask email]
Subject: Re: Bad performance - cannot pinpoint problem area



Mark,
1.are your tables partitioned? If so have you bound your packages,or plan
with degree any ?
2.How big are the buffer pools you are using for the tablespaces and
indexspaces.
3.One thing you might look for, we had a similar problem where the
application people bound a package with reopt vars and it was doing a lot
of get pages to do a mini bind on the package each time sql was being
executed. When I changed it to noreopt vars get page requests went down.
Bob Yoder
Tech Specialist
Phone: 217.424.7117
Fax: 217.362.3897
[login to unmask email]




"Vickers, Mark"

<[login to unmask email] To:
[login to unmask email]
CO.COM> cc:

Sent by: DB2 Data Subject: Bad performance -
cannot pinpoint problem area
Base Discussion

List

<[login to unmask email]

ORG>





12/11/2003 10:56

AM

Please respond to

DB2 Database

Discussion list

at IDUG









We have a data warehouse update job which we think is performing badly.
I will apologize in advance for the length of this, but I am trying
to answer all the obvious questions up front (like, Is the power on ?).

We have DB2 v7 running on IBM T-Rex.

The batch program is doing 1,7 million selects only using all 9 columns of
the index (there are only another 5 columns of data - I know that is
excessive, but being a data mart, some of the tables are de-normalized.
There is only one index, which is the primary key, as required by our 4GL
COBOL Application Development Tool.

The table has 7mil rows, the explain looks good, 9 matching columns on the
index scan, the table was in good shape and did not need reorg.

Here are the stats from the job (MainView):

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~
RUNTIME ANALYSIS IN DB2 IN APPL. TOTAL %IN DB2(=)
TOTAL(*)
---------------- -------- -------- -------- 0
...25...50...75..100%
ELAPSED TIME 01:02:21 46 s 01:03:07 |
===================*
|
CPU TIME 00:02:38 17 s 00:02:55 | <
|
DB2 WAIT TIME 00:55:39 | =================
|
- - - - - - ACTIVITY - - - - - - - - - - - KEY INDICATORS - - - - - -
-
TOTAL SQL..................1,723K FILTER: GETPAGE = 4,387K (> 102K)

GETPAGES...................4,387K SQL: SELECT=1,723K, FETCH= 0

SYNC READS (PRLL=00) ......1,006K I/O RSP: SYNC= 1,944 us, ASYNC= 10
ms
PREFETCH PAGES READ........6,156K LOCK SUSPENSIONS = 55

UPDATES/COMMIT................0.0

BFR HIT RATIOS:...VP= 0%,HP=100%

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~

Here is the part that concerns me:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~
- - - - - - - - ELAPSED TIME ANALYSIS (ACCTG CLASSES 2,3 ONLY) - - - - - -
-
-
CATEGORY #EVENTS AVG/EVENT ELAPSED %TOTAL

---------------- ------- --------- -------- ------- 0
...25...50...75..100%
ELAPSED TIME |
|
IN DB2 01:02:21 98.77 | *******************
|
IN APPLICATION 46 s 1.22 | <
|
--TOTALS-- 01:03:07 100.00 |
********************
|
WAITS IN DB2 (LOCAL) |
|
LOCK/LATCH 535 1,226 us 656 ms 0.01 | <
|
I/O WAIT 1,006K 1,944 us 00:32:35 51.63 | **********
|
LOG WRITE I/O 0 0 us 0 us 0.00 |
|
OTHER READ I/O 135,265 10 ms 00:23:03 36.52 | *******
|
OTHER WRITE I/O 0 0 us 0 us 0.00 |
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~

There seems way too much wait time in DB2.
I/O Wait is synchronous read I/O and the OTHER READ I/O is:
Elapsed time spent in DB2 waiting for read I/O for I/O done by
another thread such as sequential prefetch, sequential detection, or
list prefetch (QWACAWTR).
Of course this implies contention, so I made sure none of the other plans
that access this table were running.

After re-running runstats to be sure, DSNACCAV did not report needing a
reorg.
In desperation we reorged the table and rebound the package yesterday and
got
exactly the same results today.

The only locks are 1,7m lock and 1,7m unlock for the isolation "CS".

I am running out of ideas, can anyone see or suggest anything please ?

thank you,
Mark Vickers
@ANICO.

----------------------------------------------------------------------------
-----

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

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

Myron Miller

Re: Bad performance - cannot pinpoint problem area
(in response to Mark Vickers)
Mark,
Some of the numbers from the buffer pool stats concern me. You're saying
you get 9 column index match on the explain. Yet, you're seeing roughly 2.5
getpages per every SELECT. 1700k selects versus 4300k getpages. And you're
doing a Sync read for almost every single SELECT. Again 1000k sync reads versus
1700k SELECTs. And this doesn't count all the prefetches. That implies to me
that essentially for every single select, you are doing random physical I/Os.
Confirmed by your buffer hit ratio of 0. So that means you'll be spending the
vast majority of your time waiting for I/O to complete.

I don't know what your program is processing but I'd strongly recommend that it
be slightly rewritten to sort the input data prior to processing the table.
Then you could process the table in somewhat sequential order and could benefit
by some of the prefetches.

As it is right now, for all practical purposes, most of the prefetches being
done are worthless and the data has to be thrown away and physical I/O for that
index record done anyway. Look at the number of prefetch pages returned
(6,156K ) versus the number of SYNC I/Os.

Since you say you have index only access, that means that you are fetching the
high-level of the index and the 2nd and 3rd levels almost all the time
directly.

To me, this is almost a classic death by random I/O situation.

Myron

--- "Vickers, Mark" <[login to unmask email]> wrote:
> Bob,
> 1. No partitioning. Packages and Plans have degree(1).
>
> 2. Buffer Pools (Data Warehouse has their own):
> POOL VP HP
> ID SIZE SIZE
> Data: BP6 7,760 13,700
> Index: BP7 7,688 17,100
>
> 3. REOPTVAR = no
>
> Thanks for your help.
> Mark.
>
>
> -----Original Message-----
> From: [login to unmask email] [mailto:[login to unmask email]
> Sent: Thursday, December 11, 2003 11:28 AM
> To: [login to unmask email]
> Subject: Re: Bad performance - cannot pinpoint problem area
>
>
>
> Mark,
> 1.are your tables partitioned? If so have you bound your packages,or plan
> with degree any ?
> 2.How big are the buffer pools you are using for the tablespaces and
> indexspaces.
> 3.One thing you might look for, we had a similar problem where the
> application people bound a package with reopt vars and it was doing a lot
> of get pages to do a mini bind on the package each time sql was being
> executed. When I changed it to noreopt vars get page requests went down.
> Bob Yoder
> Tech Specialist
> Phone: 217.424.7117
> Fax: 217.362.3897
> [login to unmask email]
>
>
>
>
> "Vickers, Mark"
>
> <[login to unmask email] To:
> [login to unmask email]
> CO.COM> cc:
>
> Sent by: DB2 Data Subject: Bad performance -
> cannot pinpoint problem area
> Base Discussion
>
> List
>
> <[login to unmask email]
>
> ORG>
>
>
>
>
>
> 12/11/2003 10:56
>
> AM
>
> Please respond to
>
> DB2 Database
>
> Discussion list
>
> at IDUG
>
>
>
>
>
>
>
>
>
> We have a data warehouse update job which we think is performing badly.
> I will apologize in advance for the length of this, but I am trying
> to answer all the obvious questions up front (like, Is the power on ?).
>
> We have DB2 v7 running on IBM T-Rex.
>
> The batch program is doing 1,7 million selects only using all 9 columns of
> the index (there are only another 5 columns of data - I know that is
> excessive, but being a data mart, some of the tables are de-normalized.
> There is only one index, which is the primary key, as required by our 4GL
> COBOL Application Development Tool.
>
> The table has 7mil rows, the explain looks good, 9 matching columns on the
> index scan, the table was in good shape and did not need reorg.
>
> Here are the stats from the job (MainView):
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> ~~
> RUNTIME ANALYSIS IN DB2 IN APPL. TOTAL %IN DB2(=)
> TOTAL(*)
> ---------------- -------- -------- -------- 0
> ...25...50...75..100%
> ELAPSED TIME 01:02:21 46 s 01:03:07 |
> ===================*
> |
> CPU TIME 00:02:38 17 s 00:02:55 | <
> |
> DB2 WAIT TIME 00:55:39 | =================
> |
> - - - - - - ACTIVITY - - - - - - - - - - - KEY INDICATORS - - - - - -
> -
> TOTAL SQL..................1,723K FILTER: GETPAGE = 4,387K (> 102K)
>
> GETPAGES...................4,387K SQL: SELECT=1,723K, FETCH= 0
>
> SYNC READS (PRLL=00) ......1,006K I/O RSP: SYNC= 1,944 us, ASYNC= 10
> ms
> PREFETCH PAGES READ........6,156K LOCK SUSPENSIONS = 55
>
> UPDATES/COMMIT................0.0
>
> BFR HIT RATIOS:...VP= 0%,HP=100%
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> ~~
>
> Here is the part that concerns me:
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> ~~
> - - - - - - - - ELAPSED TIME ANALYSIS (ACCTG CLASSES 2,3 ONLY) - - - - - -
> -
> -
> CATEGORY #EVENTS AVG/EVENT ELAPSED %TOTAL
>
> ---------------- ------- --------- -------- ------- 0
> ...25...50...75..100%
> ELAPSED TIME |
> |
> IN DB2 01:02:21 98.77 | *******************
> |
> IN APPLICATION 46 s 1.22 | <
> |
> --TOTALS-- 01:03:07 100.00 |
> ********************
> |
> WAITS IN DB2 (LOCAL) |
> |
> LOCK/LATCH 535 1,226 us 656 ms 0.01 | <
> |
> I/O WAIT 1,006K 1,944 us 00:32:35 51.63 | **********
> |
> LOG WRITE I/O 0 0 us 0 us 0.00 |
> |
> OTHER READ I/O 135,265 10 ms 00:23:03 36.52 | *******
> |
> OTHER WRITE I/O 0 0 us 0 us 0.00 |
> |
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> ~~
>
> There seems way too much wait time in DB2.
> I/O Wait is synchronous read I/O and the OTHER READ I/O is:
> Elapsed time spent in DB2 waiting for read I/O for I/O done by
> another thread such as sequential prefetch, sequential detection, or
> list prefetch (QWACAWTR).
> Of course this implies contention, so I made sure none of the other plans
> that access this table were running.
>
> After re-running runstats to be sure, DSNACCAV did not report needing a
> reorg.
> In desperation we reorged the table and rebound the package yesterday and
> got
> exactly the same results today.
>
> The only locks are 1,7m lock and 1,7m unlock for the isolation "CS".
>
> I am running out of ideas, can anyone see or suggest anything please ?
>
> thank you,
> Mark Vickers
> @ANICO.
>
> ----------------------------------------------------------------------------
> -----
>
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
> page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
> "Join or Leave the list". If you will be out of the office, send the SET
> DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
> can be reached at [login to unmask email] Find out the latest on
> IDUG conferences at http://conferences.idug.org/index.cfm
>
>
---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
> page
=== message truncated ===


__________________________________
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/

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

Joel Goldstein

Re: Bad performance - cannot pinpoint problem area
(in response to Myron Miller)
Really need a lot more data here.

You have an index with nine cols, you are matching on all nine, and doing
an index "scan"?
Understand that this is a warehouse, but then this is still a pretty poor
index.

How many other objects are in the same pools as this index and data?
What do the overall pools stats look like, I/O rate, etc.
The data you sent shows a VP hit of 0% and HP hit at 100%
Certainly can be, but not terribly logical to expect all the referenced
pages to have
migrated to the HP. Maybe these objects are not heavily accessed?

Is the prefetch indicated pure sequential, or Dynamic?

If this application is getting a low system priority for some reason, the
high synch I/O
could be due to pool thrashing, where the data is read in by prefetch,
falls off the lru q,
and then read back in with synch I/O...... but then we shouldn't see a HP
100% hit....

Sorry just a lot more questions rather than answers.

Regards,
Joel




Message text written by DB2 Database Discussion list at IDUG
>Mark,
Some of the numbers from the buffer pool stats concern me. You're
saying
you get 9 column index match on the explain. Yet, you're seeing roughly
2.5
getpages per every SELECT. 1700k selects versus 4300k getpages. And you're
doing a Sync read for almost every single SELECT. Again 1000k sync reads
versus
1700k SELECTs. And this doesn't count all the prefetches. That implies to
me
that essentially for every single select, you are doing random physical
I/Os.
Confirmed by your buffer hit ratio of 0. So that means you'll be spending
the
vast majority of your time waiting for I/O to complete.

I don't know what your program is processing but I'd strongly recommend
that it
be slightly rewritten to sort the input data prior to processing the table.
Then you could process the table in somewhat sequential order and could
benefit
by some of the prefetches.

As it is right now, for all practical purposes, most of the prefetches
being
done are worthless and the data has to be thrown away and physical I/O for
that
index record done anyway. Look at the number of prefetch pages returned
(6,156K ) versus the number of SYNC I/Os.

Since you say you have index only access, that means that you are fetching
the
high-level of the index and the 2nd and 3rd levels almost all the time
directly.

To me, this is almost a classic death by random I/O situation.

Myron

--- "Vickers, Mark" <[login to unmask email]> wrote:
> Bob,
> 1. No partitioning. Packages and Plans have degree(1).
>
> 2. Buffer Pools (Data Warehouse has their own):
> POOL VP HP
> ID SIZE SIZE
> Data: BP6 7,760 13,700
> Index: BP7 7,688 17,100
>
> 3. REOPTVAR = no
>
> Thanks for your help.
> Mark.
>
>
> -----Original Message-----
> From: [login to unmask email] [mailto:[login to unmask email]
> Sent: Thursday, December 11, 2003 11:28 AM
> To: [login to unmask email]
> Subject: Re: Bad performance - cannot pinpoint problem area
>
>
>
> Mark,
> 1.are your tables partitioned? If so have you bound your packages,or plan
> with degree any ?
> 2.How big are the buffer pools you are using for the tablespaces and
> indexspaces.
> 3.One thing you might look for, we had a similar problem where the
> application people bound a package with reopt vars and it was doing a
lot
> of get pages to do a mini bind on the package each time sql was being
> executed. When I changed it to noreopt vars get page requests went
down.
> Bob Yoder
> Tech Specialist
> Phone: 217.424.7117
> Fax: 217.362.3897
> [login to unmask email]
>
>
>
>
> "Vickers, Mark"
>
> <[login to unmask email] To:
> [login to unmask email]
> CO.COM> cc:
>
> Sent by: DB2 Data Subject: Bad performance
-
> cannot pinpoint problem area
> Base Discussion
>
> List
>
> <[login to unmask email]
>
> ORG>
>
>
>
>
>
> 12/11/2003 10:56
>
> AM
>
> Please respond to
>
> DB2 Database
>
> Discussion list
>
> at IDUG
>
>
>
>
>
>
>
>
>
> We have a data warehouse update job which we think is performing badly.
> I will apologize in advance for the length of this, but I am trying
> to answer all the obvious questions up front (like, Is the power on ?).
>
> We have DB2 v7 running on IBM T-Rex.
>
> The batch program is doing 1,7 million selects only using all 9 columns
of
> the index (there are only another 5 columns of data - I know that is
> excessive, but being a data mart, some of the tables are de-normalized.
> There is only one index, which is the primary key, as required by our 4GL
> COBOL Application Development Tool.
>
> The table has 7mil rows, the explain looks good, 9 matching columns on
the
> index scan, the table was in good shape and did not need reorg.
>
> Here are the stats from the job (MainView):
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~
>
> ~~
> RUNTIME ANALYSIS IN DB2 IN APPL. TOTAL %IN DB2(=)
> TOTAL(*)
> ---------------- -------- -------- -------- 0
> ...25...50...75..100%
> ELAPSED TIME 01:02:21 46 s 01:03:07 |
> ===================*
> |
> CPU TIME 00:02:38 17 s 00:02:55 | <
> |
> DB2 WAIT TIME 00:55:39 | =================
> |
> - - - - - - ACTIVITY - - - - - - - - - - - KEY INDICATORS - - - - -
-
> -
> TOTAL SQL..................1,723K FILTER: GETPAGE = 4,387K (> 102K)
>
> GETPAGES...................4,387K SQL: SELECT=1,723K, FETCH= 0
>
> SYNC READS (PRLL=00) ......1,006K I/O RSP: SYNC= 1,944 us, ASYNC=
10
> ms
> PREFETCH PAGES READ........6,156K LOCK SUSPENSIONS = 55
>
> UPDATES/COMMIT................0.0
>
> BFR HIT RATIOS:...VP= 0%,HP=100%
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~
>
> ~~
>
> Here is the part that concerns me:
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~
>
> ~~
> - - - - - - - - ELAPSED TIME ANALYSIS (ACCTG CLASSES 2,3 ONLY) - - - - -
-
> -
> -
> CATEGORY #EVENTS AVG/EVENT ELAPSED %TOTAL
>
> ---------------- ------- --------- -------- ------- 0
> ...25...50...75..100%
> ELAPSED TIME |
> |
> IN DB2 01:02:21 98.77 |
*******************
> |
> IN APPLICATION 46 s 1.22 | <
> |
> --TOTALS-- 01:03:07 100.00 |
> ********************
> |
> WAITS IN DB2 (LOCAL) |
> |
> LOCK/LATCH 535 1,226 us 656 ms 0.01 | <
> |
> I/O WAIT 1,006K 1,944 us 00:32:35 51.63 | **********
> |
> LOG WRITE I/O 0 0 us 0 us 0.00 |
> |
> OTHER READ I/O 135,265 10 ms 00:23:03 36.52 | *******
> |
> OTHER WRITE I/O 0 0 us 0 us 0.00 |
> |
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~
>
> ~~
>
> There seems way too much wait time in DB2.
> I/O Wait is synchronous read I/O and the OTHER READ I/O is:
> Elapsed time spent in DB2 waiting for read I/O for I/O done by
> another thread such as sequential prefetch, sequential detection, or
> list prefetch (QWACAWTR).
> Of course this implies contention, so I made sure none of the other plans
> that access this table were running.
>
> After re-running runstats to be sure, DSNACCAV did not report needing a
> reorg.
> In desperation we reorged the table and rebound the package yesterday and
> got
> exactly the same results today.
>
> The only locks are 1,7m lock and 1,7m unlock for the isolation "CS".
>
> I am running out of ideas, can anyone see or suggest anything please ?
>
> thank you,
> Mark Vickers
> @ANICO.<

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

Mark Vickers

Re: Bad performance - cannot pinpoint problem area
(in response to Joel Goldstein)
To everyone who responded, Thank you, so far.
I am going to try to answer all the pertinent questions herein as
I am out tomorrow and do not want to appear ungrateful for your time.
I will be sure to copy all respondents as soon as I get this resolved.

> I am sure all columns were runstat-ted (word?).
> I think the bufferpools are sized OK and my previous email showed
the split of TS/IX in their own unique bufferpools.
> I misrepresented the access path by saying "scan", that is the way
Visual Explain show index access.
> Read to I/O ratio is almost 1/1, Sync reads = 1 mil,
GetPages = 4,4 mil , in fact this will be easier to read:

ACTIVITY TOTAL BP0 BP6 BP7
------------------- -------- -------- -------- --------
GETPAGES........... 4,387K 9 1,603K 2,784K
SYNC READS......... 1,006K 1 781,456 224,334
GETPAGES/READIO.... 4.4 9.0 2.1 12.4
COND. GP FAILURES.. 0 0 0 0
SEQ. PREFETCH REQS. 0 0 0 0
LIST PREFETCH REQS. 0 0 0 0
DYNAMIC PREFETCHES. 282,629 0 152,162 130,467
ASYNC PAGES READ... 6,156K 0 3,508K 2,647K
PAGES/PREFETCH REQ. 21.8 0.0 23.1 20.3
PAGE UPDATES....... 0 0 0 0
IMMEDIATE WRITES... 0 0 0 0
HP SYNC READS...... 113,704 0 45,372 68,332
HP SYNC READ FAIL.. 0 0 0 0
HP ASYNC PAGES READ 477,079 0 65,750 411,329
HP SYNC WRITES..... 0 0 0 0
HP WRITE FAILURES.. 0 0 0 0

So as I am pushed for time, I am going with Myron's prognosis of
"DEATH BY RANDOM I/O" and have told the applications folks to
sort the input.

To quote a senator (in a deep husky voice and a slight accent)
"I'll be back!" - probably on Monday.

thanks again,
Mark Vickers.


-----Original Message-----
From: Carol G [mailto:[login to unmask email]
Sent: Thursday, December 11, 2003 12:39 PM
To: [login to unmask email]
Subject: Re: Bad performance - cannot pinpoint problem area


Are you sure you are getting Stats on all the columns in the index. We had a
similiar problem also. I would definitely check that. Are your bufferpools
big enough or are you overflowing the bufferpools? Is the tablespace and
index in a different bufferpool?


Regards,
Carol


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Vickers, Mark
Sent: Thursday, December 11, 2003 11:57 AM
To: [login to unmask email]
Subject: Bad performance - cannot pinpoint problem area


We have a data warehouse update job which we think is performing badly.
I will apologize in advance for the length of this, but I am trying
to answer all the obvious questions up front (like, Is the power on ?).

.......

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

Venkat Srinivasan

Re: Bad performance - cannot pinpoint problem area
(in response to Mark Vickers)
I am looking at this thread rather late.
SQL access path is usually responsible for the heavy random i/o. Is the
SQL tuned?.
Poor cluster ratio, access by fat indexes, matching on low cardinality
cols on index are usual reasons.
Regards,
Venkat

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

Mark Vickers

Re: Bad performance - cannot pinpoint problem area
(in response to Venkat Srinivasan)
The SQL is using all 9 columns of the index.
The table did not need reorging, but I did it anyway, stats on Table and
Index, now we have also added DSTATS on the table.
thanks for your input,
Mark.

-----Original Message-----
From: Venkat Srinivasan [mailto:[login to unmask email]
Sent: Sunday, December 14, 2003 9:09 AM
To: [login to unmask email]
Subject: Re: Bad performance - cannot pinpoint problem area


I am looking at this thread rather late.
SQL access path is usually responsible for the heavy random i/o. Is the
SQL tuned?.
Poor cluster ratio, access by fat indexes, matching on low cardinality
cols on index are usual reasons.
Regards,
Venkat

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

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

Mark Vickers

Re: Bad performance - cannot pinpoint problem area
(in response to Mark Vickers)
Dirk,

1. I am not sure where this is going, or if I understand the question, but
here is my
stab at it:
SQL/sec = 25,000
GetPages/sec = 64,000

2. May suggest this if sorting the input does not help.

3. The input file needs to be compared to the data warehouse row by row, and
if there
are differences, the DW will get an insert/update, in this particular
case there
are no differences, so we are getting selects only. The only other
option I see
for the application would be a seq. merge, but I doubt if that would fly.

4. Please see my first posting, I did supply some detail.

baie dankie vir jou help, Mark.

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Friday, December 12, 2003 3:36 AM
To: [login to unmask email]
Subject: Antwort: Bad performance - cannot pinpoint problem area

Hi Mark

1. It seems your problem is sequential Processing with random DB-Access .

How many SQLs or getpages /second do you expect ?

2. Have you ever thought about Parallelising and Partitioning

1.700.000 SQLs / 1 Job on 1 Partition = 62 minutes
1.700.000 SQLs / 10 Jobs on 10 Partitions = 1-2 minutes
1.700.000 SQLs / 100 Jobs on 100 Partitions = :-)

3. Please have a deep look at your application design .

If you have 1.700.000 SQLs maybe you can lower your statement-count .

4. For detailed analysis you should provide statisitcs on statement level .

Statement , Calls , Durations , Average , Summary

Hth

Mit freundlichen Grüßen


--
Dirk Pohl
Senior Consultant, SAP/R3,z/OS,Unix,TSM,DB2,RACF u.v.m, SerCon GmbH
Mobil: 0170/9106609
EMail: [login to unmask email]









An:
[login to unmask email]
Kopie:

Thema:
Bad performance - cannot pinpoint problem area


"Vickers, Mark" Sicherheit:
|-------------------|
<[login to unmask email]>
| [ ] SerCon Intern |
11.12.2003 17:56
|-------------------| (Embedded image moved to file:
Gesendet von: DB2 Data pic00113.jpg)

Base Discussion List

<[login to unmask email]>

Bitte antworten an DB2

Database Discussion list

at IDUG








We have a data warehouse update job which we think is performing badly.
I will apologize in advance for the length of this, but I am trying
to answer all the obvious questions up front (like, Is the power on ?).

We have DB2 v7 running on IBM T-Rex.

The batch program is doing 1,7 million selects only using all 9 columns of
the index (there are only another 5 columns of data - I know that is
excessive, but being a data mart, some of the tables are de-normalized.
There is only one index, which is the primary key, as required by our 4GL
COBOL Application Development Tool.

The table has 7mil rows, the explain looks good, 9 matching columns on the
index scan, the table was in good shape and did not need reorg.

Here are the stats from the job (MainView):

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~
RUNTIME ANALYSIS IN DB2 IN APPL. TOTAL %IN DB2(=)
TOTAL(*)
---------------- -------- -------- -------- 0
...25...50...75..100%
ELAPSED TIME 01:02:21 46 s 01:03:07 |
===================*
|
CPU TIME 00:02:38 17 s 00:02:55 | <
|
DB2 WAIT TIME 00:55:39 | =================
|
- - - - - - ACTIVITY - - - - - - - - - - - KEY INDICATORS - - - - - -
-
TOTAL SQL..................1,723K FILTER: GETPAGE = 4,387K (> 102K)

GETPAGES...................4,387K SQL: SELECT=1,723K, FETCH= 0

SYNC READS (PRLL=00) ......1,006K I/O RSP: SYNC= 1,944 us, ASYNC= 10
ms
PREFETCH PAGES READ........6,156K LOCK SUSPENSIONS = 55

UPDATES/COMMIT................0.0

BFR HIT RATIOS:...VP= 0%,HP=100%

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~

Here is the part that concerns me:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~
- - - - - - - - ELAPSED TIME ANALYSIS (ACCTG CLASSES 2,3 ONLY) - - - - - -
-
-
CATEGORY #EVENTS AVG/EVENT ELAPSED %TOTAL

---------------- ------- --------- -------- ------- 0
...25...50...75..100%
ELAPSED TIME |
|
IN DB2 01:02:21 98.77 | *******************
|
IN APPLICATION 46 s 1.22 | <
|
--TOTALS-- 01:03:07 100.00 |
********************
|
WAITS IN DB2 (LOCAL) |
|
LOCK/LATCH 535 1,226 us 656 ms 0.01 | <
|
I/O WAIT 1,006K 1,944 us 00:32:35 51.63 | **********
|
LOG WRITE I/O 0 0 us 0 us 0.00 |
|
OTHER READ I/O 135,265 10 ms 00:23:03 36.52 | *******
|
OTHER WRITE I/O 0 0 us 0 us 0.00 |
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~

There seems way too much wait time in DB2.
I/O Wait is synchronous read I/O and the OTHER READ I/O is:
Elapsed time spent in DB2 waiting for read I/O for I/O done by
another thread such as sequential prefetch, sequential detection, or
list prefetch (QWACAWTR).
Of course this implies contention, so I made sure none of the other plans
that access this table were running.

After re-running runstats to be sure, DSNACCAV did not report needing a
reorg.
In desperation we reorged the table and rebound the package yesterday and
got
exactly the same results today.

The only locks are 1,7m lock and 1,7m unlock for the isolation "CS".

I am running out of ideas, can anyone see or suggest anything please ?

thank you,
Mark Vickers
@ANICO.

----------------------------------------------------------------------------
-----

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

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

Isaac Yassin

Re: Bad performance - cannot pinpoint problem area
(in response to Mark Vickers)
Hi,
1. Make sure the index is CLUSTERING ant the data is clustered by it.
2. Sort the input file before running the program in the order of the index.
3. Don't SELECT the rows - issue UPDATE where ..... (all your "equal" fields) -
If SQLCODE = 0 then next sequential row.
If SQLCODE = +100 then INSERT and then next seq. row.
Otherwise - it's an error.


Isaac Yassin



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of
Vickers, Mark
Sent: Tuesday, December 16, 2003 4:49 PM
To: [login to unmask email]
Subject: Re: Bad performance - cannot pinpoint problem area

Dirk,

1. I am not sure where this is going, or if I understand the question, but
here is my
stab at it:
SQL/sec = 25,000
GetPages/sec = 64,000

2. May suggest this if sorting the input does not help.

3. The input file needs to be compared to the data warehouse row by row, and
if there
are differences, the DW will get an insert/update, in this particular
case there
are no differences, so we are getting selects only. The only other
option I see
for the application would be a seq. merge, but I doubt if that would fly.

4. Please see my first posting, I did supply some detail.

baie dankie vir jou help, Mark.

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

Mark Vickers

Re: Bad performance - cannot pinpoint problem area
(in response to Isaac Yassin)
Isaac,
1. affirmative.
2. I have asked the developers to do this.
3. I totally agree with this but the insert only happens if there is a
difference -
then the current row is retired via changing the timestamp and inserting
the row,
the input record is then used to update the current row. Plus, the code
is
generated, so we don't have a lot of options.
thanks,
Mark.

-----Original Message-----
From: Isaac Yassin [mailto:[login to unmask email]
Sent: Tuesday, December 16, 2003 9:12 AM
To: [login to unmask email]
Subject: Re: Bad performance - cannot pinpoint problem area


Hi,
1. Make sure the index is CLUSTERING ant the data is clustered by it.
2. Sort the input file before running the program in the order of the index.
3. Don't SELECT the rows - issue UPDATE where ..... (all your "equal"
fields) -
If SQLCODE = 0 then next sequential row.
If SQLCODE = +100 then INSERT and then next seq. row.
Otherwise - it's an error.


Isaac Yassin



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of
Vickers, Mark
Sent: Tuesday, December 16, 2003 4:49 PM
To: [login to unmask email]
Subject: Re: Bad performance - cannot pinpoint problem area

Dirk,

1. I am not sure where this is going, or if I understand the question, but
here is my
stab at it:
SQL/sec = 25,000
GetPages/sec = 64,000

2. May suggest this if sorting the input does not help.

3. The input file needs to be compared to the data warehouse row by row, and
if there
are differences, the DW will get an insert/update, in this particular
case there
are no differences, so we are getting selects only. The only other
option I see
for the application would be a seq. merge, but I doubt if that would fly.

4. Please see my first posting, I did supply some detail.

baie dankie vir jou help, Mark.

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

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

Isaac Yassin

Re: Bad performance - cannot pinpoint problem area
(in response to Mark Vickers)
Hi,
OK. We are close now :-)
Having the input file sorted can lower your random I/O (depends on key distance)
As for the tool - is there no way whatsoever to make the generated code more
"friendly"? (BTW - which tool?)- the logic stinks (sorry)

Isaac Yassin

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of
Vickers, Mark
Sent: Tuesday, December 16, 2003 5:41 PM
To: [login to unmask email]
Subject: Re: Bad performance - cannot pinpoint problem area

Isaac,
1. affirmative.
2. I have asked the developers to do this.
3. I totally agree with this but the insert only happens if there is a
difference -
then the current row is retired via changing the timestamp and inserting
the row,
the input record is then used to update the current row. Plus, the code
is
generated, so we don't have a lot of options.
thanks,
Mark.

-----Original Message-----
From: Isaac Yassin [mailto:[login to unmask email]
Sent: Tuesday, December 16, 2003 9:12 AM
To: [login to unmask email]
Subject: Re: Bad performance - cannot pinpoint problem area


Hi,
1. Make sure the index is CLUSTERING ant the data is clustered by it.
2. Sort the input file before running the program in the order of the index.
3. Don't SELECT the rows - issue UPDATE where ..... (all your "equal"
fields) -
If SQLCODE = 0 then next sequential row.
If SQLCODE = +100 then INSERT and then next seq. row.
Otherwise - it's an error.


Isaac Yassin

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

Mark Vickers

Re: Bad performance - cannot pinpoint problem area
(in response to Isaac Yassin)
Isaac,
My plan is, if the sorted input does not render significant improvement, to
try adding the suggested "OPTIMIZE FOR 1 ROW" to the SQL which will
hopefully reduce the prefetch - this could have the adverse affect too !
And as much as this should have been resolved already, it is never as
urgent, when you ask them to test something, related to the URGENT
production problem you dropped everything to look at, so patience please...

Don't apologize - I agree, and this is not the only drawback of this code
generator (ETI).
With over 900 jobs in our data warehouse cycles, I am avoiding getting
involved in some redesign project (I have done my time in development, so
hopefully before I die, we can adopt a better Q.A. strategy before migrating
to production! This could be a window for that soap-box to come out, but we
will try to avoid that :-)

DB2-L Please Note: I did try to investigate and answer each individual point
from everyone who kindly gave their input, but if you feel I may have missed
something, I would appreciate it if you remind me (just tell me to re-read
your email if applicable).

I'll keep this thread going until we have a satisfactory resolution.
thanks again,
Mark.

-----Original Message-----
From: Isaac Yassin [mailto:[login to unmask email]
Sent: Tuesday, December 16, 2003 11:28 AM
To: [login to unmask email]
Subject: Re: Bad performance - cannot pinpoint problem area


Hi,
OK. We are close now :-)
Having the input file sorted can lower your random I/O (depends on key
distance)
As for the tool - is there no way whatsoever to make the generated code more
"friendly"? (BTW - which tool?)- the logic stinks (sorry)

Isaac Yassin

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of
Vickers, Mark
Sent: Tuesday, December 16, 2003 5:41 PM
To: [login to unmask email]
Subject: Re: Bad performance - cannot pinpoint problem area

Isaac,
1. affirmative.
2. I have asked the developers to do this.
3. I totally agree with this but the insert only happens if there is a
difference -
then the current row is retired via changing the timestamp and inserting
the row,
the input record is then used to update the current row. Plus, the code
is
generated, so we don't have a lot of options.
thanks,
Mark.

-----Original Message-----
From: Isaac Yassin [mailto:[login to unmask email]
Sent: Tuesday, December 16, 2003 9:12 AM
To: [login to unmask email]
Subject: Re: Bad performance - cannot pinpoint problem area


Hi,
1. Make sure the index is CLUSTERING ant the data is clustered by it.
2. Sort the input file before running the program in the order of the index.
3. Don't SELECT the rows - issue UPDATE where ..... (all your "equal"
fields) -
If SQLCODE = 0 then next sequential row.
If SQLCODE = +100 then INSERT and then next seq. row.
Otherwise - it's an error.


Isaac Yassin

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

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

Philip Sevetson

Re: Bad performance - cannot pinpoint problem area
(in response to Mark Vickers)
Mark,

I was trained as an ETI*EXTRACT master user a few years ago. One thing
which was clear in the training was that all generated code is a
compromise -- ease of generation is usually considered before efficiency,
since each platform must be customized differently. Where performance is
key, your master user must use the code blocks (?) which write specific
code as a way to insert individually optimized SQL statements.

ETI is designed to make the non-critical 95% of your code easy to write
and maintain, and easy to implement across multiple platforms; for the
1-5% which gets executed billions of times, you must individually tune the
code blocks and customize using the CIPs.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
230 Raritan Center Parkway, P5-105
Edison, NJ 08837
Phone: (732) 225-8086
Fax: (732) 225-8109
mailto:[login to unmask email]






"Vickers, Mark" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/16/2003 02:54 PM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: Re: Bad performance - cannot pinpoint problem area


Isaac,
My plan is, if the sorted input does not render significant improvement,
to
try adding the suggested "OPTIMIZE FOR 1 ROW" to the SQL which will
hopefully reduce the prefetch - this could have the adverse affect too !
And as much as this should have been resolved already, it is never as
urgent, when you ask them to test something, related to the URGENT
production problem you dropped everything to look at, so patience
please...

Don't apologize - I agree, and this is not the only drawback of this code
generator (ETI).
With over 900 jobs in our data warehouse cycles, I am avoiding getting
involved in some redesign project (I have done my time in development, so
hopefully before I die, we can adopt a better Q.A. strategy before
migrating
to production! This could be a window for that soap-box to come out, but
we
will try to avoid that :-)

DB2-L Please Note: I did try to investigate and answer each individual
point
from everyone who kindly gave their input, but if you feel I may have
missed
something, I would appreciate it if you remind me (just tell me to re-read
your email if applicable).

I'll keep this thread going until we have a satisfactory resolution.
thanks again,
Mark.

-----Original Message-----
From: Isaac Yassin [mailto:[login to unmask email]
Sent: Tuesday, December 16, 2003 11:28 AM
To: [login to unmask email]
Subject: Re: Bad performance - cannot pinpoint problem area


Hi,
OK. We are close now :-)
Having the input file sorted can lower your random I/O (depends on key
distance)
As for the tool - is there no way whatsoever to make the generated code
more
"friendly"? (BTW - which tool?)- the logic stinks (sorry)

Isaac Yassin

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of
Vickers, Mark
Sent: Tuesday, December 16, 2003 5:41 PM
To: [login to unmask email]
Subject: Re: Bad performance - cannot pinpoint problem area

Isaac,
1. affirmative.
2. I have asked the developers to do this.
3. I totally agree with this but the insert only happens if there is a
difference -
then the current row is retired via changing the timestamp and
inserting
the row,
the input record is then used to update the current row. Plus, the
code
is
generated, so we don't have a lot of options.
thanks,
Mark.

-----Original Message-----
From: Isaac Yassin [mailto:[login to unmask email]
Sent: Tuesday, December 16, 2003 9:12 AM
To: [login to unmask email]
Subject: Re: Bad performance - cannot pinpoint problem area


Hi,
1. Make sure the index is CLUSTERING ant the data is clustered by it.
2. Sort the input file before running the program in the order of the
index.
3. Don't SELECT the rows - issue UPDATE where ..... (all your "equal"
fields) -
If SQLCODE = 0 then next sequential row.
If SQLCODE = +100 then INSERT and then next seq. row.
Otherwise - it's an error.


Isaac Yassin

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

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



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

Isaac Yassin

Re: Bad performance - cannot pinpoint problem area
(in response to Philip Sevetson)
Hi,

I don't remember a CURSOR mentioned in the discussion up to now, but I may have
missed it.
I understood we are talking on SELECT (as singleton select).
OPTIMIZE for 1 row is meant to influence cursor to look for a cursor which
avoids the sort for the ORDER BY. It has nothing to do with lowering direct I/O
(actually it may increase it until dynamic prefetch is kicked in).

Looks to me like I'm missing something about the actual access generated by the
culprit :-)


Isaac Yassin

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of
Vickers, Mark
Sent: Tuesday, December 16, 2003 9:55 PM
To: [login to unmask email]
Subject: Re: Bad performance - cannot pinpoint problem area

Isaac,
My plan is, if the sorted input does not render significant improvement, to
try adding the suggested "OPTIMIZE FOR 1 ROW" to the SQL which will
hopefully reduce the prefetch - this could have the adverse affect too !
And as much as this should have been resolved already, it is never as
urgent, when you ask them to test something, related to the URGENT
production problem you dropped everything to look at, so patience please...

Don't apologize - I agree, and this is not the only drawback of this code
generator (ETI).
With over 900 jobs in our data warehouse cycles, I am avoiding getting
involved in some redesign project (I have done my time in development, so
hopefully before I die, we can adopt a better Q.A. strategy before migrating
to production! This could be a window for that soap-box to come out, but we
will try to avoid that :-)

DB2-L Please Note: I did try to investigate and answer each individual point
from everyone who kindly gave their input, but if you feel I may have missed
something, I would appreciate it if you remind me (just tell me to re-read
your email if applicable).

I'll keep this thread going until we have a satisfactory resolution.
thanks again,
Mark.

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

Mark Vickers

Re: Bad performance - cannot pinpoint problem area
(in response to Isaac Yassin)
Isaac,
Good observation, and you were right in that the statement was a select and
not using a cursor.
I hang my head in shame ...

However, we sorted the input and the test in development went great:
Job took 6 minutes (157 CPU seconds), instead of 1 hour 2 minutes (270 CPU
seconds in production), so I am hopeful that this will solve our problem.

I will have to logon before I go to bed - 25 years in this game and I still
get excited about these little things (probably because I have only done the
DBA thing for the last 3 years :-)

tune in tomorrow for the season finale of this exciting saga.
Mark.

-----Original Message-----
From: Isaac Yassin [mailto:[login to unmask email]
Sent: Wednesday, December 17, 2003 9:59 AM
To: [login to unmask email]
Subject: Re: Bad performance - cannot pinpoint problem area


Hi,

I don't remember a CURSOR mentioned in the discussion up to now, but I may
have
missed it.
I understood we are talking on SELECT (as singleton select).
OPTIMIZE for 1 row is meant to influence cursor to look for a cursor which
avoids the sort for the ORDER BY. It has nothing to do with lowering direct
I/O
(actually it may increase it until dynamic prefetch is kicked in).

Looks to me like I'm missing something about the actual access generated by
the
culprit :-)


Isaac Yassin

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of
Vickers, Mark
Sent: Tuesday, December 16, 2003 9:55 PM
To: [login to unmask email]
Subject: Re: Bad performance - cannot pinpoint problem area

Isaac,
My plan is, if the sorted input does not render significant improvement, to
try adding the suggested "OPTIMIZE FOR 1 ROW" to the SQL which will
hopefully reduce the prefetch - this could have the adverse affect too !
And as much as this should have been resolved already, it is never as
urgent, when you ask them to test something, related to the URGENT
production problem you dropped everything to look at, so patience please...

Don't apologize - I agree, and this is not the only drawback of this code
generator (ETI).
With over 900 jobs in our data warehouse cycles, I am avoiding getting
involved in some redesign project (I have done my time in development, so
hopefully before I die, we can adopt a better Q.A. strategy before migrating
to production! This could be a window for that soap-box to come out, but we
will try to avoid that :-)

DB2-L Please Note: I did try to investigate and answer each individual point
from everyone who kindly gave their input, but if you feel I may have missed
something, I would appreciate it if you remind me (just tell me to re-read
your email if applicable).

I'll keep this thread going until we have a satisfactory resolution.
thanks again,
Mark.

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

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

Mark Vickers

Re: Bad performance - cannot pinpoint problem area
(in response to Mark Vickers)
All input was relevant, but with good stats, and best possible coding of the
SQL (maybe not the best program design, but didn't have that much leeway
there), we tested the "DEATH BY RANDOM I/O" possibility and...

Here are the results before and after sorting the input:

17DEC03 LONG RUNNING BATCH JOB

END TIME PLAN AUTHID CONNECT ELAPSED CPU # STMTS
GETPAGE
----------- -------- -------- -------- -------- -------- -------
-------
UNSORTED: 22:21:52 DW088P1 AD9H20 BATCH 00:52:02 00:02:24 1,740K
4,429K
SORTED: 20:53:08 DW088P1 AD9H20 BATCH 00:08:15 00:01:28 1,740K
580,530

thanks again to all who helped.

I hope the season is all you hoped for,
and the new year will bring health and happiness.
Mark.

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