Symptom
You want to set Oracle database parameters for the Business Information Warehouse (BW).
Only when you work with an Oracle Version higher than 8.1.7
For Oracle 8.1.7.x, use BW Parameter Note 632427
For Oracle 9.2.0.x , use BW Parameter Note 632556
For Oracle 10g , use Note 830576
This note is relevant only if you work with an Oracle version higher than Oracle 8.1.7
The performance of the BW system is inadequate.
ORA-600 errors occur.
You require more information about Oracle standard parameter settings for BW.
To ensure error-free database operation and good system performance, you must set the database parameters optimally.
This note does not apply to R/3 systems. You can find the R/3 parameter settings in Note 124361.
As part of the DB system check (transaction DB16 or DB17), SAPDBA also checks Oracle database parameters. Adjust the test values in accordance with this note (use transaction DB17) to avoid error messages in DB16. However, you must make the actual parameter changes in init<SID>.ora.
Only when you work with an Oracle Version higher than 8.1.7
For Oracle 8.1.7.x, use BW Parameter Note 632427
For Oracle 9.2.0.x , use BW Parameter Note 632556
For Oracle 10g , use Note 830576
This note is relevant only if you work with an Oracle version higher than Oracle 8.1.7
The performance of the BW system is inadequate.
ORA-600 errors occur.
You require more information about Oracle standard parameter settings for BW.
To ensure error-free database operation and good system performance, you must set the database parameters optimally.
This note does not apply to R/3 systems. You can find the R/3 parameter settings in Note 124361.
As part of the DB system check (transaction DB16 or DB17), SAPDBA also checks Oracle database parameters. Adjust the test values in accordance with this note (use transaction DB17) to avoid error messages in DB16. However, you must make the actual parameter changes in init<SID>.ora.
Other Terms
Performance, BW, ORA-600, ORA 600 12700, parallel query, EarlyWatch,
GoingLive, init.ora, profile parameter, sapdba, DB16 -check, DBCHECKORA,
DB17, ORA-03113, ORA, 3113
Reason and Prerequisites
The recommendations in this note are based on discussions with all
the relevant groups in SAP that deal with questions regarding Oracle
performance and high availability as well as analysis and tuning of
numerous SAP/Oracle customer systems.
Due to Oracle software errors, it is always possible that you may have to set parameters differently to the values listed here to avoid the relevant error. In this case, follow the instructions from Oracle support at SAP. However, you should return to the previous settings once you have corrected the error. Only if you have precise knowledge of the effect should you deviate from the standard recommendations listed below.
Note that a hardware sizing of your system is necessary to determine the values of the parameters db_block_buffers and shared_pool_size before production startup. All other parameters should be set as specified for the production startup.
Due to Oracle software errors, it is always possible that you may have to set parameters differently to the values listed here to avoid the relevant error. In this case, follow the instructions from Oracle support at SAP. However, you should return to the previous settings once you have corrected the error. Only if you have precise knowledge of the effect should you deviate from the standard recommendations listed below.
Note that a hardware sizing of your system is necessary to determine the values of the parameters db_block_buffers and shared_pool_size before production startup. All other parameters should be set as specified for the production startup.
Solution
This note is updated regularly. Therefore read this note regularly.
Latest changes:
MAY/08/2001 - Inserting 8.1.7 parameters
MAY/31/2001 - STAR_TRANSFORMATION_ENABLED and Note 380523
JUN/26/2001 - Upgrade to 8.1.7.1 or higher recommended as soon as available
DEC/06/2001 - Do not set ALWAYS_SEMI_JOIN other than to default
SEP/02/2001 - Inserting 9.2.0 parameters
Note: This note is NOT valid for R/3 systems
Note that the Oracle parameter optimizer_index_cost_adj must never be set. Delete this parameter from the (init<SID>.ora) Oracle profile, if it exists there.
If a parameter is marked with the recommendation '<delete from profile>', this parameter should be deleted from the Oracle profile (init<SID>.ora) so that the default value can be taken from Oracle.
I Oracle 8.0.4
Parameter Name Recommended Value
--------------------------- ------------------------------
always_anti_join hash
bitmap_merge_area_size 33554432
b_tree_bitmap_plans < delete from profile>
compatible 8.0.4.x.y (the exact release)
control_file_record_keep_time >= 30 (as of BRBACKUP Release 4.5A)
cursor_space_for_time <delete from profile>
create_bitmap_area_size 33554432
db_block_buffers should never be smaller than
9000 (approximately 70 MB)
db_block_checkpoint_batch >= 8
db_block_lru_latches # CPUs * 2
db_block_size 8192
db_file_multiblock_read_count 32
distributed_transactions 0 (only if you do not use any
distributed data retention or database
links)
event:
10181 Do NOT set!
10183 Do NOT set!
10191 Refer to Note 128221
hash_area_size sort_area_size * 2
hash_join_enabled true
hash_multiblock_io_count 32
log_archive_start true
log_buffer 1048576
log_checkpoint_interval 3000000000
log_checkpoint_timeout 0
log_checkpoints_to_alert true
log_simultaneous_copies # CPUs * 2
max_dump_file_size 20000 (If larger trace files are
required, the value can be adjusted
using an alter system
command)
open_cursors 800
optimizer_features_enable <delete from profile>
optimizer_mode choose
optimizer_percent_parallel 100
parallel_broadcast_enabled true
parallel_execution_message_size 16384
parallel_max_servers # CPUs * 2
processes > (2 * #BW work processes + 20)
replication_dependency_tracking false (ONLY if you do not use
replication)
replication) row_cache_cursors 300
shared_pool_size should never be smaller than
50000000 (approximately 50 MB)
shared_pool_reserved_size 10% of shared_pool_size
sessions >= processes
sort_area_retained_size 0
sort_area_size >= 10485760 (corresponds to 10 MB;
depending on the main memory)
Refer to Note 164925 and especially
for NT, Note 335230.
sort_direct_writes true
sort_write_buffers 4
sort_write_buffer_size 262144
star_transformation_enabled true (<Z1>absolutely</> do see
Note 380523) timed_statistics true
transaction_auditing false
II Oracle 8.0.5
Install at least patch 8.0.5.2. Note 127395 contains information about available patch sets for Oracle.
Parameter Name Recommended Value
--------------------------- ------------------------------
always_anti_join hash
bitmap_merge_area_size 33554432
b_tree_bitmap_plans < delete from profile>
compatible 8.0.5.x.y (the exact release)
control_file_record_keep_time >= 30 (at least BRBACKUP Release 4.5A)
cursor_space_for_time <delete from profile>
create_bitmap_area_size 33554432
db_block_buffers should never be smaller than
9000 (approximately 70 MB)
db_block_checkpoint_batch >= 8
db_block_lru_latches number of CPUs * 2
db_block_size 8192
db_file_multiblock_read_count 32
distributed_transactions 0 (only if you do not use any
distributed data retention or database
links)
events:
10181 Do NOT set!
10183 Do NOT set!
hash_area_size sort_area_size * 2
hash_join_enabled true
hash_multiblock_io_count 32
log_archive_start true
log_buffer 1048576
log_checkpoint_interval 3000000000
log_checkpoint_timeout 0´
log_checkpoints_to_alert true
log_simultaneous_copies # CPUs * 2
max_dump_file_size 20000
open_cursors 800
optimizer_index_cost_adj < delete from profile >
optimizer_features_enable <delete from profile>
optimizer_mode choose
optimizer_percent_parallel 100
parallel_broadcast_enabled true
parallel_execution_message_size 16384
parallel_max_servers # CPUs * 2
processes > (# BW work processes + 20)
replication_dependency_tracking false (ONLY if you do not use
replication)
replication) row_cache_cursors 300
shared_pool_size should never be smaller than
50000000 (approximately 50 MB)
shared_pool_reserved_size 10% of shared_pool_size
sessions >= processes
sort_area_retained_size 0
sort_area_size >= 10485760 (corresponds to 10 MB;
depending on the main memory)
Refer to Note 164925 and especially
for NT, Note 335230.
sort_direct_writes true
sort_write_buffers 4
sort_write_buffer_size 262144
star_transformation_enabled true (<Z1>absolutely</> do see
Note 380523) timed_statistics true
transaction_auditing false
III Oracle 8.0.6
Use the same parameter settings as for 8.0.5.
Exception:
Parameter Name Recommended Value
--------------------------- ------------------------------
compatible 8.0.6.x.y (the exact release)
IV Oracle 8.1.5
For the changes of the parameter settings for 8.0.5, refer to the following table. Set all other parameters as for 8.0.5.
Parameter Name Recommended Value
--------------------------- ------------------------------
b_tree_bitmap_plans obsolete - remove from profile
compatible 8.1.5.x.y (the exact release)
complex_view_merge obsolete - remove from profile
db_block_checkpoint_batch obsolete - remove from profile
db_file_multiblock_read_count 32
hash_multiblock_io_count should have the same value as
db_file_multiblock_read_count
log_checkpoint_interval 3000000
log_simultaneous_copies obsolete - delete from profile
log_small_entry_max_size obsolete - delete from profile
optimizer_index_caching < delete from profile >
optimizer_index_cost_adj < delete from profile >
optimizer_features_enable <delete from profile>
(you MUST read Note 217478)
parallel_automatic_tuning < delete from profile >
parallel_max_servers # CPUs * 8
parallel_threads_per_cpu 1
processes > (4 * #BW work processes + 20)
query_rewrite_enabled <delete from profile>
See Note 375130.
row_cache_cursors obsolete - remove from profile
sort_direct_writes obsolete - delete from profile
sort_multiblock_read_count should have the same value as
db_file_multiblock_read_count
sort_read_fac obsolete - delete from profile
sort_write_buffers obsolete - delete from profile
sort_write_buffer_size obsolete - delete from profile
star_transformation_enabled true (you MUST
read Note 217478)
temporary_table_locks obsolete - delete from profile
timed_statistics false (due to Oracle bug)
V Oracle 8.1.6
For differences to the parameter settings for 8.1.5, refer to the following table. Set all other parameters as for 8.1.5.
Parameter Name Recommended Value
--------------------------- ------------------------------
compatible 8.1.6.x.y (the exact release)
optimizer_max_permutations 79999 (read Note 336402)
star_transformation_enabled true (you MUST
read Note 380523)
timed_statistics true (you MUST
read Note 332105)
For 8.1.7.x and higher ORACLE Releases
separate notes are maintained for each release.
Refer to the note relevant for your release.
VI Oracle 8.1.7
Note: SAP recommends that you import patch Set 8.1.7.1 or higher as soon as it is available for the corresponding platform (see Note 362060).
Refer to Note 632427.
See Note 387056 for information about the necessary bug fixes.
VI Oracle 9.2.0
Refer to Note 632556.
If the following ORA-600 errors occur, consult the changed parameter settings in accordance with the attached notes:
ORA-00600 [2865] Note 332696
Important note for Release 8.1 (64-bit releases)
If performance problems occur due to long parsing times after the upgrade, refer to Note 376905.
Parameters no longer supported as of Oracle Release 8.1:
For an overview of all parameters that are no longer valid as of Release 8.1, see Note 320540
Latest changes:
MAY/08/2001 - Inserting 8.1.7 parameters
MAY/31/2001 - STAR_TRANSFORMATION_ENABLED and Note 380523
JUN/26/2001 - Upgrade to 8.1.7.1 or higher recommended as soon as available
DEC/06/2001 - Do not set ALWAYS_SEMI_JOIN other than to default
SEP/02/2001 - Inserting 9.2.0 parameters
Note: This note is NOT valid for R/3 systems
Note that the Oracle parameter optimizer_index_cost_adj must never be set. Delete this parameter from the (init<SID>.ora) Oracle profile, if it exists there.
If a parameter is marked with the recommendation '<delete from profile>', this parameter should be deleted from the Oracle profile (init<SID>.ora) so that the default value can be taken from Oracle.
I Oracle 8.0.4
Parameter Name Recommended Value
--------------------------- ------------------------------
always_anti_join hash
bitmap_merge_area_size 33554432
b_tree_bitmap_plans < delete from profile>
compatible 8.0.4.x.y (the exact release)
control_file_record_keep_time >= 30 (as of BRBACKUP Release 4.5A)
cursor_space_for_time <delete from profile>
create_bitmap_area_size 33554432
db_block_buffers should never be smaller than
9000 (approximately 70 MB)
db_block_checkpoint_batch >= 8
db_block_lru_latches # CPUs * 2
db_block_size 8192
db_file_multiblock_read_count 32
distributed_transactions 0 (only if you do not use any
distributed data retention or database
links)
event:
10181 Do NOT set!
10183 Do NOT set!
10191 Refer to Note 128221
hash_area_size sort_area_size * 2
hash_join_enabled true
hash_multiblock_io_count 32
log_archive_start true
log_buffer 1048576
log_checkpoint_interval 3000000000
log_checkpoint_timeout 0
log_checkpoints_to_alert true
log_simultaneous_copies # CPUs * 2
max_dump_file_size 20000 (If larger trace files are
required, the value can be adjusted
using an alter system
command)
open_cursors 800
optimizer_features_enable <delete from profile>
optimizer_mode choose
optimizer_percent_parallel 100
parallel_broadcast_enabled true
parallel_execution_message_size 16384
parallel_max_servers # CPUs * 2
processes > (2 * #BW work processes + 20)
replication_dependency_tracking false (ONLY if you do not use
replication)
replication) row_cache_cursors 300
shared_pool_size should never be smaller than
50000000 (approximately 50 MB)
shared_pool_reserved_size 10% of shared_pool_size
sessions >= processes
sort_area_retained_size 0
sort_area_size >= 10485760 (corresponds to 10 MB;
depending on the main memory)
Refer to Note 164925 and especially
for NT, Note 335230.
sort_direct_writes true
sort_write_buffers 4
sort_write_buffer_size 262144
star_transformation_enabled true (<Z1>absolutely</> do see
Note 380523) timed_statistics true
transaction_auditing false
II Oracle 8.0.5
Install at least patch 8.0.5.2. Note 127395 contains information about available patch sets for Oracle.
Parameter Name Recommended Value
--------------------------- ------------------------------
always_anti_join hash
bitmap_merge_area_size 33554432
b_tree_bitmap_plans < delete from profile>
compatible 8.0.5.x.y (the exact release)
control_file_record_keep_time >= 30 (at least BRBACKUP Release 4.5A)
cursor_space_for_time <delete from profile>
create_bitmap_area_size 33554432
db_block_buffers should never be smaller than
9000 (approximately 70 MB)
db_block_checkpoint_batch >= 8
db_block_lru_latches number of CPUs * 2
db_block_size 8192
db_file_multiblock_read_count 32
distributed_transactions 0 (only if you do not use any
distributed data retention or database
links)
events:
10181 Do NOT set!
10183 Do NOT set!
hash_area_size sort_area_size * 2
hash_join_enabled true
hash_multiblock_io_count 32
log_archive_start true
log_buffer 1048576
log_checkpoint_interval 3000000000
log_checkpoint_timeout 0´
log_checkpoints_to_alert true
log_simultaneous_copies # CPUs * 2
max_dump_file_size 20000
open_cursors 800
optimizer_index_cost_adj < delete from profile >
optimizer_features_enable <delete from profile>
optimizer_mode choose
optimizer_percent_parallel 100
parallel_broadcast_enabled true
parallel_execution_message_size 16384
parallel_max_servers # CPUs * 2
processes > (# BW work processes + 20)
replication_dependency_tracking false (ONLY if you do not use
replication)
replication) row_cache_cursors 300
shared_pool_size should never be smaller than
50000000 (approximately 50 MB)
shared_pool_reserved_size 10% of shared_pool_size
sessions >= processes
sort_area_retained_size 0
sort_area_size >= 10485760 (corresponds to 10 MB;
depending on the main memory)
Refer to Note 164925 and especially
for NT, Note 335230.
sort_direct_writes true
sort_write_buffers 4
sort_write_buffer_size 262144
star_transformation_enabled true (<Z1>absolutely</> do see
Note 380523) timed_statistics true
transaction_auditing false
III Oracle 8.0.6
Use the same parameter settings as for 8.0.5.
Exception:
Parameter Name Recommended Value
--------------------------- ------------------------------
compatible 8.0.6.x.y (the exact release)
IV Oracle 8.1.5
For the changes of the parameter settings for 8.0.5, refer to the following table. Set all other parameters as for 8.0.5.
Parameter Name Recommended Value
--------------------------- ------------------------------
b_tree_bitmap_plans obsolete - remove from profile
compatible 8.1.5.x.y (the exact release)
complex_view_merge obsolete - remove from profile
db_block_checkpoint_batch obsolete - remove from profile
db_file_multiblock_read_count 32
hash_multiblock_io_count should have the same value as
db_file_multiblock_read_count
log_checkpoint_interval 3000000
log_simultaneous_copies obsolete - delete from profile
log_small_entry_max_size obsolete - delete from profile
optimizer_index_caching < delete from profile >
optimizer_index_cost_adj < delete from profile >
optimizer_features_enable <delete from profile>
(you MUST read Note 217478)
parallel_automatic_tuning < delete from profile >
parallel_max_servers # CPUs * 8
parallel_threads_per_cpu 1
processes > (4 * #BW work processes + 20)
query_rewrite_enabled <delete from profile>
See Note 375130.
row_cache_cursors obsolete - remove from profile
sort_direct_writes obsolete - delete from profile
sort_multiblock_read_count should have the same value as
db_file_multiblock_read_count
sort_read_fac obsolete - delete from profile
sort_write_buffers obsolete - delete from profile
sort_write_buffer_size obsolete - delete from profile
star_transformation_enabled true (you MUST
read Note 217478)
temporary_table_locks obsolete - delete from profile
timed_statistics false (due to Oracle bug)
V Oracle 8.1.6
For differences to the parameter settings for 8.1.5, refer to the following table. Set all other parameters as for 8.1.5.
Parameter Name Recommended Value
--------------------------- ------------------------------
compatible 8.1.6.x.y (the exact release)
optimizer_max_permutations 79999 (read Note 336402)
star_transformation_enabled true (you MUST
read Note 380523)
timed_statistics true (you MUST
read Note 332105)
For 8.1.7.x and higher ORACLE Releases
separate notes are maintained for each release.
Refer to the note relevant for your release.
VI Oracle 8.1.7
Note: SAP recommends that you import patch Set 8.1.7.1 or higher as soon as it is available for the corresponding platform (see Note 362060).
Refer to Note 632427.
See Note 387056 for information about the necessary bug fixes.
VI Oracle 9.2.0
Refer to Note 632556.
If the following ORA-600 errors occur, consult the changed parameter settings in accordance with the attached notes:
ORA-00600 [2865] Note 332696
Important note for Release 8.1 (64-bit releases)
If performance problems occur due to long parsing times after the upgrade, refer to Note 376905.
Parameters no longer supported as of Oracle Release 8.1:
For an overview of all parameters that are no longer valid as of Release 8.1, see Note 320540
No comments:
Post a Comment