Learn more about SQL Tuning Advisor (reprint)

1, Preface: SQL Tuning has been the DBA of the more laborious technology, and many DBAs if not engaged in the development of the work, then tuning is a headache work, even if the SQL tuning is a strong master, in the process of SQL tuning to constantly analyze the execution plan, Add hint, analyze statistical information and so on. Starting with Oracle 10G, the database has taken a lot of intelligent management, with the SQL optimizer (SQL Tuning Advisor:sta), which greatly improves the efficiency of DBA SQL optimization;

2, Principle Introduction:

When SQL statements was executed by the Oracle database, the query optimizer was used to generate the execution plans of th e SQL statements. The query optimizer operates in the Modes:a Normal mode and a tuning mode.

In normal mode, the optimizer compiles the SQL and generates an execution plan. The normal mode of the optimizer generates a reasonable execution plan for the vast majority of SQL statements. Under normal mode, the optimizer operates with very strict time constraints, usually a fraction of a second, during which It must find a good execution plan.

In tuning mode, the optimizer performs additional analysis to check whether the execution plan produced under normal mode can be improved further. The output of the query optimizer is a execution plan, but a series of actions, along with their rationale and expect Ed benefit for producing a significantly superior plan. When running in the tuning mode, the optimizer are referred to as the Automatic tuning Optimizer.

Under tuning mode, the optimizer can take several minutes to tune a single statement. It is both time and resource intensive to invoke the Automatic Tuning Optimizer every time a query have to be hard-parsed. The Automatic Tuning Optimizer is meant to being used for complex and high-load SQL statements that has non-trivial impact O n the entire system. The Automatic Database Diagnostic Monitor (ADDM) proactively identifies high-load SQL statements which are good candidates For SQL tuning. See Chapter 6, "Automatic Performance Diagnostics". The automatic SQL Tuning feature of Oracle Database also automatically identifies problematic SQL statements and implement s tuning recommendations during system maintenance windows as an automated maintenance task.

There are two modes of the query optimizer: Normal mode and modulation mode.

Optimizer in normal mode, when SQL is executed, the query optimizer generates an execution plan for SQL, and if there are many optional paths to SQL, the optimizer must be within the time limit, select an appropriate execution plan, and the query optimizer performs normal mode by default;

When the optimizer is in tune mode, the optimizer needs to perform additional analysis to determine whether the optimizer's execution plan in normal mode is likely to be improved, when the optimizer is not outputting an execution plan, but a series of actions, based on the principle of tuning, to produce a more optimized execution plan, The optimizer needs to spend a certain amount of time tuning a single SQL, and automatically tuning the optimizer every time the query is hard-resolved.

SQL Tuning Advisor gets the AWR report in High-load SQL, and the query optimizer generates a best execution plan in tuning mode for SQL tuning purposes;

3, Function Introduction:

The Automatic Tuning Optimizer performs four types of Tuning analysis:

• Statistics Analysis
• SQL Profiling
• Access Path Analysis
• SQL Structure Analysis

4. Management of SQL Tuning Advisor:

View of the task: select * from Dba_autotask_window_clients;

Start of Task:

Begindbms_auto_task_admin. ENABLE (client_name = ' SQL Tuning Advisor ', operation = Null,window_name = NULL); end;/

Stop the task: Begindbms_auto_task_admin. DISABLE (client_name = ' SQL Tuning Advisor ', operation = Null,window_name = NULL); end;/

View of SQL Tuning Advisor results:

Select dbms_sqltune.report_tuning_task(' Sys_auto_sql_tuning_task ') from dual;

5. Manually run SQL Tuning Advisor for tuning

Description: Creates two large tables and small tables, and neither creates an index and then associates it. optimize recommendations by executing SQL Tuning advisor

Step one: Create a table

Script: Create table Hr.big as select RowNum as "id", a.* from Sys.all_objects A;

Script: Create table Hr.small as select RowNum as "id", a.* from Sys.all_tables A;

Then run the following script several times to increase the data in the table:

INSERT INTO Hr.big select * from Hr.big;

INSERT INTO Hr.small select * from Hr.small;

