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

 

5. Sta Tuning
STA provides an OEM graphical interface and API tuning. This document describes the API dbms_sqltune.create_tuning_task method.
The following are the API methods that can be accepted by create_tuning_task:
A. directly provide SQL statement text
B. reference the SQL statement (SQL _id) in the Shared Pool)
C. reference the SQL statement (SQL _id) in the AWR automatic workload)
D. It is recommended that the SQL tuning set (batch tuning)

6. Demonstrate SQL Tuning

-- Environment Scott @ ora11g> select * from V $ version where rownum <2; banner login Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-production -- create a demo table Scott @ ora11g> Create Table T 2 nologging 3 as 4 select * 5 from dba_source, 6 (select * 7 from Dual 8 connect by rownum <5); table created. -- execute SQL statement Scott @ ora11g> select count (*) 2 from t a 3 where. rowid> (select Min (B. rowid) 4 from t B 5 where. owner = B. owner and. name = B. name and. type = B. type and. line = B. line); count (*) ---------- 18727561 row selected. -- start automatic SQL adjustment and report results -- the script tune_last_ SQL. SQL contains the creation optimization task, start execution optimization, and report optimization results. For the script content, see Scott @ ora11g> @ tune_last_sqlrecs submit General information section explain tuning Task Name: task_833tuning task owner: scottworkload type: single SQL statementscope: comprehensivetime limit (seconds ): 1800 completion status: completedstarted at: 05/22/2013 15: 06: 06 completed at: 05/22/2013 15:07:17 explain schema name: scottsql ID: 44tg722u0ypqhsql text: Select count (*) from t a where. rowid> (select Min (B. rowid) from t B where. owner = B. owner and. name = B. name and. type = B. type and. line = B. line) explain findings section (1 Finding) limit 1-Statistics finding ------------------- table "Scott ". "T" was not analyzed. recommendation ---------------consider collecting optimizer statistics for this table. execute dbms_stats.gather_table_stats (ownname => 'Scott ', tabname => 'T', estimate_percent => dbms_stats.auto_sample_size, method_opt =>' for all columns size auto '); rationale --------- the optimizer requires up-to-date statistics for the table in order to select a good execution plan. ------------------------------------------------------------------------------- explain plans section ------------------------------------ ---------------------------------------- ---1-original ----------- plan hash value: 1985065416 bytes | ID | operation | Name | rows | bytes | tempspc | cost (% CPU) | time | bytes | 0 | SELECT statement | 1 | 134 | 42648 (1) | 00:08:32 | 1 | sort aggregate | 1 | 134 | * 2 | hash join | 129k | 16 M | 195m | 42648 (1) | 00:08:32 | 3 | table access full | T | 2590k | 165m | 11596 (1) | 00:02:20 | 4 | View | vw_sq_1 | 2590k | 165m | 11674 (1) | 00:02:21 | 5 | hash group by | 2590k | 11674 MB | (1) | 00:02:21 | 6 | table access full | T | 2590k | 165m | 11596 (1) | 00:02:20 | descripredicate information (identified by Operation ID ): ------------------------------------------------- 2-access ("". "owner" = "item_1" and "". "name" = "item_2" and "". "type" = "item_3" and "". "line" = "item_4") filter ("". rowid> "min (B. rowid) ") -- the preceding report is divided into three parts, these are the basic information of SQL optimization, the recommended findings for SQL optimization, and the Execution Plan of SQL. The basic information section contains the name and status of the SQL optimization task, execution, completion time, complete SQL statement, and so on -- the finding part shows the results of this optimization, for example, this prompt shows a lack of statistical information -- the execution plan and predicate information of the current SQL statement are provided in the execution plan part --> collect statistical information Scott @ ora11g> begin 2 dbms_stats.gather_table_stats (ownname => 'Scott ', 3 tabname => 'T', 4 estimate_percent => dbms_stats.auto_sample_size, 5 method_opt => 'for all columns size auto'); 6 end; 7/PL/SQL procedure successfully completed. --> Add the order prompt to the original SQL statement and execute Scott @ ora11g> select/* + ordered */count (*) 2 from T A 3 where. rowid> (select Min (B. rowid) 4 from t B 5 where. owner = B. owner and. name = B. name and. type = B. type and. line = B. line); count (*) ---------- 18727561 row selected. -- tune the SQL statement Scott @ ora11g> @ tune_last_sqlrecs tuning General information section tuning Task Name: task_849tuning task owner: scottworkload type: single SQL statementscope: comprehensivetime limit (seconds ): 1800 completion status: completedstarted at: 05/22/2013 21: 26: 07 completed at: 05/22/2013 21:26:42 explain schema name: scottsql ID: fsp3852n56gf8sql text: Select/* + ordered */count (*) from t a where. rowid> (select Min (B. rowid) from t B where. owner = B. owner and. name = B. name and. type = B. type and. line = B. line) explain findings section (1 Finding) limit 1-SQL profile finding (see explain plans section below) limit a potentially better execution plan was found for this statement. recommendation (estimated benefit: 67.95%) -------------------------------------------consider accepting the recommended SQL profile. execute dbms_sqltune.accept_ SQL _profile (task_name => 'Task _ 000000', task_owner => 'Scott ', replace => true ); ------------------------------------------------------------------------------- explain plans section ------------------------------------ ---------------------------------------- ---1-original with adjusted cost -------------------------------- plan hash value: 2929971977 bytes | ID | operation | Name | rows | bytes | tempspc | cost (% CPU) | time | bytes | 0 | SELECT statement | 1 | 218 K (1) | 00:43:47 | 1 | sort aggregate | 1 | 2 | View | vm_nwvw_2 | 551k | 218 K (1) | 00:43:47 | * 3 | filter | 4 | hash group by | 551k | 51m | 1197m | 218 K (1) | 00:43:47 | * 5 | hash join | 11m | 1031m | 145m | 37646 (1) | 00:07:32 | 6 | table access full | T | 2497k | 1200m | 11596 (1) | 00:02:20 | 7 | table access full | T | 2497k | 1200m | 11596 (1) | 00:02:20 | descripredicate information (identified by Operation ID ): ------------------------------------------------- 3-filter ("". rowid> min ("B ". rowid) 5-access ("". "owner" = "B ". "owner" and "". "name" = "B ". "name" and "". "type" = "B ". "type" and "". "line" = "B ". "line") 2-using SQL profile ------------------ plan hash value: 1985065416 bytes | ID | operation | Name | rows | bytes | tempspc | cost (% CPU) | time | percent | 0 | SELECT statement | 1 | 116 | 70117 (1) | 00:14:02 | 1 | sort aggregate | 1 | 116 | * 2 | hash join | 2025k | 224m | 145m | 70117 (1) | 00:14:02 | 3 | table access full | T | 2497k | 1200m | 11596 (1) | 00:02:20 | 4 | View | vw_sq_1 | 2497k | 159m | 41851 (1) | 00:08:23 | 5 | hash group by | 2497k | 1200m | 153m | 41851 (1) | 00:08:23 | 6 | table access full | T | 2497k | 1200m | 11596 (1) | 00:02:20 | descripredicate information (identified by Operation ID ): ------------------------------------------------- 2-access ("". "owner" = "item_1" and "". "name" = "item_2" and "". "type" = "item_3" and "". "line" = "item_4") filter ("". rowid> "min (B. rowid) ") Explain for the preceding SQL statement, the SQL optimizer finds a more efficient execution plan and prompts us to accept the execution plan, as follows -- a potentially better execution plan was found for this statement. -- Recommendation (estimated benefit: 67.95%) -- consider accepting the recommended SQL profile -- Author: Robinson -- Blog: extends profilescott @ ora11g> exec partition (task_name => 'Task _ 123456 ', task_owner => 'Scott ', replace => true); PL/SQL procedure successfully completed. -- After accepting the SQL profile, run the SQL statement Scott @ ora11g> set autot trace exp with the order prompt again; scott @ ora11g> select/* + ordered */count (*) 2 from t a 3 where. rowid> (select Min (B. rowid) from t B 4 where. owner = B. owner and. name = B. name and. type = B. type 5 and. line = B. line); Execution Plan -------------------------------------------------------- plan hash value: 1985065416 bytes | ID | operation | Name | rows | bytes | tempspc | cost (% CPU) | time | percent | 0 | SELECT statement | 1 | 116 | 70117 (1) | 00:14:02 | 1 | sort aggregate | 1 | 116 | * 2 | hash join | 2025k | 224m | 145m | 70117 (1) | 00:14:02 | 3 | table access full | T | 2497k | 1200m | 11596 (1) | 00:02:20 | 4 | View | vw_sq_1 | 2497k | 159m | 41851 (1) | 00:08:23 | 5 | hash group by | 2497k | 1200m | 153m | 41851 (1) | 00:08:23 | 6 | table access full | T | 2497k | 1200m | 11596 (1) | 00:02:20 | descripredicate information (identified by Operation ID ): ------------------------------------------------- 2-access ("". "owner" = "item_1" and "". "name" = "item_2" and "". "type" = "item_3" and "". "line" = "item_4") filter ("". rowid> "min (B. rowid) ") Note ------SQL profile" sys_sqlprof_013ecc70b5f70000 "used for this statementscott @ ora11g> set autot off; -- In the autotrace above, the last part indicates that the current SQL statement uses the stored SQL profile execution plan.

7. Related views
Dba_advisor_log
Dba_advisor_tasks
Dba_advisor_findings
Dba_advisor_recommendations
Dba_advisor_rationale
Dba_sqltune_statistics
Dba_sqltune_binds
Dba_sqltune_plans

8. Demonstrate the scripts used

SET ECHO OFF TERMOUT ON FEEDBACK OFF VERIFY OFF  SET SCAN ONSET LONG 1000000 LINESIZE 180COL recs FORMAT a135VARIABLE tuning_task VARCHAR2(30)DECLARE  l_sql_id v$session.prev_sql_id%TYPE;BEGIN  SELECT prev_sql_id INTO l_sql_id  FROM v$session  WHERE audsid = userenv('SESSIONID');    :tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);  dbms_sqltune.execute_tuning_task(:tuning_task);END;/SELECT dbms_sqltune.report_tuning_task(:tuning_task) as recs FROM dual;SET VERIFY ON FEEDBACK ON 

 

More references

DML error logging

PL/SQL --> cursor

PL/SQL --> implicit cursor (SQL % found)

Batch SQL forall statements

Bulk collect clause for batch SQL

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan