ORA-04030 out of process memory error

[rwozniak71]

Hey Folks,

Does anyone have experience with this error (I pulled a posting off an Oracle site):

ORA-04030
ORA-04030: out of process memory when trying to allocate string bytes (string,string)
Cause: Operating system process private memory has been exhausted.
Action: See the database administrator or operating system administrator to increase process memory quota. There may be a bug in the application that causes excessive allocations of process memory space.

We get this error ocassionaly when we query a table with 150 million rows or so and specify at least 2 IN clauses with a large number of values (300 or so).

The question I have is how one would go about altering process memory space. Specifically, what parameters does the DBA need to update? Currently the DBA is not being very helpful telling us that process memory is pinned on our server. Does anyone know how I could verify this? We also think it could be a third-party vendor's tool that is causing problems. How can I test this?

Thanks in advance,

Rob

 

[sem]

I had a similar problem with hash joins. I didn't chage HASH_AREA_SIZE or HASH_JOIN_ENABLED but rather revised the execution plan and got my query working about 100 times faster and without errors A tip: check whether statistics is up to date.

Regards, Dima

http://seminihin.narod.ru

 

[rwozniak71]

Thanks Dima,

I found this white paper on otn that suggests looking at the SORT_AREA_SIZE parameter when working with these types of errors:

http://www.oracle.com/technology/oramag/oracle/02-jul/o42tuning.html

I've updated the Statistics to no avail. I'm not able to perform an explain plan-the error comes up when I run the explain plan as well.

I think I'll see if we can play with sizing the SORT_AREA_SIZE parameter.

Rob

 

[dbtoo2001]

If this is being done with a particular userid, setup a login trigger to trace the session. Then you should be able to use tkprof and see where the problem arises. Maybe you are using a view, which is using functions, etc.

 

[rwozniak71]

I think it's a problem with the number of rows in the table we're querying. I have a smaller table (fewer rows and fewer columns) but all the same keys so I can "replace" it in my queries. Typically I get no problems.

I'm thinking some setting in the SGA is not large enough. We tried changing the SORT_AREA_SIZE parameter but saw no improvement. Maybe there is a similar parameter for 'HASH_AREA_SIZE'? There are a lot of NESTED LOOPS and HASH JOINS in the explain plan.

Any contributions appreciated,

Rob

 

[dbtoo2001]

is this a unix server? what are the values in /etc/system for oracle?
This is what ours are set to, yours will be different.
* max size (bytes) of a shared memory segment
set shmsys:shminfo_shmmax=4294967295
* min size (bytes) of a shared memory segment
set shmsys:shminfo_shmmin=1
* max number of shared memory identifiers
set shmsys:shminfo_shmmni=1000
* max number of shared memory segments connected to a single process
set shmsys:shminfo_shmseg=200
* max number of semaphores in system
set semsys:seminfo_semmns=12800
* number of semaphore set identifiers in system
set semsys:seminfo_semmni=65535
* max number of semaphores in a set
set semsys:seminfo_semmsl=3200
* undo structures
set semsys:seminfo_semmnu=3200
* max undo entries per process
set semsys:seminfo_semume=3200
* semaphor maximum value
set semsys:seminfo_semvmx=65534

 

[sem]

How did you run explain plan? I can't imagine that this operation (that doesn't need statement execution) might lead to the same error. But in any case, try to add FIRST_ROWS hint to minimise the probability of using HJ instead of NL. I'd also suggest to avoid using IN constructs, because according to my own experience in many cases they are falsely executed using HJ instead of much fatser NL, especially with 8i optimizer. Try to replace them with EXISTS or ordinary joins.

Regards, Dima

http://seminihin.narod.ru

 

[rwozniak71]

Thanks Everyone,

The problem is we're using a third party tool to generate the queries and we can't customize the query at all. So using hints and avoiding use of 'IN' to specify ranges won't work.

I get the error when I run an explain plan using the cost based optmizer.

We are on an AIX machine so I will compare some the parameters dbtoo gave me.

Thanks again,

Rob

 

[sem]

I run an explain plan using the cost based optmizer

Could you post an exact command you called? As well as exact error code/text.

Regards, Dima

http://seminihin.narod.ru

 

[rwozniak71]

OK Dima,

Here's the query (generated by Hyperion EIS):

