When an Oracle database becomes slow, the first question every DBA needs to answer is:

What is actually causing the performance problem?

Is it CPU?
Is it I/O?
Is it a bad SQL query?
Is another session blocking the application?
Is there memory pressure?
Or is the problem somewhere outside the database?

Instead of guessing, an Oracle DBA should follow a systematic troubleshooting approach.

In this guide, we will learn how to troubleshoot Oracle Database performance using AWR, ASH, ADDM, wait events, Top SQL, and execution plans.

The examples are useful for Oracle Database 19c and newer environments, including Oracle AI Database 26ai.

What Is Oracle Database Performance Tuning?

Oracle Database performance tuning is the process of identifying the reason a database is taking too long to complete work and then making targeted changes to improve performance.

A simple performance-tuning process is:

Identify the problem → Collect evidence → Find the root cause → Apply the fix → Validate the result

The most important part is to avoid making changes before understanding the problem.

For example, if CPU utilization is high, increasing CPU may not solve the real problem.

A single SQL statement performing unnecessary work could be responsible for most of the CPU usage.

Therefore, the first question should always be:

Why is the resource being consumed?

Common Oracle Database Performance Problems

Oracle performance problems generally fall into a few common categories.

1. High CPU Usage

A database may become slow because the server is spending too much time processing CPU-intensive workload.

Common symptoms include:

• High server CPU utilization
• High DB CPU
• SQL statements consuming excessive CPU
• Slow application response
• CPU-related wait activity

The important question is:

Which SQL or workload is consuming the CPU?

Do not immediately increase CPU resources. First identify the workload responsible.

2. I/O Bottleneck

An Oracle database can also become slow when sessions spend significant time waiting for disk or storage operations.

Common symptoms include:

• High physical reads
• High I/O wait time
• Slow queries
• Slow full table scans
• High storage latency

However, high I/O does not always mean the storage system is the problem.

For example:

A poorly designed SQL statement may read millions of blocks unnecessarily.

The real problem may therefore be the SQL execution plan rather than the storage itself.

3. Blocking and Lock Contention

Sometimes the database is not actually processing slowly.

Sessions may simply be waiting for another session to release a lock.

For example:

Session A
   |
   | Holds lock
   ↓
Table / Row
   ↑
   | Waiting
Session B

This can result in:

• Application timeouts
• Long-running transactions
• Sessions appearing hung
• DML failures
• Lock-related wait events

Before killing a session, understand which session is blocking whom and what transaction is involved.

4. High-Load SQL

One SQL statement can sometimes consume a significant amount of database resources.

A SQL statement may consume large amounts of:

• CPU
• DB time
• Logical reads
• Physical reads
• I/O
• Memory
• Concurrency resources

Finding the SQL responsible is often one of the most important steps in Oracle performance troubleshooting.

Oracle Database Performance Troubleshooting Workflow

When an application team reports:

“The Oracle database is slow.”

A practical DBA workflow is:

Application reports slowness
          ↓
Identify the exact time period
          ↓
Check database health
          ↓
Check CPU / Memory / I/O
          ↓
Review ADDM
          ↓
Review AWR
          ↓
Use ASH for the specific time window
          ↓
Identify Top SQL
          ↓
Check the SQL execution plan
          ↓
Find the root cause
          ↓
Apply a targeted fix
          ↓
Validate the result

This approach is much safer than randomly changing database parameters.

What Is AWR in Oracle?

AWR stands for Automatic Workload Repository.

AWR collects and stores database performance information that can be used to analyze workload over time.

AWR data can include information about:

• Database workload
• DB time
• CPU usage
• Wait events
• SQL statistics
• System statistics
• Time model statistics
• Active Session History information
• Object activity

AWR is especially useful when the performance problem happened in the past and is no longer occurring.

For example, suppose users reported that the application was slow between 10:00 AM and 12:00 PM.

You can compare AWR snapshots covering that period and investigate what happened.

Important licensing note

AWR and other Diagnostic Pack features are subject to Oracle licensing requirements. Always verify the applicable Oracle licensing terms for your environment before using licensed functionality.

How to Check AWR Snapshots

You can check available AWR snapshots using:

