Re: Oracle internal memory management

chafey_at_ecst.csuchico.edu (Chris Hafey) wrote:

> Hello,
> We have been getting 4031 (unable to allocate shared memory) errors. The
> only help for this error is "increase shared memory". We have tried this,
> but it doesn't solve the problem.
>
The advice should be 'Increase Shared Pool size'. You do this by increasing the SHARED_POOL_SIZE parameter in your init.ora file.

> 2) Is there any problem with constantly generating unique SQL?

Possibly. See below.

> 3) Is there any problem putting the column values in the SQL instead of
> using bind variables? (SET FOO='bar' vs SET FOO=:foovar)

Again, possibly, See Below.

> 4) Does Oracle really keep each unique SQL statement cached in memory? Why?
>
Yes. Up to a point. Oracle will keep as many copies of SQL statements as can be held in SHARED_POOL_SIZE bytes worth of memory. The statements are aged out using a Least Recently Used algorithm. So, if you're writing LOTS of unique SQL statements it's possible that you're ageing out even those statements that can be reused.

Why does Oracle keep unique SQL statements cached? Basically because Oracle has no way of knowing what is a unique statement and what can be reused. It makes an effort to keep as much as possible because, if it needs to be reused you DON'T want the overhead of re-parsing the statement, which can add 100-500% to your execution time.

> We suspect that Oracle is having problems because we have too much dynamic
> SQL. Before we spend the time making sure every SQL statement uses bind
> variables, I wanted to verify that this is the problem. We thought that
> opening and closing the cursor might help, but again I wanted to see what
> other people had to say.
>

The best advice I can give would be for you to read the Oracle Server Application Developers Guide. It's part of the standard Oracle manual set.

Beyond that I would say the best thing any Oracle developer can do is to parameterise their code wherever possible. Use bind variables EVERYWHERE. It will make your code smaller, more efficient, easier to read, more modular and will increase your application's performance no end.

--
Peter Moore.
DBA, IS Ops,
Sequent Computer Systems Ltd, Weybridge, UK.
"I never forget a face, but in your case I'll be happy to make an exception"