个人简介
偶尔有空上来看看
文章分类
文章存档
最近访客
相关博文
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： 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/
给主人留下些什么吧！~~