Skip to content
DBAExperts
Oracle Diagnostics

How to find slow queries in Oracle with AWR and v$sql

Before you optimize you need to know which query is consuming the resources. Oracle stores that information; you just have to read it correctly.

  1. 1

    Find the most expensive SQL in memory

    v$sql shows active or recent queries ordered by elapsed time or reads.

    SELECT sql_id, elapsed_time/1e6 seg, executions, buffer_gets, sql_text
FROM v$sql
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;
  2. 2

    Generate an AWR report

    AWR compares two snapshots and summarizes the wait events and top SQL for the period. It requires the Diagnostics Pack license.

    SQL> @?/rdbms/admin/awrrpt.sql
  3. 3

    Review the actual plan of a SQL

    Use the SQL_ID to see the plan Oracle actually executed, not just the estimated one.

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

    Estimate with EXPLAIN PLAN

    For a query that has not run yet, review the estimated plan and watch for unexpected full scans.

    EXPLAIN PLAN FOR
SELECT * FROM pedidos WHERE fecha > SYSDATE - 30;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

// common mistake

Optimizing by total time without dividing by executions is misleading: a query that runs a million times can weigh more than a slow one that runs once.

// when it's worth an expert

If the system is slow and you cannot isolate the cause, or the plan changes for no apparent reason, a focused diagnosis is worthwhile. At dba.mx we do SQL tuning at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.