Sql> Select COUNT (*) from Hr.small; COUNT (*) ---------- 2835 Sql> Select COUNT (*) from Hr.big; COUNT (*) ---------- 727438

Step two: Direct correlation test

Sql> Set Timing on Sql> set Autot on Sql> Select COUNT (*) from Hr.big A, hr.small b where a.object_name=b.table_name; COUNT (*) ---------- 31500 elapsed:00:00:00.50 Execution Plan ---------------------------------------------------------- Plan Hash value:1355302734 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | ----------------------------------------------------------------------------- | 0 |     SELECT STATEMENT |     |     1 | 34 | 3064 (1) | 00:00:37 | | 1 |     SORT AGGREGATE |     |     1 |         34 |     | | |* 2 |     HASH JOIN |    |     453k| 14m| 3064 (1) | 00:00:37 | | 3 | TABLE ACCESS full| SMALL | 2915 |     49555 | 31 (0) | 00:00:01 | | 4 | TABLE ACCESS full|    BIG |     610k| 9m| 3029 (1) | 00:00:37 | ----------------------------------------------------------------------------- predicate information (identified by Operation ID): --------------------------------------------------- 2-access ("A". ") object_name "=" B "." TABLE_NAME ") Note ----- -Dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- Ten recursive calls 2 db block gets 11362 consistent gets 6332 Physical Reads 0 Redo Size 527 Bytes sent via sql*net to client 523 Bytes received via sql*net from client 2 sql*net roundtrips To/from Client 2 Sorts (memory) 0 Sorts (disk) 1 rows processed

Step three: Create an optimization task

By calling the function Create_tuning_task to create the optimization task, call the stored procedure Execute_tuning_task perform the task:

Sql> set Autot off Sql> Set Timing off Sql> DECLARE My_task_name VARCHAR2 (30); My_sqltext CLOB; BEGIN My_sqltext: = ' SELECT count (*) from Hr.big A, Hr.small b where a.object_name=b.table_name '; My_task_name: = Dbms_sqltune. Create_tuning_task ( Sql_text = My_sqltext, user_name = ' HR ', Scope = ' comprehensive ', Time_limit = 60, Task_name = ' Tuning_sql_test ', Description = ' Task to tune a query on a specified table '); Dbms_sqltune. Execute_tuning_task (task_name = ' tuning_sql_test '); END; /

function Create_tuning_task Description:

• Sql_text is a statement that needs to be optimized
• User_name is the statement through which user execution, note is uppercase, otherwise will error, user invalid
• Scope is the optimization range (limited or comprehensive),
• Time_limit the time limit of the optimization process,
• Task_name Optimization Task Name,
• Description Optimization Task description.

Step four: Perform the optimization task

Perform the previously created optimization task by calling the Dbms_sqltune.execute_tuning_task procedure.

sql> exec dbms_sqltune.execute_tuning_task (' tuning_sql_test ');

Step Five: Check the status of the optimization task

You can view the current status of the optimization task by viewing the User_advisor_tasks/dba_advisor_tasks view.

Sql> SELECT task_name,status from user_advisor_tasks WHERE task_name= ' tuning_sql_test '; Task_name STATUS ---------------- -------------- ----------- Tuning_sql_test completed

Step Six: View optimization results

The results of the optimization task can be obtained through the Dbms_sqltune.report_tning_task function.

Sql> SELECT Dbms_sqltune. Report_tuning_task (' Tuning_sql_test ') from DUAL;

