If you have a problem with your Oracle database performance you have several options and tools for tuning it. If the problem is caused by a wrong execution plan, then you can use Store Outlines, SQL Profiles or SQL Plan Management (SPM) to give oracle more informations for better optimizer decissions and execution plan stability.
SQL Plan Management is actually the one Oracle database feature you should use if you want
- get suggestions for may be better execution plans generated by oracle
- have full control over activation and deactivation of this suggestions
SQL Plan Management was introduced in Oracle 11g and was enhanced in 12cR1 with adaptive features. Tim Hall’s blog is excellent for getting started with this topic.
Until now oracle do not recommend using automatic capture of execution plans. It is disabled by default. Until 12.1 you can enable it for the global database including all recursive SQLs only. This can blow up the SYSAUX tablespace on databases with heavy workloads.
But thanks to Maria Colgan for her new blog post … I realized today …
This means starting with 12.2 SQL Plan Management you can limit the automatic capture process on a subset of SQLs using filters based on:
- Schema names,
- Module names,
- Action names and
- SQL text
Now we can activate the automatic capture process globally and control the SYSAUX space consumtion using filter definitions.
That is great!
For more details on SPM look at this: