SQL plan directives

 SQL plan directives

SQL plan directives are automatically created based on information learnt via automatic re-optimization. A SQL plan directive is additional information that the optimizer uses to generate a more optimal execution plan. For example, when joining two tables that have a data skew in their join columns, a SQL plan directive can direct the optimizer to use dynamic statistics to obtain a more accurate join cardinality estimate.

SQL plan directives are created on query expressions rather than at a statement or object level to ensure they can be applied to multiple SQL statements. It is also possible to have multiple SQL plan directives used for a SQL statement. The number of SQL plan directives used for a SQL statement is shown in the note section under the execution plan (Figure 15).

Figure 15: The number of SQL plan directives used for a statement is shown in the note section under the plan

The database automatically maintains SQL plan directives and stores them in the SYSAUX tablespace. Any SQL plan directive that is not used after 53 weeks will be automatically purged. SQL plan directives can also be manually managed (altered or deleted) using the package DBMS_SPD but it is not possible to manually create a SQL plan directive. SQL plan directives can be monitored using the views DBA_SQL_PLAN_DIRECTIVES and DBA_SQL_PLAN_DIR_OBJECTS (See Figure 16).

Figure 16: Monitoring SQL plan directives automatically created based on information learnt via re-optimization

There a two types of SQL plan directive rows: DYNAMIC_SAMPLING and DYNAMIC_SAMPLING_RESULT. The dynamic sampling type tells the optimizer that when it sees this particular query expression (for example, filter predicates on country_id, cust_city, and cust_state_province being used together) it should use dynamic sampling to address the cardinality misestimate.

The dynamic sampling result type is present from Oracle Database 12c Release 2 onwards and signifies where results from dynamic sampling queries are stored in the SQL directive repository (instead of the Server Result Cache as used by Oracle Database 12c Release 1).

Figure 17: Dynamic sampling results stored in the SQL plan directive repository in Oracle Database 12c Release 2 onwards.

From Oracle Database 12c Release 2 onwards, SQL plan directives are created but they are not used at parse time to modify SQL execution plans. This prioritizes plan stability over maximizing query execute performance. The optimizer's use of SQL plan directives is controlled by the database initialization parameter optimizer_adaptive_statistics, which has the default value of FALSE. This setting is recommended for most systems.

In addition to finding optimal SQL execution plans, SQL plan directives can be used by the database to determine if extended statistics2, specifically column groups, are missing and would resolve the cardinality misestimates. After a SQL directive is used the optimizer decides if the cardinality misestimate could be resolved with a column group. If so, the database can automatically create that column group the next time statistics are gathered on the appropriate table. This step is “always on” in Oracle Database 12c Release 1, but from Oracle Database 12c Release 2, it is controlled by the DBMS_STATS preference AUTO_STAT_EXTENSIONS. The recommended (and default) value is OFF. This prioritizes plan stability over maximizing query performance. Automatic column group creation can be enabled using the following step:

EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_STAT_EXTENSIONS', 'ON')

Extended statistics will be used in place of the SQL plan directive when possible (equality predicates, group bys etc.). If the SQL plan directive is no longer necessary it will be automatically purged after 53 weeks.