Re: append hint and ORA-04030
- From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- To: hemantkchitale@xxxxxxxxx
- Date: Sat, 3 Jan 2015 09:06:10 +0100
Anupam
I have been exposed to a similar error which I managed to solve using this
*xmldom.freedocument*
The error i faced was a slightly different from yours
ORA-04030: out of process memory when trying to allocate 4032 bytes
(qmxdGetElemsBy,qmemNextBuf:alloc)
I've blogged about the issue and how I have solved it in this article
https://hourim.wordpress.com/2014/05/07/ora-04030-when-parsing-xml-messages/
Best regards
Mohamed Houri
www.hourim.wordpress.com
2015-01-03 2:06 GMT+01:00 Hemant K Chitale <hemantkchitale@xxxxxxxxx>:
> Anupam,
>
> ORA-4030 is a PGA memory allocation error.
> Direct Path Insert (the APPEND Hint) bypasses buffer allocation on the
> buffer cache in the SGA.
> The two are different memory allocation structures.
>
> Sent from my smart phone.
> Hemant K Chitale
> http://hemantoracledba.blogspot.com
> On 03-Jan-2015 2:21 AM, "Anupam Pandey" <my.oralce@xxxxxxxxx> wrote:
>
>> 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,
>> t3.mt,
>> t3.ht,
>> t3.pi
>> 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'),
>> '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
>>
>>
--
Houri Mohamed
Oracle DBA-Developer-Performance & Tuning
Member of Oraworld-team <http://www.oraworld-team.com/>
Visit My - Blog <http://www.hourim.wordpress.com/>
Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
<https://twitter.com/MohamedHouri>
Other related posts:
