ORA-04020 Error

[karmst]

HI all,

I was wondering if anyone could help me...i am continually getting then following error:

ORA-04030: out of process memory when trying to allocate 262144 bytes (cursor
work he,kllcqas:kllsltba)

The SQL that is running is very large, and quite complex. When I take the WHERE clause out, it seems to work fine (this is what I would expect)


My INITODSD.ORA file has the following paramaters:

open_cursors = 100
db_block_buffers = 64000
db_writer_processes = 1
shared_pool_size = 157286400
large_pool_size = 31457280


1) Is there anything that I can change to eliminate this error?
2) What does this error REALLY mean?

Thanks in advance,

Keith

[edsperez]

I know I've experienced this type of error before or something related to this.

Try increasing the MAX_CURSOR parameter to 500.

Ed

[edsperez]

I mean the OPEN_CURSOR

Ed

[jdorlon]

I think it is bombing because your query is so long that Oracle is running out of memory trying to parse it and determine an execution plan.

Does the where clause contain something like this :

where some_column in (1,2,3,4,5,6,7....)

except much, much longer?

If so, you should do this instead if possible :

where some_column in (select whatever from wherever)

where the subquery is a select that will produce the list you need.

-John

[hrishy]

INCREASE YOUR MAX OPEN CURSORS PARAMETER IN INIT.ORA TO 150 THIS SHOULD SOLVE YOUR PROBLEM

[sambavan]

have a good high number of open cursors parameter setted up. Then try running the query and see whether it goes successful. If it fails then you might have to increase the shared_pool_size and also make sure that you have a good size of temp table space, if your query were to do some kind of order by clause. Mostly, the OPEN_CURSORS parameter should fix it all. One other thing is that do an explain plan on your query and see how the execution plan goes and you might be able to modify it to use indices and etc

If you encounter any problems, pl post the problem and the error message...

Sam

[karmst]

I increased the OPEN_CURSORS to 500, and I still get the same error message.

The thing is, the Database was running this query fine on Friday, with not even a hint of a problem. Could it be because there are more instances on the AIX machine swallowing all the memory allocation?

I reset my INITODSD.ORA file to what is was before (except for OPEN_CURSORS):

open_cursors = 500
db_block_buffers = 64000
db_writer_processes = 1
dbwr_io_slaves = 2

large_pool_size = 20971520
shared_pool_size = 104857600


Thanks in advance, Keith

[sambavan]

Check your alert log file and let us know of the error that you were getting now after resetting the open_cursors. Also could you let us know of the database version and your db_block_size.

Sam

[karmst]

The error i am getting is still

ORA-04030: out of process memory when trying to allocate 262144 bytes (cursor work he,kllcqas:kllsltba)

The ALERT LOG is doing fine, there are no errors in there - this error arises from SQL*PLUS.

The Database Version is ORACLE 8.1.6, and the

THE DB_BLOCK_SIZE is 2048 - this is merely a development machine.

db_block_buffers = 64000

Thanks again, Keith