Re: BAMIMA:Bam Buffer

You can dynamically build the where statement AND use bind variables. I've worked places where we did that and it works just fine. Also Sieble does that. (not that Sieble does a lot right, but at least they use bind variables) If you use bind variables in an API - it doesn't have to be something dogy at all - (and I've done that and it works rather well, so don't use that as an excuse NOT to use bind variables.) fashion you will find that in an application you end up using only so many statements. (people in general don't actually use all permutations.

eg
select ... from .. where x=:hvbind1 and y=:hvbind2 (assuming someone is seaching on x and y)
Jim

"Ray Teale" <ray_at_BLAHholly.com.au> wrote in message news:%7sFb.35$VJ3.2763_at_nnrp1.ozemail.com.au...
> I'm not sure I buy that static sql can always be used instead of dyanmic
sql
> as some people have suggested.
>
> Dynamic sql which builds up "WHERE" clauses based on input values is very
> useful, particularly when building APIs.
> The alternative will be to nvl something to itself - ala "Where x =
> nvl(:inputval,x)" which seems pretty dodgy. Of course the developers can
> use the USING clause to put bind variables into the dynamic sql.
>
> On another note - I'm surprised that Oracle doesn't age out statements in
> the shared pool rather than failing with this fragmentation error. It has
> been suggested that the 8.1.7.4 patch contains a number of memory
management
> improvements - so I will start by applying this.
>
> Finally - I've managed to get the developers to revist their code.
>
> Thanks again for all your input
>
> Ray
>
>
> "Andy Spaven" <andy.spaven_at_eps-hq.co.uk> wrote in message
> news:HvFEb.63$M4.46_at_newsr2.u-net.net...
> > Jim wrote: -
> >
> > "It is actually easier to use bind variables"
> >
> > I wish this were always true! but not it isn't always and not due to
> Oracle
> > limitations or anything - just due to intransigent or stubborn
developers
> or
> > development managers.
> >
> > Bind vars is the way to go if you can force that change. cursor sharing
> is
> > the dba's best response to stupid development decisions. Perhaps Ray
> could
> > use cursor sharing = force to demonstrate the benefits in order to add
> > weight to the move to bind variables but it could backfire - management
> will
> > just say "ah well that fixes it just as good as bind vars so we'll not
> > bother changing the code" :-). Not that I've been down that road myself
> > obviously - all just theoretical management decision bashing :-))).
> >
> > Andy
> >
> >
> > "Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in
message
> > news:0IEEb.603343$Fm2.547536_at_attbi_s04...
> > > 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 Sun Dec 21 2003 - 21:52:54 CST