What Causes High CPU Usage in Oracle Database and How to Troubleshoot It?

 

Introduction

High CPU usage in an Oracle Database can lead to sluggish application performance, delayed queries, and frustrated users. As a DBA or developer, pinpointing the root cause and resolving it is critical to maintaining a healthy database environment. This comprehensive guide explores the common causes of high CPU usage, provides actionable troubleshooting steps, and includes real-life examples with scripts to help you diagnose and fix issues. Whether you're new to Oracle or an experienced professional, this tutorial covers basic to advanced techniques, best practices, and standards to keep your database running smoothly.

---

Common Causes of High CPU Usage

High CPU usage in Oracle Database can stem from various factors. Understanding these causes is the first step toward effective troubleshooting:

1. Inefficient SQL Queries: Poorly optimized queries with excessive logical reads, complex joins, or nested loops.

2. Session Contention: Multiple sessions competing for CPU resources, often due to high concurrency.

3. Parsing Overhead: Excessive hard parsing caused by non-reusable SQL statements.

4. Background Processes: Oracle background processes like DBWn or LGWR consuming CPU.

5. Application Design Issues: Inefficient application logic sending repetitive or poorly structured queries.

6. System Resource Constraints: Limited CPU cores or overcommitted server resources.

7. External Factors: Other applications or processes on the server competing for CPU.

---

Step 1: Identifying High CPU Usage

Before resolving high CPU usage, you need to confirm it’s happening and identify the contributing factors.

1.1 Using V$SYSSTAT and V$OSSTAT

The V$SYSSTAT view provides database-level CPU usage, while V$OSSTAT shows OS-level CPU metrics.

Example Query to Check CPU Usage

SELECT 
    s.name,
    s.value / 100 AS cpu_seconds
FROM 
    v$sysstat s
WHERE 
    s.name LIKE '%CPU used%';

SELECT 
    stat_name,
    value
FROM 
    v$osstat
WHERE 
    stat_name IN ('NUM_CPUS', 'BUSY_TIME', 'IDLE_TIME');

Explanation:

• CPU used by this session in V$SYSSTAT shows CPU time consumed by database sessions (in centiseconds).

• V$OSSTAT provides the number of CPUs and their busy/idle times.

• High BUSY_TIME relative to IDLE_TIME indicates CPU contention.

Real-Life Scenario: For an inventory management system, users report slow response times during peak hours. The above query shows cpu_seconds spiking to 500 seconds and BUSY_TIME at 80% of total CPU time, confirming high CPU usage.

1.2 Using Automatic Workload Repository (AWR)

AWR reports provide a detailed breakdown of CPU usage over time.

Steps:

1. Run the AWR report: @$ORACLE_HOME/rdbms/admin/awrrpt.sql

2. Select a snapshot range (e.g., last 1 hour).

3. Check the “Top Timed Events” section for CPU-related events like “CPU + Wait for CPU.”

Pros:

• Comprehensive view of CPU usage across workloads.

• Identifies top CPU-consuming SQL statements.

Cons:

• Requires Diagnostic Pack license.

• Can be complex for beginners.

Alternative: Use Active Session History (ASH) for real-time insights without a license.

ASH Query Example:

SELECT 
    sql_id,
    COUNT(*) AS session_count,
    SUM(cpu_time_total) / 1000000 AS cpu_seconds
FROM 
    v$active_session_history
WHERE 
    sample_time > SYSDATE - 1/24 -- Last hour
    AND session_state = 'ON CPU'
GROUP BY 
    sql_id
ORDER BY 
    cpu_seconds DESC
FETCH FIRST 5 ROWS ONLY;

---

Step 2: Pinpointing CPU-Intensive Queries

Once high CPU usage is confirmed, identify the queries or sessions driving it.

2.1 Using V$SQL

The V$SQL view helps identify CPU-intensive queries based on cpu_time.

Example Query:

SELECT 
    sql_id,
    sql_text,
    executions,
    cpu_time / 1000000 AS cpu_seconds,
    buffer_gets
FROM 
    v$sql
WHERE 
    cpu_time > 1000000 -- Queries using > 1 second CPU
    AND executions > 0
ORDER BY 
    cpu_time DESC
FETCH FIRST 10 ROWS ONLY;

Observation: Queries with high cpu_time and buffer_gets indicate excessive logical reads, often due to inefficient SQL.

2.2 Using V$SESSION

