Re: append hint and ORA-04030

From: Stefan Koehler <contact_at_soocs.de>
Date: Fri, 2 Jan 2015 22:27:03 +0100 (CET)
Message-ID: <494605303.34919.1420234023677.open-xchange_at_app03.ox.hosteurope.de>

Hi Anupam,
unfortunately you have neither provided your Oracle version nor the heap dump (summary) for your issue.

However based on the exact ORA-04030 error text and the XMLTable construct, it seems like you have hit Oracle bug #12824731 / #15979366, which is a memory leak. For more detailed information please check MOS ID #1512845.1 - "ORA-04030 (KXS-HEAP-W,QMEMNEXTBUF:LARGE ALLOC) from XMLTABLE Passing XMLTYPE with Binary XML".

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK

> Anupam Pandey <my.oralce_at_gmail.com> hat am 2. Januar 2015 um 19:19 geschrieben:
>
> Hi All,
> I am executing below mentioned query with append hint .
>
> insert /*+ append */ into MYUSER.MONTH_DATA
> select 201411 as month_key,
> 1 as id,
> 7 as key ,
> 24 as md,
> t3.wd,
> <http://t3.mt> ,
> <http://t3.ht> ,
> t3.pi
> <http://t3.th> ,
> decode( t3.td1,'NaN','0',t3.td1) ,
> decode( t3.td2,'NaN','0',t3.td2) ,
> decode( t3.td3,'NaN','0',t3.td3) ,
> decode( t3.td4,'NaN','0',t3.td4) ,
> decode( t3.td5 ,'NaN','0',t3.td5) ,
> decode( t3.td6 ,'NaN','0',t3.td6) ,
> decode( t3.td7 ,'NaN','0',t3.td7)
> from MYUSER.STAGE_MONTH t1 ,
> XMLTable(xmlnamespaces(default ' http://mydataproc.com/New' <http://mydataproc.com/New'> ),
> 'REPORT/TABLE/TBODY/TR'
> PASSING t1.object_value
> COLUMNS TH VARCHAR2(100) PATH 'TH',
> wd VARCHAR2(200) PATH 'TH/_at_wd',
> mt VARCHAR2(200) PATH 'TH/_at_mt',
> ht VARCHAR2(200) PATH 'TH/_at_ht',
> pi VARCHAR2(200) PATH 'TH/_at_pi',
> TD1 VARCHAR2(100) PATH 'TD[1]',
> TD2 VARCHAR2(100) PATH 'TD[2]',
> TD3 VARCHAR2(100) PATH 'TD[3]',
> TD4 VARCHAR2(100) PATH 'TD[4]',
> TD5 VARCHAR2(100) PATH 'TD[5]',
> TD6 varchar2(100) PATH 'TD[6]',
> TD7 VARCHAR2(100) PATH 'TD[7]') t3 ;
>
> But it fails after sometime with below error .
> ORA-04030: out of process memory when trying to allocate 55600 bytes (kxs-heap-w,qmemNextBuf:Large
> Alloc)
>
> I was thinking that with append hint it will take route of directly loading into the disk
> bypassing memory which does not seem to be the case .
>
> Is there any other way I should rewrite insert statement to avoid memory error. Total number of rows
> getting loaded here is 150K Or let me know if there is anything else I need to change .
>
> Thanks,
> Anupam

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 02 2015 - 22:27:03 CET