General Information Sections ------------------------------------------------------------------------------- Tuning Task Name:tuning_sql_test Tuning Task Owner:sys Workload Type:single SQL Statement Execution Count:2 Current Execution:exec_12 Execution Type:tune SQL Scope:comprehensive Time Limit (seconds): 60 Completion status:completed Started at:12/30/2013 15:56:04 Completed at:12/30/2013 15:56:06 ------------------------------------------------------------------------------- Schema NAME:HR SQL id:6fnz11x82cuv4 SQL text:select Count (*) from Hr.big A, Hr.small b where A.object_name=b.table_name ------------------------------------------------------------------------------- Findings section (3 findings) ------------------------------------------------------------------------------- 1-statistics finding --------------------- ????? "HR". " SMALL "? Recommendation -------------- - ???????????????? Execute dbms_stats.gather_table_stats (ownname = ' HR ', tabname = ' SMALL ', estimate_percent = dbms_stats. Auto_sample_size, Method_opt = ' For all COLUMNS SIZE AUTO '); Rationale --------- ??????????, ???????????????? 2-statistics finding --------------------- ????? "HR". " BIG "? Recommendation -------------- - ???????????????? Execute dbms_stats.gather_table_stats (ownname = ' HR ', tabname = ' BIG ', Estimate_percent = Dbms_stats. Auto_sample_size, method_opt = ' For all COLUMNS SIZE AUTO '); Rationale --------- ??????????, ???????????????? 3-index finding (see Explain plans section below) -------------------------------------------------- ???????????????????????? Recommendation (estimated benefit:67.55%) ------------------------------------------ - ????????????????????????????? CREATE INDEX HR. idx$$_00150001 on HR. SMALL ("table_name"); - ????????????????????????????? CREATE INDEX HR. idx$$_00150002 on HR. BIG ("object_name"); Rationale --------- ?????????????????????????, ????? Sql????? "????" ?????????????????????????????, ???????????????????? ------------------------------------------------------------------------------- EXPLAIN Plans Section ------------------------------------------------------------------------------- 1-original ----------- Plan Hash value:1355302734 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 34 | 3064 (1) | 00:00:37 | | 1 | SORT AGGREGATE | | 1 | 34 | | | |* 2 | HASH JOIN | | 453k| 14m| 3064 (1) | 00:00:37 | | 3 | TABLE ACCESS full| SMALL | 2915 | 49555 | 31 (0) | 00:00:01 | | 4 | TABLE ACCESS full| BIG | 610k| 9m| 3029 (1) | 00:00:37 | ----------------------------------------------------------------------------- predicate information (identified by Operation ID): --------------------------------------------------- 2-access ("A". ") object_name "=" B "." TABLE_NAME ") 2-using New Indices -------------------- Plan Hash value:3519661237 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 34 | 994 (2) | 00:00:12 | | 1 | SORT AGGREGATE | | 1 | 34 | | | |* 2 | HASH JOIN | | 453k| 14m| 994 (2) | 00:00:12 | | 3 | INDEX FAST Full scan| idx$$_00150001 | 2915 | 49555 | 12 (0) | 00:00:01 | | 4 | INDEX FAST Full scan| idx$$_00150002 | 610k| 9m| 978 (1) | 00:00:12 | ----------------------------------------------------------------------------------------- predicate information (identified by Operation ID): --------------------------------------------------- 2-access ("A". ") object_name "=" B "." TABLE_NAME ") -------------------------------------------------------------------------------

Interpreting the report:

Red section: Basic information about this optimization task: such as task name, execution time, scope, statement involved, and so on.

Green section: The problems found in this optimization task and the recommendations for optimization.

• The collection of statistics that run the small table;
• Run the collection of big table statistics;
• To add the index;

Blue: Comparison of pre-optimization and optimized execution plans shows a significant decrease in cost.

V. Delete optimization tasks

You can delete an existing optimization task by calling Dbms_sqltuen.drop_tuning_task

Sql>exec dbms_sqltune.drop_tuning_task (' tuning_sql_test ');

PL/SQL procedure successfully completed.

Summary: SQL Tuning Advisor relieves a lot of the burden of DBA tuning, and in general I'm tuning it in this way, and here's a little bit of advice:

1. After all, the tool is fixed, generally only for a single statement to give advice, not in the overall performance of the whole database to consider whether the tuning method is feasible, this must be the DBA grasp;
2. It is also necessary for DBAs to understand why it is so tuned that they are better able to give specific tuning options based on the actual situation, or else the high-quality work will become cheap.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

This article John

Oracle Technology Blog: Oracle Hunter Note Database Technology Group: 367875324 (note Oracle management)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Learn more about SQL Tuning Advisor (reprint)