通过shell定制dbms_advisor.quick_tune(r4笔记第15天)

在平时的调优工作中,在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 <<END
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 <<END      

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即可。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-01-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏陈满iOS

iOS·宏封装线程锁小技巧

891
来自专栏Greenplum

Greenplum常见创建表方式与说明

drop table if exists test_head; create table test_head(id int primary key) dist...

2210
来自专栏乐沙弥的世界

Oracle 控制文件(CONTROLFILE)

为二进制文件,初始化大小由CREATE DATABASE指定,可以使用RMAN备份

1232
来自专栏张善友的专栏

非域环境下使用证书设置数据库镜像

数据库镜像是将数据库事务处理从一个SQL Server数据库移动到不同SQL Server环境中的另一个SQL Server数据库中。镜像不能直接访问;它只用在...

2155
来自专栏乐沙弥的世界

使用带dblink方式的datapump迁移Oracle 10g到11g

      对于从Oracle 10g下迁移数据库到Oracle 11g,除了使用RMAN方式之外,我们可以使用带dblink的datapump方式来实现基于逻...

711
来自专栏数据和云

触类旁通:那些关于 TBL$OR$IDX$PART$NUM 的诡异案例和知识

你是否留意过数据库中有一个奇怪的函数 TBL$OR$IDX$PART$NUM ,你是否留意过很多场景下都出现过它的身影?

1454
来自专栏杨建荣的学习笔记

备库归档删除策略失效的问题分析 (r7笔记第6天)

最近碰到了一个有些奇怪的问题,自己当时排查问题时间紧,没有细细琢磨,今天抽空看了下,终于发现了端倪。 首先是在早晨收到了报警邮件,报警邮件内容如下: ZABBI...

4078
来自专栏杨建荣的学习笔记

只言片语分析datapump的工作原理(r2第18天)

datapump是从oracle 10g推出的新的数据导入导出工具,可以说是exp/imp的加强版,主要的亮点在于服务端,结合了direct+parallel,...

2443
来自专栏杨建荣的学习笔记

关于sysdba,sysoper,dba的区别(r3笔记第62天)

关于sysdba,sysoper,dba这些名词在工作中可能接触的比较多,如果接触的环境是服务器端的,sysdba可能是经常用到的。如果是数据库的维护工作,db...

2736
来自专栏乐沙弥的世界

ORA-01157: cannot identify/lock data file n 故障一例

    最近在使用swingbench的时候碰到了ORA-01157故障,下面是其具体描述与解决。

1064

扫码关注云+社区

领取腾讯云代金券