Archive for the ‘Siebel’ Category

DBA tips for tuning Siebel on RAC and Exadata

In Database tuning, DBA, Exadata, Oracle database, Oracle internals, RAC, Siebel on April 21, 2013 at 11:27

More than 100 years ago, Mark Twain said that “the elastic heart of youth cannot be compressed into one constrained shape long at a time”.

In modern technology, “the elastic cloud can be compressed with the constraints of long datatypes”. Let me explain the meaning of my statement in terms of Siebel applications running on RAC and Exadata.


Chained and migrated rows are often a big problem in a Siebel database. The issue of chained rows can be resolved by using big block size while migrated rows require segment reorganization with higher PCTFREE (20, 30 or even 50). Chained tables in Siebel are often S_ORG_EXT, S_ASSET, S_ORDER_ITEM and S_ORDER_ITEM_OM. At least these will require using a bigger block size.

For LONG and CLOB details in a Siebel CRM, check CLOB Physical Type in Siebel.

But as Joel Goodman noted in How Smart is Your Smart Scan?, there is an Exadata specific situation that causes migrated rows.

When a row is updated in a Hybrid Columnar Compressed (HCC) table, then it is migrated to another block in the segment that is managed using “OLTP compression“. Any HCC Compression Unit (CU) containing at least one migrated row, will also cause the block containing that row to be accessed by the server using a “cell single block physical read“.

Look at the “Top 5 Timed Foreground Events” in a Siebel on Exadata OLTP DB using HCC:

cell single block physical read

The situation with HCC in OLTP is tricky for the following 3 reasons:

1. Every update of a record stored in HCC format results in a migrated row
2. The new row is stored in a new block that is marked for OLTP compression
3. Non-direct path inserts will be loaded into OLTP compressed blocks as opposed to HCC format

For the above reasons, mixing HCC with DML is not recommended. Partitioning can provide a mechanism for avoiding these issues since each partition can have its own storage format.

Only after decompressing the OLTP tables, the event “cell single block physical read“ disappeared and the performance got significantly improved.

Another good tip for Siebel on RAC is the usage of high number of hash partitions for hot indexes. High means 256 and more (should be a power of 2).

Look at the situation with gc buffer busy waits before the high hash partitioning:


As you can see the “gc buffer busy acquire” was the top event:


Looking at issues with these events in MOS might incline you to believe that this is a bug. However, this was not the case as you can see what happened after making the number of hash partitions for some of the indexes 256, the database performance was back to normal:


Note that also non-partitioned tables can have hash partitioned indexes!

Another tip: SecureFiles is a feature introduced in Oracle Database 11g that is *specifically* engineered to deliver *high performance* for this type of unstructured data. I have seen several queries getting even 10 times faster after migrating LOBs to SecureFiles.

About sequences: in RAC/Exadata, using the CACHE and NOORDER options together results in the best performance for a sequence. For example, in a Siebel database the S_DOCK_TXN_LOG_S sequence is used to generate the transaction ID used by S_DOCK_TXN_LOG table. The default cache size for sequences in Oracle is 20. If you are having thousands of concurrent users, Siebel/Oracle suggest you increase the cache size to be at least 10000.

Last 2 things:

– For gathering Siebel database statistics use always the latest version of coe_siebel_stats.sql. As of now, the latest version is
– The script coe_siebel_profile.sql provides a list of columns that are not indexed but potentially are good candidates for indexing according to their usage by the Optimizer.

For additional tips, check Oracle’s white paper Siebel on Exadata!

And using Oracle Enterprise Manager makes all tuning so much easier!


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_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
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:

_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:

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″

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.