BAMIMA:Bam Buffer
Ray Teale
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
Norman Dunbar
> Version : 8.1.7.0.0
You might be better patching to the latest (8.1.7.4) patch level. A
lot of problems are solved between 8.1.7.0 and 8.1.7.4 - at least,
they were for me on HP servers.
Cheers,
Norm.
PS. The email address above no longer works ....
Andy Spaven
I've seen exactly the same issues. In addition to upgrading as Norman
suggests you could try setting the init.ora parameter CURSOR_SHARING=FORCE.
Take a look at issues (in metalink) first though in case you're going to hit
one of the few gotchas left in 8.1.7.4 - there are loads of problems with
this setting in 8.1.7.0 to .3 so don't use it till you've upgraded unless
you're either a) very brave b) very foolish or c) very desperate.
As for why it happens - I think the SQL is aged out but the "holes" left by
these statements are scattered throughout the SGA and are of different sizes
(due to the usually different length of SQL strings when constructing SQL
with literals in the predicates) and so you end up with a highly fragmented
SGA where there is lots of free space but it's all in pieces to small to fit
the statement you want into. Someone on the list might correct me here if
I'm wrong.
Andy
"Ray Teale" <r...@BLAHholly.com.au> wrote in message
news:DruEb.74$g21....@nnrp1.ozemail.com.au...
sybr...@yahoo.com
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
Ray Teale
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
<sybr...@yahoo.com> wrote in message
news:a1d154f4.0312...@posting.google.com...
Jim Kennedy
the developers aren't competent.
Jimnews:bXzEb.267$g21....@nnrp1.ozemail.com.au...
Andy Spaven
"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-downwit...@attbi.net> wrote in message
news:0IEEb.603343$Fm2.547536@attbi_s04...
Ray Teale
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....@eps-hq.co.uk> wrote in message
news:HvFEb.63$M4...@newsr2.u-net.net...
Jim Kennedy
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" <r...@BLAHholly.com.au> wrote in message
news:%7sFb.35$VJ3....@nnrp1.ozemail.com.au...