It is actually easier to use bind variables and a LOT more scalable. Maybe
the developers aren't competent.
Jim
"Ray Teale" <ray_at_BLAHholly.com.au> wrote in message
news:bXzEb.267$g21.6776_at_nnrp1.ozemail.com.au...
> 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 - 09:21:32 CST
