1、前言：一直以來SQL調優都是DBA比較費力的技術活，而且很多DBA如果沒有從事過開發的工作，那麼調優更是一項頭疼的工作，即使是SQL調優很厲害的高手，在SQL調優的過程中也要不停的分析執行計劃、加HINT、分析統計資訊等等。從ORACLE 10G開始，資料庫採取了很多智慧化的管理工作，其中SQL最佳化器(SQL Tuning Advisor：STA)，大大的提高了DBA進行SQL最佳化的效率；

2、原理介紹：

When SQL statements are executed by the Oracle database, the query optimizer is used to generate the execution plans of the SQL statements. The query optimizer operates in two 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 not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly superior plan. When running in the tuning mode, the optimizer is 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 has to be hard-parsed. The Automatic Tuning Optimizer is meant to be used for complex and high-load SQL statements that have non-trivial impact on 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 implements tuning recommendations during system maintenance windows as an automated maintenance task.

查詢最佳化器有兩種模式：普通模式與調化模式。

最佳化器在普通模式下，當SQL被執行時，查詢最佳化器將生成SQL的執行計劃，如果SQL的可選路徑很多，最佳化器必須是限制時間內，選擇一個合適的執行計劃，查詢最佳化器預設執行普通模式；

當最佳化器在調優模式下，最佳化器需要執行額外的分析去判斷最佳化器在普通模式下產生的執行計劃是否有可能被改進，此時最佳化器輸出的不是一個執行計劃，而是一系列的動作，根據調優的原理、產生一個更最佳化的執行計劃，最佳化器需發花費一定的時間去調優單個的sql，自動調優的最佳化器每次查詢都是硬解析。

SQL Tuning Advisor獲取AWR報告中High-Load SQL，查詢最佳化器在調優模式下生成一個最好的執行計劃，從而實現SQL調優的目的；

3、功能介紹：

The Automatic Tuning Optimizer performs four types of tuning analysis:

Statistics Analysis
SQL Profiling
Access Path Analysis
SQL Structure Analysis

4、SQL Tuning Advisor的管理：

任務的檢視：select * from DBA_AUTOTASK_WINDOW_CLIENTS;

任務的啟動：

BEGINDBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);END;/

任務的停止：BEGINDBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);END;/

SQL Tuning Advisor結果的檢視：

select dbms_sqltune.report_tuning_task('SYS_AUTO_SQL_TUNING_TASK') from dual;

5、手工執行SQL Tuning Advisor進行調優

說明：建立兩張大表和小表，並且都沒建立索引，然後進行關聯。透過執行SQL Tuning Advisor得到最佳化建議

步驟一：建立表

指令碼：create table hr.big as select rownum as "id",a.* from sys.all_objects a;

指令碼：create table hr.small as select rownum as "id", a.* FROM sys.all_tables a;

然後多執行幾次下面的指令碼，增加表裡的資料：

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

步驟二：直接進行關聯測試

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

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 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

----------------------------------------------------------

10 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

步驟三：建立最佳化任務

透過呼叫函式CREATE_TUNING_TASK來建立最佳化任務，呼叫儲存過程EXECUTE_TUNING_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;

/

函式CREATE_TUNING_TASK說明：

sql_text是需要最佳化的語句
user_name是該語句透過哪個使用者執行，注意是大寫，不然會報錯，使用者無效
scope是最佳化範圍（limited或comprehensive），
time_limit最佳化過程的時間限制，
task_name最佳化任務名稱，
description最佳化任務描述。

步驟四：執行最佳化任務

透過呼叫dbms_sqltune.execute_tuning_task過程來執行前面建立好的最佳化任務。

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

步驟五：檢查最佳化任務的狀態

透過檢視user_advisor_tasks/dba_advisor_tasks檢視可以檢視最佳化任務的當前狀態。

SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name='tuning_sql_test';

TASK_NAME STATUS

---------------- -------------- -----------

tuning_sql_test COMPLETED

步驟六：檢視最佳化結果

透過dbms_sqltune.report_tning_task函式可以獲得最佳化任務的結果。

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from DUAL;

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

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

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 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")

-------------------------------------------------------------------------------

解讀報告：

紅色部分：關於這次最佳化任務的基本資訊：如任務名稱、執行時間、範圍、涉及到的語句等等。

綠色部分：關於這次最佳化任務的所找到的問題以及給出的最佳化建議。

執行SMALL表的統計資訊的收集；
執行BIG表統計資訊的收集；
進行索引的新增；

藍色部分：最佳化前和最佳化後的執行計劃的對比，可以看出COST值大大下降。

五、刪除最佳化任務

透過呼叫dbms_sqltuen.drop_tuning_task可以刪除已經存在的最佳化任務

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

PL/SQL procedure successfully completed.

總結：SQL Tuning Advisor為DBA的調優工作減輕了不少負擔，一般情況下我也是透過這種方法進行調優的，這裡有點小小的建議：

工具畢竟是固定的，一般只會針對單個語句給出建議，不會在整個資料庫的整體效能上面去思考該調優方法是否可行，這點必須由DBA把握；
DBA還是有必要了解為什麼要這麼調優，才能更好的根據實際情況給出具體的調優辦法，不然調優這種高大上的工作將變得廉價。

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

本文作者：JOHN

ORACLE技術部落格：ORACLE 獵人筆記資料庫技術群：367875324 （請備註ORACLE管理）

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

深入瞭解SQL Tuning Advisor

相關文章