Insights into Statistics, Query Optimization and the Oracle Optimizer
-
SQL Plan Management
January 30, 2017
New SQL Plan Management White Paper
A new white paper for SQL Plan Management is now available. Just go to the Query Optimization page in OTN and click the SQL Plan Management with Oracle Database link.
Just like the Optimizer white paper, I've kept the content and format similar to the way Maria organized it so that it will be familiar to those of you that have read previous versions.
Post comments to this blog if you want to give me feedback on the white paper's content. If you want more examples, let me know and I'll add suggestions to my to-do list.
Join the discussion
Comments ( 4 )
Thanks for the amazing documentation on SPM. I had a query.
I am planning to implement this at one of our Clients VLDB environment. We have some very critical batch jobs which run every night and we cannot afford to have delays.
What if when the query runs, the optimizer finds a better plan based on the bind variables, however if I implement SPM it will not be used. Any way I can evolve the plan during run time and use if the cost is less ?
You can initiate an evolve task manually any time you like - so you could make it part of the batch flow. DBMS_SPM.CREATE_EVOLVE_TASK, DBMS_SPM.EXECUTE_EVOLVE_TASK and then DBMS_SPM.IMPLEMENT_EVOLVE_TASK.
I don't think that this will definitively give you what you (perhaps) want. There will be a delay in enabling a new plan, so evolve might find a better plan but only after an existing SQL plan baseline plan will have been used at least once. I would be inclined to stick to using SPM in a maintenance window.
Regards,
Nigel