Oracle Adaptive Plans: Disable For Better Performance

Improving Oracle Optimizer by Disabling Adaptive Features

In 2019, our Senior Oracle DBA Matt Edwards underwent a Windows to Linux migration, along with an upgrade from 10.2.0.5 to 12.2.0.1. In the retrospective blog post he detailed problems he ran into with the Oracle Optimizer, and the steps he took to diagnose and fix the performance issues. Click here to read his full blog post.

In the years since that post was published, we have discovered the various ways performance issues can be overcome by disabling certain Oracle Adaptive features. Some of these Oracle Optimizer features include:

optimizer_adaptive_plans default TRUE

• Nested loop join/Hash join selection
• The optimizer chooses between nested loops or hash joins at query runtime.
• Adaptive parallel distribution method
• The parallel distribution method is determined at runtime.
• Star transformation bitmap pruning
• Certain bitmap indexes may be removed from the SQL execution plan at runtime if selectivity is significantly poorer than the estimate.

optimizer_adaptive_statistics default FALSE

Description:

• SQL plan directives
• SQL plan directives are created and used to adapt SQL execution plans.
• Statistics feedback for joins
• Cardinality from table joins is used to improve SQL execution plans.
• Adaptive dynamic sampling for parallel execution 
• Dynamic statistics are gathered for certain parallel queries to improve cardinality estimates. The sample size is determined automatically.

From Oracle 12c onward, adaptive plans are enabled and disabled using the database parameter optimizer_adaptive_plans. In Oracle Database 12c Release 2, this parameter has been made obsolete and replaced with two new parameters that control adaptive plans and adaptive statistics separately.


Diagnosing performance issues

When diagnosing performance issues, the most difficult part may be understanding where to start. From the end-user perspective, performance issues are going to appear as slow applications or long wait times for queries. For the database manager, however, it becomes a case of troubleshooting and understanding what’s going on in your Oracle database.

Our Oracle database Expert Matt Edwards was familiar with the troubleshooting process, allowing him to discover what needed to be disabled in the Oracle Optimizer when first diagnosing the database performance issue, but sometimes the cause of the problem isn’t as obvious. In our most recent whitepaper, we outline the most common database performance issues to look out for, including:

1. Absent or misconfigured database maintenance
2. Misconfigured tempdb
3. Missing indexes
4. Misconfigured fill factor based on instance or database function
5. Missed steps during a cloud migration or upgrade

Once you’ve gone through the initial diagnosis of database problems, the Oracle Adaptive feature Automatic SQL Tuning can be used to analyze and further improve execution plans that could be causing database performance issues.

Automatic SQL Tuning

Automatic SQL Tuning was a new default tool released in Oracle 19c update 19.7. In the later Oracle release updates, the default has been disabled and users can manually choose whether to turn on Automatic SQL Tuning or not.  

Automatic SQL Tuning automates the often complex and time-consuming task of manual SQL Tuning. This update also exposed a new database infrastructure component called the Automatic SQL Tuning Set (ASTS). A SQL Tuning Set (STS) is a database object that includes one or more SQL statements along with their execution statistics and execution context, along with a possible user priority ranking. The SQL statements can be loaded into a SQL Tuning Set from different SQL sources, such as the Automatic Workload Repository, the cursor cache, or custom SQL provided by the user. An STS includes:

• A set of SQL statements
• Associated execution context, such as user schema, application module name and action, list of bind values, and the cursor compilation environment
• Associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk reads, rows processed, cursor fetches, the number of executions, the number of complete executions, optimizer cost, and the command type
• Associated execution plans and row source statistics for each SQL statement (optional)

Users sometimes shy away from automated system-maintained SQL Tuning Sets because of the massive amounts of SQL statements they capture. However, the SQL tuning set is designed to handle this amount of data and every resource it uses can be checked within the SQL plan management. Overall, it is still a useful tool to track historic SQL execution plans and performance metrics, and to repair SQL performance regressions very quickly using SQL plan management.

It’s also important to know that when loading an Oracle Adaptive plan into a SQL Plan Baseline, only the original plan is loaded as SQL Plan Baseline and only the NESTED LOOPS operations are captured in the baseline.

Automatic SQL Tuning vs Dynamic Statistics

During the compilation of a SQL statement, the optimizer decides if the available statistics are sufficient to generate a good execution plan or if it should consider using dynamic sampling. Dynamic sampling is used to compensate for missing or insufficient statistics that would otherwise lead to a very bad plan.

For the case where one or more of the tables in the query does not have statistics, dynamic sampling is used by the optimizer to gather basic statistics on these tables before optimizing the statement. The statistics gathered in this case are not as high quality (due to sampling) or as complete as the statistics gathered using the DBMS_STATS package.

Dynamic adaptive statistics allow the optimizer to augment existing statistics to get more accurate cardinality estimates for not only single table accesses but also joins and group-by predicates.

Overall, Oracle Optimizer features can be disabled, enabled, or used in a specific way to diagnose database performance issues if you know what you’re doing. Automatic SQL Tuning is one of the most efficient ways to analyze and improve execution plans that may be the root cause of database performance issues.

If you’re unsure of how to get started with Oracle Optimizer, fill out the form below to get in contact with one of our expert DBAs. For more Oracle tips and content, visit the Oracle blog section on our Resources page as well.