[20190816]12c執行exec SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS()報錯.txt

[20190816]12c執行exec SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS()報錯.txt

--//重複測試：

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看看，連結，
--//注意不要拿生產系統加密的指令碼上去解密,以免洩密。
--//注意是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語句,編譯不會報錯。