SELECT snap_id,
       begin_interval_time,
       end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC;

You can then generate an AWR report using Oracle’s supplied script:

@?/rdbms/admin/awrrpt.sql

Oracle will ask you for information such as:

• Report type
• Number of days
• Begin Snapshot ID
• End Snapshot ID
• Report name

For example:

Begin Snapshot: 1000
End Snapshot:   1002

This allows you to analyze the workload between those snapshots.

How to Read an AWR Report

An AWR report contains a lot of information.

You do not need to read every section from top to bottom.

Start with the sections that answer one question:

Where is the database spending its time?

1. Load Profile

The Load Profile provides a quick overview of database workload.

Important metrics include:

• DB Time
• DB CPU
• Logical reads
• Physical reads
• Parses
• Executes
• Transactions
• User calls

Look for unusual values and compare them with a normal period whenever possible.

2. Top Foreground Events

The wait-event section helps you understand where foreground sessions are spending time.

For example:

Event                              % DB Time
------------------------------------------------
db file sequential read               35%
CPU + Wait for CPU                    28%
log file sync                         15%
enq: TX - row lock contention        10%
Other                                 12%

These numbers are only an example.

The important point is that a wait event should not automatically be treated as the root cause.

A wait is often a symptom of another problem.

For example:

High physical reads
        ↓
High I/O waits
        ↓
Why are reads high?
        ↓
Inefficient SQL
        ↓
Poor execution plan

The DBA should follow the chain back to the root cause.

What Is ASH in Oracle?

ASH stands for Active Session History.

ASH provides sampled information about active database sessions.

It is particularly useful when the performance problem:

• Happened for a short period
• Is intermittent
• Is happening right now
• Is difficult to identify from a larger AWR interval

For example, suppose an application team reports:

“The application was extremely slow between 10:32 AM and 10:37 AM.”

Instead of looking at an entire day’s workload, ASH can help you investigate that specific period.

What Should You Look for in ASH?

Important ASH dimensions include:

• SQL ID
• Session
• User
• Wait event
• Wait class
• Instance
• Service
• Module
• Action

For example:

Time       SQL_ID       Event
-----------------------------------------
10:32      8f3abc       db file sequential read
10:33      8f3abc       db file sequential read
10:34      8f3abc       CPU
10:35      8f3abc       CPU

Now you have a useful starting point.

You can investigate SQL ID 8f3abc and determine why it was consuming resources.

What Is ADDM?

ADDM stands for Automatic Database Diagnostic Monitor.

ADDM analyzes AWR data and identifies significant database performance problems.

Depending on the environment and workload, ADDM can identify issues involving areas such as:

• CPU
• I/O
• High-load SQL
• Concurrency
• Memory
• Configuration
• Application workload
• RAC-related performance

ADDM can also provide recommendations.

However, an ADDM recommendation should not automatically be implemented in production.

A DBA should validate the recommendation against the actual environment and business workload before making changes.

AWR vs ASH vs ADDM

This is also a common Oracle DBA interview question.

An easy way to remember:

AWR → What happened?

ASH → What was happening during a specific period?

ADDM → What performance problems did Oracle identify?

Top SQL → Which SQL consumed the resources?

Execution Plan → How was that SQL executed?

Finding Top SQL in Oracle

Once you know that SQL is responsible for the performance problem, identify the SQL ID.

For example:

SELECT *
FROM (
    SELECT sql_id,
           executions,
           ROUND(elapsed_time / 1000000, 2) AS elapsed_seconds,
           ROUND(cpu_time / 1000000, 2) AS cpu_seconds,
           buffer_gets,
           disk_reads
    FROM v$sql
    ORDER BY elapsed_time DESC
)
WHERE ROWNUM <= 20;

This provides a starting point for identifying SQL statements with high total elapsed time.

However, do not automatically assume that the SQL with the highest total elapsed time is always the statement that needs immediate attention.

Consider:

• Total elapsed time
• Average elapsed time
• Number of executions
• CPU consumption
• Logical reads
• Physical reads
• Business importance

For example:

SQL A
Executions:   1
Elapsed time: 30 minutes

