通过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);
5、将结果以HTML格式导出
6、查看获取的结果
