Tweet
Looking at changing this value as it seem some of our queries are not picking up on their new access plans. Looks like *YES and *FORCE are the same thing but could have some performance implications. *ONLY_REQUIRED looks like a decent plan but I will still need to run *YES or *FORCE for a little while to get some of the older queries updated. Just looking for any experience here.
-
-
Re: setting option reoptimize access plan in the qaqqini file
The SQL Queries always take the last/current access plan!
If an query is run for the first time in a job or in an activation group a full open must be performed, i.e. the access plan is created or validated and if neccessary updated.
If an ODP is opend (and stays open), reusable ODP, only a pseudo open will be performed, i.e. only the data in the existing ODP are updated.
After a query is executed, it is again optimized under the cover. If a better access plan can be found the existing access plan is updated
Also if a query runs and does not return data within 2 seconds it is optimized under the cover again. If a better access plan is found that can return the data faster, query execution might be cancelled and restarted with the new access plan. (AQP - Advanced Query Processing)
The option set in with QAQQINI file is the best one and should not be changed.
Birgitta -
Re: setting option reoptimize access plan in the qaqqini file
I am not a query expert but it was not choosing the right access path, this program was taking 5 hours to run and even after we created the index it still did not use it until we forced it to re-optimize? I guess I am still not seeing how leaving the qaqqini alone is right. After forcing the change, the program takes under a minute.Comment
Comment