Find SQL details from AWR for a specific SQL

Retrieving SQL metadata from AWR (Automatic Workload Repository) is essential. In this blog post, we will walk you through a simple SQL query that will help you to get SQL details from AWR for a specific SQL_ID.

Here is the SQL query you can use to retrieve SQL details from AWR:

col BEGIN_INTERVAL_TIME for a25
set pages 2500
set lines 250
select s.snap_id, s.begin_interval_time, sql.sql_id as sql_id, sql.PLAN_HASH_VALUE, sql.executions_delta as exe_delta, sql.EXECUTIONS_TOTAL, elapsed_time_delta/1000000 "elapsed_time_delta In Seconds", cpu_time_delta/1000000 "cpu_time_delta In Seconds"
from dba_hist_sqlstat sql, dba_hist_snapshot s where s.snap_id = SQL.snap_id
and sql.SQL_ID='&SQL_ID' order by s.begin_interval_time;

Sample Output:

After running the query, you should get an output similar to the following:

Find SQL details from AWR for a specific SQL_ID: Easy Guide

Snap ID: This is the identifier for the specific snapshot.
Begin_Interval_Time: This is the timestamp when the snapshot was taken.
SQL_ID: The SQL statement's unique identifier.
PLAN_HASH_VALUE: The SQL execution plan is represented by the numeric number.
Execution_Delta: Shows how many times the SQL was executed between snapshots.
Executions_Total: Indicates the cumulative count of executions since it was first captured in AWR.
Elapsed_Time_Delta: Total amount of time (in seconds) spent executing the SQL statement between two snapshots.
CPU_Time_Delta: Total amount of CPU time (in seconds) used by the SQL statement between two snapshots.

By executing this SQL query, you may quickly obtain SQL details from AWR and use it to inform your decision-making to optimize your queries and boost database performance in general. This query is helpful for monitoring a particular SQL statement’s performance over time. You can examine whether SQL performance continuously uses excessive amounts of CPU or time, as well as whether it becomes better or worse over snapshots. This can assist in determining whether a certain execution strategy is causing delays or whether the SQL query needs to be tuned.

You can go through the Oracle documentation for more information: Oracle AWR Documentation

For more insights on SQL performance optimization, you can check out our find the top SQL in the Library Cache.