SELECT AM.ACCT_MEMBER
, LM.LOC_MEMBER
, SFD.DEPARTMENT
, SFD.CATEGORY
, SFD.JE_SOURCE
, SFD.SL_BATCH
, SFD.JE_JEADER_DESC
, SFD.POSTED_AMT
, TM.TIME_MEMBER
, SFD.POSTED_DATE
, SFD.ACCOUNTING_PERIOD
, SFD.COMPANY
, SFD.ACCRUAL
, SFD.SOURCE
, SFD.EVENT
, SFD.SL_REASON_CD
, SFD.DATA_CODE
, SFD.UNITS
, SFD.DEBIT_AMT
, SFD.CREDIT_AMT
, SFD.DIVISION
, SFD.SL_FOOD_NONFOOD
, SFD.DATA_SOURCE
, AM.ACCT_MEMBER_DESC
, TM.TIME_MEMBER_DESC
, SM.SCEN_MEMBER
, BM.BOOK_MEMBER
, DM.DEPT_MEMBER_DESC
, LM.LOC_MEMBER_DESC
, SFD.FACT_DETAIL_KEY
, SFD.LAST_REBUILD_DATE
FROM
VW_SL_FACT_DETAIL SFD
, VW_ACCOUNT_MEMBERS AM
, VW_TIME_MEMBERS TM
, VW_SCENARIO_MEMBERS SM
, VW_BOOK_MEMBERS BM
, VW_LOCATION_MEMBERS LM
, VW_DEPARTMENT_MEMBERS DM
WHERE
SFD.ACCT_MEMBER_KEY = AM.ACCT_MEMBER_KEY
AND
SFD.TIME_MEMBER_KEY = TM.TIME_MEMBER_KEY
AND
SFD.SCEN_MEMBER_KEY = SM.SCEN_MEMBER_KEY
AND
SFD.BOOK_MEMBER_KEY = BM.BOOK_MEMBER_KEY
AND
SFD.LOC_MEMBER_KEY = LM.LOC_MEMBER_KEY
AND
SFD.DEPT_MEMBER_KEY = DM.DEPT_MEMBER_KEY
AND
(AM.ACCT_MEMBER = ( 'A755008'))
AND
(TM.TIME_MEMBER = ( 'PC09'))
AND
(SM.SCEN_MEMBER = ( 'TY'))
AND
(BM.BOOK_MEMBER = ( 'DOLLARS'))
AND -- 65 departments
(DM.DEPT_MEMBER IN ( 'DEPT001XXX', 'DEPT002XXX' , 'DEPT003XXX' , 'DEPT004XXX' , 'DEPT005XXX' , 'DEPT006XXX' ,
'DEPT007XXX' , 'DEPT009XXX' , 'DEPT010XXX' , 'DEPT011XXX' , 'DEPT012XXX' , 'DEPT013XXX' , 'DEPT014XXX' ,
'DEPT016XXX' , 'DEPT017XXX' , 'DEPT018XXX' , 'DEPT019XXX' , 'DEPT020XXX' , 'DEPT021XXX' , 'CAT022000' ,
'CAT022999' , 'CAT022001' , 'CAT022002' , 'CAT022003' , 'CAT022004' , 'CAT022005' , 'CAT022006' ,
'CAT022007' , 'CAT022008' , 'CAT022009' , 'CAT022010' , 'CAT022011' , 'CAT022012' , 'CAT022013' , 'CAT022014' ,
'CAT022015' , 'CAT022016' , 'CAT022017' , 'CAT022018' , 'CAT022019' , 'CAT022020' , 'CAT022021' , 'CAT022022' ,
'CAT022023' , 'CAT022025' , 'CAT022026' , 'CAT022027' , 'CAT022028' , 'CAT022029' , 'CAT022030' , 'CAT022031' ,
'CAT022032' , 'DEPT023XXX' , 'DEPT024XXX' , 'CAT025000' , 'CAT025999' , 'CAT025501' , 'CAT025502' , 'CAT025503' ,
'CAT025504' , 'CAT025505' , 'CAT025506' , 'CAT025507' , 'CAT025510' , 'CAT025511' ,'CAT025512' , 'CAT025513' ,
'CAT025514' ))
AND
(LM.LOC_MEMBER IN ( -- 274 locations
'LOC0001', 'LOC0002' , 'LOC0006' , 'LOC0007' , 'LOC0008' , 'LOC0009' , 'LOC0010' ,
'LOC0011' , 'LOC0013' , 'LOC0015' , 'LOC0017' , 'LOC0018' , 'LOC0019' , 'LOC0020' , 'LOC0021' , 'LOC0022' ,
'LOC0023' , 'LOC0024' , 'LOC0025' , 'LOC0026' , 'LOC0027' , 'LOC0028' , 'LOC0029' , 'LOC0030' , 'LOC0031' ,
'LOC0032' , 'LOC0033' , 'LOC0034' , 'LOC0035' , 'LOC0036' , 'LOC0037' , 'LOC0038' , 'LOC0039' , 'LOC0040' ,
'LOC0041' , 'LOC0044' , 'LOC0045' , 'LOC0046' , 'LOC0047' , 'LOC0048' , 'LOC0049' , 'LOC0050' , 'LOC0051' ,
'LOC0052' , 'LOC0053' , 'LOC0054' , 'LOC0055' , 'LOC0056' , 'LOC0057' , 'LOC0058' , 'LOC0059' , 'LOC0060' ,
'LOC0061' , 'LOC0062' , 'LOC0063' , 'LOC0064' , 'LOC0066' , 'LOC0067' , 'LOC0068' , 'LOC0069' , 'LOC0070' ,
'LOC0071' , 'LOC0072' , 'LOC0073' , 'LOC0074' , 'LOC0075' , 'LOC0076' , 'LOC0077' , 'LOC0078' , 'LOC0079' ,
'LOC0084' , 'LOC0085' , 'LOC0086' , 'LOC0091' , 'LOC0092' , 'LOC0093' , 'LOC0094' , 'LOC0096' , 'LOC0097' ,
'LOC0099' , 'LOC0100' , 'LOC0102' , 'LOC0105' , 'LOC0106' , 'LOC0107' , 'LOC0108' , 'LOC0109' , 'LOC0110' ,
'LOC0111' , 'LOC0112' , 'LOC0114' , 'LOC0115' , 'LOC0116' , 'LOC0117' , 'LOC0118' , 'LOC0119' , 'LOC0120' ,
'LOC0121' , 'LOC0122' , 'LOC0123' , 'LOC0124' , 'LOC0125' , 'LOC0126' , 'LOC0127' , 'LOC0128' , 'LOC0129' ,
'LOC0130' , 'LOC0131' , 'LOC0132' , 'LOC0133' , 'LOC0134' , 'LOC0135' , 'LOC0136' , 'LOC0138' , 'LOC0139' ,
'LOC0140' , 'LOC0141' , 'LOC0142' , 'LOC0143' , 'LOC0144' , 'LOC0145' , 'LOC0146' , 'LOC0148' , 'LOC0150' ,
'LOC0151' , 'LOC0301' , 'LOC0302' , 'LOC0303' , 'LOC0306' , 'LOC0307' , 'LOC0308' , 'LOC0309' , 'LOC0310' ,
'LOC0311' , 'LOC0312' , 'LOC0313' , 'LOC0314' , 'LOC0315' , 'LOC0316' , 'LOC0319' , 'LOC0320' , 'LOC0500' ,
'LOC0098' , 'LOC0404' , 'LOC0405' , 'LOC0408' , 'LOC0411' , 'LOC0413' , 'LOC0414' , 'LOC0416' , 'LOC0420' ,
'LOC0421' , 'LOC0422' , 'LOC0426' , 'LOC0428' , 'LOC0434' , 'LOC0436' , 'LOC0437' , 'LOC0438' , 'LOC0439' ,
'LOC0440' , 'LOC0444' , 'LOC0446' , 'LOC0447' , 'LOC0451' ,
'LOC0454' , 'LOC0466' , 'LOC0467' , 'LOC0470' , 'LOC0489' , 'LOC0490' , 'LOC0491' , 'LOC0492' , 'LOC0493' ,
'LOC0494' , 'LOC0495' , 'LOC0496' , 'LOC0497' , 'LOC0498' , 'LOC0499' , 'LOC0800' , 'LOC0801' , 'LOC0820' ,
'LOC0840' , 'LOC0700' , 'LOC0701' , 'LOC0702' , 'LOC0703' , 'LOC0704' , 'LOC0720' , 'LOC0721' , 'LOC0722' ,
'LOC0723' , 'LOC0725' , 'LOC0726' , 'LOC0727' , 'LOC0728' , 'LOC0740' , 'LOC0741' , 'LOC0900' , 'LOC0902' ,
'LOC0920' , 'LOC0940' , 'LOC0781' , 'LOC0880' , 'LOC0881' , 'LOC0980' , 'LOC0981' , 'LOC0945' , 'LOC0950' ,
'LOC0951' , 'LOC0952' , 'LOC0088' , 'LOC0089' , 'LOC0090' , 'LOC0670' , 'LOC0671' , 'LOC0672' , 'LOC0673' ,
'LOC0674' , 'LOC0675' , 'LOC0676' , 'LOC0677' , 'LOC0678' , 'LOC0699' , 'LOC0095' , 'LOC0101' , 'LOC0147' ,
'LOC0149' , 'LOC0152' , 'LOC0154' , 'LOC0155' , 'LOC0156' , 'LOC0159' , 'LOC0317' , 'LOC0318' , 'LOC0113' ,
'LOC0153' , 'LOC0157' , 'LOC0160' , 'LOC0161' , 'LOC0162' , 'LOC0165' , 'LOC0167' , 'LOC0169' , 'LOC0321' ,
'LOC0003' , 'LOC0004' , 'LOC0005' , 'LOC0012' , 'LOC0014' , 'LOC0016' , 'LOC0104' , 'LOC0080' , 'LOC0158' ,
'LOC0199' , 'LOC0215' , 'LOC0216' , 'LOC0217' , 'LOC0219' , 'LOC0550' , 'LOC0551' , 'LOC0999' , 'LOC9999' ,
'LOC5996' , 'LOC5997' ))
ORDER BY
1 ASC
, 2 ASC
, 3 ASC
, 4 ASC
, 5 ASC

Here's the error message:

The following error has occurred:

ORA-04030: out of process memory when trying to allocate 146848 bytes (callheap,temporary memory)




Details:
ORA-04030: out of process memory when trying to allocate 146848 bytes (callheap,temporary memory)

 

[sem]

I asked about explain plan command, that according to your post, caused the same error. And how did you gather statistics? BTW what is the summary length of columns you're ordering by (1-5)?

Regards, Dima

http://seminihin.narod.ru