SQL B
Executions:   500,000
Elapsed time: 20 minutes

SQL B may deserve significant investigation because it runs a very large number of times.

Always interpret SQL statistics in context.

Check the SQL Execution Plan

After identifying a problematic SQL statement, examine its execution plan.

A useful command is:

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

Replace YOUR_SQL_ID with the actual SQL ID.

Look for:

• Full table scans
• Unexpected join methods
• Incorrect row estimates
• Large differences between estimated and actual rows
• Excessive rows processed
• Poor access paths
• Large sorts
• Excessive logical reads
• Excessive physical reads

But remember:

A full table scan is not automatically a problem.

If Oracle needs to read a large percentage of a table, a full table scan may be the appropriate access method.

The goal is not to eliminate every full table scan.

The goal is to determine whether the execution plan is appropriate for the workload.

One of the Most Important Oracle DBA Rules

Never tune based on a single symptom.

For example:

High CPU

does not automatically mean:

Increase CPU

Similarly:

High physical reads

does not automatically mean:

Increase buffer cache

And:

High waits

does not automatically mean:

Change a database parameter

Instead, follow this approach:

Symptom
   ↓
Collect evidence
   ↓
Analyze the workload
   ↓
Identify root cause
   ↓
Apply targeted fix
   ↓
Validate

This mindset is one of the most important skills for an Oracle DBA.

Example: Troubleshooting a Slow SQL Query

Suppose an application report normally takes 2 minutes but suddenly takes 20 minutes.

Step 1: Identify the Time Period

First determine exactly when the problem occurred.

For example:

Problem window:
10:00 AM – 10:20 AM

Step 2: Check ADDM

Look for significant findings reported during the affected period.

Step 3: Check AWR

Review:

• DB Time
• DB CPU
• Wait events
• Top SQL
• Load Profile

Step 4: Check ASH

Use ASH to identify active sessions and SQL IDs during the exact problem window.

Suppose you identify:

SQL_ID = 4m7abc123

Step 5: Check the Execution Plan

Run:

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

Suppose you find:

Estimated rows: 100
Actual rows: 5,000,000

That difference is an important clue.

You can then investigate:

• Optimizer statistics
• Data distribution
• SQL predicates
• Bind variables
• Indexes
• Join conditions
• Execution plan changes

Step 6: Apply a Targeted Fix

Only after identifying the root cause should you make a production change.

The appropriate fix depends on what the investigation shows.

Step 7: Validate the Result

After implementing the fix, measure the performance again.

For example:

Before:
Elapsed time = 20 minutes

After:
Elapsed time = 2 minutes

Performance tuning is not complete until the improvement has been measured and validated.

Useful Oracle Performance SQL Queries

1. Check Database Time Model

SELECT stat_name,
       ROUND(value / 1000000, 2) AS seconds
FROM v$sys_time_model
WHERE stat_name IN (
    'DB CPU',
    'DB time'
);

2. Find SQL With High Elapsed Time

SELECT *
FROM (
    SELECT sql_id,
           executions,
           ROUND(elapsed_time / 1000000, 2) AS elapsed_seconds,
           ROUND(cpu_time / 1000000, 2) AS cpu_seconds,
           buffer_gets,
           disk_reads
    FROM v$sql
    ORDER BY elapsed_time DESC
)
WHERE ROWNUM <= 20;

3. Find SQL With High CPU Usage

SELECT *
FROM (
    SELECT sql_id,
           executions,
           ROUND(cpu_time / 1000000, 2) AS cpu_seconds,
           buffer_gets,
           disk_reads
    FROM v$sql
    ORDER BY cpu_time DESC
)
WHERE ROWNUM <= 20;

4. Check Active Sessions

SELECT sid,
       serial#,
       username,
       status,
       event,
       wait_class,
       sql_id
FROM v$session
WHERE status = 'ACTIVE'
ORDER BY sid;

Common Oracle DBA Performance Tuning Mistakes

Mistake 1: Immediately Changing SGA or PGA

Do not change memory parameters simply because the database is slow.

First establish whether memory is actually the bottleneck.

Mistake 2: Killing Sessions Without Investigation

A session may be blocking other sessions, but killing it can cause:

