X

Insights into Statistics, Query Optimization and the Oracle Optimizer

New SQL Plan Management White Paper

Nigel Bayliss
Product Manager

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 )
  • Chuck Tuesday, May 23, 2017
    Is there any documentation regarding limitations, restrictions, recommendations, or best practices for the use of SPM within database heavily constrained with Virtual Private Database policies restricted tables and views? My SYS_AUTO_SQL_TUNING_TASK is flooded with ORA-28113 and ORA-00936 errors because the policy exempt SYS cannot run the application queries it is trying to evaluate.
  • Nigel Bayliss Wednesday, May 24, 2017
    Hi Chuck, no there isn't. It's hard to imagine how there could be a best practice solution applicable to many (IMO). In the example you give, isn't this an issue with auto SQL tuning? I'll get in touch with you and we can discuss further.
  • Abhishek Saturday, March 7, 2020
    Hi Nigel,
    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 ?
  • Nigel Bayliss Tuesday, March 10, 2020
    Hi Abhishek,

    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
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.