Unanswered: Optimization Profiler for Stored procedure
I have an SQL which when fired from an application retrieves result very faster as this query is added to the optimization profiler. However i tried to make use of that query in a stored procedures where the values to that query in where condition are fetched from the session table created by the procedure. In this case the same query is running for 30 secs where as the standalone query completes in less than a second.
I captured the query fired from the SPROC and added the same to the optimization profiler but still the SPROC is not mahing use of the opt profiler.
Can someone tell me how to make use of the optimization profiler for stored procedures or how can i optimize the access plan of these SQL's written in SPROC to retrieve results faster.
Im using DB2 v 9.5
I have around 100's of procedures created and all having the same performance issue. Im just trying to implement this feature in one procedure and check if i can implement the same in all other procedures. If fine tuning is the only way to fix this performance issue then i will have to make this change in all procedures. Hence please let me know if i can apply any other staright forward fix like adding the SPROC to optimization profile which can resolve the performance issue in all procedures.
I dont understand exactly what is your meaning of "other staright forward fix"
As far as i kown there are some ways to influnce db2 to choose a better access plan:
eg: collect statistics ,statistics view ,selectivity hint, reorg table to improve cluster ratio,
add some indexes,tuning some config parameter .....
But they all need to do bad access plan analysis first。
Would you mind to publish your optimization file and the bad access plan here?