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
******************************************
No comments:
Post a Comment