当我们使用awr生成报告的时候,可能对不知道系统什么时候繁忙而不知道选择怎么样的snapid而纠结,以下sql可以获得数据库的elapsed time和db time的比值,从而来对数据库的繁忙程度做出快色的一个筛选评估,sql如下:

select i.db_name db_name,        s.snap_id snap_id,        to_char(s.startup_time, 'mm/dd/yyyy HH24:MI:SS') startup_time,        to_char(s.begin_interval_time, 'mm/dd/yyyy HH24:MI:SS') begin_interval_time,        to_char(s.end_interval_time, 'mm/dd/yyyy HH24:MI:SS') end_interval_time,        round(extract(day from s.end_interval_time - s.begin_interval_time) * 1440 +              extract(hour from s.end_interval_time - s.begin_interval_time) * 60 +              extract(minute from                      s.end_interval_time - s.begin_interval_time) +              extract(second from                      s.end_interval_time - s.begin_interval_time) / 60,              2) elapsed_time,        round((e.value - b.value) / 1000000 / 60, 2) db_time,        round(((((e.value - b.value) / 1000000 / 60) /              (extract(day from                         s.end_interval_time - s.begin_interval_time) * 1440 +              extract(hour from                         s.end_interval_time - s.begin_interval_time) * 60 +              extract(minute from                         s.end_interval_time - s.begin_interval_time) +              extract(second from                         s.end_interval_time - s.begin_interval_time) / 60)) * 100),              2                            ) pct_db_time    from wrm$_snapshot s,        (select distinct dbid, db_name           from wrm$_database_instance          where db_name = 'ONIMEI') i,        dba_hist_sys_time_model e,        dba_hist_sys_time_model b  where i.dbid = s.dbid    and s.dbid = b.dbid    and b.dbid = e.dbid    and e.snap_id = s.snap_id    and b.snap_id = s.snap_id - 1    and e.stat_id = b.stat_id    and e.stat_name = 'DB time'  order by i.db_name, s.snap_id