Part-1 Complex SQL Tuning Migraine...runs for 7 hrs have only 4.5hrs at to do that...what's a man to do?
Hi all, I hope all is well in your homes. I do have s set of SQLs that is giving me migraine because the performance is driving me up the wall. The set of SQL is coming from SIEBEL generated SDLs, indexed to dearth, I think, runs from 12 am in the morning with a mandate to be done with at 5 am in a 24/7 business, and I have nary a sophisticated or clever tool to help in this work. Lately, this code has been running till 7am and it causing our business partners to miss their commitments to other partners...not a good place to be as a DB2 DBA or programmer!!
I need your collective tuning expertise to solve this one. I have attached one set of this SQL - this one set works like a WLM-managed - each set must complete to complete a unit of wor (UOW) to produce a useable output for the next.
One of my thoughts is to look at ways to combine the three SQLs to one or only two because they almost identical.
Another thought is that between these SQLs are over 60 indexes with mostly index scans on all of them - a terrible code made possible by a code generator. I will like to see if I can utilize multi-dimentional clustering to facilitate less scans, and the heavy-load that excessive indexing that this imposses on the CPU and run-time, to say the lest.
The third thought that I have is if MQT can help this case. Can anyone gives his/her considered thought and opinion?
Finally, any other idea that anyone has for consideration will be highly highly appreciated.
Also, to make t worth the and to show my heightened concerns, I am willing to make each of my questions a seperate but related question so that people can focuse on areas they believe they can offer more effective advise. Is that okay with everyone?
Thanks everyone. WLM-SET1-SQL.sql