12C DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name

最新推荐文章于 2023-04-26 19:29:17 发布

董董-酱

最新推荐文章于 2023-04-26 19:29:17 发布

阅读量3.4k

点赞数 2

分类专栏： Oracle Oracle 12c 文章标签： oracle

原文链接：http://www.xifenfei.com/tag/wri_adv_sqlt_rtn_plan

版权

Oracle 同时被 2 个专栏收录

22 篇文章 0 订阅

订阅专栏

Oracle 12c

3 篇文章 0 订阅

订阅专栏

转发自惜分飞：http://www.xifenfei.com/tag/wri_adv_sqlt_rtn_plan

文章目录

近日遇到告警日志报错，如下：

2020-06-04T16:31:48.532177+08:00
WARNING: too many parse errors, count=513 SQL hash=0x750004bb
PARSE ERROR: ospid=32623, error=933 for statement: 
2020-06-04T16:31:48.532328+08:00
DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name
Additional information: hd=0x2bea60738 phd=0x2bea60968 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 
2020-06-04T16:31:48.567323+08:00
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x460e30f08       259  type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
0x6b8d30e8      2134  package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK
0x6b8d30e8      7342  package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS
0x474636780         1  anonymous block
2020-06-04T16:31:50.024334+08:00
WARNING: too many parse errors, count=613 SQL hash=0x750004bb
PARSE ERROR: ospid=32623, error=933 for statement: 
2020-06-04T16:31:50.024485+08:00
DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name
Additional information: hd=0x2bea60738 phd=0x2bea60968 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0

找到了最靠谱的飞大哥的Blog。特此记录。

数据库版本

SQL> set lines 200
SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                                0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0

alert 日志报错

每天的早上9:19 左右都会有这么一坨的报错。强迫症患者要犯病了。！！

2020-06-04T08:09:19.535349+08:00
WARNING: too many parse errors, count=100 SQL hash=0x750004bb
PARSE ERROR: ospid=28430, error=933 for statement:
2020-06-04T08:09:19.535490+08:00
DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name
Additional information: hd=0x2bea60738 phd=0x2bea60968 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
2020-06-04T08:09:19.535632+08:00
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x460e30f08       259  type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
0x6b8d30e8      2134  package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK
0x6b8d30e8      7342  package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS
0x7776f560         1  anonymous block
2020-06-04T08:09:24.309711+08:00
WARNING: too many parse errors, count=200 SQL hash=0x750004bb
PARSE ERROR: ospid=28430, error=933 for statement:
2020-06-04T08:09:24.309846+08:00
DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name
Additional information: hd=0x2bea60738 phd=0x2bea60968 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
2020-06-04T08:09:24.310005+08:00
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x460e30f08       259  type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
0x6b8d30e8      2134  package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK
0x6b8d30e8      7342  package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS
0x7776f560         1  anonymous block
2020-06-04T08:09:26.535249+08:00
WARNING: too many parse errors, count=300 SQL hash=0x750004bb
PARSE ERROR: ospid=28430, error=933 for statement:
2020-06-04T08:09:26.535401+08:00
DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name
Additional information: hd=0x2bea60738 phd=0x2bea60968 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
2020-06-04T08:09:26.535567+08:00
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x460e30f08       259  type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
0x6b8d30e8      2134  package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK
0x6b8d30e8      7342  package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS
0x7776f560         1  anonymous block
WARNING: too many parse errors, count=400 SQL hash=0x750004bb
PARSE ERROR: ospid=28430, error=933 for statement:
2020-06-04T08:09:27.481993+08:00
DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name
Additional information: hd=0x2bea60738 phd=0x2bea60968 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
2020-06-04T08:09:27.482208+08:00
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x460e30f08       259  type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
0x6b8d30e8      2134  package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK
0x6b8d30e8      7342  package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS
0x7776f560         1  anonymous block

这里比较明显由于DELETE FROM wri$_adv_sqlt_rtn_planWHERE这条sql语法不对，导致无法解析因此报了ORA-00933错误.

情景再现

手动执行以下调用SQL，会再次触发该问题：

SQL>  exec SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS();

PL/SQL procedure successfully completed.

就是他！就是他！。。报错如下：

2020-06-04T16:31:50.024688+08:00
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x460e30f08       259  type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
0x6b8d30e8      2134  package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK
0x6b8d30e8      7342  package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS
0x474636780         1  anonymous block
2020-06-04T16:31:51.234957+08:00
WARNING: too many parse errors, count=713 SQL hash=0x750004bb
PARSE ERROR: ospid=32623, error=933 for statement:
.
.（此处忽略了大部分报错）
.
....