ORA-27163: 内存不足

分析一个400多秒的大sql。

set long 1000000
set longchunksize 1000000
set lin 200
set pages 1000
set trim on
set trimspool on
set echo off
set feedback off
select dbms_sqltune.report_sql_monitor(sql_id=>'&sqlid',type=>'TEXT',report_level=>'ALL') mon_rpt from dual;

输入 sqlid 的值:  cwx04v3hzfkwy
原值    1: select dbms_sqltune.report_sql_monitor(sql_id=>'&sqlid',type=>'TEXT',report_level=>'ALL') mon_rpt from dual
新值    1: select dbms_sqltune.report_sql_monitor(sql_id=>'cwx04v3hzfkwy',type=>'TEXT',report_level=>'ALL') mon_rpt from dual
ERROR:
ORA-27163: 内存不足
ORA-06512: 在 "SYS.DBMS_SQLTUNE", line 13969
ORA-06512: 在 "SYS.DBMS_SQLTUNE", line 14265
ORA-06512: 在 line 1




SQL> !oerr ora 27163
27163, 0000, "out of memory"
// *Cause:  The program ran out of memory when allocating a temporary
//          data structure.
// *Action: Increase the amount of memory on the system.


SQL> !sqlplus -v


SQL*Plus: Release 11.2.0.3.0 Production




SQL> alter session set events '31156 trace name context forever,level 0x400';
SQL> 
select dbms_sqltune.report_sql_monitor(sql_id=>'&sqlid',type=>'TEXT',report_level=>'ALL') mon_rpt from dual;SQL> 
输入 sqlid 的值:  cwx04v3hzfkwy
原值    1: select dbms_sqltune.report_sql_monitor(sql_id=>'&sqlid',type=>'TEXT',report_level=>'ALL') mon_rpt from dual
新值    1: select dbms_sqltune.report_sql_monitor(sql_id=>'cwx04v3hzfkwy',type=>'TEXT',report_level=>'ALL') mon_rpt from dual


MON_RPT
------------------------------------------------------------------------------------------------------------
SQL Monitoring Report


SQL Text
------------------------------
INSERT INTO XXX ( ACCCODE , --科目编码 ACCNO , --会计机构 ACCOUNTNO , --账户号 AMORTNO 
...