Sql最佳化是有一定難度的工作，需要dba具有紮實的知識和較豐富的經驗。在oracle10g以後，引入了一些自動調優技術，包括sql tuning advisor、sql access advisor、ADDM等，不需要了解JOIN的型別、次序，也不需瞭解最佳化器，新手也能用這些工具進行一些最佳化工作。隨著自動管理、自動監控技術的發展，今後dba這一職業是否會消失呢？[@more@]

本篇介紹如何用sql tuning advisor來建立sql profile對sql進行調優。

(一) Sql tuning advisor

Sql tuning advisor可以在enterprise manager圖形介面中呼叫，也可透過過程包呼叫。使用方法見後面的例子。

以下內容可以作為sql tuning advisor的輸入，調優其中的問題sql：

l Addm （每小時自動執行）

l Awr

l Shared sql area

l Sql tuning set

呼叫sql tuning advisor時，最佳化器進入調優模式。

Oracle最佳化器有兩種模式：

l Normal mode

快速產生對大多數sql來說合理的計劃。要求生成計劃的時間非常短

l Tuning mode

進行額外分析，如sql結構分析、sql profile、訪問路徑分析、替代寫法等等，給出最佳化建議。通常調優一句sql需要幾分鐘，下面減少其中的sql profile。

(二) Sql profile是什麼

Sql profile不是執行計劃，而是提供以下資訊幫助最佳化器選擇plan

l 環境：db配置，繫結變數值，optimizer statistics,data set

l Supplemental statistics

因此可以說sql profile之於sql，相當於optimizer statistics之於table/index。

Sql profile的優點是：

l 與hints不同，使用sql profile不需改源程式。這點和outline一樣

l 與hint和stored outline不同，profile通常不會固定執行計劃，而是糾正最佳化器不正確的估計，因此在不同環境下更有彈性

Sql profile用來最佳化sql。當然使用了sql profile之後，也會達到穩定執行計劃的目的。

(三) 應用舉例

1. 準備

create table t1 as select * from dba_objects;

create table t2 as select * from dba_tables;

create index t1_idx on t1(object_name);

exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'LUW',tabname =>'t1',estimate_percent => 5,degree => 4,cascade => TRUE);

exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'LUW',tabname =>'t2',estimate_percent => 5,degree => 4,cascade => TRUE);

set autotrace traceonly

select t1.*,t2.owner from t1,t2 where t2.table_name like '%TO%' and t1.object_name=t2.table_name;

Plan hash value: 282751716

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

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

| 0 | SELECT STATEMENT | | 24 | 3000 | 350 (1)| 00:00:05 |

|* 1 | HASH JOIN | | 24 | 3000 | 350 (1)| 00:00:05 |

|* 2 | TABLE ACCESS FULL| T2 | 192 | 5184 | 42 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL| T1 | 3734 | 357K| 307 (1)| 00:00:04 |

顯示兩張table全表掃描

2. 最佳化目標

目標是透過最佳化，使sql使用索引t1_idx，關聯模式由hash join改為nest loop。

我們以前介紹過如何透過hint來修改執行計劃。這裡我們看看如何透過sql profile來達到同一目的。

3. 呼叫sql tuning advisor

在v$sql中找到上面那句sql的sql_id為8ug5kpjdjngp3

var tuning_task varchar2(100);

DECLARE

l_sql_id v$session.prev_sql_id%TYPE;

l_tuning_task VARCHAR2(30);

BEGIN

l_sql_id:='8ug5kpjdjngp3';

l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);

:tuning_task:=l_tuning_task;

dbms_sqltune.execute_tuning_task(l_tuning_task);

dbms_output.put_line(l_tuning_task);

END;

/

TASK_8723

4. 輸出sql tuning advisor報告

set pagesize 0 long 30000000 longchunksize 2000

SELECT dbms_sqltune.report_tuning_task(:tuning_task) FROM dual;

GENERAL INFORMATION SECTION

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

Tuning Task Name : TASK_8723

Tuning Task Owner : LUW

Workload Type : Single SQL Statement

Scope : COMPREHENSIVE

Time Limit(seconds): 1800

Completion Status : COMPLETED

Started at : 04/01/2013 16:47:31

Completed at : 04/01/2013 16:47:32

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

Schema Name: LUW

SQL ID : 8ug5kpjdjngp3

SQL Text : select t1.*,t2.owner from t1,t2 where t2.table_name like '%TO%'

and t1.object_name=t2.table_name

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

FINDINGS SECTION (2 findings)

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

1- SQL Profile Finding (see explain plans section below)

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

A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 62.97%)

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

- Consider accepting the recommended SQL profile.

execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_8723',

task_owner => 'LUW', replace => TRUE);

Validation results

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

The SQL profile was tested by executing both its plan and the original plan

and measuring their respective execution statistics. A plan may have been

only partially executed if the other could be run to completion in less time.

Original Plan With SQL Profile % Improved

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

Completion Status: COMPLETE COMPLETE

Elapsed Time (s): .067587 .004296 93.64 %

CPU Time (s): .068 .004 94.11 %

User I/O Time (s): 0 0

Buffer Gets: 1234 459 62.8 %

Physical Read Requests: 0 0

Physical Write Requests: 0 0

Physical Read Bytes: 0 0

Physical Write Bytes: 0 0

Rows Processed: 171 171

Fetches: 171 171

Executions: 1 1

Notes

-----

1. Statistics for the original plan were averaged over 10 executions.

2. Statistics for the SQL profile plan were averaged over 10 executions.

2- Restructure SQL finding (see plan 1 in explain plans section)

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

The predicate "T1"."OBJECT_NAME" LIKE '%TO%' used at line ID 3 of the

execution plan contains an expression on indexed column "OBJECT_NAME". This

expression prevents the optimizer from selecting indices on table "LUW"."T1".

Recommendation

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

- Rewrite the predicate into an equivalent form to take advantage of

indices. Alternatively, create a function-based index on the expression.

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

EXPLAIN PLANS SECTION

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

1- Original With Adjusted Cost

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

Plan hash value: 282751716

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

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

| 0 | SELECT STATEMENT | | 179 | 22375 | 350 (1)| 00:00:05 |

|* 1 | HASH JOIN | | 179 | 22375 | 350 (1)| 00:00:05 |

|* 2 | TABLE ACCESS FULL| T2 | 192 | 5184 | 42 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL| T1 | 1016 | 99568 | 307 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - access("T1"."OBJECT_NAME"="T2"."TABLE_NAME")

2 - filter("T2"."TABLE_NAME" LIKE '%TO%')

3 - filter("T1"."OBJECT_NAME" LIKE '%TO%' AND "T1"."OBJECT_NAME" IS

NOT NULL)

2- Using SQL Profile

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

Plan hash value: 2963590314

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

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

| 0 | SELECT STATEMENT | | 13 | 1716 | 26 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 13 | 1274 | 3 |

| 2 | NESTED LOOPS | | 13 | 1716 | 26 |

|* 3 | TABLE ACCESS FULL | T2 | 1 | 34 | 23 |

|* 4 | INDEX RANGE SCAN | T1_IDX | 1 | | 2 |

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

上述報告亮色字型部分，顯示tuning advisor找到了sql profile，以及採用該profile的command。並顯示當前的執行計劃和cost（為350）；採用profile後的執行計劃和cost（為26），透過cost對比說明採用profile效能是能提升的。

5. 根據advisor

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25462274/viewspace-2124925/，如需轉載，請註明出處，否則將追究法律責任。

Sql最佳化(二十二) 自動調優工具：sql tuning advisor和sql profile介紹

相關文章