Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1176721
  • 博文数量: 221
  • 博客积分: 1860
  • 博客等级: 上尉
  • 技术积分: 2562
  • 用 户 组: 普通用户
  • 注册时间: 2008-04-07 08:51
个人简介

偶尔有空上来看看

文章分类

全部博文(221)

文章存档

2020年(74)

2019年(11)

2018年(9)

2017年(9)

2016年(17)

2015年(6)

2014年(1)

2013年(1)

2012年(10)

2011年(26)

2010年(35)

2009年(11)

2008年(11)

分类: Oracle

2020-09-11 22:34:53


set lines 200 pages 999;
col instance_number for 9 heading "I";
col date for a14;
col SQL_TIME      for 999.99 ;
col DB_CPU        for 999.99 ;
col PARSE_TIME    for 999.99 ;
col HARD_P_TIME   for 999.99 ;
col SEQ_LOAD_T    for 999.99 ;
col CONN_MA_CA_T  for 999.99 ;
col PLSQL_E_T     for 999.99 ;
col HARD_P_S_T    for 999.99 ;
col REP_B_T       for 999.99 ;
col HARD_P_M_T    for 999.99 ;
col PLSQL_comp_T  for 999.99 ;
col DB_TIME       for 9999.99;
col BK_E_TIME     for 999.99 ;
col BK_CPU_TIME   for 999.99 ;
with t_statd as (
select n.snap_id,n.instance_number,n.stat_name,(m.value-n.value)/1000000/60 del
from DBA_HIST_SYS_TIME_MODEL m ,DBA_HIST_SYS_TIME_MODEL n
where m.snap_id=n.snap_id+1
and m.snap_id>=(select min(snap_id) from (select snap_id from dba_hist_snapshot where to_char(begin_interval_time,'yyyymmdd')='&&date') )
and m.snap_id<=(select max(snap_id) from (select snap_id from dba_hist_snapshot where to_char(begin_interval_time,'yyyymmdd')='&&date') )
and m.INSTANCE_NUMBER=n.instance_number and m.stat_id=n.stat_id
and m.stat_name in ('sql execute elapsed time','DB CPU','parse time elapsed','hard parse elapsed time','sequence load elapsed time',
'connection management call elapsed time','PL/SQL execution elapsed time','hard parse (sharing criteria) elapsed time',
'repeated bind elapsed time','hard parse (bind mismatch) elapsed time','PL/SQL compilation elapsed time','DB time','background elapsed time','background cpu time'))
select to_char(a.END_INTERVAL_TIME,'mmdd HH24:MI:SS') "DATE",instance_number,
round((select del from t_statd where snap_id=a.snap_id and INSTANCE_NUMBER=a.instance_number and STAT_NAME='sql execute elapsed time' and rownum=1),2) "SQL_TIME",
round((select del from t_statd where snap_id=a.snap_id and INSTANCE_NUMBER=a.instance_number and STAT_NAME='DB CPU' and rownum=1),2) "DB_CPU",
round((select del from t_statd where snap_id=a.snap_id and INSTANCE_NUMBER=a.instance_number and STAT_NAME='parse time elapsed' and rownum=1),2) "PARSE_TIME",
round((select del from t_statd where snap_id=a.snap_id and INSTANCE_NUMBER=a.instance_number and STAT_NAME='hard parse elapsed time' and rownum=1),2) "HARD_P_TIME",
round((select del from t_statd where snap_id=a.snap_id and INSTANCE_NUMBER=a.instance_number and STAT_NAME='sequence load elapsed time' and rownum=1),2) "SEQ_LOAD_T",
round((select del from t_statd where snap_id=a.snap_id and INSTANCE_NUMBER=a.instance_number and STAT_NAME='connection management call elapsed time' and rownum=1),2) "CONN_MA_CA_T",
round((select del from t_statd where snap_id=a.snap_id and INSTANCE_NUMBER=a.instance_number and STAT_NAME='PL/SQL execution elapsed time' and rownum=1),2) "PLSQL_E_T",
round((select del from t_statd where snap_id=a.snap_id and INSTANCE_NUMBER=a.instance_number and STAT_NAME='hard parse (sharing criteria) elapsed time' and rownum=1),2) "HARD_P_S_T",
round((select del from t_statd where snap_id=a.snap_id and INSTANCE_NUMBER=a.instance_number and STAT_NAME='repeated bind elapsed time' and rownum=1),2) "REP_B_T",
round((select del from t_statd where snap_id=a.snap_id and INSTANCE_NUMBER=a.instance_number and STAT_NAME='hard parse (bind mismatch) elapsed time' and rownum=1),2) "HARD_P_M_T",
round((select del from t_statd where snap_id=a.snap_id and INSTANCE_NUMBER=a.instance_number and STAT_NAME='PL/SQL compilation elapsed time' and rownum=1),2) "PLSQL_comp_T",
round((select del from t_statd where snap_id=a.snap_id and INSTANCE_NUMBER=a.instance_number and STAT_NAME='DB time' and rownum=1),2) "DB_TIME",
round((select del from t_statd where snap_id=a.snap_id and INSTANCE_NUMBER=a.instance_number and STAT_NAME='background elapsed time' and rownum=1),2) "BK_E_TIME",
round((select del from t_statd where snap_id=a.snap_id and INSTANCE_NUMBER=a.instance_number and STAT_NAME='background cpu time' and rownum=1),2) "BK_CPU_TIME"
from (select * from (select END_INTERVAL_TIME,snap_id,instance_number,dbid from dba_hist_snapshot   where  to_char(END_INTERVAL_TIME,'yyyymmdd')='&&date' order by snap_id desc) where rownum<200) a
order by 2,1;

转自:http://blog.itpub.net/30109892/viewspace-1813859/
阅读(13) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~