Hello Friends

Can any one tell me What is the correct optimizer level to set in DSS environment?

Here, complicated queries -- minimum with 10 table joins to get the result set from the tables which contains more than 100 billion rows each.

Please help!

-Racha

Hi,

We prefer to use 5 and we are pretty happy with the performance of the
same.

So i feel you can go ahead and set DFT_QUERYOPT=5 and monitor your performance.

HTH

Nitin

Hi,

same with us. My boss did some tests on dss loads with version 7 one year ago and found level 5 to be most effective.

Johann

I'll side Nitin and Johann .... 5 is a good choice ...

It may be worth using Visual Explain to see if higher optimization levels achieve anything ...

Cheers
Sathyaram

OOOPS!!!

Iam sorry I did not mention the Version

Yes its Db2 V8.1 FP3 AIX-5.2

Did some benchmarking on OPT's

Optimizer 7 -- No good with performance
Optimizer 5 -- No good with performance
Optimizer 3 -- Performance is good but (Missing Hash Join)
Optimizer 9 -- Perf is good and plan to change it to..

Any suggestions, ideas..

-Racha

Based on your results (poor optimization of level 7), I would make sure all your tables are reorged and you have full runstats on all tables, indexes, and columns. You probably don't need to continually execute runstats if your data is pretty much the same over time, but you need to full and comprehensive statistics at least once.