Elapsed time problem; need help

Walter Janißen

Elapsed time problem; need help
Hello list

Currently a job is running since yesderday. Doing a quick shot, I have some
problems in understanding certain numbers for a SQL in an application trace.

The SQL is as follows:

SELECT MAX(CHECKP_NR)
FROM RWTB0512
WHERE VORGANG = :H

Explain shows a one fetch index only access (accesstype I1) with 1 matching
index, i.e. the best accesspath DB2 is offering.

Application trace shows the following:

Data Rows Rows Rows .... Pages
Type Proces Looked Qual/DM .... Scanned
---- ------ ------ ------- ---- -------
INDX 1 34974 1 333

Could anybody explain the high numbers of rows looked and pages scanned.
Because it is an I1 access path, I would expect rows looked 1 and pages
scanned say 3 (number of levels).

Some years ago we have a similar problem with type2-indexes and pseudo-
deleted rows. But that was an index access with a few matching columns
where DB2 has to scan a few index entries. But in this case the first index
leaf entry must be the right value.

Any hints would be very appriciated.

P.S. The number of entries for the host-variable in question is about
36000, something near the rows processed.



Terry Purcell

Re: Elapsed time problem; need help
(in response to Walter Janißen)
Walter,

There are many questions to be asked regarding your problem:

Firstly, which explain shows an I1 fetch? Is it a dynamic explain or
EXPLAIN(YES) on the most recent bind?

What access path does Omegamon (I'm assuming it's an Omegamon trace) show?
I'm no Omegamon expert, but it looks like you have an ascending, rather than
descending index. Has someone dropped the descending index?

Finally, why is this job still running? Is this query being executed a few
million times?

Hope this helps your investigation.
Regards
Terry Purcell
Yevich Lawson & Associates
[login to unmask email]
WWW.YLASSOC.COM
WWW.DB2-SYMPOSIUM.COM

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Walter Janissen
Sent: Friday, January 12, 2001 3:49 AM
To: [login to unmask email]
Subject: Elapsed time problem; need help


Hello list

Currently a job is running since yesderday. Doing a quick shot, I have some
problems in understanding certain numbers for a SQL in an application trace.

The SQL is as follows:

SELECT MAX(CHECKP_NR)
FROM RWTB0512
WHERE VORGANG = :H

Explain shows a one fetch index only access (accesstype I1) with 1 matching
index, i.e. the best accesspath DB2 is offering.

Application trace shows the following:

Data Rows Rows Rows .... Pages
Type Proces Looked Qual/DM .... Scanned
---- ------ ------ ------- ---- -------
INDX 1 34974 1 333

Could anybody explain the high numbers of rows looked and pages scanned.
Because it is an I1 access path, I would expect rows looked 1 and pages
scanned say 3 (number of levels).

Some years ago we have a similar problem with type2-indexes and pseudo-
deleted rows. But that was an index access with a few matching columns
where DB2 has to scan a few index entries. But in this case the first index
leaf entry must be the right value.

Any hints would be very appriciated.

P.S. The number of entries for the host-variable in question is about
36000, something near the rows processed.








Walter Janißen

Re: Elapsed time problem; need help
(in response to Terry Purcell)
Well here are some items to further clarifa my problem

1. The explain is the most recent bind of a static statement.

2. The index in question is:

VORGANG ASC
CHECKP_NR DESC

3. The statement was executed many times (I don't know it it was a million
times), but the trace output (yes it is Omegamon output) shows one single
SELECT.