Re: Oracle10G error!!!!

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 28 Oct 2004 19:01:13 +1000
Message-Id: <4180b547$0$24942$afc38c87@news.optusnet.com.au>

Holger Baer wrote:

> Howard J. Rogers wrote:

>> Praveen wrote:
>> 

> [...]
>

>>>  __large_pool_size        = 20971520
>>>  large_pool_size          = 4194304
>>>  __java_pool_size         = 20971520
>>>  java_pool_size           = 20971520
>>>  sga_target               = 167772160

> [...]
>

>>>
>>>I'm getting the following error frequently. And around 35 programmers
>>>will connect daily.
>>>
>>>ORA-00604: error occurred at recursive SQL level 1
>>>ORA-12414: internal LBAC error: zllcsi:OCIStmtExecute
>>> Error: ORA-04031: unable to allocate 64088 bytes of shared memory
>>>("large pool","unknown object","session heap","kksfal:1:frame
>>>segment")
>>>
>>>Can anybody help me why this error is coming?
>> 
>> 
>> Yes, it's what the error message explicitly says. You've run out of space
>> in the large pool, which is apparently just 4MB big. Give it some more
>> memory.
>> 
>> Regards
>> HJR
>> 

>
> I don't quite understand your comment, and although David seems to agree,
> could you please elaborate a bit more:
>
> SGA_TARGET is set, so the pool should be dynamically managed and the 4MB
> figure is just the minimum.

And that minimum is clearly insufficient...

> Oracle realised already that it needed 5 times
> the number:
> __large_pool_size = 20971520

....because Oracle's had to increase it... but that still clearly isn't enough, given the error message. (One presumes that it cannot dynamically add more, because there is insufficient target).

OK. Maybe I should have posted to increase the target. But I would also suggest increasing the minimum. 4MB is about as small as it can go, after all.

> I haven't researched Automatic Shared Memory Tuning sofar, but I'd expect
> that the, as the New Features Guide puts them,'self tuning algorithms'
> would prevent such a situation from happing.

I wouldn't bank on that in any Oracle first release.

> What am I missing?

Probably not a lot. He's just running out of memory, I guess. You can only dynamically shuffle so far... once all the other pools have pinched their bit, and it's in use, you can't start spooning out extra dollops to the large pool.

Regards
HJR  
> Cheers,
> Holger
Received on Thu Oct 28 2004 - 04:01:13 CDT