[20211129]更新dpcawr1.sql指令碼.txt

[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