ORA-04031: unable to allocate 64 bytes of shared memory (Microsoft OLE DB error -2147467259)

We are running a very large sql statement and getting this error from one of our databases.

Microsoft OLE DB Provider for ODBC Drivers : error -2147467259  [Microsoft][ODBC driver for Oracle][Oracle]ORA-04031: unable to allocate 64 bytes of shared memory ("shared pool", select distinct ...",sql area", "optdef : apanlg")

I think it is because the sql statement is so long. I'd like to know what needs to be done in order to allow the statement to run?

Is there any limit on the maximum length of and sql statement in Oracle?

The version of Oracle being used is 8.1.

Thanks...

LVL 18
JR2003Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
schwertnerConnect With a Mentor Commented:
The problem is not in the query. The problem is in the fragmentation of SHARED POOL.
0
 
JankovskyCommented:
It's not cause of the length of sql statement, it would be another error message.

Try to increase a memory allocated as  SHARED_POOL_SIZE in the init<SID>.ora initialization file and restart the database.
0
 
schwertnerCommented:
Most common ORA-4031 occurrences are related to the SHARED POOL SIZE, therefore the diagnostic steps provided will mostly address issues related to the shared pool. For other areas like large_pool or java_pool where the memory allocation algorith is simpler, normally the error is caused by an undersized structure.

ORA-04031 error can be due to either an inadequeate sizing of the SHARED POOL size or due to heavy fragmentation leading the database to not finding large enough chuncks of memory.
Inadequate Sizing: The first thing is determining if the ORA-04031 error is a result of lack of contiguous space in the library cache by verifying the following:
REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is < 
SHARED_POOL_RESERVED_MIN_ALLOC
or
REQUEST_FAILURES is 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC
If this is the case, consider lowering SHARED_POOL_RESERVED_MIN_ALLOC to
allow the database putting more objects into the shared pool reserved space and then increase the
SHARED_POOL_SIZE if the problem is not resolved.
Fragmentation: If this is not the case, then you must determine if the ORA-04031 was a result of fragmentation in the library cache or in the shared pool reserved space by following this rule:
REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is > 
SHARED_POOL_RESERVED_MIN_ALLOC.
To resolve this  consider increasing SHARED_POOL_RESERVED_MIN_ALLOC to lower
the number of objects being cached into the shared pool reserved space and
increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE to increase the
available memory in the shared pool reserved space.
·      Small shared pool size

In many cases, a small shared pool can be the cause of the ORA-04031 error.
The following information will help you to adjust the size of the shared pool:
 
o      Library Cache Hit Ratio
The hit ratio helps to measure the usage of the shared pool based on how many times a SQL/PLSQL statement needed to be parsed instead of being reused. The following SQL statement help you to calculate the library cache hit ratio:
SELECT SUM(PINS) "EXECUTIONS",
            SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
            FROM V$LIBRARYCACHE;
If the ratio of misses to executions is more than 1%, then try to reduce the library cache misses by increasing the shared pool size.

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
JR2003Author Commented:
The reason I'm pretty sure it's the actual length of the query is that the database works fine with all queries execpt one that is generated by an program and is about 200k Bytes in length. I know it's very long but is this something Oracle should be able to deal with?
0
 
JankovskyConnect With a Mentor Commented:
It seems it would deal with it, but you have to increase SHARED_POOL_SIZE to give him enaugh space.
0
 
JR2003Author Commented:
Could fragmentation of SHARED POOL be caused by execution of very large sql strings?
I know it stored the sql in the sql area view is this is the shared pool?
0
 
JankovskyCommented:
Usage of Shared pool depends on size of sql strings (contains sqls).
0
All Courses

From novice to tech pro — start learning today.