Troubleshooting Oracle SQL Performance with Execution Plans, ASH, and Targeted Statistics

Oracle performance problems rarely have a single universal solution. Adding an index, rewriting a query, gathering statistics, or adding a hint can all improve performance—but each can also make performance worse when applied without understanding what the optimizer is doing.

My approach is therefore to collect evidence before changing the SQL.

Start with the Execution Plan

For a statement being tested directly, I may begin with:

EXPLAIN PLAN FOR
SELECT ...;

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY(
        NULL,
        NULL,
        'ALL'
    )
);

For a statement that has actually executed, I generally prefer the runtime cursor plan:

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

This matters because an estimated plan and what actually occurred at runtime can be very different.

Compare Estimated and Actual Rows

E-Rows  → Estimated rows
A-Rows  → Actual rows

Large discrepancies can indicate that the optimizer does not have an accurate picture of the data. That may lead it to choose an inefficient join method, join order, scan strategy, or partition-access path.

Check Statistics Before Forcing a Plan

On very large partitioned tables, gathering statistics for the entire table may be unnecessary and expensive. Oracle allows statistics to be gathered for a specific partition:

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname  => 'MY_SCHEMA',
        tabname  => 'LARGE_FACT_TABLE',
        partname => 'P202403',
        cascade  => TRUE
    );
END;
/

Before gathering statistics across a massive table, I ask which data changed, which partition changed, whether the statistics are stale, and whether optimizer estimates resemble the actual row counts.

Look Backward with ASH

A particularly valuable Oracle capability is that the slow query does not necessarily need to still be running for investigation to begin. Active Session History can help reveal what happened during an earlier incident by examining values such as:

SQL_ID
EVENT
WAIT_CLASS
SESSION_STATE
SAMPLE_TIME
BLOCKING_SESSION

This helps answer whether the SQL was consuming CPU, waiting on I/O, blocked by another session, or behaving differently because of concurrency or a plan change.

Hints Are Evidence Too

The fact that a hint appears in SQL text does not guarantee that the optimizer honored it. A hint may be ignored because it is invalid, conflicts with another hint, references the wrong object or query block, or cannot be applied.

My General Performance Workflow

Identify the SQL_ID
       ↓
Check runtime behavior
       ↓
Examine execution plan
       ↓
Compare estimated vs actual rows
       ↓
Check predicates and join order
       ↓
Review statistics
       ↓
Check partition pruning
       ↓
Review indexes
       ↓
Use ASH/AWR for historical behavior
       ↓
Only then consider SQL changes or hints