Top 10 Expensive SQL Queries in Oracle database: Easy Guide

Sometimes we need to find sql queries from AWR as performance is slowed down by costly SQL queries that use a lot of database resources. In this post, we will demonstrate how to use a basic SQL script to identify the 10 most expensive SQL queries in Oracle.

Source: AWR (Automatic Workload Repository) / DBA_HIST_SQLSTAT
Purpose: Identifies the SQL statements that consumed the most database resources (CPU, elapsed time, I/O) over a longer period.
Scope: Looks at historical SQL execution details stored in AWR snapshots rather than real-time queries.
Use Case:
Helps in identifying SQL performance issues over time.
Useful for tuning queries that have been consistently expensive over a period.
Requires Oracle Diagnostics Pack (AWR is available in Enterprise Edition with a license).

The SQL Query to extract SQL text from AWR:

The following script helps you identify the top 10 most expensive SQL queries in your Oracle database:

SET NUMWIDTH 10
SET TAB OFF
SET LINESIZE 200 
SET PAGESIZE 100 
SET LONG 1000000 
SET LONGCHUNKSIZE 1000
SET FEEDBACK OFF
SET VERIFY OFF
COLUMN sql_id FORMAT A15 
COLUMN elapsed FORMAT 9999999.90
COLUMN sql_text_fragment FORMAT A100 
VARIABLE newl VARCHAR2(64);
BEGIN
  :newl := chr(10); -- Using chr(10) for a new line
END;
/
PROMPT
PROMPT 10 Most Expensive SQL in the Workload Repository
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT * 
FROM (
  SELECT stat.sql_id AS sql_id, 
         SUM(elapsed_time_delta) / 1000000 AS elapsed, 
         (SELECT LISTAGG(TO_CHAR(SUBSTR(REPLACE(st.sql_text, :newl, ' '), 1, 55)), ' ') 
                 WITHIN GROUP (ORDER BY st.sql_id)
          FROM dba_hist_sqltext st 
          WHERE st.dbid = stat.dbid AND st.sql_id = stat.sql_id) AS sql_text_fragment
  FROM dba_hist_sqlstat stat
  WHERE EXISTS (
    SELECT 1 FROM dba_hist_sqltext text 
    WHERE text.sql_id = stat.sql_id AND text.dbid = stat.dbid)
  GROUP BY stat.dbid, stat.sql_id
  ORDER BY elapsed DESC
) 
WHERE ROWNUM <= 10;

undefine sqlid;
SET FEEDBACK ON;
SET VERIFY ON;