oracle dbms advisor,通过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即可。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-1401969/，如需转载，请注明出处，否则将追究法律责任。