使用shell來定製dbms_sqltune

在sql調優中使用dbms_sqltune是一個很高效的工具，如果說awr發現了效能問題sql,addm可以給出調優建議，sql monitor能夠監控效能問題sql和執行計劃，那麼dbms_sqltune就是最後的最佳化顧問了，它給出的建議是經過深思熟慮的。而且相對來說更加權威。尤其是對於老DBA而言，在這個地方其實就有些吃虧，因為這個功能著實太強大了，一般來說給出的建議都是非常中肯的，一般對於sql語句，大體有下面幾種形式的建議方式。
首先是發現統計資訊問題，建議收集統計資訊，
發現執行計劃問題，會綜合分析，建議一個新的執行計劃，而且還會執行幫你準備好這部分指令碼，我們只需要接受就可以。
發現語句中可能存在不合理的全表掃描或者索引掃描，會建議新增或者重建某一個索引，會給出完整的建立語句。
根據資源使用情況，建議透過並行等方式來充分利用資源，提高執行的效率。
而且更好的是，它不只是說說，它會給出一攬子的解決方案，比如某個建議，如果採用，效能會提高多少，都做了細緻的量化，這對於我們來說是很有說服力的。
一個專業的調優報告就是如此，畢竟從業務場景和具體的應用中，並不是所有的方案都是最好的，但是它已經做了相對比較全面的分析，這些著實難能可貴。
但是從我瞭解到的情況來看，大多數DBA想去調優一個sql,很少會用到這個包，主要原因就在於使用感覺不是很方便，因為整個過程就像是自己開發了一段pl/sql程式碼一樣，而且複用性較差。
當然我也有這個困惑和感受，不過我覺得這個工具這麼好，能夠方便的利用起來，就是一個利器。
所以我簡單寫了下面的指令碼來進行了簡單定製。
echo "set serveroutput on" > gentunerpt_$1.sql
echo "  var tuning_task varchar2(100);  ">>gentunerpt_$1.sql
echo " DECLARE " >> gentunerpt_$1.sql
echo "    l_sql_id varchar2(200); " >> gentunerpt_$1.sql
echo "    l_tuning_task VARCHAR2(30); " >> gentunerpt_$1.sql
echo " BEGIN " >> gentunerpt_$1.sql
echo "    l_sql_id:='$1';  " >> gentunerpt_$1.sql
echo "    l_tuning_task:=dbms_sqltune.create_tuning_task(sql_id => l_sql_id,scope       => 'COMPREHENSIVE',time_limit  => 120);  " >> gentunerpt_$1.sql
echo "    :tuning_task:=l_tuning_task;  ">>gentunerpt_$1.sql
echo "    dbms_sqltune.execute_tuning_task(l_tuning_task); " >> gentunerpt_$1.sql
echo "    dbms_output.put_line(l_tuning_task); " >> gentunerpt_$1.sql
echo " END; " >> gentunerpt_$1.sql
echo " /  " >> gentunerpt_$1.sql
 
echo "print tuning_task;  ">>gentunerpt_$1.sql

echo "set long 99999" >> gentunerpt_$1.sql
echo "SELECT dbms_sqltune.report_tuning_task(:tuning_task) comm FROM dual;" >> gentunerpt_$1.sql

sqlplus -s  $DB_CONN_STR@$SH_DB_SID <<ENS
set pages 0
set linesize 200
col comm format a200
spool gentunerpt_$1.lst
@gentunerpt_$1.sql
spool off
ENS
這個指令碼的輸入比較簡單，只需要輸入sql_id即可，就會建立對應的task,然後開始呼叫sql_tune包進行分析，最後輸出結果。
比如sql_id為9htcqxkzu0tyg
則執行sh gentunerpt.sh 9htcqxkzu0tyg會生成兩個檔案gentunerpt_9htcqxkzu0tyg.lst和 gentunerpt_9htcqxkzu0tyg.sql
-rw-r--r-- 1 oracle oinstall  1120 Aug 31 14:41 gentunerpt.sh
-rw-r--r-- 1 oracle oinstall 79453 Aug 31 14:41 gentunerpt_9htcqxkzu0tyg.lst
-rw-r--r-- 1 oracle oinstall   539 Aug 31 14:41 gentunerpt_9htcqxkzu0tyg.sql
透過上面的指令碼會自動生成下面的pl/sql程式碼塊。
set serveroutput on
  var tuning_task varchar2(100);  
 DECLARE
    l_sql_id varchar2(200);
    l_tuning_task VARCHAR2(30);
 BEGIN
    l_sql_id:='9a5vcjjtgkf1k';  
    l_tuning_task:=dbms_sqltune.create_tuning_task(sql_id => l_sql_id,scope       => 'COMPREHENSIVE',time_limit  => 120);  
    :tuning_task:=l_tuning_task;  
    dbms_sqltune.execute_tuning_task(l_tuning_task);
    dbms_output.put_line(l_tuning_task);
 END;
 /  
print tuning_task;  
set long 99999
SELECT dbms_sqltune.report_tuning_task(:tuning_task) comm FROM dual;


TASK_20659      
GENERAL INFORMATION SECTION 
-------------------------------------------------------------------------------
Tuning Task Name   : TASK_20659
Tuning Task Owner  : ADBA      
Workload Type      : Single SQL Statement  
Scope              : COMPREHENSIVE
Time Limit(seconds): 120      
Completion Status  : COMPLETED
Started at         : 12/06/2015 23:36:59   
Completed at       : 12/06/2015 23:37:46                                                                                                                                                               
------------------------------------------------------------------------------
Schema Name: APP_MBI_BG
SQL ID     : 9a5vcjjtgkf1k  
SQL Text   : xxxxx
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  2 potentially better execution plans were found for this statement. Choose
  one of the following SQL profiles to implement.                                                                                                                                                                    
  Recommendation (estimated benefit: 51.39%)    
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_20659',
            task_owner => 'ADBA', replace => TRUE); 
...
所以透過上面的調優分析可以看出，建議了另外一個執行計劃，能夠提高50%左右。簡單確認就可以生效了。