ORA-12801and ORA-04030

[Ganimede-Dignan]

Hi,

in a BI 7.0 system on Windows 32 with Oracle 10.2.0.2 with patch 17 we can see in SM21 this errors:

Database error 12801 at EXE

> ORA-12801: error signaled in parallel query server

> P003#ORA-04030: out of process memory when trying to allocat

> 64528 bytes (sort subheap,sort key)

Database error 12801

Database error 4030 at EXE

> ORA-04030: out of process memory when trying to allocate

> 260028 bytes (qerbi subheap,kdibhn[] in kdibhopen)

Database error 4030

Database error 4030 at FET

> ORA-04030: out of process memory when trying to allocate

> 123404 bytes (QERHJ hash-joi,kllcqas:kllsltba)

Database error 4030

Database error 4030 at FET

> ORA-04030: out of process memory when trying to allocate

> 123404 bytes (QERHJ hash-joi,kllcqas:kllsltba)

Database error 4030

Database error 4030 at FET

> ORA-04030: out of process memory when trying to allocate

> 123404 bytes (QERHJ hash-joi,kllcqas:kllsltba)

Database error 4030

Database error 4030 at FET

> ORA-04030: out of process memory when trying to allocate

> 123404 bytes (QERHJ hash-joi,kllcqas:kllsltba)

Database error 4030

Have you got any advice for us ?

init<SID>.ora

*._B_TREE_BITMAP_PLANS=FLASE

*._ELIMINATE_COMMON_SUBEXPR=FLASE

*._fix_control='4728348:OFF'

*._in_memory_undo=FALSE

*._index_join_enabled=FALSE

*._optim_peek_user_binds=FALSE

*._optimizer_join_sel_sanity_check=TRUE

*._optimizer_mjc_enabled=FALSE

*._sort_elimination_cost_ratio=10

*._TABLE_LOOKUP_PREFETCH_SIZE=0

*.background_dump_dest='G:\oracle\SID\saptrace\background'

*.bitmap_merge_area_size=33554432

*.compatible='10.2.0'

*.control_file_record_keep_time=30

*.control_files='X:\oracle\SID\origlogA\cntrl\cntrlSID.dbf','W:\oracle\SID\sapdata1\system_1\cntrl\cntrlSID.dbf','K:\oracle\SID\saparch\cntrl\cntrlSID.dbf'

*.core_dump_dest='G:\oracle\SID\saptrace\background'

*.create_bitmap_area_size=33554432

*.db_block_size=8192

*.db_cache_size=1G

*.db_files=254

*.db_name='SID'

*.DB_WRITER_PROCESSES=4

*.dml_locks=4000

*.event='10191 trace name context forever,level 1','10027 trace name context forever, level 1','10028 trace name context forever, level 1','38068 trace name context forever, level 100','10162 trace name context forever, level 1','10629 trace name context forever, level 1','14532 trace name context forever, level 1','10183 trace name context forever, level 1','10191 trace name context forever, level 1','38087 trace name context forever, level 1'

*.fast_start_mttr_target=900

*.FILESYSTEMIO_OPTIONS='SETALL'

*.hash_area_size=20971520

*.java_pool_size=167772160

*.log_archive_dest='K:\oracle\SID\oraarch\SIDarch'

*.LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf'

*.LOG_BUFFER=1048576

*.log_checkpoint_interval=0

*.log_checkpoints_to_alert=TRUE

*.max_dump_file_size='20000'

*.open_cursors=800

*.parallel_execution_message_size=16384

*.parallel_max_servers=40

*.parallel_threads_per_cpu=1

*.pga_aggregate_target=4G

*.processes=230

*.query_rewrite_enabled='FALSE'

*.RECYCLEBIN='OFF'

*.remote_os_authent=TRUE

*.replication_dependency_tracking=FALSE

*.sessions=440

*.sga_max_size=1258291200

*.shared_pool_reserved_size=62914560

*.shared_pool_size=550M

*.sort_area_retained_size=0

*.sort_area_size=10485760

*.star_transformation_enabled='TRUE'

*.statistics_level='TYPICAL'

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=86400

*.undo_tablespace='PSAPUNDO'

*.user_dump_dest='G:\oracle\SID\saptrace\usertrace'

*.workarea_size_policy='AUTO'

Regards.

Ganimede Dignan.

[stefan_koehler · Accepted solution]

Hello Ganimede,

do you run a windows 32-bit system?

If yes, you hit the 2 GB limit. Have you set the /3GB in the boot.ini?

For some solutions or workarounds .. take a look at #335230.

Btw. i would reduce the following => "*.pga_aggregate_target=4G".

Regards

Stefan

[Ganimede-Dignan · Accepted solution]

Hi,

>do you run a windows 32-bit system?

Yes...

>If yes, you hit the 2 GB limit. Have you set the /3GB in the boot.ini?

Yes...

>For some solutions or workarounds .. take a look at #335230.

I read it...

> Btw. i would reduce the following => "*.pga_aggregate_target=4G".

I try... but into 838240 note pga_aggregate_target should be 40% of main memory... we have 16GB so 4Gb sgould be small.

Regards.

[stefan_koehler · Accepted solution]

Hello Ganimede,

> I try... but into 838240 note pga_aggregate_target should be 40% of main memory... we have 16GB so 4Gb sgould be small.

But this would not help you. You cannot allocate 4 GB on windows 32-bit with the designed process structure (one oracle process with different threads).

You can reduce your memory settings or you will run into the ORA-04030.

Regards

Stefan

[Ganimede-Dignan · Accepted solution]

Hi,

>But this would not help you. You cannot allocate 4 GB on windows 32-bit with the designed process >structure (one oracle process with different threads).

>You can reduce your memory settings or you will run into the ORA-04030.

