[20190816]12c执行exec SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS()报错.txt

[20190816]12c执行exec SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS()报错.txt --//http://www.xifenfei.com/2019/08/delete-wri_adv_sqlt_rtn_plan-error.html,重复测试： 1.环境： SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 2.测试： SYS@test> exec SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS(); PL/SQL procedure successfully completed. --//当前环境并没有报错，使用10046跟踪看看。 SYS@test> @ 10046on 12 old   1: alter session set events '10046 trace name context forever, level &1' new   1: alter session set events '10046 trace name context forever, level 12' Session altered. SYS@test> exec SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS(); PL/SQL procedure successfully completed. SYS@test> @ 10046off Session altered. --//检查转储可以发现如下信息： ===================== PARSE ERROR #361662816:len=86 dep=1 uid=0 oct=7 lid=0 tim=5918632927 err=933 DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name --//显然语句写错了，前面少1个空格。 CLOSE #361662816:c=0,e=2,dep=1,type=0,tim=5918633093 ===================== PARSING IN CURSOR #361662816 len=8 dep=1 uid=0 oct=45 lid=0 tim=5918633211 hv=2761672982 ad='0' sqlid='8sst43uk9rk8q' ROLLBACK END OF STMT --//查看alert日志发现： 2019-08-16T21:31:03.200907+08:00 DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name Additional information: hd=000007FF13893488 phd=000007FF13391170 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 2019-08-16T21:31:03.201907+08:00 ----- PL/SQL Call Stack -----   object      line  object   handle    number  name 000007FF139887E8       259  type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION 000007FF131707F0      2134  package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK 000007FF131707F0      7342  package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 000007FF13B48618         1  anonymous block 3.一些简单分析: SYS@test> @ sharepool/shp4 000007FF13893488 0 old  20:  WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2 new  20:  WHERE kglobt03 = '000007FF13893488'  or kglhdpar='000007FF13893488' or kglhdadr='000007FF13893488' or KGLNAHSH= 0 TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09 -------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 子游标句柄地址 000007FF13893488 000007FF13391170 DELETE FROM wri$_adv                              0          0        384 00               00                        0          0       3165      3165       3165 1962935483 0aj728juh015v          0 SYS@test> @ sharepool/shp4 0aj728juh015v 0 old  20:  WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2 new  20:  WHERE kglobt03 = '0aj728juh015v'  or kglhdpar='0aj728juh015v' or kglhdadr='0aj728juh015v' or KGLNAHSH= 0 TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09 -------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 子游标句柄地址 000007FF13893488 000007FF13391170 DELETE FROM wri$_adv                              0          0        384 00               00                        0          0       3165      3165       3165 1962935483 0aj728juh015v          0 父游标句柄地址 000007FF13391170 000007FF13391170 DELETE FROM wri$_adv                              0          0        384 000007FF13893A20 00                     4072          0          0      4072       4072 1962935483 0aj728juh015v      65535 --//子游标信息没有KGLOBHD0,KGLOBHD6信息。000007FF13893488对应前面的hd=000007FF13893488(子游标)，000007FF13391170对应 --//phd=000007FF13391170(父游标)。 SYS@test> select kglnaobj from x$kglob where kglobt03='0aj728juh015v'; KGLNAOBJ ------------------------------ DELETE FROM wri$_adv DELETE FROM wri$_adv --//错误的sql语句仅仅能看到这么一截信息，输出仅仅20个字符，不知道出错的sql语句都是20个字符。 SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000007FF131707F0', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ old   1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ new   1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000007FF131707F0', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ ADDR                   INDX    INST_ID     CON_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 0000000015A3E440      32474          1          1          1          1 KGLHD            000007FF131707C0        816 recr             80 00 SYS@test> @ tpt/fcha 000007FF13391170 Find in which heap (UGA, PGA or Shared Pool) the memory address 000007FF13391170 resides... WARNING!!! This script will query X$KSMSP, which will cause heavy shared pool latch contention in systems under load and with large shared pool. This may even completely hang your instance until the query has finished! You probably do not want to run this in production! Press ENTER to continue, CTRL+C to cancel... old  14:     to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX') new  14:     to_number(substr('000007FF13391170', instr(lower('000007FF13391170'), 'x')+1) ,'XXXXXXXXXXXXXXXX') old  32:     to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX') new  32:     to_number(substr('000007FF13391170', instr(lower('000007FF13391170'), 'x')+1) ,'XXXXXXXXXXXXXXXX') old  50:     to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX') new  50:     to_number(substr('000007FF13391170', instr(lower('000007FF13391170'), 'x')+1) ,'XXXXXXXXXXXXXXXX') LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- SGA 000007FF13391140          1          1 KGLHD                   816 recr             80 00 SYS@test> @ tpt/fcha 000007FF13893488 Find in which heap (UGA, PGA or Shared Pool) the memory address 000007FF13893488 resides... WARNING!!! This script will query X$KSMSP, which will cause heavy shared pool latch contention in systems under load and with large shared pool. This may even completely hang your instance until the query has finished! You probably do not want to run this in production! Press ENTER to continue, CTRL+C to cancel... old  14:     to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX') new  14:     to_number(substr('000007FF13893488', instr(lower('000007FF13893488'), 'x')+1) ,'XXXXXXXXXXXXXXXX') old  32:     to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX') new  32:     to_number(substr('000007FF13893488', instr(lower('000007FF13893488'), 'x')+1) ,'XXXXXXXXXXXXXXXX') old  50:     to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX') new  50:     to_number(substr('000007FF13893488', instr(lower('000007FF13893488'), 'x')+1) ,'XXXXXXXXXXXXXXXX') LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- SGA 000007FF13893458          1          1 KGLHD                   560 recr             80 00 --//看这些东西头都大了。 4.unwrap看看： --//手头没有unwrap，使用在线unwrap看看，链接https://www.codecrete.net/UnwrapIt/， --//注意不要拿生产系统加密的脚本上去解密,以免泄密。 --//注意是unwap type WRI$_ADV_SQLTUNE,在type里面找WRI$_ADV_SQLTUNE，这里浪费1点点时间。 --//摘录出问题的代码：   253     SUBST_PATTERN := DBMS_SQLTUNE_UTIL0.ADD_SUBST_PATTERN(   254                                                       'wri$_adv_sqlt_rtn_plan');   255     ORIG_QRY :=  'DELETE FROM '|| SUBST_PATTERN ||   256              'WHERE task_id = :tid AND exec_name = :execution_name';   257        258     DBMS_SQLTUNE_UTIL0.GET_SUBST_QUERY(ORIG_QRY, SUBST_QRY);   259     EXECUTE IMMEDIATE SUBST_QRY USING TID,EXECUTION_NAME; --//很明显在256行的where前面少了一个空格，因为是动态sql语句,编译不会报错。