Hello,

I have a query which runs well in the first 15-30 minutes (lots of user-
mode CPU-time and/or lots of I/O), but which then deteriorates into a
situation with only CPU wait-time and close to no I/O (80 read-I/Os per
second, yielding around 1MB/s). The deteriorated state may last for more
than a day (after which I kill the query and give up).

The query is expected to be very heavy, but I can't find an explanation
as to why the query degenerates into the zombie-like state after a while.

Let's call the server which displays the above situation "zombie".

On another server - let's call it "tarzan" - the same query runs within
around two hours. There doesn't seem to be any differences between the
table/index structures between the two servers, although some object
names are slightly different.

Query-explains on the two servers yield slightly different total cost
estimations:
zombie: 13530173
tarzan: 17700850

Clearly, the cost estimation on "zombie" is way off in comparison with
reality.

The query involves five UNIONs, resulting in five different "legs" on the
access plans. The two servers to choose different access plans for some
of these "legs", so I'm intested in finding out which "leg(s)" is being
worked on when the "zombie" is in it's degenerate state.

Is there a way to find out which part of an access plain is currently
being worked on?

--
Troels

try any luck this way,

select distinct db_name, left(stmt_text,2000) from SYSIBMADM.SNAPSUBSECTION

Yonghang Wang wrote:
Thanks, but that doesn't seem to give me a clue as to which part of the
access plan is currently being executed.

--
Troels

Troels, thanks for letting me know that doesn't work.

Troels, basically I believe all branches are alive if they all take time.

to determine which part are running, try combination as below,

1. run db2exfmt/db2expln to get the access plan. pls see to the "subsection".
you will see the two branches are subsection #1 and #2.
RETURN
( 1)
( 2)
/ \
GRPBY GRPBY
( 3) ( 9)
( 4) ( 10)
( 5) ( 11)
( 6) ( 12)
( 7) ( 13)
( 8) ( 14)
xxx yyy
2. run the sql.
3. get the snapshot for the application which is running the sql. you will get the running subsection #.

......
Dynamic SQL statement text:
select 'x', xxx, count(*) from xxxx group by xxx_idn union all select 'y', yyy, count(*) from yyyy group by yyy

Subsection number = 1
Subsection database partition number = 1
Subsection status = Executing
Execution elapsed time (seconds) = 27
Total user CPU time (sec.ms) = 14.860000
Total system CPU time (sec.ms) = 0.210000
Current number of tablequeue buffers overflowed = 0
Total number of tablequeue buffers overflowed = 0
Maximum number of tablequeue buffers overflowed = 0
Rows received on tablequeues = 0
Rows sent on tablequeues = 0
Rows read = 2042650
Rows written = 0
Number of agents working on subsection = 1

Agent process/thread ID = 26835

Subsection number = 2
Subsection database partition number = 1
Subsection status = Executing
Execution elapsed time (seconds) = 27
Total user CPU time (sec.ms) = 14.160000
Total system CPU time (sec.ms) = 0.270000
Current number of tablequeue buffers overflowed = 0
Total number of tablequeue buffers overflowed = 0
Maximum number of tablequeue buffers overflowed = 0
Rows received on tablequeues = 0
Rows sent on tablequeues = 0
Rows read = 1922264
Rows written = 0
Number of agents working on subsection = 1

Agent process/thread ID = 12558

Yonghang Wang wrote:
[...]
Ah, now I get it (I think). Thanks a lot.

--
Troels

Hello again,

Yonghang Wang wrote:
It seems that no matter which query is running, I always get
Section number = 203

This leads me to think that GET SNAPSHOT FOR APPLICATION's section number
information cannot really be used to get an impression of which part(s) of
an execution plan is currently being worked on.

--
Troels

DPF? you need to run the GET SNAPSHOT FOR APPLICATION on data partition.