bbessa
New User
Joined: 03 Aug 2006
Posts: 14
Location: Brazil
|
|
|
|
Hello,
I'm looking for some help on using MQTs created with ENABLE QUERY OPTIMIZATION in DB2 v8 for z/OS.
The MQT I'm testing has basically a join of two tables a SUM() field. After refresh, I've decided to enable query optimization and test the AQR features of DB2.
So I'd chosen exactly the same query of the MQT's DDL to test. Before running the actual query, I EXPLAIN'ed it and the results says that DB2 is using the MQT in the query's access path. Visual Explain also says the same thing.
So far, so good. But while running the query, I noted that it was taking too long to return the results. Using DB2 Admin, I've checked that my query is really running on the MQT. I didn't wait until the end of the execution because it was already many times higher than the same query running directly against the base tables. Then I think: well, there is something wrong with the MQT. So I disable query optimization and run "SELECT * FROM MY_MQT". The query runs smoothly and returns the results way faster then running against the base tables, as expected.
I check ASG's TMON data for any clues and find something weird.
Running the query against the base tables, I've the following data:
SQL COUNTS: 4 (1 FETCH)
REMOTE CALLS: 7
GETPAGES: 19
TOTAL TIME: 14 minutes
DB2 Elapsed time: 2 minutes 29s
OK, now look this. Running the query after ENABLE QUERY OPTIMIZATION.
SQL COUNTS: 1129556 (1129554 FETCHES!)
REMOTE CALLS: 1129557
GETPAGES: 7216
TOTAL TIME: 48min 45s (until cancelled)
DB2 Elapsed time: 25s (!)
25s of DB2 elapsed time in thread of 48 minutes total running time? More than 1 million remote calls and SQL executed? What DB2 is doing here? I can't understand that behavior and the data gave me more questions then answers. Could anyone please explain what could be wrong? Anyone had similar issue?
Thank you,
bbessa |
|