OK, I try to reduce... is 2GB a right value ?

Regards.

Ganimede Dignan.

[Former Member · Accepted solution]

Hi,

check following note might helps you

The maximum 'process address space' on 32-bit MS Windows NT/2000 and Windows 2003 is limited to 2 GB or 3 GB (if the Enterprise Edition on NT/2000 or Windows 2003 Server Enterprise Edition is being used).

Note 130140 - ORA-4030 on NT, out of process memory

Note 502782 - Composite SAP Note ora-4030

regards,

kaushal

[stefan_koehler · Accepted solution]

Hello,

> OK, I try to reduce... is 2GB a right value ?

Have you take a look at your configuration?

-> *.db_cache_size=1G

-> *.shared_pool_size=550M

These two values are already 1.55 GB (with a a sga max size of 1.2 GB)..

You should fix your settings at first and think about in which areas you need the memory. (take a look at the wait events, and so on..)

Regards

Stefan

[Ganimede-Dignan · Accepted solution]

Hi,

>Have you take a look at your configuration?

>-> *.db_cache_size=1G

>-> *.shared_pool_size=550M

>

>These two values are already 1.55 GB (with a a sga max size of 1.2 GB)..

>You should fix your settings at first and think about in which areas you need the memory. (take a look at >the wait events, and so on..)

... ops, so I must change sga_max to "1550M"

... is it OK ?

Regards.

[stefan_koehler · Accepted solution]

Hello,

take a look at your alert.log file. Oracle raises the sga maxsize up to a value where all SGA components will fit in. This value is your sga maxsize. The entries will look like:

>Sun Mar 30 17:41:40 2008

>Specified value of sga_max_size is too small, bumping to 1895825408

But this is only a "cosmetic" problem.

You also have to think about where you want to add the memory (Buffer Cache, Shared Pool, PGA, and so on..)

The sum of all that memory must not reach the 32-bit limit.

Regards

Stefan

[Ganimede-Dignan · Accepted solution]

>take a look at your alert.log file. Oracle raises the sga maxsize up to a value where all SGA components >will fit in. This value is your sga maxsize. The entries will look like:

>>Sun Mar 30 17:41:40 2008

>>Specified value of sga_max_size is too small, bumping to 1895825408

>But this is only a "cosmetic" problem.

Yes, I see this message in allert.log

Re: ORA-12801and ORA-04030

Posted: Apr 1, 2008 2:58 PM

Sun Mar 30 23:44:22 2008

Specified value of sga_max_size is too small, bumping to 1828716544

[stefan_koehler · Accepted solution]

Hello,

ok then you can calculate how much memory you can use for the PGA with your actual SGA settings.

1.7 GB SGA

+ Process local memory (memory that is needed to run shadow / background processes)

+ PGA

= Max. 2 / 3 GB limit

Regards

Stefan

[Ganimede-Dignan · Accepted solution]

What do you think about this parameters ?

Regards.

*._B_TREE_BITMAP_PLANS=FLASE

*._ELIMINATE_COMMON_SUBEXPR=FLASE

*._fix_control='4728348:OFF'

*._in_memory_undo=FALSE

*._index_join_enabled=FALSE

*._optim_peek_user_binds=FALSE

*._optimizer_join_sel_sanity_check=TRUE

*._optimizer_mjc_enabled=FALSE

*._sort_elimination_cost_ratio=10

*._TABLE_LOOKUP_PREFETCH_SIZE=0

*.background_dump_dest='G:\oracle\SID\saptrace\background'

*.bitmap_merge_area_size=33554432

*.compatible='10.2.0'

*.control_file_record_keep_time=30

*.control_files='X:\oracle\SID\origlogA\cntrl\cntrlSID.dbf','W:\oracle\SID\sapdata1\system_1\cntrl\cntrlSID.dbf','K:\oracle\SID\saparch\cntrl\cntrlSID.dbf'

*.core_dump_dest='G:\oracle\SID\saptrace\background'

*.create_bitmap_area_size=33554432

*.db_block_size=8192

*.db_cache_size=1G

*.db_files=254

*.db_name='SID'

*.DB_WRITER_PROCESSES=4

*.dml_locks=4000

*.event='10191 trace name context forever,level 1','10027 trace name context forever, level 1','10028 trace name context forever, level 1','38068 trace name context forever, level 100','10162 trace name context forever, level 1','10629 trace name context forever, level 1','14532 trace name context forever, level 1','10183 trace name context forever, level 1','10191 trace name context forever, level 1','38087 trace name context forever, level 1'

*.fast_start_mttr_target=900

*.FILESYSTEMIO_OPTIONS='SETALL'

*.hash_area_size=20971520

*.java_pool_size=167772160

*.log_archive_dest='K:\oracle\SID\oraarch\SIDarch'

*.LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf'

*.LOG_BUFFER=1048576

*.log_checkpoint_interval=0

*.log_checkpoints_to_alert=TRUE

*.max_dump_file_size='20000'

*.open_cursors=800

*.parallel_execution_message_size=16384

*.parallel_max_servers=40

*.parallel_threads_per_cpu=1

*.pga_aggregate_target=500M

*.processes=230

*.query_rewrite_enabled='FALSE'

*.RECYCLEBIN='OFF'

*.remote_os_authent=TRUE

*.replication_dependency_tracking=FALSE

*.sessions=440

*.sga_max_size=1800M

*.shared_pool_size=550M

*.sort_area_retained_size=0

*.sort_area_size=10485760

*.star_transformation_enabled='TRUE'

*.statistics_level='TYPICAL'

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=86400

*.undo_tablespace='PSAPUNDO'

*.user_dump_dest='G:\oracle\SID\saptrace\usertrace'

*.workarea_size_policy='AUTO'