Identify sessions consuming CPU in real time.

Example Query:

SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.sql_id,
    s.status,
    s.cpu_usage
FROM 
    v$session s
WHERE 
    s.status = 'ACTIVE'
    AND s.cpu_usage > 0
ORDER BY 
    s.cpu_usage DESC;

Real-Life Scenario: In the inventory system, the V$SQL query reveals a sql_id = 'xyz789' consuming 10 CPU seconds per execution due to a complex report query scanning a large ORDERS table.

---

Step 3: Analyzing Query Execution Plans

High CPU usage often results from suboptimal execution plans. Analyze them to identify inefficiencies.

3.1 Using EXPLAIN PLAN

Generate the execution plan for a suspect query.

Example:

EXPLAIN PLAN FOR
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date > SYSDATE - 30;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Sample Output:

------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Cost |
------------------------------------------------------
|   0 | SELECT STATEMENT   |            | 10000 |  2000|
|   1 |  HASH JOIN         |            | 10000 |  2000|
|   2 |   TABLE ACCESS FULL| ORDERS     | 50000 |  1200|
|   3 |   TABLE ACCESS FULL| CUSTOMERS  |   500 |   300|
------------------------------------------------------

Observation: The TABLE ACCESS FULL on ORDERS suggests a full table scan, which can be CPU-intensive for large tables.

3.2 Using DBMS_XPLAN.DISPLAY_CURSOR

For real-time plans, use DBMS_XPLAN.DISPLAY_CURSOR.

Example:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('xyz789', NULL, 'ALLSTATS LAST'));

Pros:

• Shows actual CPU usage and row counts.

• Highlights discrepancies between estimated and actual costs.

Cons:

• Requires sql_id and cursor information.

• May need elevated permissions.

Best Practice: Compare estimated and actual plans to identify optimizer misjudgments.

---

Step 4: Resolving High CPU Usage

With the problematic queries or sessions identified, apply these techniques to reduce CPU usage.

4.1 Optimizing SQL Queries

Rewrite inefficient queries to reduce CPU consumption.

Example: Inefficient Query

SELECT order_id
FROM orders
WHERE TO_CHAR(order_date, 'YYYY-MM-DD') = '2025-08-01';

Optimized Version:

SELECT order_id
FROM orders
WHERE order_date = TO_DATE('2025-08-01', 'YYYY-MM-DD');

Why It’s Better:

• Avoids function-based operations on columns, enabling index usage.

• Reduces CPU overhead from data conversions.

Real-Life Scenario: In the inventory system, rewriting a report query to avoid TO_CHAR reduced CPU usage by 40%.

4.2 Adding Indexes

Indexes reduce logical reads, lowering CPU usage.

Example: Create an index on order_date.

CREATE INDEX idx_orders_date ON orders(order_date);

After Index Execution Plan:

------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Cost |
------------------------------------------------------
|   0 | SELECT STATEMENT     |                 | 10000 |   400|
|   1 |  HASH JOIN           |                 | 10000 |   400|
|   2 |   INDEX RANGE SCAN   | IDX_ORDERS_DATE | 10000 |   100|
|   3 |   TABLE ACCESS FULL  | CUSTOMERS       |   500 |   300|
------------------------------------------------------

Pros:

• Reduces CPU usage by minimizing logical reads.

• Speeds up query execution.

Cons:

• Increases storage and DML overhead.

• Requires regular maintenance.

Best Practice: Use composite indexes for multi-column filters, e.g., CREATE INDEX idx_orders_date_cust ON orders(order_date, customer_id).

4.3 Reducing Hard Parsing

Excessive hard parsing due to non-reusable SQL consumes CPU. Use bind variables to promote cursor sharing.

Example: Non-Reusable SQL

SELECT product_name FROM products WHERE product_id = 123;
SELECT product_name FROM products WHERE product_id = 456;

Optimized with Bind Variables:

SELECT product_name FROM products WHERE product_id = :prod_id;

Pros:

• Reduces CPU usage from parsing.

• Improves scalability for high-concurrency systems.

Cons:

• Requires application code changes.

• May not suit all dynamic SQL scenarios.

Best Practice: Set CURSOR_SHARING = FORCE at the database level for legacy applications, but test thoroughly.

4.4 Managing Session Contention

High concurrency can lead to CPU contention. Limit parallel sessions or adjust resource allocation.

