Re: BAMIMA:Bam Buffer

Thanks for all your input.

It is definitely a problem with fragmentation caused by the app developers using dynamic sql without bind variables.

A more thorough seach of the sqlarea showed lots of examples of the same sql statement with different constant values.

Guess I'll have to convince the app developers to rewrite some of this. CURSOR_SHARING=FORCE may work as a temporary fix, but I'm not game to try it on the production system.

Regards

Ray

<sybrandb_at_yahoo.com> wrote in message
news:a1d154f4.0312190059.96b97b7_at_posting.google.com...
> "Ray Teale" <ray_at_BLAHholly.com.au> wrote in message
news:<DruEb.74$g21.2793_at_nnrp1.ozemail.com.au>...
> > Version : 8.1.7.0.0
> > Sun Solaris 2.8
> >
> > I'm getting the old BAMIMA:Bam Buffer error regularly on my database.
> > I have tried pinning packages to no avail. I have also increased the
size
> > of the shared pool size and it is now three times the initial size
(About
> > 300M). All that is happening is that it is taking longer between
failures.
> >
> > I suspect that the application which is using the database may have a
lot of
> > dynamic SQL. I'm wondering if this might be an issue as the dynamic
> > statements do not have bind variables.
> >
> > I'm confused about the error because I thought select statements would
be
> > aged out rather than the shared pool run out of memory.
> >
> > Can anybody shed light?
> >
> > Regards
> >
> > Rau
>
>
> Dynamic sql will definitely exhaust the sql area. For statements
> without bind variables, each statement will be loaded in the sqlarea.
> Eventually they will be aged out, but if someone executes the
> statement again it will be reloaded.
> Such scenario easily results in fragmentation of the shared pool, as
> likely not all the statements have the same statement length. In which
> you are left with ora-4031.
> As far as I know, apart from throwing the app out of the window/start
> using bind variables, the only other method to alleviate the problem
> is to make sure the cursor_sharing parameter is set to FORCE. This
> will have Oracle set up bind variables and translate the hardcoded
> literals in the statement into bind variables.
>
> Regards
>
> Sybrand Bakker
> Senior Oracle DBA
Received on Fri Dec 19 2003 - 04:00:09 CST