ITPub博客

首页 > 数据库 > Oracle > [20211129]更新dpcawr1.sql脚本.txt

[20211129]更新dpcawr1.sql脚本.txt

原创 Oracle 作者:lfree 时间:2021-11-29 11:36:12 0 删除 编辑

[20211129]更新dpcawr1.sql脚本.txt

--//想使用dbms_xplan.display_awr查看旧执行计划的outline,发现我写的脚本有问题,做一个更新并记录:

1.环境:
> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

> @ desc_proc sys dbms_xplan display_awr
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats

OWNER      PACKAGE_NAME         OBJECT_NAME SEQUENCE ARGUMENT_NAME        DATA_TYPE            IN_OUT    DEFAULTED
---------- -------------------- ----------- -------- -------------------- -------------------- --------- ----------
SYS        DBMS_XPLAN           DISPLAY_AWR        1                      TABLE                OUT       N
                                                   3 SQL_ID               VARCHAR2             IN        N
                                                   4 PLAN_HASH_VALUE      NUMBER               IN        Y
                                                   5 DB_ID                NUMBER               IN        Y
                                                   6 FORMAT               VARCHAR2             IN        Y


$ cat dpcawr1.sql
set verify off
select * from table(dbms_xplan.display_awr(NVL('&1',NULL),nvl(&3,NULL),NULL,'ALLSTATS LAST PEEKED_BINDS &2 cost'));

> @ dpcawr1 avgpwqjsdrgzp outline ''
select * from table(dbms_xplan.display_awr(NVL('avgpwqjsdrgzp',NULL),nvl(,NULL),NULL,'ALLSTATS LAST PEEKED_BINDS outline cost'))
                                                                         *
ERROR at line 1:
ORA-00936: missing expression

--//参数3是数字,我带入字符'',替换出错,我这样写法要加入单引号。

$ cp  dpcawr1.sql  dpcawrx.sql
$ cat dpcawrx.sql
set verify off
select * from table(dbms_xplan.display_awr(NVL('&1',NULL),nvl('&3',NULL),NULL,'ALLSTATS LAST PEEKED_BINDS &2 cost'));

> @ dpcawrx avgpwqjsdrgzp outline ''

--//执行ok,单独带入PLAN_HASH_VALUE测试也可以执行,字符自动转换数字。
--//顺便贴一下我写的dpc.sql脚本:

$ cat dpc.sql
set verify off
--select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALLSTATS LAST PEEKED_BINDS &2 cost partition'));
select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALL ALLSTATS LAST PEEKED_BINDS cost partition -projection -outline &2'));
prompt
prompt argment : typical all advanced partition predicate remote note parallel projection alias peeked_binds outline adaptive
prompt

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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    3135
  • 访问量
    6839378