OEM 结合dba_hist_sqlstat gv$sql 诊断 SQL 执行计划优劣

SQL的SQL PlAN变化情况

select sql_id, PLAN_HASH_VALUE,
       to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') Timestamp
from DBA_HIST_SQL_PLAN
where sql_id= 'd9xzht6822jfg'
ORDER BY timestamp 

SQL_ID              PLAN_HASH_VALUE        Timestamp 
------------------- --------------- ---------------- 
3utfphunvx822            2108069209  8/19/2009 15:10  
3utfphunvx822            3546232202  8/18/2009 14:43  
The example above shows that for SQL_ID '3utfphunvx822', 2 plans exist and that:

PLAN_HASH_VALUE '3546232202'  was produced at 8/18/2009 14:43
PLAN_HASH_VALUE '2108069209'  was produced at 8/19/2009 15:10 .

NOTE: As an alternative to the select above you could use SQLT 
to provide you with this information along with much more information related to the performance of a statement:

Document 215187.1 SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly

Another alternative would be to use the 'awrsqrpt.sql' SQL script

$ORACLE_HOME/rdbms/admin/awrsqrpt.sql  查询结果没有时间最少的那个，说明执行时间过短，未出现在AWR的报告中

    Plan Hash           Total Elapsed                 1st Capture   Last Capture
#   Value                    Time(ms)    Executions       Snap ID        Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1   2478071708            224,419,521        1,4647         62063          62075
2   4225144920             64,841,128          3143         62076          62082
3   764513711               1,860,137            52         62076          62082
4   194793287                  13,902           100         62077          62082
          -------------------------------------------------------------

OEM 中execute rate在图形化显示中是不准确的，如果选AWR 和选Cursor，Cursor更准确一点

 

   
   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 sql_id = 'd9xzht6822jfg'
order by sql_id, child_number;

---elapsed_time_avg  us28898 =28.9ms

select sql_id, a.snap_id,b.begin_interval_time, 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 a ,dba_hist_snapshot  b
where sql_id = 'd9xzht6822jfg'
and a.snap_id =b.snap_id and b.instance_number=1
order by sql_id, snap_id;