Re: ORA-04031 and shared pool

"Terry Dykstra" <dontreply_tdykstra_at_cfol.ab.ca> wrote in <W28j7.1841$IU3.595152_at_news0.telusplanet.net>:

>If you don't use Java, don't allocate memory for it. You can shrink it
>to 32K:
>java_pool_size = 32768 (on 817 you can set it to 0, but behind the
>scenes Oracle will change it to 32768 anyways)
>
>--
>Terry Dykstra
>Canadian Forest Oil Ltd.
>"Mark D Powell" <mark.powell_at_eds.com> wrote in message
>news:178d2795.0108290604.1ba782a6_at_posting.google.com...
>> Pete Finnigan <pete_at_peterfinnigan.demon.co.uk> wrote in message
>news:<f0cy1PArdLj7EwcU_at_peterfinnigan.demon.co.uk>...
>> > Hi
>> >
>> > You could use
>> >
>> > alter session set events 'immediate trace name library_cache level
>> > 10';
>> >
>> > This will dump the sql from the shared pool
>> >
>> > cheers
>> >
>> > Pete Finnigan
>> > www.pentest-limited.com
>> >
>> > In article <9mgsa2$ie7$1_at_news.online.de>, MT <mtechera_at_wpmc.com>
>> > writes
>> > >Hi Everyone:
>> > >
>> > >I'm working on 8.1.7. I have a SHARED_POOL of 75 MB
>> > >(my SHARED_POOL_RESERVED_SIZE = 7.5 MB) and am still getting that
>dreaded
>> > >ORA-04031. This happens although I can see that the free memory in
>> > >the
>shared
>> > >pool
>> > >is over 20%.
>> > >
>> > >I have tried as an emergency measure to use:
>> > >
>> > >ALTER SYSTEM FLUSH SHARED_POOL;
>> > >
>> > >but this does not reduce the shared memory usage.
>> > >
>> > >Does anyone know why the shared pool may refuse to flush? I have
>> > >tried
>this on
>> > >an
>> > >identical system and the shared pool usage drops immediately.
>> > >
>> > >Does anyone know why, when the shared pool still has over 20% free
>space,
>> > >Oracle gives 04031? I though that it used an LRU algorithm. If this
>> > >is
>the case,
>> > >why aren't older queries simply being aged out?
>> > >
>> > >I am also now in the hunt for failure to use bind variables. Does
>anyone have
>> > >any
>> > >good scripts or links to scripts that can help me pull out
>> > >everything
>in the
>> > >shared pool
>> > >for analysis?
>> > >
>> > >Any help and ideas are welcome.
>> > >
>> > >Regards to all,
>> > >Mario
>> > >
>> > --
>> > Pete Finnigan
>> > IT Security Consultant
>> > PenTest Limited
>> >
>> Mario, we had have the same problem since upgrading to version 8.1.7.
>> We have entered a two tars with Oracle support and they have been
>> little help. We have verified all our shared memory settings. We ran
>> the SQL Oracle provided to see SQL that is causing other SQL to flush
>> out of the pool and no luck there. We upped our shared pool by 20M
>> over version 8.0.5 (not including separate 20M for java, which we do
>> not use) and no help. We show 20% free when jobs start dieing.
>>
>> We can run about three weeks before we have a problem. But once we
>> have a problem jobs just die with 04031 or 06508 and the only way to
>> clear the problem is to bounce the system. Flushing the pool does not
>> work. We pin all our large packages at instance startup plus most of
>> the heavy hit Oracle provided packages. If we do weekly maintenance
>> and bounce the system every week we do not get the errors, but leave
>> the system running for about three weeks and production dies.
>>
>> We are going with the weekend bounce. What a pain.
>>
>> -- Mark D Powell --
>
>
>

I don't think the original post mentioned which patch level of 8.1.7 was being used. There are numerous bug reports of a memory leak in 8.1.7.1 fixed by the _db_handles_cached =0 workaround, or the one off patch for 8.1.7.1. Also fixed in 8.1.7.2.

Search metalink for 4031 and 8.1.7.

Scott Received on Wed Aug 29 2001 - 11:41:20 CDT