Every Oracle DBA has experienced that moment when monitoring alerts suddenly explode across the dashboard. Everything was running smoothly, and then without a warning - the CPU utilization jumps to 95%, applications slow down, and users start reporting performance issues.
One of the most frustrating parts of such incidents is when nothing obvious changed. No deployments, no schema modifications, and no new batch jobs. Yet the database is clearly struggling.
In many real-world cases, the root cause is surprisingly subtle: a change in optimizer statistics leading to a different execution plan. Oracle's automatic statistics gathering is powerful, but it is not always perfect and especially when data distribution is skewed or histograms behave unexpectedly.
In this article, we will walk through a real-world Oracle CPU spike investigation. You will see the step-by-step troubleshooting approach using AWR, execution plan history, and histogram analysis. We’ll also cover how the issue was fixed using manual statistics and SQL plan baselines to stabilize performance.
If you ar an Oracle DBA responsible for production environments, this guide will help you recognize the warning signs and quickly resolve similar high CPU incidents before they impact users.
🔹When the Database Suddenly Went High CPU
It started as a normal afternoon when monitoring alerts began appearing.
-
CPU utilization: 95%+ and Application response times was extremely slow
-
No deployments, changes or maintenance activities
From an operational perspective, this scenario is common in large enterprise databases. Performance issues often arise not from new changes, but from optimizer behaviour reacting to existing data differently.
At this stage, the priority was clear- that is to identify the SQL statement consuming the most CPU.
Step 1: Identify the Top SQL Consuming CPU
The first step in any Oracle performance investigation is to determine which SQL statement is responsible for the workload.
A quick query against v$sql revealed the top CPU consumers.
SELECT sql_id, executions, elapsed_time, cpu_time, sql_text FROM v$sql ORDER BY cpu_time DESC FETCH FIRST 5 ROWS ONLY;
One query clearly stood out that it was dominating CPU usage.
This approach is widely recommended in Oracle performance tuning because a small number of SQL statements typically account for the majority of database load.
Once the SQL ID was identified, the next step was to determine why the query suddenly became expensive.
Step 2: Investigate Execution Plan Changes
Oracle queries can suddenly become slower if the optimizer decides to use a different execution plan.
To verify this, the historical execution plans were checked using:
SELECT *
FROM dba_hist_sql_plan
WHERE sql_id='sqlid0sqltext1'
ORDER BY timestamp;
The result showed something interesting.
The query's execution plan changed after the nightly statistics collection job. This explained why the query had been performing well previously but suddenly started consuming excessive CPU resources.
Execution plan changes triggered by statistics updates are a well-known cause of performance issues in large Oracle environments.
But why did the optimizer choose a different plan? The answer often lies in histograms and data distribution.
Step 3: Check Histogram Behaviour on Critical Columns
Histograms help Oracle understand data distribution within columns, which is essential for generating efficient execution plans. However, they can also introduce instability when data is skewed.
To investigate further, the histogram for the relevant column was examined.
SQL> SELECT endpoint_value, endpoint_number FROM dba_tab_histograms WHERE table_name='EMPLOYEE' AND column_name='EMPLOYEE_FEED';
The results showed a highly skewed distribution in the EMPLOYEE_FEED column.
When statistics were gathered using the default AUTO_SAMPLE_SIZE, the optimizer misinterpreted the data distribution and selected an inefficient execution plan.
This is not uncommon in production environments with skewed data patterns, such as regional or status-based columns.
🔹Fixing the Issue: Stabilizing the Optimizer
Once the root cause was identified, two corrective actions were taken.
1. Regather Statistics with Controlled Sampling
Instead of relying on automatic sampling, statistics were gathered with a manual sample size.
EXEC DBMS_STATS.GATHER_TABLE_STATS( 'SALES', 'CUSTOMERS', estimate_percent=>50, method_opt=>'FOR ALL COLUMNS SIZE AUTO' );
Using a higher sample percentage provides more accurate cardinality estimates, especially for skewed datasets.
2. Capture the Good Execution Plan Using SQL Plan Baselines
To prevent the optimizer from choosing an unstable plan again, the working plan was locked using SQL Plan Management.
EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id=>'sqlid0sqltext1' );
SQL Plan Baselines allow Oracle to preserve known good plans, preventing unexpected performance regressions caused by statistics updates.
Results After the Fix
The impact of these changes was immediate.
-
CPU usage dropped from 95% → 40%
-
Query response times returned to normal
-
Application performance stabilized
This confirmed that the issue was entirely caused by optimizer misestimation due to statistics sampling and skewed data.
Unique Insight: Why Auto Statistics Sometimes Fail
Oracle's automatic statistics gathering works well in most environments, but it can struggle in situations involving:
-
Highly skewed data
-
Rapidly changing data distributions
-
Large partitioned tables
-
Queries sensitive to histogram accuracy
Many DBAs assume automatic statistics are always safe, but in high-scale systems, controlled statistics strategies often produce more predictable performance.
Experienced DBAs frequently implement:
-
Custom stats gathering schedules
-
Column-level histogram control
SQL Plan Baselines for critical queries
This combination provides both optimizer intelligence and plan stability.
Quick Takeaways
-
High CPU spikes in Oracle databases are often caused by execution plan changes.
-
Always identify the top CPU-consuming SQL statements first.
-
Statistics updates can trigger unexpected optimizer decisions.
-
Histograms on skewed columns may cause incorrect cardinality estimates.
-
Manual statistics sampling can improve optimizer accuracy.
-
SQL Plan Baselines help prevent performance regressions.
-
Stable statistics strategies are critical for large production systems.
🔹Conclusion
Unexpected CPU spikes are among the most stressful incidents for any Oracle DBA. What makes them particularly challenging is that they often occur without obvious changes to the system.
As this case demonstrated, the root cause can sometimes be hidden in something as routine as automatic statistics collection. When data distribution is skewed, the optimizer may misinterpret cardinality estimates and choose inefficient execution plans.
The key to resolving such issues quickly is a structured investigation approach. Start by identifying the top SQL statements consuming resources, verify whether the execution plan has changed, and analyze histograms or statistics that might have influenced the optimizer’s decision.
More importantly, prevention is just as critical as troubleshooting. Implementing controlled statistics gathering, monitoring histogram behavior, and protecting critical queries with SQL Plan Baselines can significantly reduce the risk of unexpected performance regressions.
For Oracle DBAs managing high-volume production systems, understanding how the optimizer interprets statistics is a crucial skill. The database optimizer is powerful—but like any intelligent system, it performs best when guided with the right data and safeguards.
If you’ve encountered similar optimizer surprises in your environment, consider reviewing your statistics strategy today. A small adjustment could prevent the next major performance incident.
🔹Frequently Asked Questions (FAQs)
1. Why do Oracle execution plans change after statistics gathering?
Execution plans may change because the optimizer uses updated statistics to estimate cardinality and costs. If the new statistics differ significantly from previous ones, the optimizer may choose a different plan.
2. What is AUTO_SAMPLE_SIZE in Oracle statistics gathering?
AUTO_SAMPLE_SIZE allows Oracle to automatically determine the sampling rate for statistics collection. While convenient, it may sometimes misrepresent skewed data distributions.
3. When should DBAs use SQL Plan Baselines?
SQL Plan Baselines are useful for critical queries where performance stability is essential, especially in systems with frequent statistics updates.
4. How do histograms affect query performance?
Histograms provide detailed information about data distribution. They help the optimizer estimate row counts accurately, but if misinterpreted, they can also lead to inefficient execution plans.
5. How can DBAs prevent optimizer-related performance issues?
Best practices include:
-
Monitoring execution plan changes
-
Managing histograms carefully
-
Using SQL Plan Management
-
Gathering statistics with controlled sampling
Have you ever experienced a sudden Oracle CPU spike caused by an optimizer plan change? Would love to hear how you diagnosed and resolved it in your environment.
If you found this article useful, feel free to share it with fellow DBAs or your database engineering team. Real-world troubleshooting stories often help others avoid the same production surprises.
Your feedback and experiences are always welcome!
No comments:
Post a Comment