Oracle – Finding SQL ID, SQL Text, and Execution Details Between AWR Snap IDs

awrdatabase-internalsoracle

The AWR report provides the SQL information for top 10-20 SQL statements. Is it possible to get information on SQL statements that do not make it to the top list? How do we find all sql id, sql text, number of executions, execution time between two AWR snap ids in oracle?

Best Answer

This will get those fields between 2 snap IDs, just replace the snap ID's in the where clause

select t.sql_id,
    t.sql_text,
    s.executions_total,
    s.elapsed_time_total
from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t
where s.snap_id between 1000 and 2000;
Related Question