Re: ORA-04031 and shared pool

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 --