本文主要是介绍提取某一天内awr中的Time Model Statistics中统计值,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
分享一个sql脚本:
提取某一天内awr中的Time Model Statistics中统计值 (可以对比一天内,某个参数指标的变化)
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;
查询显示结果如下:(可以对比一天内,某个参数指标的变化 )
DATE I SQL_TIME DB_CPU PARSE_TIME HARD_P_TIME SEQ_LOAD_T CONN_MA_CA_T PLSQL_E_T HARD_P_S_T REP_B_T HARD_P_M_T PLSQL_comp_T DB_TIME BK_E_TIME BK_CPU_TIME
-------------- -- -------- ------- ---------- ----------- ---------- ------------ --------- ---------- ------- ---------- ------------ -------- --------- -----------
1013 00:00:09 1 221.60 66.57 5.55 .22 .20 .11 1.93 .04 .00 .01 .02 252.94 34.46 17.60
1013 01:00:19 1 325.48 84.36 10.25 .10 1.40 .11 .01 .03 .01 .01 .00 388.76 55.44 24.55
1013 02:00:29 1 413.14 76.57 2.57 .09 .18 .10 .01 .02 .00 .00 .00 461.63 49.34 20.99
1013 03:00:39 1 457.44 90.92 2.78 .07 .92 .11 .01 .01 .01 .00 .01 522.45 60.79 25.83
1013 04:00:07 1 239.61 63.82 1.52 .14 .16 .10 .01 .02 .00 .00 .00 267.35 25.40 13.04
1013 05:00:16 1 467.21 106.59 4.10 .66 2.71 .10 .02 .02 .01 .01 .00 565.45 89.32 37.68
1013 06:00:10 1 313.15 76.91 2.38 .51 .76 .10 .02 .05 .01 .04 .01 351.57 36.65 17.12
gt
提取某一天内awr中的Time Model Statistics中统计值 (可以对比一天内,某个参数指标的变化)
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;
查询显示结果如下:(可以对比一天内,某个参数指标的变化 )
DATE I SQL_TIME DB_CPU PARSE_TIME HARD_P_TIME SEQ_LOAD_T CONN_MA_CA_T PLSQL_E_T HARD_P_S_T REP_B_T HARD_P_M_T PLSQL_comp_T DB_TIME BK_E_TIME BK_CPU_TIME
-------------- -- -------- ------- ---------- ----------- ---------- ------------ --------- ---------- ------- ---------- ------------ -------- --------- -----------
1013 00:00:09 1 221.60 66.57 5.55 .22 .20 .11 1.93 .04 .00 .01 .02 252.94 34.46 17.60
1013 01:00:19 1 325.48 84.36 10.25 .10 1.40 .11 .01 .03 .01 .01 .00 388.76 55.44 24.55
1013 02:00:29 1 413.14 76.57 2.57 .09 .18 .10 .01 .02 .00 .00 .00 461.63 49.34 20.99
1013 03:00:39 1 457.44 90.92 2.78 .07 .92 .11 .01 .01 .01 .00 .01 522.45 60.79 25.83
1013 04:00:07 1 239.61 63.82 1.52 .14 .16 .10 .01 .02 .00 .00 .00 267.35 25.40 13.04
1013 05:00:16 1 467.21 106.59 4.10 .66 2.71 .10 .02 .02 .01 .01 .00 565.45 89.32 37.68
1013 06:00:10 1 313.15 76.91 2.38 .51 .76 .10 .02 .05 .01 .04 .01 351.57 36.65 17.12
gt
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30109892/viewspace-1813859/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30109892/viewspace-1813859/
这篇关于提取某一天内awr中的Time Model Statistics中统计值的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!