?
Solved

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

Posted on 2005-03-30
7
Medium Priority
?
2,157 Views
Last Modified: 2008-03-06
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...

0
Comment
Question by:JR2003
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 6

Expert Comment

by:Jankovsky
ID: 13662445
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
 
LVL 48

Expert Comment

by:schwertner
ID: 13662886
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
 
LVL 18

Author Comment

by:JR2003
ID: 13663211
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 6

Assisted Solution

by:Jankovsky
Jankovsky earned 1000 total points
ID: 13669387
It seems it would deal with it, but you have to increase SHARED_POOL_SIZE to give him enaugh space.
0
 
LVL 48

Accepted Solution

by:
schwertner earned 1000 total points
ID: 13670428
The problem is not in the query. The problem is in the fragmentation of SHARED POOL.
0
 
LVL 18

Author Comment

by:JR2003
ID: 13670445
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
 
LVL 6

Expert Comment

by:Jankovsky
ID: 13670460
Usage of Shared pool depends on size of sql strings (contains sqls).
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question