Oracle Database Discussions

Hello,

I am receiving the following error when running sql code that one of our applications generates:

ORA-04031: unable to allocate 83236 bytes of shared memory ("shared pool", "SELECT..."sql area", "optdef : qcsocrop")

So far I have tried increasing the size of the shared pool, the Large pool, java pool, buffer cache & the shared_pool_reserved_size but the sql will still not run. They are currently set to:

Shared Pool 256MB
Buffer Cache 64MB
Large Pool 256MB
Java Pool 40MB
Aggregate PGA Target 64MB
shared_pool_reserved_size 67MB

We are running Oracle 9.2.0.1.0 on Windows, with 2GB RAM. There is little else running on the Server. Flushing the shared pool temporarily fixes the issue for some code the application runs, but not all. We have also tried rebooting the Server but this does not help.

Reading through the "Troubleshooting and Diagnosing ORA-4031 Error" doc gives me some ideas as to the issue, but I cannot be absolutely sure what is causing this.

Does our Server seem underpowered or should we crank up the memory allocated to the Shared Pool? Are there any other parameters that need changing - some or the cursor related ones perhaps?

The sql code that is causing the error is definitely using a lot memory (128mb sharable_mem in v$sqlarea last time I looked) so I am wondering if it is not using Bind Variables?

I would greatly appreciate any input people have on possible causes and tuning advice regarding this issue.

Thanks