Re: Oracle internal memory management
Date: 1996/10/29
Message-ID: <555i30$jgd_at_inet-nntp-gw-1.us.oracle.com>#1/1
In article <553elt$qcv_at_charnel.ecst.csuchico.edu>, chafey_at_ecst.csuchico.edu (Chris Hafey) 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.
You don't mention what version of Oracle you are using. Oracle7 Release 7.3 has a number of improvements to the shared cursor code, which reduces 4031 errors.
|>
|> 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?
Not necessarily. See below.
|> 2) Is there any problem with constantly generating unique SQL?
No, as long as you use bind variables. 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)
Oh yes. This is the whole point of bind variables! If you use bind variables, then the two statements can be shared. This means that it will only be parsed once (the first time). The second time the statement is issued (with different values of the bind variables perhaps) it not only will be shared, but it will not have to be parsed again.
|> 4) Does Oracle really keep each unique SQL statement cached in memory? Why?
Yes. The reason is that typically customers have lots of users, all running the same program. You don't mention if you are doing that here. In that case, all of the programs will be issuing the same SQL statements (with different values of the bind variables) and thus all programs can share the same cursor (which was only parsed once, I might add).
|>
|> 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.
Opening and closing the cursor won't help much. In fact, it will add unnecessary overhead. When you reuse the cursor for a different statement, the original statement is canceled (in the sense of ocan()). This signals that you are no longer interested in the cursor, and Oracle can free resources associated with it (temp space, locks, etc.) The original SQL statement, though, may remain in the shared pool. So you can issue a cancel command after you finish with a statement, especially if your application then does a bunch of non-SQL processing before it issues the next SQL statement.
|>
|> Thanks!
|>
|> Chris Hafey
|>
|> --
|> chafey_at_ecst.csuchico.edu http://www.ecst.csuchico.edu/~chafey
Scott Urman Oracle Corporation surman_at_us.oracle.com
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2 Published by Oracle Press - http://www.osborne.com/oracle/index.htm
"The opinions expressed here are my own, and are not necessarily that of Oracle Corporation"
Received on Tue Oct 29 1996 - 00:00:00 CET