My Oracle Support Banner

SQL Tuning Advisor Job, ORA$AT_SQ_SQL_SW_xxxx, Fails With ORA-31011, ORA-06512, ORA-19213, LPX-00007 (Doc ID 2675644.1)

Last updated on AUGUST 06, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.

Symptoms

An Automatic SQL Tuning job that runs regularly is generating errors and traces.  The alert.log and trace file show:

2020-03-31T23:00:20.120803+02:00
Errors in file /u01/oracle/diag/rdbms/oracle/oracle/trace/oracle_j003_59454.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_SQ_SQL_SW_45417"
ORA-31011: XML parsing failed
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 4541
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 5914
ORA-19213: error occurred in XML processing at lines 1
LPX-00007: unexpected end-of-file encountered
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 5943
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 5705
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 4504
ORA-06512: at "SYS.DBMS_SPM", line 2810
ORA-06512: at "SYS.PRVT_ADVISOR", line 3546
ORA-06512: at "SYS.PRVT_ADVISOR", line 932
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 14140
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 14167
ORA-06512: at "SYS.WRI$_ADV_SPM_EVOLVE", line 7
ORA-06512: at "SYS.PRVT_ADVISOR", line 915
ORA-06512: at "SYS.PRVT_ADVISOR", line 3451
ORA-06512: at "SYS.DBMS_ADVISOR", line 276
ORA-06512: at "SYS.DBMS_SPM", line 2790
ORA-06512: at line 34


Gathering an errorstack by enabling the event:

alter system  set events '31011  trace name ERRORSTACK level 3';

Generated a trace file that showed the sql that was generating the errors:

----- Error Stack Dump -----
<error barrier> at 0x7ffcb9def4a0 placed dbkda.c@296
ORA-31011: XML parsing failed
ORA-19213: error occurred in XML processing at lines 1
LPX-00007: unexpected end-of-file encountered
<error barrier> at 0x7ffcb9dfed70 placed kpoodr.c@237
<error barrier> at 0x7ffcb9e10a40 placed kpoodr.c@237

----- Current SQL Statement for this session (sql_id=1p9s927vay3ww) -----
SELECT EXTRACTVALUE(XMLTYPE(P.OTHER_XML), '/*/info[@type="plan_hash_2"]') FROM TABLE( :B1 ) P WHERE P.OTHER_XML IS NOT NULL

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xbdec7960       272  type body SYS.XMLTYPE.XMLTYPE
0x80973878         1  anonymous block
0x1964758c8      5943  package body SYS.DBMS_SPM_INTERNAL.I_PROCESS_SQLSET_ROW_PHV
0x1964758c8      5705  package body SYS.DBMS_SPM_INTERNAL.LOAD_PLANS_FROM_AWR
0x1964758c8      4504  package body SYS.DBMS_SPM_INTERNAL.LOAD_ALTERNATE_PLANS
0xbdb28010     14140  package body SYS.DBMS_SQLTUNE_INTERNAL.I_SUB_EXECUTE_CALLOUT
0xbdb28010     14167  package body SYS.DBMS_SQLTUNE_INTERNAL.I_SUB_EXECUTE
0x1a47ba090         7  type body SYS.WRI$_ADV_SPM_EVOLVE.SUB_EXECUTE
0xa7d70230       915  package body SYS.PRVT_ADVISOR.COMMON_SUB_EXECUTE
0xa7d70230      3451  package body SYS.PRVT_ADVISOR.COMMON_EXECUTE_TASK
0x7c4bac70       276  package body SYS.DBMS_ADVISOR.EXECUTE_TASK
0x9cfe1fe8      2790  package body SYS.DBMS_SPM.EXECUTE_EVOLVE_TASK



Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Symptoms
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.