Hi, I have a complicated select sql run in stress test database with execution time 24min. When I run the same sql in production database with similar data volume and similar configuration, the sql cannot be completed in 12 hours! Attached sql, dbcfg, dbmcfg, explan of both database. Can anyone help?

Thanks in advance

one more file here

Wow!

You've got a massive SQL. Optimizing this could be a major task. However, there are always a few things to do:
1) Are the explains similar? In this case, no. The problem can be there...
2) Beware the TBSCAN. When reading a table with merge scan, doing JOIN + TBSCAN is a murder. At least, EX_BOARD in the prod-explain seems to have TBSCAN on.
3) And how to eat an elephant? One part at a time. Consider breaking this sentence into many small sentences, for explain-purpoise only. If any TBSCAN's jump out, consider adding a new index.

If I had to optimize this beast - I'll probably allocate 50-80 workhours.

Cheers, Bill

If you have a good access path on one system, you could try using an optimization hint with the good access path when binding on the other system.

I'll point out the obvious... RUNSTATS and REORG.

Presumably same indexes and everything on both systems?

In addition to RUNSTATS and REORG, for complex queries change the queryopt level to 7. The default is 5.

One more I've found tremendously useful lately is db2advis... often it makes recomendations that at the very least are a good starting point.

Thanks all guys.

I forgot to post some basic info: UDB V7.2, FP7, SOLARIS

to hurmavi: I will try to break down the sql, but it would not be an easy job!

to urquel: Could you show me how to use optimization hint? I didn't know if I can add hint similar to that of Oracle.

to J Petruk: reorg and runstats were already run. Indexes are the same on both system. I will try using db2advis to see if can help.

to Marcus_A: currently we are using optimization level 0. when we used default 5 in stress test database, the query need 11GB temp tablespace and run for nearly 1 hour. After we changed to level 0, only 0.2GB temp tablespace needed and run for 24 min only. Thus we use level 0. However, when we do the same on production database, it is a nightmare! Users do not expect to change optimization level in different db or in different time. And also, I don't quite understand why the explain plan in both database are so different.

Again, thanks all. I will post update info as well as my testing result asap.

It looks like optimization hint is available on mainframe, but I don't see any reference to it in the midrange manuals. If you were on the mainframe, you would just copy the rows from the PLAN_TABLE corresponding to the access path you want to use and insert them into the PLAN_TABLE where you want to implement the optimization hint. Then update all of those rows and set the OPTHINT column = "MYHINT" where ... Then, REBIND and add the parameter OPTHINT(MYHINT). Of course, I doubt that this will work on your Solaris box, but you could give it a try.

Hi urquel, I can't find and table named 'plan_table' in the database. Is it under any particular schema?

Hi,

PLAN_TABLE is available in SYSIBM schema but you have to excute script to create that table as it is not autamatically created .