Mastering Oracle System Optimization: Real‑World Case Study and Practical Lessons

Problem Overview

The production Oracle RAC system experienced intermittent failures during peak periods. Monitoring showed normal CPU usage (occasional spikes to 100%) and modest load (≈1,878 executions per second) with acceptable hit ratios, indicating that CPU was not the bottleneck.

Root Cause Analysis

IO Subsystem

IO latency spikes >100 ms were observed on several disks during business peaks.

Four databases shared two storage arrays implemented with legacy LVM mirroring.

Only five disks of the EMC CX3‑40 array were allocated to the critical TC/QC databases, creating a severe performance bottleneck.

EMC CX3‑40 cache was heavily biased toward write buffering (≈2.5 GB write, 0.5 GB read), leaving insufficient read cache for a read‑intensive workload.

Database Design

Large tables such as B_RP_DeptProductMonthReport (≈52 M rows, 59 K blocks) were scanned fully because they were either unpartitioned or partitioned quarterly. Users typically queried data by month, making quarterly partitions inefficient.

Diagnostic Steps

Collected OS‑level storage performance samples during peak hours; identified disks with average response times >100 ms.

Queried v$asm_disk to map slow disks to the online and query databases.

Compared the two storage arrays: EMC CX3‑40 (13 + 15 × 300 GB 10K disks, only 5 used by TC/QC) vs. HP EVA3000 (13 + 15 × 73 GB 10K disks, auto‑RAID with balanced distribution).

Analyzed cache allocation on CX3‑40 and confirmed a 5:1 write‑to‑read buffer ratio.

Performed a partitioning test on a copy of the “Customer Summary” query (table B_OD_OrderDetailTL) by changing from quarterly to monthly partitions.

Optimization Actions

Storage cache rebalance : Adjusted cache to a 50/50 read‑write split, reducing average disk response time to <10 ms.

KEEP pool tuning : Reduced KEEP pool size for the QC database from 2000 MB to 256 MB to free memory and eliminate paging.

KEEP pool object placement : Moved high‑IO objects into the KEEP pool, which then accounted for ~33 % of logical reads and eliminated them from the top‑IO consumers.

Partition redesign : Converted large tables from quarterly to monthly partitions, enabling partition pruning and cutting scanned blocks dramatically.

Performance test : In the test environment, the “Customer Summary” query execution time dropped from 42.34 s to 6.62 s after applying monthly partitions.

Results

Disk I/O latency decreased from ~100 ms to ~30 ms after initial cache tuning and further to <10 ms after cache rebalance.

IO generated by the top objects fell sharply after moving them to the KEEP pool.

Business users reported noticeable response‑time improvements, confirming that technical gains translated to user experience.

Key Lessons

Start optimization with a system‑wide demand analysis; CPU metrics alone may miss the real bottleneck.

Storage architecture and cache configuration can have a larger impact than individual SQL tuning.

Align partition granularity with query patterns (monthly vs. quarterly) to reduce unnecessary block scans.

Appropriate KEEP pool sizing and object placement can substantially lower IO.

Continuous collaboration among DBAs, developers, and business stakeholders is essential for sustainable performance gains.