Generate SPA report ORA-27163: outofmemory

Some time ago, we did a SPA test. Our environment was 11.2.0.4.2. After the previous analysis, an error occurred while generating the SPA report. Outofmemory is reported. The error log is as follows: settrimspoolonsettrimonsetpages0setlinesize1000setlong20000000000setlongchunksize0000000000spoolspa

Some time ago, we did a SPA test. Our environment was 11.2.0.4.2. After the previous analysis, an error occurred while generating the SPA report. Reports out of memory. The error log is as follows: set trimspool on set trim on set pages 0 set linesize 1000 set long 20000000000 set longchunksize 10000000000 spool spa

Some time ago, we did a SPA test. Our environment was 11.2.0.4.2. After the previous analysis, an error occurred while generating the SPA report. Reports out of memory. The error log is as follows:

set trimspool on set trim on set pages 0 set linesize 1000 set long 20000000000 set longchunksize 10000000000 spool spa_report_elapsed_time.html SELECT dbms_sqlpa.report_analysis_task('SPA_TEST', 'HTML', 'ALL','ALL', top_sql=>300,execution_name=>'Compare_elapsed_time') FROM dual; spool off; ERROR: ORA-27163: out of memory ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 8211 ORA-06512: at "SYS.DBMS_SQLPA", line 515 ORA-06512: at line 1 

In this internal issue, we had to look for a search on MOS. At the same time, I also opened the SR. Soon the SR replied, and I also found a document named XML Parser Fails With ORA-27163 (Out Of Memory) (Document ID 1599434.1 ). The method for SR reply is the same as the solution I found. You need to set event 31156. This can be set directly at the session level. After the setting is complete, no error is reported when the SPA report is generated again.

ALTER SESSION SET EVENTS '31156 trace name context forever, level 0x400';

It is strange that this problem exists in 11.2.0.3, but 11.2.0.4 does not, but I still encountered this problem. At the same time, SR points OUT that it is a Bug 12685875: ORA-27163 out of memory when executing DBMS_SQLPA.REPORT_ANALYSIS_TASK. This bug does not contain workground. However, I can solve the problem through the above events.

Reference: XML Parser Fails With ORA-27163 (Out Of Memory) (Document ID 1599434.1)

Bug 12685875: ORA-27163 out of memory when executing DBMS_SQLPA.REPORT_ANALYSIS_TASK

Source Address: Generate SPA report ORA-27163: out of memory, thanks to the original author to share.