• Transaction rollback
• Application errors
• Business impact

Identify the blocker and understand the transaction before taking action.

Mistake 3: Assuming Every Problem Is a Database Problem

Performance issues can exist at multiple layers:

Application
     ↓
SQL
     ↓
Database
     ↓
Storage
     ↓
Network

The DBA should identify the affected layer instead of automatically assuming the database is responsible.

Mistake 4: Tuning SQL Without Checking the Execution Plan

Changing SQL or adding indexes without understanding the execution plan can make performance worse.

Always investigate before making changes.

Mistake 5: Looking Only at Averages

A database may look healthy over an entire day while experiencing a severe five-minute performance incident.

This is one reason ASH and targeted time-window analysis are valuable.

Oracle RAC Performance Troubleshooting

In an Oracle RAC environment, always consider whether the problem affects one instance or the entire cluster.

For example:

Instance 1 → Normal
Instance 2 → High CPU
Instance 3 → Normal

A problem may therefore be isolated to a particular instance, service, SQL workload, or resource.

RAC troubleshooting may require investigation of:

• Instance
• Service
• SQL ID
• Wait events
• Global Cache activity
• Interconnect
• Hot blocks
• CPU
• I/O

A RAC performance problem should not automatically be treated as a database-wide problem.

Oracle Database 26ai and Performance Troubleshooting

Oracle AI Database 26ai is the current long-term-support release of Oracle Database.

While 26ai is well known for its AI and vector capabilities, Oracle has also introduced improvements across database administration and performance management.

Oracle continues to enhance capabilities around workload analysis and performance monitoring.

For DBAs, this makes traditional skills such as:

• AWR analysis
• ASH analysis
• SQL tuning
• Wait-event analysis
• Execution-plan analysis
• Root-cause troubleshooting

still highly relevant in modern Oracle environments.

Oracle DBA Performance Tuning Checklist

When someone says:

“Oracle Database is slow.”

Work through this checklist.

Database Health

• Is the database available?
• Are required services running?
• Are there unusual alerts?
• Are sessions increasing unexpectedly?

CPU

• Is CPU saturated?
• Is DB CPU high?
• Which SQL is consuming CPU?

Memory

• Is there memory pressure?
• Is PGA usage unusually high?
• Are there memory-related findings?

I/O

• Are physical reads high?
• Are I/O wait events significant?
• Is storage latency normal?

Sessions

• Are there blocking sessions?
• Are sessions waiting?
• Are there abnormal connection spikes?

SQL

• Which SQL consumes the most DB time?
• Which SQL consumes the most CPU?
• Which SQL performs excessive reads?
• Has the execution plan changed?

AWR

• What changed during the problem period?
• What are the major wait events?
• Which SQL statements dominate DB time?

ASH

• What was happening during the exact incident?
• Which SQL IDs were active?
• Which sessions were waiting?

ADDM

• What problems did Oracle identify?
• Do the recommendations match the evidence?

Validation

• Did the change improve performance?
• Did it create another problem?
• Can the improvement be measured?

The Oracle Performance Tuning Formula

A simple way to remember the complete process is:

Don't Guess
     ↓
Collect Data
     ↓
Find DB Time
     ↓
Identify Waits
     ↓
Find Top SQL
     ↓
Check Execution Plan
     ↓
Find Root Cause
     ↓
Apply Targeted Fix
     ↓
Validate

Final Thoughts

Oracle performance tuning is not about memorizing hundreds of initialization parameters.

It is about knowing how to investigate a problem systematically.

When a production database becomes slow, do not immediately change:

• SGA
• PGA
• Optimizer parameters
• Indexes
• Database parameters

Start with evidence.

Use ADDM to understand diagnosed performance problems.

Use AWR to analyze historical workload.

Use ASH to investigate active sessions and specific time periods.

Use Top SQL to identify resource-intensive SQL.

Use execution plans to understand how SQL is being executed.

Then make a targeted change and measure the result.

The most important rule for an Oracle DBA is simple:

Find the evidence first. Fix the root cause second.

If you follow this approach consistently, you can troubleshoot many Oracle performance problems without relying on guesswork.