Re: Shared Memory. What's it all about? - initlive.ora (0/1)

In article <34cb6a26.4627651_at_news.demon.co.uk>, Steve_at_back2front.demon.co.uk wrote: OK,

Firstly, you are using 7.2 which has memory problems. I believe there are some patches available from oracle that help a little.

Secondly, the 4031 related to your SHARED_POOL. Whenever you use a stored procedure, a cursor, some SQL; it needs to find space in the SHARED_POOL.

You could try increasing you SHARED_POOL parameter in the init{SID}.ora file. This will, of course, take more of your real memory.

You say that you are building all of your SQL statements and sending them to ORACLE. I assume that you are concatenating strings dynamically and sending them through ODBC.

e.g.

SELECT col1, col2 FROM table1 WHERE col3 = 'XXXXX';
SELECT col1, col2 FROM table1 WHERE col3 = 'YYYYY';
SELECT col1, col2 FROM table1 WHERE col3 = 'ZZZZZ';

Now to Oracle, these statements above are very different, and both must be stored in the SHARED_POLL separately. However, if you can use BIND variables, you will greatly reduce the overhead.

SELECT col1, col2 FROM table1 WHERE col3 = :BIND_VARIABLE

If you cannot use bind variable (I seem to remember some ODBC problem with them) you may need to clear-out the SHARED_POOL on regular occasions using:

ALTER SYSTEM FLUSH SHARED_POOL; regs

Neil Chandler

>Hello,
>
>I would be very grateful if some clever soul could shed some light on
>how I might resolve a Shared Memory error which just refuses to go
>away!!
>
>The error is of the form -
>ORA-04031: unable to allocate ?? bytes of shared memory ("unknown
>object", "library cache","kgldahds")
>,where ?? is some arbitrary number (seemingly picked at random by
>oracle :) )
>
>And here's the background -
>1. Oracle version 7.2.2.3
>2. An HP Server running Unix 10.2
>3. Server memory 128M
>4. Running on a Novell network.
>5. Windows 3.11/95 clients
>4. INIT.ORA (Please see attached file)
>
>I'm accessing the server via Oracle Objects from Visual Basic 4.
>The app is run after hours when there is maybe only two or three
>connections to oracle.
> A basic SQL statement is built and sent to the Server by the VB app.
Received on Fri Jan 16 1998 - 00:00:00 CST