Essential Oracle Database Parameter Tuning: Hidden Risks and Performance Boosts

During a community discussion on Oracle database initialization, several DBAs shared practical insights about the most important parameters to review before a production rollout, especially for Oracle 11.2.0.4 on AIX and Linux platforms.

General and Performance‑Related Parameters

Yang Jianrong (Beijing): Highlighted the need to differentiate between generic parameters, performance‑tuned parameters, and deprecated or new‑feature parameters such as segment_deferred_creation, case‑sensitive features, and hidden settings like skip_scan that may be disabled in some environments.

Old A (Zhou Liang): Recommended disabling certain new‑feature parameters that can affect stability, for example the DRM feature in RAC and the direct‑path read for small tables introduced in 11g.

Wang Chaoyang: Stressed that many parameters have been optimized in newer releases but still require business‑specific tuning, including file and cursor limits, parallel rollback settings, and OS‑level values such as shmmax and /dev/shm size.

Oracle‑Specific Hidden Parameters

Ivan Yao: Referred to Oracle’s official documentation and MOS notes for baseline OS kernel and database settings, noting that RAC upgrades can reset OS parameters (e.g., AIX 6.1 → AIX 7.1) and cause cluster failures.

Peng Xiaobo: Listed three performance‑related hidden parameters: _kghdsidx_count – controls the number of shared‑pool sub‑pools; useful when the shared pool exceeds 250 MB on a 4‑CPU system. _kgl_hot_object_copies – adjusts the number of copies for hot objects to reduce mutex contention. _CURSOR_OBSOLETE_THRESHOLD – defines when a cursor version should be discarded and reparsed, helping to avoid excessive soft parses; can be tuned via _cursor_features_enabled or event 106001 in versions prior to 11.2.0.3.

He also pointed to MOS article 296377.1 for a deeper look at SQL version handling.

Operational and System‑Level Considerations

Li Guangcai: Emphasized that initial database configuration must align with business characteristics, covering system settings, memory allocation, parallelism, cache planning, file layout, statistics, indexing, and fragmentation management—all from a performance‑optimization perspective.

He raised several specific flags to verify after instance creation, such as:

Whether to disable deferred_segment_creation (affects export operations).

Impact of _use_adaptive_log_file_sync on log_sync_waits.

Whether to turn off _skip_unusable_indexes when indexes are invalid but not harmful.

Risks of leaving _optimizer_use_feedback enabled (known bugs in 11g).

Choosing between crashing the instance ( _datafile_write_errors_crash_instance) versus offlineing a datafile on I/O timeout.

Effect of optimizer_capture_sql_plan_baselines on SYS_AUX growth.

Security settings such as case‑sensitive passwords and login‑failure delay validation.

Audit defaults, profile password expiration, DRM, direct‑path reads, and shared‑memory IPC parameters.

Examples of Parameter Adjustments in Real Deployments

Ricky Zhu: Noted that Oracle retains many obsolete or deprecated parameters for backward compatibility while adding new ones. During an EBS upgrade from 10gR2 to 11.2.0.4, several EBS‑specific parameters had to be set according to Oracle notes, e.g., _b_tree_bitmap_plans=FALSE, _fast_full_scan_enabled=FALSE, _fix_control='13704562:OFF', _gby_hash_aggregation_enabled=FALSE, _like_with_bind_as_equality=TRUE, _optimizer_autostats_job=FALSE, _sort_elimination_cost_ratio=5, _sqlexec_progression_cost=2147483647, _system_trig_enabled=TRUE. He also highlighted the need to align memory_target and sga_target between test and production environments.

In another case, a production system using Oracle Streams suffered latency alerts because streams_pool_size remained at its default after enabling Automatic Memory Management. Adjusting this parameter based on MOS recommendations eliminated the alerts and improved replication throughput.

OS‑Level Tuning and Hardware Considerations

Yang Zhihong: Stressed the importance of AIX kernel parameters, HugePage configuration on Linux, and disabling USB heartbeat on RAC nodes to avoid unexpected reboots.

Other Contributors: Mentioned storage and network tuning, such as adjusting queue depth on AIX, configuring ASM memory targets ( asm_memory_target), and ensuring proper NIC settings (full‑duplex, speed) in virtualized environments (VIOS, VMX).

Overall, the discussion underscores that Oracle database parameter tuning is a detailed, scenario‑specific activity that requires careful testing, reference to official MOS notes, and alignment with both application workloads and underlying operating system characteristics.