ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL生成AWR中的SQL ordered by Elapsed Time

SQL生成AWR中的SQL ordered by Elapsed Time

原创 Linux操作系统 作者:tolywang 时间:2013-03-20 17:45:47 0 删除 编辑

 --提取&beg_snap 、&end_snap
 select * from dba_hist_snapshot x ;

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

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

运行的时候输入上面的值(如果需要输入多次,那么输入同样值即可)

 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);

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-768609/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13473523