Unlock SQL Details from AWR: Easy guide

The analysis of SQL statements is the foundation of Oracle’s performance.This post outlines a step-by-step procedure for retrieving SQL details from AWR using an optimized SQL script. Whether you are a developer or a DBA, using this information will help your SQL queries run faster.

SQL Script to Find SQL Details from AWR:

SET linesize 200
COLUMN execs FORMAT 999,999,999
COLUMN elapsed_time FORMAT 999,999,999.9
COLUMN avg_elapsed_time FORMAT 999,999.999
COLUMN avg_cpu_time FORMAT 999,999.999
COLUMN avg_lio FORMAT 999,999,999.9
COLUMN avg_pio FORMAT 9,999,999.9
COLUMN begin_interval_time FORMAT a30
COLUMN instance_node FORMAT 99999

BREAK ON plan_hash_value SKIP 1 ON begin_interval_time

SELECT sql_id, 
       plan_hash_value, 
       SUM(executions) AS execs, 
       SUM(elapsed_time) AS elapsed_time, 
       SUM(elapsed_time) / SUM(executions) AS avg_elapsed_time, 
       SUM(cpu_time) / SUM(executions) AS avg_cpu_time, 
       SUM(logical_io) / SUM(executions) AS avg_lio, 
       SUM(physical_io) / SUM(executions) AS avg_pio
FROM (
    SELECT 
           ss.snap_id, 
           ss.instance_number AS instance_node, 
           ss.begin_interval_time, 
           sql_id, 
           plan_hash_value,
           NVL(executions_delta, 0) AS executions,
           elapsed_time_delta / 1000000 AS elapsed_time,
           (elapsed_time_delta / DECODE(NVL(executions_delta, 0), 0, 1, executions_delta)) / 1000000 AS avg_elapsed_time,
           buffer_gets_delta AS logical_io,
           disk_reads_delta AS physical_io,
           cpu_time_delta / 1000000 AS cpu_time,
           (buffer_gets_delta / DECODE(NVL(executions_delta, 0), 0, 1, executions_delta)) AS avg_lio,
           (cpu_time_delta / DECODE(NVL(executions_delta, 0), 0, 1, executions_delta)) AS avg_cpu_time
    FROM DBA_HIST_SQLSTAT s, 
         DBA_HIST_SNAPSHOT ss
    WHERE sql_id = NVL('&sql_id', 'default_sql_id')
    AND ss.snap_id = s.snap_id
    AND ss.instance_number = s.instance_number 
    AND executions_delta > 0
)
GROUP BY sql_id, plan_hash_value
ORDER BY avg_elapsed_time;

This SQL script queries the AWR (Automatic Workload Repository) to extract SQL details and execution statistics such as execution count (execs), elapsed time (etime), logical I/Os (LIO), physical I/Os (PIO), and CPU time.

Sample Output and Explanation:

The above output provides insight into the performance and resource usage of a specific SQL ID 2sxqqx5hx76qr.

SQL_ID: The SQL statement's unique identifier.
PLAN_HASH_VALUE: The SQL execution plan is represented by the numeric number.
EXECS: The number of executions of the SQL statement.
ELAPSED TIME: The sum of the elapsed times of the executions.
AVG_ELAPSED_TIME: Average elapsed time per execution.
AVG_CPU_TIME: Average CPU time per execution.
AVG_LIO: Average number of logical I/Os per execution.
AVG_PIO: Average number of physical I/Os per execution.

For a more detailed explanation of how to interpret these statistics and further optimizations, you can refer to Oracle AWR Documentation

If you want to monitor a particular SQL statement’s performance over time, then you can follow my article Find SQL Query Information from AWR.