Re: Oracle internal memory management

In article <553elt$qcv_at_charnel.ecst.csuchico.edu>, Chris Hafey <chafey_at_ecst.csuchico.edu> writes
>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.

There is a known bug in several Oracle versions (7.1.6 and early 7.2.? I think) where the shared pool becomes overly fragmented leading to this problem. There are patches available. The workaround is to execute an 'alter system flush shared pool' command as often as necessary to prevent the problem. You lose the advantage of cached SQL in the shared pool this way, but you don't seem to be using it anyway.
>
> Our application is written in C using OCI libraries on a HPUX machine. The
>application opens a single connection to the database and opens a single
>cursor. The cursor is reused (never closed) for every SQL command. The
>application is loading data into the database on a continuous basis. The
>application generates unique sql for almost every SQL command.
>
>1) Is there any problem with reusing the same cursor for the duration of the
> application?

No.
>2) Is there any problem with constantly generating unique SQL?
 Yes. 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)
Yes. The whole idea of the shared pool is so that many users running the same programs (as in a large OLTP system) share the same pre-parssed SQL and avoid the overhead of parsing everytime. To make this times aving worthwhile, the process of determining what constitutes identical SQL statements has to be fairly simple. In order for SQL statements to be counted as the same the have to be textually identical, down to case and spacing. That is why bind variables are better than constants.

>4) Does Oracle really keep each unique SQL statement cached in memory? Why?
>
Yes. SQL statements occupy the cache and are discarded on a least recently used basis. If it wasn't for the bug this would be transparent to you apart from the performance hit.

>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.
>
>Thanks!
>
>Chris Hafey
>
 Please use comp.databases.oracle.server for this type of query

-- 
Jim Smith