SQL statement past and current performance

Using the following statements we can view the performance details of a particular sql statement (sql_id) that is currently in the shared pool or has run in the past based on the retention in the AWR.

Looking at a statement currently in the shared pool using gv$SQL. This contains each child cursor for the sql statement.

select inst_id, last_load_time, sql_id, plan_hash_value, executions, elapsed_time/decode(nvl(executions,0),0,1,executions)/1000000 avg_etime, buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, disk_reads/decode(nvl(executions,0),0,1,executions) avg_pio, rows_processed/decode(nvl(executions,0),0,1,executions) avg_rows, (cpu_time/decode(nvl(executions,0),0,1,executions))/1000000 avg_cpu_wait, (user_io_wait_time/decode(nvl(executions,0),0,1,executions))/1000000 avg_user_io_wait, (cluster_wait_time/decode(nvl(executions,0),0,1,executions))/1000000 avg_clu_wait, (application_wait_time/decode(nvl(executions,0),0,1,executions))/1000000 avg_app_wait, (CONCURRENCY_WAIT_TIME/decode(nvl(executions,0),0,1,executions))/1000000 avg_concurrent_wait from gv$sql where sql_id = '50ph8shy0408h'; --Add SQL_ID here

If we need to look at the history of a sql statement, we can use the following query and look in the AWR

select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio, (disk_reads_delta/decode(nvl(disk_reads_delta,0),0,1,executions_delta)) avg_pio, (rows_processed_delta/decode(nvl(rows_processed_delta,0),0,1,executions_delta)) avg_rows, (CPU_TIME_DELTA/decode(nvl(CPU_TIME_DELTA,0),0,1,executions_delta))/1000000 avg_cpu_wait, (IOWAIT_DELTA/decode(nvl(IOWAIT_DELTA,0),0,1,executions_delta))/1000000 avg_user_io_wait, (CLWAIT_DELTA/decode(nvl(CLWAIT_DELTA,0),0,1,executions_delta))/1000000 avg_clu_wait, (APWAIT_DELTA/decode(nvl(APWAIT_DELTA,0),0,1,executions_delta))/1000000 avg_app_wait, (CCWAIT_DELTA/decode(nvl(CCWAIT_DELTA,0),0,1,executions_delta))/1000000 avg_concurrent_wait from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where sql_id in '717nanf12r543' --Add SQLid here and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 order by 1, 2, 3;

******************************************
 keywords: sql history
******************************************
 rdbms version: 11g
******************************************