Oracle-查询某个SCHEMA的AWR的SQL ordered by Elapsed Time

大家都知道AWR的中文名叫“自动负载信息库”，英文名叫“Automatic Workload Repository”。它是通过对比两次快照收集到的统计信息，来生成报表数据。

这里只说一说SQL ordered by Elapsed Time相关的SQL。

SQL ordered by Elapsed Time 的报告信息，按SQL语句执行总时长排序。总时长 ，总是在某一个时间段内，比如一小时或是一天。一般情况下，我们关注的是每执行一次的时间，也就是Elap per Exec(s)，它指执行一次SQL的平均时间，单位时间为秒。

但是呢，AWR是针对于全库的，那么怎么查某个SCHEMA的相关 SQL ordered by Elapsed Time 呢？看下面。

--取得实例相关
select instance_number,instance_name,host_name from v$instance;

INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME
--------------- ---------------- ----------------------------------------------------------------
              1 baoyw            baoyw

--取得DB相关
select dbid,name from v$database;

      DBID NAME
---------- ---------
2505883968 baoy

--在sqlplus中不以科学计数法显示
set numwidth 30

--取得差异时间 时间段
SELECT e.VALUE - b.value as diff_value
  FROM DBA_HIST_SYS_TIME_MODEL B, DBA_HIST_SYS_TIME_MODEL E
 WHERE e.dbid = b.dbid
   and e.instance_number = b.instance_number
   and e.STAT_ID = b.STAT_ID
   and B.DBID = '2505883968'
   AND B.INSTANCE_NUMBER = '1'
   AND B.SNAP_ID = '3128'
   AND E.SNAP_ID = '3129'
   AND B.STAT_NAME = 'DB time';

dba_hist_sqlstat 和 dba_hist_sqltext 都是历史数据。v$sql_monitor 是监控数据，它有用户信息，所以这里关联查询。按时间倒序。这三张表的详情，自行查查官方文档，相信就明白了。

下面的 a 表中只保留了我需要的字段，b 表也是一样。根据自己的需要来即可。

--查询用户执行一次SQL的平均时间
--&db_time 上面取得的时间差值
set linesize 999
set pagesize 999
set numwidth 30
col "Elapsed Time per Exec (s)" for a30
col "% Total DB Time" for a16
col sql_id for a18
col username for a16
select *
  from (select to_char(decode(sqt.exec,0, to_number(null),(sqt.elap / sqt.exec / 1000000)),'9999990.99') as "Elapsed Time per Exec (s)",
               to_char((100 * (sqt.elap / &db_time )),9990.0)  as "% Total DB Time",
               sqt.sql_id
          from (select sql_id, 
                             max(module) module,
                             sum(elapsed_time_delta) elap,
                             sum(cpu_time_delta)     cput,
                             sum(executions_delta)   exec,
                             sum(iowait_delta)       iowt
                  from dba_hist_sqlstat
                 where dbid = '2505883968'
                   and instance_number = 1
                   and 3128 < snap_id
                   and snap_id <= 3129
                 group by sql_id
                ) sqt,
               dba_hist_sqltext st
         where st.sql_id(+) = sqt.sql_id
           and st.dbid(+) = '2505883968') a,(select distinct user#,username,sql_id from v$sql_monitor where user# in ('54','55')) b where a.sql_id = b.sql_id 
           order by 1 desc;