Re: append hint and ORA-04030
- From: Stefan Koehler <contact@xxxxxxxx>
- To: my.oralce@xxxxxxxxx
- Date: Fri, 2 Jan 2015 22:27:03 +0100 (CET)
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: @OracleSK
> Anupam Pandey <my.oralce@xxxxxxxxx> 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/@wd',
> mt VARCHAR2(200) PATH 'TH/@mt',
> ht VARCHAR2(200) PATH 'TH/@ht',
> pi VARCHAR2(200) PATH 'TH/@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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
