使用SQL语句单独生成AWR报表中“SQL ordered by Elapsed Time” 部分

通过AWR脚本生成的SQL ordered by Elapsed Time部分内容
使用SQL语句单独生成AWR报表中“SQL ordered by Elapsed Time” 部分
1、提取&beg_snap 、&end_snap
--提取&beg_snap 、&end_snap
select * from dba_hist_snapshot x ;
--提取最小的snap_id
select min(snap_id) from dba_hist_snapshot;
--提取最大的snap_id
select max(snap_id) from dba_hist_snapshot;

这里取值如下:
&beg_snap:18854
&end_snap:18865
使用SQL语句单独生成AWR报表中“SQL ordered by Elapsed Time” 部分
2、提取&dbid

--提取&dbid
select * from v$database;

这里取值如下:
&dbid:1504679784
使用SQL语句单独生成AWR报表中“SQL ordered by Elapsed Time” 部分
3、提取$inst_num

--提取$inst_num
select * from v$instance;

这里取值如下:
$inst_num:1
使用SQL语句单独生成AWR报表中“SQL ordered by Elapsed Time” 部分
4、执行SQL语句,将上述提取的数值填写到对应位置
脚本内容:

select *
  from (select round(nvl((sqt.elap / 1000000), to_number(null)),2) "Elapsed Time (s)",
              round( nvl((sqt.cput / 1000000), to_number(null)),2) "CPU Time (s)",
               sqt.exec,
               round(decode(sqt.exec,
                      0,
                      to_number(null),
                      (sqt.elap / sqt.exec / 1000000)),2) "Elap per Exec (s)",
               round((100 *
               (sqt.elap / (select sum(e.value) - sum(b.value)
                               from dba_hist_sys_time_model b,
                                    dba_hist_sys_time_model e
                              where b.snap_id = &beg_snap and
                                    e.snap_id = &end_snap and
                                    b.dbid = &dbid and
                                    e.dbid = &dbid and
                                    b.instance_number = &inst_num and
                                    e.instance_number = &inst_num and
                                    e.stat_name = ‘DB time‘ and
                                    b.stat_name = ‘DB time‘))) ,2)norm_val,
               sqt.sql_id,
               decode(sqt.module, null, null, ‘Module: ‘ || sqt.module) SqlModule,
               nvl(to_nchar(SUBSTR(st.sql_text,1,2000)) , (‘ ** SQL Text Not Available ** ‘)) SqlText
          from (select sql_id,
                       max(module) module,
                       sum(elapsed_time_delta) elap,
                       sum(cpu_time_delta) cput,
                       sum(executions_delta) exec
                  from dba_hist_sqlstat
                 where dbid = &dbid and
                       instance_number = &inst_num and
                       &beg_snap < snap_id and
                       snap_id <= &end_snap
                 group by sql_id) sqt,
               dba_hist_sqltext st
         where st.sql_id(+) = sqt.sql_id and
               st.dbid(+) = &dbid
         order by nvl(sqt.elap, -1) desc,
                  sqt.sql_id)
 where rownum < 65 and
       (rownum <= 10 or norm_val > 1);

使用SQL语句单独生成AWR报表中“SQL ordered by Elapsed Time” 部分
5、将结果以HTML格式导出
使用SQL语句单独生成AWR报表中“SQL ordered by Elapsed Time” 部分
6、查看获取的结果
使用SQL语句单独生成AWR报表中“SQL ordered by Elapsed Time” 部分

使用SQL语句单独生成AWR报表中“SQL ordered by Elapsed Time” 部分

上一篇:JdbcTemplate基本使用


下一篇:Mysql索引的底层实现原理