Sql Information from Memory ... ..  

Sql information

Sql Information from Memory ...


set pages 100 lines 400
col sql_profile          for a32
col first_load_time   for a20
col last_load_time    for a20
col outline_category for a20
select sql_id, child_number, plan_hash_value, first_load_time, last_load_time, outline_category, sql_profile, executions,
trunc(decode(executions, 0, 0, rows_processed/executions))              rows_avg,
trunc(decode(executions, 0, 0, fetches/executions))                             fetches_avg,
trunc(decode(executions, 0, 0, disk_reads/executions))                       disk_reads_avg,
trunc(decode(executions, 0, 0, buffer_gets/executions))                      buffer_gets_avg,
trunc(decode(executions, 0, 0, cpu_time/executions))                         cpu_time_avg,
trunc(decode(executions, 0, 0, elapsed_time/executions))                   elapsed_time_avg,
trunc(decode(executions, 0, 0, application_wait_time/executions))     apwait_time_avg,
trunc(decode(executions, 0, 0, concurrency_wait_time/executions))   cwait_time_avg,
trunc(decode(executions, 0, 0, cluster_wait_time/executions))            clwait_time_avg,
trunc(decode(executions, 0, 0, user_io_wait_time/executions))           iowait_time_avg,
trunc(decode(executions, 0, 0, plsql_exec_time/executions))               plsexec_time_avg,
trunc(decode(executions, 0, 0, java_exec_time/executions))                javexec_time_avg
from gv$sql
where 1=1
and sql_id = '&sql_id'
and USERS_EXECUTING!=0 ---- Currently Executing
order by sql_id, child_number;

sql information from AWR
set pages 100 lines 400
col sql_profile          for a32
col first_load_time   for a20
col last_load_time    for a20
col outline_category for a20
select sql_id, snap_id, plan_hash_value, sql_profile, executions_total,
trunc(decode(executions_total, 0, 0, rows_processed_total/executions_total))    rows_avg,
trunc(decode(executions_total, 0, 0, fetches_total/executions_total))                  fetches_avg,
trunc(decode(executions_total, 0, 0, disk_reads_total/executions_total))            disk_reads_avg,
trunc(decode(executions_total, 0, 0, buffer_gets_total/executions_total))           buffer_gets_avg,
trunc(decode(executions_total, 0, 0, cpu_time_total/executions_total))              cpu_time_avg,
trunc(decode(executions_total, 0, 0, elapsed_time_total/executions_total))        elapsed_time_avg,
trunc(decode(executions_total, 0, 0, iowait_total/executions_total))                   iowait_time_avg,
trunc(decode(executions_total, 0, 0, clwait_total/executions_total))                   clwait_time_avg,
trunc(decode(executions_total, 0, 0, apwait_total/executions_total))                  apwait_time_avg,
trunc(decode(executions_total, 0, 0, ccwait_total/executions_total))                   ccwait_time_avg,
trunc(decode(executions_total, 0, 0, plsexec_time_total/executions_total))         plsexec_time_avg,
trunc(decode(executions_total, 0, 0, javexec_time_total/executions_total))         javexec_time_avg
from dba_hist_sqlstat
where 1=1
and sql_id = '&sql_id'
order by sql_id, snap_id;