Some Real-World Performance Issue- Scenarios
Some Real-World Performance Issue- Scenarios
Here are some real-world, high-impact performance scenarios faced by Oracle DBAs and Performance Engineers, along with their root causes and precise solutions.
1. Unexpected Full Table Scans After Auto-Gathering Statistics
Scenario: A major query suddenly drops from taking 2 seconds to 45 minutes after the nightly GATHER_STATS_JOB runs.
Root Cause: The auto-job updated statistics on a highly skewed column, causing Optimizer Cost-Based (CBO) estimation errors because no histogram was created (or a default height-balanced histogram miscalculated cardinality).
Solution: Create an extended statistic or forced frequency histogram for the skewed column and lock the statistics using DBMS_STATS.LOCK_TABLE_STATS once a baseline plan is established. Use a SQL Plan Baseline (DBMS_SPM) to enforce the optimal plan.
2. High db file sequential read Waits (Single-Block I/O Bottleneck)
Scenario: Application experiences severe latency. AWR shows db file sequential read as the top wait event with high average wait times (>10ms).
Root Cause: Unindexed foreign keys, suboptimal index range scans on low-cardinality columns, or high index fragmentation forcing excessive single-block disk reads.
Solution: Identify the top SQLs using V$SQL_PLAN and V$ACTIVE_SESSION_HISTORY. Convert index range scans to index fast full scans where appropriate, rebuild/reorganize highly fragmented indexes, or move hot tables/indexes to NVMe storage or Flash Cache (STORAGE (CELL_FLASH_CACHE KEEP) in Exadata).
3. Severe Log Buffer Space & Log File Switch Waits
Scenario: High insert/update batch jobs experience extreme slowness. Top wait events show log buffer space, log file switch (checkpoint incomplete), or log file switch (archiving needed).
Root Cause: Online Redo Log (ORL) groups are too small, causing frequent checkpointing and log archiving bottlenecks.
Solution: Increase the size of Online Redo Logs (e.g., from 1GB to 8GB or larger depending on throughput), increase the number of log groups, and ensure Redo Logs reside on high-speed, unthrottled storage.
4. Excessive library cache lock / library cache pin
Scenario: High session concurrency causes sudden application hangs across the entire cluster.
Root Cause: Hard parsing spikes combined with DDL operations or high invalidation of dependent objects (e.g., gathering stats on hot tables during peak business hours).
Solution: Convert literal SQLs to bind variables using CURSOR_SHARING = FORCE (as a short-term fix) or update application code. Schedule DDLs and statistics collection outside peak hours and set NO_INVALIDATE => TRUE in DBMS_STATS.
5. High row lock contention (TX — enqueue contention)
Scenario: Application sessions lock up with enq: TX — row lock contention.
Root Cause: Multiple concurrent sessions trying to update the exact same rows, or unindexed Foreign Key constraints causing table-level lock escalation during DELETE/UPDATE operations on the parent table.
Solution: Index all Foreign Key columns to prevent full-table locks. Audit application logic to eliminate hotspot rows and enforce strict transaction commit ordering.
6. Buffer Busy Waits (buffer busy waits)
Scenario: Concurrent threads inserting into a single table cause high CPU and buffer busy waits.
Root Cause: Multiple sessions competing for the exact same data block or index leaf block simultaneously (Hot Block contention).
Solution: Hash partition the table/index to distribute inserts across multiple distinct physical blocks, increase INITRANS for the table/index, or use Reverse Key Indexes for monotonically increasing sequences.
7. Temp Space Exhaustion During Massive Joins/Sorts
Scenario: Batch ETL jobs fail with ORA-01652: unable to extend temp segment by X in tablespace TEMP.
Root Cause: Suboptimal Hash Joins or large ORDER BY / GROUP BY operations spilling to disk due to insufficient PGA allocation or poor cardinality estimates by CBO.
Solution: Increase PGA_AGGREGATE_TARGET (or set PGA_AGGREGATE_LIMIT appropriately), run SQL Tuning Advisor to fix cardinality estimates, and use Hash/Range Partitioning to allow smaller in-memory join chunks.
8. Exadata Smart Scan Offloading Not Working
Scenario: Queries on Exadata execute much slower than expected, showing high cell multiblock physical read instead of Smart Scan offloading.
Root Cause: Smart Scan offloading requirements are violated (e.g., using single-block reads, non-direct path reads, or unindexed functions in SQL).
Solution: Ensure queries run via Direct Path Read by tweaking _small_table_threshold or using /*+ FULL(t) DIRECT_PATH */ hints, avoid PL/SQL wrapper functions inside WHERE clauses, and verify Exadata storage indexes are active.
9. Undo Tablespace Bloat & ORA-01555: snapshot too old
Scenario: Long-running analytics queries fail with ORA-01555, while concurrent DMLs fail due to ORA-30036: unable to extend segment in undo tablespace.
Root Cause: High-volume DML overwriting undo blocks needed by long-running read queries before UNDO_RETENTION expires.
Solution: Increase the UNDO_TABLESPACE size, set RETENTION GUARANTEE on the Undo tablespace, tune long-running queries to reduce runtime, and break massive DML transactions into smaller batches.
10. Memory Starvation via Automatic Shared Memory Management (ASMM) Shrinkage
Scenario: Periodic response time spikes occur. Automatic Memory Management shrinks DB_CACHE_SIZE to expand SHARED_POOL_SIZE dynamically.
Root Cause: High rate of hard parses constantly consuming Shared Pool memory, forcing Oracle to automatically shrink the Buffer Cache to compensate.
Get Anurag’s stories in your inbox
Join Medium for free to get updates from this writer.
Solution: Set explicit minimum thresholds for critical memory pools (e.g., ALTER SYSTEM SET db_cache_size = 40G MINIMUM), fix hard-parsing issues in the application, and flush unused pinned cursors.
11. Latency Spikes from Misconfigured Resource Manager
Scenario: High-priority OLTP transactions experience random 500ms delays when background reporting runs.
Root Cause: Resource Manager consumer groups are incorrectly configured, or CPU throttling is triggering prematurely.
Solution: Tune DBMS_RESOURCE_MANAGER directives. Assign MGMT_P1 explicitly to interactive OLTP workloads and map long-running batch jobs to lower priority groups with mandatory execution time limits (MAX_IDLE_TIME or SWITCH_TIME).
12. RAC Interconnect Congestion (gc cr block busy / gc buffer busy acquire)
Scenario: Adding nodes to an Oracle RAC cluster decreases overall throughput rather than scaling up.
Root Cause: High Global Cache cross-node traffic due to bad data affinity — sessions on different RAC nodes frequently updating the same database blocks.
Solution: Partition application workloads logically by node (e.g., Node 1 handles Order Entry, Node 2 handles Inventory), upgrade to dedicated 100GbE InfiniBand/RoCE interconnects, and enable Jumbo Frames (MTU 9000).
13. Suboptimal Parallel Execution Demotion
Scenario: Parallel SQL query runs slower than serial execution, showing wait event PX Deq: Execution Msg.
Root Cause: Parallel threads were demoted or queued due to exhaustion of PARALLEL_MAX_SERVERS or heavy CPU skew among parallel slaves.
Solution: Enable Concurrent Parallel Execution Queueing (PARALLEL_DEGREE_POLICY = AUTO), tune PARALLEL_MIN_SERVERS, and use PX Statement Queuing to prevent thread exhaustion.
14. Performance Degradation from Stale/Corrupted Materialized Views
Scenario: Query rewrite using Materialized Views (MVs) suddenly stops working, causing queries to hit underlying base tables and crash performance.
Root Cause: MV became stale or invalid following a direct-path insert or uncommitted DML on base tables, invalidating the query rewrite engine.
Solution: Set QUERY_REWRITE_INTEGRITY = STALE_TOLERATED if real-time accuracy isn’t mandatory, convert to Fast Refresh (ON COMMIT or ON DEMAND), and create Materialized View Logs on base tables.
15. Cardinality Feedback (Statistics Feedback) Causing Plan Instability
Scenario: A SQL statement runs fast on execution 1, slow on execution 2, fast on 3, cycling endlessly.
Root Cause: Oracle’s Automatic Optimizer Feedback re-optimizes execution plans dynamically based on execution stats, but oscillating data patterns cause constant plan flips.
Solution: Disable Statistics Feedback for the problematic SQL using the /*+ OPT_PARAM(‘optimizer_use_feedback’, ‘false’) */ hint or fix the underlying stats issue by adding Extended Statistics.
16. CPU Throttling inside VM / Containerized Environments
Scenario: Host CPU usage appears low (<40%), but Oracle sessions report high CPU used by this session and long resmgr:cpu quantum wait times.
Root Cause: Hypervisor or cgroup CPU capping/vCPU overcommit limits execution despite physical CPU availability.
Solution: Pin Oracle DB instance processes specific NUMA nodes/sockets using CPU affinity, disable hypervisor overcommit, and set CPU_COUNT explicitly inside the Oracle parameter file.
17. High Latency Due to ASM Rebalance During Disk Failure
Scenario: A physical drive fails in an ASM disk group, causing immediate 5x latency spikes across the database.
Root Cause: ASM automatically initiated an aggressive rebalance operation (ASM_POWER_LIMIT), consuming disk I/O bandwidth.
Solution: Adjust ASM_POWER_LIMIT dynamically to a lower number during business hours to prioritize database I/O, and raise it during off-peak hours to complete mirror reconstruction faster.
18. Suboptimal Partition Pruning Due to Data Type Mismatch
Scenario: Query on a 2TB partitioned table performs a full scan of all 500 partitions instead of pruning to just one.
Root Cause: Implicit data type conversion in the WHERE clause (e.g., comparing a VARCHAR2 partition key column to a NUMBER literal or bind variable).
Solution: Ensure bind variables and column data types match strictly in application queries. Use EXPLAIN PLAN and verify that PARTITION START and PARTITION STOP show specific partition numbers rather than 1 to KEY.
19. Excessive Adaptive Cursor Sharing (ACS) Child Cursors
Scenario: High CPU utilization and high execution time for a single query. V$SQL shows hundreds of child cursors for the same SQL_ID.
Root Cause: Adaptive Cursor Sharing continuously creates new execution plans due to wide variation in bind variable values across executions.
Solution: Purge the cursor and lock a single robust plan using SQL Plan Baselines (DBMS_SPM), or disable ACS for that query using /*+ OPT_PARAM(‘_optimizer_adaptive_cursor_sharing’, ‘false’) */.
20. Excessive “Cell Single Block Physical Read” on Flash Storage
Scenario: High-throughput Exadata environment suffers slow batch execution despite having full NVMe Flash Cache storage.
Root Cause: Smart Flash Cache is operating in Write-Through mode instead of Write-Back mode, forcing all write operations directly to spinning disks.
Solution: Enable Exadata Smart Flash Cache in Write-Back mode to catch both reads and writes in high-speed flash storage, drastically dropping disk write latency.
