What is it?
In previous releases the database stored compilation and execution statistics in a shared sql area which is non persistent. Starting in 12c the database can use a sql plan directive, which is additional information and instructions that the optimizer can use to generate a better execution plan. SQL plan directive may instruct the optimizer to record a column groups and expressions statistics.
For example, a sql plan directive might instruct the optimizer to:
Collect missing statistics
Create column group statistics
Perform dynamic statistics (Dynamic Sampling).
During sql compilation or execution, the database analyzes the query that is missing statistics or that miss-estimated for cardinality to create a sql plan directive.
When the optimizer generates an execution plan, the directives give the optimizer additional information about objects that are referenced in the plan.
SQL plan directives are not tight to a specific statement or specific sql id. Directives can be used and shared between multiple statements:
The optimizer can use directives that are collected on query expressions.
Directives are automatically maintained created as needed during compilation or execution (missing statistics, cardinality misestimates) and purged if not
used after a year.Directives can be monitored using dba_sql_plan_directives and dba_sql_plan_dir_objects dictionary tables.
Sql plan directives are persisted to disk in the SYSAUX tablespace; a plan directive is created and purged to disk (SYSXAUX) every 15 minutes.
Disable SQL Plan Directives:
alter system set “_sql_plan_directive_mgmt_control” = 0;
alter system set “_optimizer_dsdir_usage_control” = 0;
Enable SQL Plan Directives:
The first two commands below will set the default values for the two hidden parameters in the running instance and revert the change introduce previously. The last two commands will remove the parameters from the spfile if they were added to ensure default are used at next startup.
alter system set “_sql_plan_directive_mgmt_control” = 3;
alter system set “_optimizer_dsdir_usage_control” = 126;
alter system reset “_sql_plan_directive_mgmt_control”;
alter system reset “_optimizer_dsdir_usage_control”;
Use as Hint to disable:
select /*+ opt_param(‘_optimizer_use_feedback’ ‘false’) */
* from table;
