Re: ORA-00604, ORA-04031

On Dec 4, 4:34 am, Steve Robin <ocma..._at_gmail.com> wrote:
> On Dec 3, 5:07 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
>
>
> > On Dec 3, 10:53 am, Steve Robin <ocma..._at_gmail.com> wrote:
>
> > > ORA-00604: error occurred at recursive SQL level 1
> > > ORA-04031: unable to allocate 3896 bytes of shared memory ("shared
> > > pool","select file#, block#, ts# fr...","sga heap(1,0)","kglsim object
> > > batch")
>
> > > I got these error in oracle 10g, I was not able to investigate the
> > > reason, because I was not able to connect with database. I needed to
> > > kill process and then restart the database.
>
> > > I got following line in mmon trc file.
>
> > > SGA POLICY: Cache below reserve and cant get memory from any other
> > > component.
>
> > > sga_target = 380M
> > > sga_max_size = 380M
>
> > > Oracle : 10.2.0.2
> > > OS : SunOS 5.9
>
> > > Please let me know if I cann't find the cause for it, as well
> > > solution.
>
> > The reason is fairly clear: your shared pool isn't large enough. The
> > cause of _that_ may be unknown, however.
>
> > Have you monitored v$sgastat lately? You'll find information to help
> > you in that view, including how (generally speaking) your various
> > pools are allocated and how much free space you have in each.
>
> > When do you see these errors? A bit more detail regarding the
> > circumstances surrounding these occurrences would be most helpful.
>
> > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks David.
>
> I didn't checked v$sgastat, after restart but don't you think so that
> it can give us current status of sga, not the past,
> as well as when I checked ASMM setting shared_pool_size was 0 in init
> file but _shared_pool_size was 192M. Is it a problem?
> I was not able to trace on database level as noone was able to connect
> with database.
>
> Regards
> Steve- Hide quoted text -
>
> - Show quoted text -

Monitoring v$sgastat is intended to show you what's going on now, not then, and possibly can shed light on what may be going 'wrong' to cause your problem to occur. I never said to query v$sgastat to see what DID happen; you'll never get that information. Your error clearly indicates a shared_pool_size problem; monitoring v$sgastat MIGHT show you where all of that memory is going BEFORE it's all gone and you again experience this error. Knowing where it's being consumed might lead you to WHAT is consuming it and WHY, and provide insight into how to correct the problem, such as poor application design, etc.

David Fitzjarrell Received on Tue Dec 04 2007 - 09:35:00 CST