Yet another performance conundrum
Posted on 2011-09-02
I have posted earlier in regards to this topic and how got a lot of help from the experts. Was hoping for one more piece of advise.
We have 4 MQT's (with refresh deferred) that refresh in parallel. Each of the MQT's have close to 200 million records. I used a lot of advise for improving the performance:
1) Journal are turned off
2) Commitment control is off
3) Indexes are dropped before refresh and created after the refresh
4) We have used service programs to improve efficiency
5) MQT's are refreshed in parallel.
6) All queries in the program use SQE.
7) The IGNORE_DERIVED_INDEX is set to *YES in QAQQINI
8) DEGREE is set to *OPTIMIZE
9) OPTIMIZATION_GOAL is set to *ALLIO in QAQQINI
After all this, before we deployed to our production schema we ran the jobs on a staging region on our production server. The refresh of all 4 MQT's in parallel took 60 minutes on the production server on the staging schema. Unfortunately, when the job moved to production and ran on the production schema (on the same server as the staging region), the job took more than twice the time (i ran the refresh multiple times to ensure plan caches were built).
One of the differences that i noticed was that the longest running MQT was assigned multiple threads when i do a "Work with Threads" on the staging schema. On production schema, however, this same job is being assigned only thread. These are threads that the system is assigning and is not something i am doing in my program. Is there any way i can control this? Would this be causing the job to perform slower?
Any ideas on what else could be the difference?