Dontcheff

The init.ora file in 11gR2 for Siebel CRM and Oracle Applications Release 12

In Database tuning, DBA, E-Business Suite, Oracle database, Siebel on August 27, 2011 at 21:15

Almost 100 init.ora parameters affect the generation of SQL execution plans and thus the performance of the Oracle database.

There are so many books written about the Oracle database but not a single one dedicated to the init.ora file (or spfile if you prefer). The reason is simple: the names and the values vary from release to release and some special applications require special parameters.

Interesting enough, iTunes have already a free init.ora application, which DBAs can download! It says: “Oracle Database initialization parameters reference guide. This offline guide helps you in finding information about 2152 Oracle Database initialization parameters even without 3G or WIFI.”

The purpose of this blog post is also simple: how to set up a good init.ora file for 11gR2 in a “normal” database, in a Siebel database and in Oracle E-Business Suite Release 12.

1. Standard database:

You can get a sample init.ora file from the Oracle 11.2 Documentation. Here is one, I have already successfully used in dozens of critical production database.

Feel free to modify it according to your needs and use the new 11g parameters only if you understand what they are meant for. Modify the floating parameters according to your hardware capabilities and filesystem structure.

# Modified by Julian on 08.08.2011

# Archive
log_archive_dest= /opt/oracle11/SAMPLE/arch
log_archive_format=arc_SAMPLE_%t_%s_%r.arc
log_checkpoints_to_alert = true
log_checkpoint_interval = 9999999
log_checkpoint_timeout = 0
fast_start_parallel_rollback = high

# Flashback
# db_flashback_retention_target = 180
# db_recovery_file_dest = /opt/oracle11/SAMPLE/arch
# db_recovery_file_dest_size = 32G

# Cache, I/O, etc.

db_block_size = 8192
memory_target = 32G
recovery_parallelism = 64
result_cache_mode = force
result_cache_max_size = 128M

# Cursors and Library Cache
open_cursors = 1000
session_cached_cursors = 500

# Database Identification
db_domain=europe.xxx.com
db_name=SAMPLE
service_names=SAMPLE
smtp_out_server = ‘xxxx’

# Diagnostics and Statistics
optimizer_capture_sql_plan_baselines = true

# File Configuration
control_files=(“/opt/oracle11/data/SAMPLE/control_01.ctl”, “/opt/oracle11/index/SAMPLE/control_02.ctl”)

# Instance Identification
instance_name = SAMPLE

# Miscellaneous
compatible = 11.2.0
db_ultra_safe = data_and_index
ddl_lock_timeout = 10

# Materialized views
query_rewrite_enabled = TRUE

# Processes and Sessions
processes = 1000

# Security and Auditing
remote_login_passwordfile = exclusive
audit_trail = ‘db_extended’
audit_sys_operations = true

# System Managed Undo
undo_retention = 3600
undo_tablespace = UNDOTBS

2. Siebel 8.x parameters

Add all these Siebel recommended init.ora parameters:

_always_semi_join = OFF
_b_tree_bitmap_plans = FALSE
_partition_view_enabled = FALSE
_gc_defer_time = 0
_no_or_expansion = FALSE
_optimizer_max_permutations = 100
optimizer_index_caching = 0
optimizer_mode = all_rows
query_rewrite_integrity = enforced
star_transformation_enabled = false
optimizer_index_cost_adj = 1
optimizer_dynamic_sampling = 1
query_rewrite_enabled = false
statistics_level = typical
memory_target = 60% of total physical memory on DB server

Remove _add_col_optim_enabled if present!

3. Oracle E-Business Suite Release 12 parameters:

Add the following parameters:

_optimizer_autostats_job=false
_trace_files_public = TRUE
_sort_elimination_cost_ratio =5
_like_with_bind_as_equality = TRUE
_fast_full_scan_enabled = FALSE
_b_tree_bitmap_plans = FALSE
optimizer_secure_view_merging = FALSE
plsql_code_type = NATIVE
sec_case_sensitive_logon = FALSE
db_files = 512
dml_locks = 10000
cursor_sharing = EXACT
open_cursors = 600
session_cached_cursors = 500
log_checkpoint_timeout = 1200
log_checkpoint_interval = 100000
log_buffer = 10485760
log_checkpoints_to_alert = TRUE
shared_pool_size = 600M
shared_pool_reserved_size = 60M
cursor_space_for_time = FALSE
aq_tm_processes = 1
job_queue_processes = 2
max_commit_propagation_delay = 0 (RAC only)
olap_page_pool_size = 4194304

If these parameters exist, you should remove them from your database initialization parameters file for Oracle Database 11gR2:

_always_anti_join
_always_semi_join
_complex_view_merging
_index_join_enabled
_kks_use_mutex_pin
_new_initial_join_orders
_optimizer_cost_based_transformation
_optimizer_cost_model
_optimizer_mode_force
_optimizer_undo_changes
_or_expand_nvl_predicate
_ordered_nested_loop
_push_join_predicate
_push_join_union_view
_shared_pool_reserved_min_alloc
_sortmerge_inequality_join_off
_sqlexec_progression_cost
_table_scan_cost_plus_one
_unnest_subquery
_use_column_stats_for_function
always_anti_join
always_semi_join
background_dump_dest
core_dump_dest
db_block_buffers
db_cache_size
db_file_multiblock_read_count
drs_start
enqueue_resources
event=”10932 trace name context level 32768″
event=”10933 trace name context level 512″
event=”10943 trace name context forever, level 2″
event=”10943 trace name context level 16384″
event=”38004 trace name context forever, level 1″
hash_area_size
java_pool_size
job_queue_interval
large_pool_size
max_enabled_roles
nls_language
optimizer_dynamic_sampling
optimizer_features_enable
optimizer_index_caching
optimizer_index_cost_adj
optimizer_max_permutations
optimizer_mode
optimizer_percent_parallel
plsql_compiler_flags
plsql_native_library_dir
plsql_native_library_subdir_count
plsql_optimize_level
query_rewrite_enabled
rollback_segments
row_locking
sort_area_size
sql_trace
sql_version
timed_statistics
undo_retention
undo_suppress_errors
user_dump_dest

These additional parameter recommendations by Oracle are based on the active Oracle E-Business Suite user counts:

For additional/detailed information check MOS ID 396009.1: Database Initialization Parameters for Oracle Applications Release 12

Although some parameters above have the default values, they are listed because of their importance.

Advertisements
  1. init.ora for Siebel – just a few thoughts on hidden parameters: _gc_defer_time=0 only applies to RAC from what I know. _like_with_bind_as_equality =true is another parameter you should add if you intend to use Siebel Automatic Trailing Wildcards or Case Insensitivity. As far normal, not hidden parameters – you only need to set two parameters – optimizer_index_cost_adj=1 and optimizer_dynamic_sampling=1, all others are defaults. If in doubt check the latest Siebel CBO tuning guide on MOS…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: