Oracle Top SQL: Finding Your Worst Queries

Why Top SQL Analysis Matters

The 80/20 rule applies almost universally to Oracle performance: 80% of the database time is consumed by 20% of the SQL statements. Often it's more extreme — a single poorly-written query or a missing index can account for 50%+ of all database time.

Before touching configuration parameters, memory settings, or hardware, identify and fix your top SQL. A well-indexed query doesn't need a faster disk.

Method 1: AWR Top SQL Sections

The AWR report includes several SQL sections, each ranking statements by a different metric. They appear in the HTML report after the instance statistics sections.

SQL Ordered by Elapsed Time

Location in AWR: "SQL ordered by Elapsed Time"

What it shows: Total wall-clock time consumed by all executions during the AWR window. This is your primary ranking — it directly answers "what made users wait?"

Elapsed Time (s)  CPU Time (s)  Executions  Elap per Exec (s)  SQL Id
--------------  -----------  ----------  -----------------  -----------
       8,234.2      6,891.3      12,847              0.641  abc123def456
       3,127.8      3,127.4         234             13.367  xyz789ghi012

How to read it: - The first entry ran 12,847 times, averaging 0.641 seconds each — a frequently executed query that's a little slow - The second ran 234 times, averaging 13.4 seconds each — a slow query with low execution count

Both are problems, but they have different fixes.

SQL Ordered by CPU Time

What it shows: Statements consuming the most CPU. High CPU usually means: - Large sort or hash join operations - Excessive parsing (hard parse CPU overhead) - String manipulation functions on large result sets - Full table scans returning many rows

When CPU dominates your wait profile, start with this section.

SQL Ordered by Gets (Buffer Gets / Logical I/O)

What it shows: Statements reading the most blocks from the buffer cache (logical reads). High gets can indicate: - Full table scans (reading millions of blocks) - Inefficient index range scans - Missing composite indexes

A query with extremely high gets but low elapsed time is often executed very frequently — the per-call cost is small but the aggregate is large.

SQL Ordered by Reads (Physical Reads)

What it shows: Statements generating the most disk I/O. If db file sequential read or db file scattered read dominate your wait profile, the top physical reads section reveals which SQL is responsible.

SQL Ordered by Executions

What it shows: The most frequently executed statements. A statement in the top-10 executions list but not in top-10 elapsed time is actually performing well despite high frequency — don't optimize it unless other symptoms point there.

SQL Ordered by Parse Calls

What it shows: Statements being parsed most frequently. High parse counts relative to execution counts indicate: - The application is not using bind variables (each different value = a new SQL text = a hard parse) - Cursor sharing is not working (cursor_sharing parameter) - The application is not reusing cursors

Method 2: Querying v$sql Directly

For real-time Top SQL analysis without generating an AWR report:

Current top SQL by elapsed time

SELECT
    sql_id,
    executions,
    ROUND(elapsed_time / 1000000, 2) AS elapsed_sec,
    ROUND(elapsed_time / NULLIF(executions, 0) / 1000000, 3) AS elapsed_per_exec_sec,
    ROUND(cpu_time / 1000000, 2) AS cpu_sec,
    disk_reads,
    buffer_gets,
    SUBSTR(sql_text, 1, 100) AS sql_text
FROM v$sql
WHERE executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

Top SQL by buffer gets (logical I/O)

SELECT
    sql_id,
    executions,
    buffer_gets,
    ROUND(buffer_gets / NULLIF(executions, 0)) AS gets_per_exec,
    ROUND(elapsed_time / NULLIF(executions, 0) / 1000000, 3) AS sec_per_exec,
    SUBSTR(sql_text, 1, 100) AS sql_text
FROM v$sql
WHERE executions > 0
ORDER BY buffer_gets DESC
FETCH FIRST 20 ROWS ONLY;

High parse ratio queries (missing bind variables)

SELECT
    sql_id,
    executions,
    parse_calls,
    ROUND(parse_calls / NULLIF(executions, 0) * 100, 1) AS parse_ratio_pct,
    SUBSTR(sql_text, 1, 100) AS sql_text
FROM v$sql
WHERE executions > 100
  AND parse_calls / NULLIF(executions, 0) > 0.9  -- more than 90% parse rate
ORDER BY parse_calls DESC
FETCH FIRST 20 ROWS ONLY;

A parse ratio close to 1.0 (100%) means the statement is re-parsed almost every time it runs — typically because literals are used instead of bind variables.

Method 3: ASH for Real-Time Top SQL

During an active performance problem, v$active_session_history shows which SQL is responsible right now:

-- Top SQL by active sessions in the last 5 minutes
SELECT
    sql_id,
    count(*) AS active_samples,
    ROUND(count(*) * 100.0 / SUM(count(*)) OVER(), 1) AS pct_of_activity,
    MAX(event) AS top_wait_event
FROM v$active_session_history
WHERE sample_time > SYSDATE - 5/1440  -- last 5 minutes
  AND session_state = 'WAITING'
  AND sql_id IS NOT NULL
GROUP BY sql_id
ORDER BY active_samples DESC
FETCH FIRST 10 ROWS ONLY;

Find the SQL causing a specific wait event

If db file sequential read is the top wait event, find the responsible SQL:

SELECT
    sql_id,
    count(*) AS samples,
    current_obj# AS object_id,
    o.object_name,
    o.object_type
FROM v$active_session_history ash
LEFT JOIN dba_objects o ON o.object_id = ash.current_obj#
WHERE event = 'db file sequential read'
  AND sample_time > SYSDATE - 1/24
GROUP BY sql_id, current_obj#, o.object_name, o.object_type
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;

Method 4: Historical Top SQL from AWR Tables

For Top SQL analysis over a specific period in the past (up to AWR retention limit):

-- Top SQL by elapsed time for a specific date range
SELECT
    s.sql_id,
    SUM(s.executions_delta) AS executions,
    ROUND(SUM(s.elapsed_time_delta) / 1000000, 2) AS elapsed_sec,
    ROUND(SUM(s.elapsed_time_delta) / NULLIF(SUM(s.executions_delta), 0) / 1000000, 3) AS sec_per_exec,
    ROUND(SUM(s.cpu_time_delta) / 1000000, 2) AS cpu_sec,
    SUM(s.disk_reads_delta) AS disk_reads,
    SUM(s.buffer_gets_delta) AS buffer_gets
FROM dba_hist_sqlstat s
JOIN dba_hist_snapshot sn ON sn.snap_id = s.snap_id
WHERE sn.begin_interval_time >= TO_DATE('2026-06-20 08:00', 'YYYY-MM-DD HH24:MI')
  AND sn.end_interval_time   <= TO_DATE('2026-06-20 18:00', 'YYYY-MM-DD HH24:MI')
GROUP BY s.sql_id
ORDER BY SUM(s.elapsed_time_delta) DESC
FETCH FIRST 20 ROWS ONLY;

Get the full SQL text for a specific sql_id:

SELECT sql_text
FROM dba_hist_sqltext
WHERE sql_id = 'abc123def456';

Getting the Execution Plan

Once you've identified a problematic SQL ID, examine its execution plan.

Current plan (from cursor cache)

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

Historical plan (from AWR)

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('abc123def456'));

What to look for in the execution plan

Full table scans on large tables:

| TABLE ACCESS FULL | ORDERS | 4500000 |

A full table scan on a multi-million row table is almost always a missing index. Check the filter predicates below the plan.

High cost operations:

| HASH JOIN          |         | 12000000 |
| NESTED LOOPS       |         |   450000 |

High row estimates on joins indicate the optimizer expects large intermediate result sets.

Wrong cardinality estimates: If the optimizer's estimated rows differ significantly from actual rows (visible with ALLSTATS), the statistics may be stale.

-- Check table statistics age
SELECT table_name, last_analyzed, num_rows
FROM dba_tables
WHERE owner = 'MYSCHEMA'
ORDER BY last_analyzed ASC NULLS FIRST;

Missing index on a join column:

| TABLE ACCESS FULL | ORDERS | (join key: CUSTOMER_ID) |

If CUSTOMER_ID is frequently joined to another table, it likely needs an index.

Common Top SQL Patterns and Fixes

Pattern 1: Full table scan, high elapsed time, high physical reads

Diagnosis: The query scans the entire table because no suitable index exists for the filter condition.

Fix:

-- Identify the filter from the execution plan
-- Create an index on the filter column(s)
CREATE INDEX idx_orders_status_date
ON orders(status, order_date)
PARALLEL 4;

-- Rebuild with statistics
DBMS_STATS.GATHER_TABLE_STATS('MYSCHEMA', 'ORDERS', cascade => TRUE);

Pattern 2: High executions, low elapsed per exec, high total time

Diagnosis: A fast query run millions of times. The individual cost is acceptable, but the aggregate is huge. Common in application code that queries within loops.

Fixes: - Review application code for N+1 query patterns - Add result caching if the data doesn't change frequently:

SELECT /*+ RESULT_CACHE */ customer_name FROM customers WHERE id = :1

- Ensure the query uses bind variables and shares cursors

Pattern 3: High parse calls relative to executions

Diagnosis: The application uses literal values in SQL, generating a unique statement for each different value. Each unique statement requires a hard parse.

-- Hard parse: two different SQL statements
SELECT * FROM orders WHERE id = 12345
SELECT * FROM orders WHERE id = 12346

-- Soft parse: one statement, reused
SELECT * FROM orders WHERE id = :1

Fixes: - Fix the application to use bind variables (preferred) - As a workaround, set cursor_sharing = FORCE (use with caution)

Pattern 4: High CPU, SORT operations in execution plan

Diagnosis: Large in-memory sorts because no index supports the ORDER BY clause, or PGA_AGGREGATE_TARGET is too small for the sort to complete in memory.

Fixes:

-- Add an index to eliminate the sort
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Or increase PGA for large sorts (session or system level)
ALTER SESSION SET sort_area_size = 67108864;  -- 64MB

-- System level (automatic PGA management)
ALTER SYSTEM SET pga_aggregate_target = 2G;

Pattern 5: High buffer gets, NESTED LOOPS with many rows

Diagnosis: A nested loop join driving many iterations against an inner table — often caused by wrong join order or missing composite index.

Fix:

-- Add a composite index covering the join key and filter
CREATE INDEX idx_order_items_order_product
ON order_items(order_id, product_id, quantity);

Tracking SQL Performance Over Time

Monitor whether your top SQL statements are improving or degrading:

-- Compare a specific SQL's performance across snapshots
SELECT
    sn.begin_interval_time,
    s.executions_delta,
    ROUND(s.elapsed_time_delta / NULLIF(s.executions_delta, 0) / 1000000, 3) AS sec_per_exec,
    s.disk_reads_delta,
    s.buffer_gets_delta
FROM dba_hist_sqlstat s
JOIN dba_hist_snapshot sn ON sn.snap_id = s.snap_id
WHERE s.sql_id = 'abc123def456'
ORDER BY sn.begin_interval_time DESC
FETCH FIRST 30 ROWS ONLY;

A sudden spike in sec_per_exec indicates a plan change — likely due to stale statistics, a new index, or parameter change.

Frequently Asked Questions

How do I find SQL that ran last night but is no longer in the cache? Query dba_hist_sqlstat (AWR) for the relevant snapshot IDs. If the SQL ran during a period covered by AWR retention (default 8 days), it will appear there.

My Top SQL changes every time I look at it — is something wrong? Not necessarily. Top SQL by elapsed time reflects the cumulative load since instance startup or last cursor flush. It changes as workload patterns shift. For a specific period, use AWR or filter v$sql by last_active_time.

What's the difference between buffer_gets and disk_reads? buffer_gets counts logical I/O — blocks read from the buffer cache (memory). disk_reads counts physical I/O — blocks read from disk because they weren't in cache. A high buffer_gets with low disk_reads means good cache utilization; the problem is still the number of logical reads, but it's a different (usually easier) fix.

Can I identify Top SQL without the Diagnostics Pack license? You can query v$sql for current cursor cache statistics without a license. The AWR-based methods (dba_hist_sqlstat, dba_hist_sqltext) require the Diagnostics Pack. Without it, you can also capture Top SQL manually using custom scripts that snapshot v$sql at regular intervals.

My Top SQL query has a good execution plan — why is it still slow? Common reasons: stale statistics (the plan looks good but estimates are wrong), data volume has grown beyond the plan's assumptions, lock contention on the accessed objects, or network latency between application and database. Use ASH to see the actual wait events during the query's execution.

Analyse Your Top SQL Automatically

Working through AWR Top SQL sections, correlating with ASH data, and knowing which execution plan issues to prioritize requires deep Oracle knowledge.

DBA Copilot automates this analysis. Upload your AWR report and the AI extracts the Top SQL, identifies the dominant wait events, correlates them with the responsible statements, and provides specific tuning recommendations — all in plain language.

Try DBA Copilot free — no credit card required