透過shell定製dbms_advisor.quick_tune

在平時的調優工作中,在11g中的新特性sql monitor可以極大的簡化效能監控的工作，對於執行時間超過5秒的sql語句都會記入v$sql_monitor中。
但是如果某個sql語句還沒有執行，或者執行時間已經是幾天前了，等發現效能問題進行調優的話就會比較困難，採用dbms_advisor.quick_tune是一個不錯的選擇。如果sql語句比較龐大，比較迷茫的時候至少可以得到一些很重要的思路。
舉個簡單的例子。
建立一個表t
create table t as select *from all_objects;

然後直接執行查詢
select *from t where object_id=100 and object_name='T'
這個時候毫無疑問是需要走全表掃描的。
如果使用dbms_advisor.quick_tune需要建立一個task，然後對需要執行的sql語句進行格式轉換,然後生成報告。
這些工作如果手動執行pl/sql是很費力的，可以透過定製shell指令碼來實現。

shell指令碼如下:
TASK_NAME=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID < set pagesize 0 feedback off verify off heading on echo off 
select  'QUICK_TSK_'||i.instance_name||'_'||to_char(sysdate,'yymmddhh24')  from  v\\$database d,
       v\\$instance i; 
exit; 
END` 

if [ -z "$TASK_NAME" ]; then 
 echo "no addm task exists, please check again" 
 exit 0 
else 
 echo '*******************************************'
 echo " $TASK_NAME    " 
 echo '*******************************************'
fi 

sed  's/'\''/'\'''\''/g'  $1 > temp_tuning_.sql

echo .
echo     format sql as below 
echo '*******************************************'
cat  temp_tuning_.sql
echo '*******************************************'

sqlplus -silent $DB_CONN_STR@$SH_DB_SID <
declare       
task_name  varchar2(30);
begin
task_name:='$TASK_NAME';
dbms_output.put_line(task_name);   

DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_name,'`cat temp_tuning_.sql ` ');
END;
/    

prompt *******************************************
prompt     recommendations as below
prompt *******************************************

set pages 50
set linesize 200
col detailed_info format a50
set long 99999
SELECT sql_id, rec_id, precost, postcost, (precost-postcost)*100/precost AS percent_benefit
     FROM dba_advisor_sqla_wk_stmts
     WHERE task_name =  '$TASK_NAME';
SELECT rec_id, action_id, substr(command,1,30) AS command,nvl(attr1,'|')||nvl(attr2,'|')||chr(10)||nvl(attr3,'|')||nvl(attr4,'|')||chr(10)||nvl(attr5,'|')||nvl(attr6,'|') detailed_info
     FROM dba_advisor_actions
     WHERE task_name = '$TASK_NAME'
     ORDER BY rec_id, action_id;    
exec DBMS_ADVISOR.DELETE_TASK('$TASK_NAME');
END


呼叫這個sql語句也比較靈活，比如sql語句比較大，我們直接嵌入pl/sql中格式化是很繁瑣的，可以單獨建立一個檔案，比如test.sql
test.sql的內容就是需要調優的sql語句，沒有任何格式變化。
select *from t where object_id=100 and object_name='T'

假設指令碼名為quick_tune.sh就可以直接執行。
ksh quick_tune.sh test.sql

輸出的結果如下：
*******************************************
 QUICK_TSK_NFTCUS1_15011417    
*******************************************
.
format sql as below
*******************************************
select *from t where object_id=100 and object_name=''T''
*******************************************
PL/SQL procedure successfully completed.
*******************************************
recommendations as below
*******************************************
SQL_ID            REC_ID    PRECOST   POSTCOST PERCENT_BENEFIT
------------- ---------- ---------- ---------- ---------------
2jg3kykdr4z38          1       1083          2      99.8153278




    REC_ID  ACTION_ID COMMAND                        DETAILED_INFO
---------- ---------- ------------------------------ --------------------------------------------------
         1          1 CREATE INDEX                   "N1"."T_IDX$$_538B0000"|
                                                     "N1"."T"BTREE
                                                     ("OBJECT_ID")

對於sql語句的調優可以使用這個指令碼來做快速調優，但是不一定能夠能夠得到最優的結果，如果需要深入的調優，可以使用dbms_sqltune來做。

另外執行dbms_advisor的時候可能會丟擲下面的錯誤，dba使用者也會丟擲這個錯誤，是因為需要advisor的許可權。
ERROR at line 1:
ORA-13616: The current user xxxxx  has not been granted the ADVISOR privilege.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_ADVISOR", line 920
ORA-06512: at "SYS.DBMS_ADVISOR", line 708
ORA-06512: at line 5

深究原因，是因為dba使用者下也沒有這個許可權，需要補上。
  1* select *from dba_role_privs where grantee='N1'
SQL> /
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
N1                             DBA                            NO  YES

SQL> L
  1* select *from dba_role_privs where grantee='N1'
SQL> c/N1/DBA
  1* select *from dba_role_privs where grantee='DBA'  --檢視DBA的許可權
SQL> /

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
DBA                            DATAPUMP_IMP_FULL_DATABASE     NO  YES
DBA                            OLAP_DBA                       NO  YES
DBA                            SCHEDULER_ADMIN                YES YES
DBA                            OLAP_XS_ADMIN                  NO  YES
DBA                            DELETE_CATALOG_ROLE            YES YES
DBA                            EXECUTE_CATALOG_ROLE           YES YES
DBA                            PLUSTRACE                      YES YES
DBA                            WM_ADMIN_ROLE                  NO  YES
DBA                            EXP_FULL_DATABASE              NO  YES
DBA                            SELECT_CATALOG_ROLE            YES YES
DBA                            JAVA_DEPLOY                    NO  YES
DBA                            GATHER_SYSTEM_STATISTICS       NO  YES
DBA                            XDB_SET_INVOKER                NO  YES
DBA                            DATAPUMP_EXP_FULL_DATABASE     NO  YES
DBA                            JAVA_ADMIN                     NO  YES
DBA                            XDBADMIN                       NO  YES
DBA                            IMP_FULL_DATABASE              NO  YES

修復使用 grant advisor to n1即可。