Oracle Database Discussions

I've only recently noted that many of the tests we were running on our Windows XP oracle 10g server were failing from lack of memory. I have performed a lot of tests, added the /3GB parameter to boot.ini, tried many values for
pga_aggregate_target , sga_target and sga_max_size , but I still get the error.

Google searches and parameter tweaking has not helped. We use a great deal of Java stored procedures in this query.

From our Java application's log:

04-12-2008 02:53:52 ERROR (ProcessLauncher.java:31) >> session @9/146 calculatePLC(null,11801,12000) stopped: ORA-04030: out of process memory when trying to allocate 4032 bytes (ioc_make_sub2,UGAClass)
04-12-2008 02:53:52 ERROR (ProcessLauncher.java:31) >>
04-12-2008 02:57:31 ERROR (ProcessLauncher.java:31) >> session @7/143 calculatePLC(null,18002,18201) stopped: ORA-04030: out of process memory when trying to allocate 8288564 bytes (joxp heap,f:Reserved3)
04-12-2008 02:57:31 ERROR (ProcessLauncher.java:31) >>
04-12-2008 03:00:20 ERROR (ProcessLauncher.java:31) >> session @8/145 calculatePLC(null,21402,21601) stopped: ORA-04030: out of process memory when trying to allocate 8388404 bytes (joxp heap,f:Reserved3)
04-12-2008 03:00:20 ERROR (ProcessLauncher.java:31) >>
04-12-2008 03:27:09 ERROR (ProcessLauncher.java:99) session @9/146 calculatePLC(null,11801,12000) stopped: ORA-04030: out of process memory when trying to allocate 4032 bytes (ioc_make_sub2,UGAClass)
session @7/143 calculatePLC(null,18002,18201) stopped: ORA-04030: out of process memory when trying to allocate 8288564 bytes (joxp heap,f:Reserved3)
session @8/145 calculatePLC(null,21402,21601) stopped: ORA-04030: out of process memory when trying to allocate 8388404 bytes (joxp heap,f:Reserved3)

from the bdump/alert_orcl.log

Tue Nov 04 10:58:25 2008
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_1476.trc:
ORA-04030: out of process memory when trying to allocate 38528564 bytes (joxp heap,f:OldSpace)

Tue Nov 04 10:58:30 2008
Thread 1 advanced to log sequence 241
Current log# 3 seq# 241 mem# 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
Tue Nov 04 10:58:39 2008
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_2880.trc:
ORA-04030: out of process memory when trying to allocate 32111412 bytes (joxp heap,f:OldSpace)
...
Thu Dec 04 02:53:34 2008
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_1840.trc:
ORA-04030: out of process memory when trying to allocate 18403380 bytes (joxp heap,f:OldSpace)

Thread 1 cannot allocate new log, sequence 3974
Checkpoint not complete
Current log# 3 seq# 3973 mem# 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
Thread 1 advanced to log sequence 3974
Current log# 1 seq# 3974 mem# 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
Thu Dec 04 02:53:37 2008
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_1840.trc:
ORA-04030: out of process memory when trying to allocate 753120 bytes (pga heap,kco buffer)
ORA-04030: out of process memory when trying to allocate 18403380 bytes (joxp heap,f:OldSpace)

Thu Dec 04 02:53:41 2008
Process startup failed, error stack:
Thu Dec 04 02:53:41 2008
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_psp0_2260.trc:
ORA-27300: OS system dependent operation:spcdr:9261:4200 failed with status: 997
ORA-27301: OS failure message: Overlapped I/O operation is in progress.
ORA-27302: failure occurred at: skgpspawn

Thu Dec 04 02:53:42 2008
Thread 1 advanced to log sequence 3975
Current log# 2 seq# 3975 mem# 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
Thu Dec 04 02:53:42 2008
Process J000 died, see its trace file
Thu Dec 04 02:53:42 2008
kkjcre1p: unable to spawn jobq slave process
...
Thu Dec 04 05:03:46 2008
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_2648.trc:
ORA-04030: out of process memory when trying to allocate 36518964 bytes (joxp heap,f:OldSpace)
...
Thu Dec 04 05:04:23 2008
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_1612.trc:
ORA-04030: out of process memory when trying to allocate 52046388 bytes (joxp heap,f:OldSpace)

Latest pfile I've tried is:
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_file_multiblock_read_count=16

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# Database Identification
###########################################
db_domain=""
db_name=orcl

###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=C:\oracle\product\10.2.0/admin/orcl/bdump
core_dump_dest=C:\oracle\product\10.2.0/admin/orcl/cdump
user_dump_dest=C:\oracle\product\10.2.0/admin/orcl/udump

###########################################
# File Configuration
###########################################
control_files=("C:\oracle\product\10.2.0\oradata\orcl\control01.ctl", "C:\oracle\product\10.2.0\oradata\orcl\control02.ctl", "C:\oracle\product\10.2.0\oradata\orcl\control03.ctl")
db_recovery_file_dest=C:\oracle\product\10.2.0/flash_recovery_area
db_recovery_file_dest_size=2147483648

###########################################
# Job Queues
###########################################
job_queue_processes=10

###########################################
# Miscellaneous
###########################################
compatible=10.2.0.1.0

###########################################
# Processes and Sessions
###########################################
processes=250

###########################################
# SGA Memory
###########################################
sga_target=1677721600
sga_max_size=1677721600

###########################################
# Security and Auditing
###########################################
audit_file_dest=C:\oracle\product\10.2.0/admin/orcl/adump
remote_login_passwordfile=EXCLUSIVE

###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=orclXDB)"

###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=629145600

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_tablespace=UNDOTBS1

--Charles

Edited by: user10601251 on Dec 6, 2008 11:51 PM