oracle里面如何查询sqlid,通过SQL_ID查看SQL历史执行信息

From Memory

set pages 1000 lines 200

col first_load_time for a20

col last_load_time for a20

col outline_category for a20

col sql_profile for a32

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 v$sql

where sql_id = '&sql_id'

order by sql_id, child_number;

From AWR

set pages 1000 lines 200

col sql_profile for a32

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 sql_id = '&sql_id'

order by sql_id, snap_id;