Use SQL tuning advisor (STA) to automatically optimize SQL statements

Oracle 10 Gb later optimizer supports two modes: normal Mode and tuning mode. In most cases, the optimizer is in normal mode. The CBO-based normal Mode only takes into account a small part of the Execution Plan set to select which execution plan, because it needs to be as short as possible, it is usually several seconds or milliseconds to parse the current SQL statement and generate an execution plan. Therefore, it is not guaranteed that SQL statements use the best execution plan each time. In tuning mode, the high-load SQL statements are directly thrown to the Optimizer to automatically analyze, debug, and provide suggestions for the Optimizer. This is the Automatic Tuning Optimizer provided by Oracle, the optimizer is automatically adjusted. The Oracle Automatic tuning optimizer is reflected by the SQL tuning advisor.

1. Basic Steps of SQL tuning
A. Identify the High-load or Top SQL to be adjusted
B. Search for improved execution plans
C. Implement execution plans that can be improved to improve SQL Efficiency

2. How to tuning SQL
A. Check whether reasonable parameters are set for the optimizer_mode, optimizer_index_caching, optimizer_index_cost_adj, and related cache size)
B. Check whether the objects involved in the SQL statement have outdated statistics or whether the histogram is missing for the skewed column.
C. Add a prompt to guide the SQL statement to use the correct access path and connection method.
D. Reconstruct equivalent SQL statements to make the SQL statement more efficient (for example, minimizing the base table and intermediate result set, avoiding Column Operations, column functions, null values, and unequal operations that invalidate the index)
E. Add reasonable indexes or materialized views, remove redundant indexes, and distribute I/O.

3. What does Automatic Tuning Optimizer do?
A. analyze statistical information
During execution plan generation, the optimizer records the types of statistical information of the objects involved in the current SQL statement and what is used or required.
After the statistical information record is complete, the optimizer automatically adjusts the optimizer to compare whether the statistical information of these objects related to the query is available or out of date or the histogram is missing for non-balanced columns.
After performing the preceding operations, you can obtain the objects without statistical information, the objects without statistical information, and the additional statistical information to generate a report.
B. Analyze the access path
The optimizer will analyze whether the access path used by the current SQL statement is reasonable, that is, the analysis of table-based access methods, such as full table scan and index scan.
The automatic adjustment optimizer creates a reasonable index based on the Hypothetical Inference of the predicate, that is, it is recommended that you add or modify the corresponding index to improve the performance.
C. SQL Structure Analysis
The optimizer will recommend structural adjustment and conversion (based on internal rules) for some SQL statements that have a great impact, such as subqueries that are not nested, materialized views are rewritten, and views are merged.
Analysis and Adjustment Based on syntax and semantic structure, such as Operation on the predicate column, use of UNION and union all, replacement between not in and not exist, etc.
Perform some estimation Analysis on intermediate result sets and connection methods.
D. SQL profiling
SQL profiling is built in the optimizer. It is an analysis tool that analyzes the current SQL based on the information obtained above to check the fault points that cause poor performance.
All the results and auxiliary information obtained from the above analysis are shown in the form of SQL profile for the user to determine whether or not to accept
When the user accepts these profiles and is in normal mode next time, the profile will be used for the same SQL statement
You can enable, disable, and modify the profile. Therefore, even if the table changes significantly, the profile can still benefit the SQL

 

4. Structure of Automatic Tuning Optimizer and SQL tuning advisor

1 2 3 Next Page