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

最新推荐文章于 2021-04-09 21:43:19 发布

DomDanrtsey

最新推荐文章于 2021-04-09 21:43:19 发布

阅读量779

点赞数

分类专栏： oracleDB 文章标签： oracle 数据库

本文链接：https://blog.csdn.net/u011635437/article/details/108733342

版权

oracleDB 专栏收录该内容

23 篇文章 3 订阅

订阅专栏

通过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
在这里插入图片描述
2、提取&dbid

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

这里取值如下：
&dbid：1504679784
在这里插入图片描述
3、提取$inst_num

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

这里取值如下：
$inst_num：1
在这里插入图片描述
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);