Example: Limit sessions per user.

ALTER PROFILE default LIMIT SESSIONS_PER_USER 5;

Alternative: Use Resource Manager to prioritize CPU allocation.

BEGIN
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        plan => 'DAYTIME_PLAN',
        group_or_subplan => 'REPORTING',
        cpu_p1 => 50 -- Allocate 50% CPU to reporting group
    );
END;
/

4.5 Updating Statistics

Stale statistics can lead to CPU-intensive plans.

Example:

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'ORDERS');

Real-Life Scenario: Updating statistics on the ORDERS table reduced CPU usage by 30% by enabling the optimizer to choose an index scan over a full table scan.

Best Practice: Schedule statistics updates during low-usage periods.

---

Step 5: Advanced Troubleshooting Techniques

For persistent CPU issues, consider these advanced approaches:

5.1 SQL Tuning Advisor

Oracle’s SQL Tuning Advisor analyzes queries and suggests optimizations.

Example:

BEGIN
    DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_id => 'xyz789',
        task_name => 'TUNE_ORDER_REPORT'
    );
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK('TUNE_ORDER_REPORT');
    DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_ORDER_REPORT');
END;
/

Pros:

• Automated index and profile recommendations.

• Integrates with Oracle’s optimizer.

Cons:

• Requires Tuning Pack license.

• May suggest overly complex solutions.

5.2 Parallel Execution

Distribute CPU-intensive queries across multiple cores.

Example:

SELECT /*+ PARALLEL(o, 4) */ order_id
FROM orders o
WHERE order_date > SYSDATE - 30;

Pros:

• Reduces CPU load per core.

• Speeds up large queries.

Cons:

• Can increase overall CPU usage.

• Not suitable for all queries.

Best Practice: Test parallel execution in a non-production environment first.

5.3 Database Configuration

Adjust parameters like CPU_COUNT or PARALLEL_MAX_SERVERS to manage CPU allocation.

Example:

ALTER SYSTEM SET parallel_max_servers = 16;

---

Step 6: Monitoring and Maintenance

Ongoing monitoring prevents CPU usage spikes:

• Use OEM: Oracle Enterprise Manager for real-time CPU alerts.

• Custom Scripts: Schedule queries to track V$SYSSTAT and V$OSSTAT.

• Regular Audits: Review top CPU-consuming queries weekly.

Example Monitoring Script:

SELECT 
    sql_id,
    sql_text,
    cpu_time / 1000000 AS avg_cpu_seconds
FROM 
    v$sql
WHERE 
    executions > 100
    AND cpu_time / executions > 1000000 -- Average CPU > 1 second
ORDER BY 
    cpu_time / executions DESC;

---

Pros and Cons of Troubleshooting Techniques

Technique	Pros	Cons
Indexing	Reduces CPU via fewer reads	Increases storage and DML overhead
Query Optimization	No additional resources needed	Requires SQL expertise
Bind Variables	Reduces parsing CPU usage	May need application changes
Resource Manager	Controls CPU allocation	Complex to configure
Statistics Updates	Improves optimizer decisions	Maintenance overhead

---

Best Practices and Standards

1. Monitor Proactively: Set up alerts for CPU usage exceeding 80%.

2. Optimize First: Focus on query tuning before adding hardware.

3. Use Bind Variables: Minimize hard parsing in high-concurrency systems.

4. Test Changes: Validate optimizations in a test environment.

5. Document Fixes: Log all changes for future reference.

---

Real-Life Case Study

Scenario: An inventory management system experiences high CPU usage during daily reports, causing delays. Using V$SQL, the DBA identifies a query (sql_id = 'xyz789') consuming 15 CPU seconds per execution.

Actions:

1. Added an index on order_date.

2. Rewrote the query to use bind variables.

3. Updated table statistics.

4. Configured Resource Manager to limit reporting sessions.

Result: CPU usage dropped by 50%, and report generation time fell from 20 seconds to 5 seconds.

---

Conclusion

High CPU usage in Oracle Database can stem from inefficient queries, parsing overhead, or resource contention. By systematically monitoring with V$SYSSTAT, V$SQL, and AWR, analyzing execution plans, and applying optimizations like indexing, query rewriting, and Resource Manager, you can restore performance. Regular monitoring and adherence to best practices ensure long-term stability. Start by running the V$SQL query on your database to identify CPU hogs and share your results in the comments!

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone