Oracle Time Model Statistics(时间模型统计)
一、 DB Time(数据库时间)与User Response Time(用户响应时间)1、DB Time(数据库时间)下图为 DB Time in Overall User Response Time 即所有花费在数据库层面调用时间的总和,包括所有会话(前台进程)以及非空闲等待,CPU时间等 2、User Response Time(用户响应时间) 下图为 DB Time in User Transaction 在上图各个部分的操作中:
二、AWR中的时间模型统计报告Time Model Statistics DB/Inst: ORA11G/ora11g Snaps: 551-553
-> Total time in database user-calls (DB Time): 1264.3s
-> Statistics including the word "background" measure background process
time,and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc,Statistic name
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 1,188.0 94.0
DB CPU 274.3 21.7
parse time elapsed 10.1 .8
connection management call elapsed time 6.5 .5
PL/SQL execution elapsed time 3.2 .3
hard parse elapsed time 2.9 .2
PL/SQL compilation elapsed time 0.5 .0
hard parse (sharing criteria) elapsed time 0.4 .0
hard parse (bind mismatch) elapsed time 0.1 .0
repeated bind elapsed time 0.0 .0
failed parse elapsed time 0.0 .0
sequence load elapsed time 0.0 .0
DB time 1,264.3
background elapsed time 857.8
background cpu time 25.4
三、时间模型统计重要组成部分说明在Oracle数据库中,最重要的时间模型统计是DB Time和DB CPU。这两个统计数据直接显示数据库的工作量和描述整个数据库的响应时间。
四、基于时间模型统计调用脚本1、查看用户CPU使用情况SELECT ss.username,se.SID,VALUE / 100 cpu_usage_seconds FROM v$session ss,v$sesstat se,v$statname sn WHERE se.STATISTIC# = sn.STATISTIC# AND NAME LIKE '%CPU used by this session%' AND se.SID = ss.SID AND ss.status = 'ACTIVE' AND ss.username IS NOT NULL ORDER BY VALUE DESC;
USERNAME SID CPU_USAGE_SECONDS
--------------- ---------- -----------------
SYSMAN 156 77.69
DBSNMP 66 25.86
SYS 131 .06
DBSNMP 66 0
SYS 131 0
2、基于时间模型查询相关等待事件COLUMN wait_class FORMAT a20
COLUMN name FORMAT a30
COLUMN time_secs FORMAT 999,999,999.99
COLUMN pct FORMAT 99.99
SELECT wait_class,NAME,ROUND (time_secs,2) time_secs,ROUND (time_secs * 100 / SUM (time_secs) OVER (),2) pct FROM (SELECT n.wait_class,e.event NAME,e.time_waited / 100 time_secs FROM v$system_event e,v$event_name n WHERE n.NAME = e.event AND n.wait_class <> 'Idle' AND time_waited > 0 UNION SELECT 'CPU','server CPU',SUM (VALUE / 1000000) time_secs FROM v$sys_time_model WHERE stat_name IN ('background cpu time','DB CPU')) ORDER BY time_secs DESC;
WAIT_CLASS NAME TIME_SECS PCT
-------------------- ------------------------------ ------------------- ------
System I/O control file parallel write 3,671.39 33.70
System I/O db file async I/O submit 1,832.96 16.82
CPU server CPU 1,824.20 16.74
System I/O log file parallel write 1,396.98 12.82
Commit log file sync 936.36 8.59 User I/O direct path write 570.01 5.23 Concurrency os thread startup 138.38 1.27 Configuration log file switch (checkpoint in 120.05 1.10 complete)
五、相关视图数据字典 V$SYS_TIME_MODEL (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |