參考SQL Tunning Adviser進行SQL優化

無論前面介紹的通過“手工”分析手段或通過“工具”分析手段，很多時候我們很有可能面臨TOP SQL語句的優化，即SQL優化。很多情況下可以說資料庫的優化就是SQL的優化，而SQL的優化呢？那就是的索引的優化！

Oracle 10g開始有個SQL優化功能，即SQL優化顧問（SQL Tuning Adviser）,這個工具可以作為SQL優化過程的參考依據，但並並不能全靠它。因為很多時候SQL的優化還涉及到業務特性，關鍵業務高峰期等多種因素。另外在使用過程中也發現，SQL Tuning Adviser很多時候對select類語句用處較大…

當我們通過前面介紹動態效能檢視的手工查詢方式、ASH或後面繼續介紹的Statspack、AWR、ADDM工具，會得到一堆TOP SQL語句，這些語句SQL_ID非常關鍵。因為我們可以根據這些SQL_ID很容易的得到SQL Tuning Adviser的建議。

另外，官方文件也介紹瞭如下查詢（當前Cache中）方法，buffer_gets值得大小根據不同系統會有不同的值：

SELECT sql_id, sql_text

FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <> ''SYS'' AND buffer_gets > 500000'))

ORDER BY sql_id;

得到了TOP SQL的SQL_ID後，按如下步驟就可以得到SQL Tuning Adviser的建議，具體步驟如下：

[@more@]

1、根據SQL_ID通過DBMS_SQLTUNE.CREATE_TUNING_TASK包建立Task

DECLARE

my_task_name1 VARCHAR2(30);

BEGIN

my_task_name1 := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_id => '6cpjmb62rhhp3',

task_name => 'my_sql_tuning_task1',

description => 'Task to tune a top sql');

END;

2、通過如下查詢查詢owner和task_id

SELECT * FROM DBA_ADVISOR_LOG WHERE task_name='my_sql_tuning_task1' --owner = 'sa'; --129863

3、根據Task名執行優化顧問

BEGIN

DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task1' );

END;

4、可以通過如下查詢得知優化執行進度

SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE TASK_ID = 129863 --and USERNAME = 'sa' ;

5、優化顧問執行完畢後，通過如下查詢即可得到最終想要的優化建議

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task1')FROM DUAL;

6、建議結果很多時候是建立索引（或組合索引），有的時候也要建議採用SQL_PROFILE，Profile的具體應用方式建議結果中有，類此如下：

----執行如下建議：

BEGIN

dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task1', replace => TRUE);

END;

7、參考完畢TASK建議後，最後步驟就是刪除已建立的TASK，因為TASK名是不能重複的。

BEGIN

DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'my_sql_tuning_task1');

END;

通過如上步驟我們會得到SQL Tunning Adviser的建議，通過如下SQL可以檢視繫結變數值：

select dbms_sqltune.extract_binds(bind_data) from sys.wrh$_sqlstat a where a.sql_id='6cpjmb62rhhp3'

或者

select dbms_sqltune.extract_bind(bind_data, 1).value_string || '-' || dbms_sqltune.extract_bind(bind_data, 2)

.value_string || '-' || dbms_sqltune.extract_bind(bind_data, 3)

.value_string || '-' || dbms_sqltune.extract_bind(bind_data, 4)

.value_string || '-' || dbms_sqltune.extract_bind(bind_data, 5)

.value_string || '-' || dbms_sqltune.extract_bind(bind_data, 6)

.value_string || '-' || dbms_sqltune.extract_bind(bind_data, 7)

.value_string

from sys.wrh$_sqlstat

where sql_id = '6cpjmb62rhhp3'

有時候我們需要收集統計資訊，未及時更新的統計資訊也會影響SQL語句執行計劃：

begin

dbms_stats.gather_table_stats(ownname => 'scott',

tabname => 'test',

estimate_percent => 20,

method_opt => 'for all indexed columns',

cascade => true);

end;

或者

analyze table scott.test compute statistics;

無論是否採用SQL Tunning Adviser,當我們知道SQL_ID後可以通過AWR的awrsqrpt指令碼檢視真正的執行計劃

SQL> @?/rdbms/admin/awrsqrpt

或者如下語句也是可以

select * from table(dbms_xplan.display_awr('fjhvhc6fytp1c'));

如下方法也能得到SQL過去執行的執行計劃：

select id,operation, options,object_owner,object_name,object_type,cost,cardinality,bytes,cpu_cost,io_cost

from DBA_HIST_SQL_PLAN where sql_id='djpvmvjddy8av'

order by id;

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