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 laterInformation 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
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
<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 |