什么是 db time
AWR中有 DB time这个术语,那么什么是DB time呢?
Oracle10gR2 官方文档 给出了详细解释(Oracle10gPerformance Tuning Guide 5.1.1.2 Time Model Statistics)
The most important of the time model statistics is DB time. This statistics represents the total time spent in
database calls and is a indicator of the total instance workload. It is calculated by aggregating the CPU and
wait times of all sessions not waiting on idle wait events (non-idle user sessions).
根据官方文档的解释,DB time=DB CPU+ DB waiting time(no-idle time)
那么下面我挑一个session来验证一下
SQL> select sid,stat_id,stat_name,value/1000000/60 from V$sess_TIME_MODEL where sid=194;
SID STAT_ID STAT_NAME VALUE/1000000/60
---------- ---------- -------------------------------------------------- ----------------
194 3649082374 DB time 33.5681982
194 2748282437 DB CPU 32.9633455
194 4157170894 background elapsed time 0
194 2451517896 background cpu time 0
194 4127043053 sequence load elapsed time 0
194 1431595225 parse time elapsed .0005325
194 372226525 hard parse elapsed time .000498433
194 2821698184 sql execute elapsed time 33.5674656
194 1990024365 connection management call elapsed time .000047933
194 1824284809 failed parse elapsed time 0
194 4125607023 failed parse (out of shared memory) elapsed time 0
194 3138706091 hard parse (sharing criteria) elapsed time 0
194 268357648 hard parse (bind mismatch) elapsed time 0
194 2643905994 PL/SQL execution elapsed time .0000051
194 290749718 inbound PL/SQL rpc elapsed time 0
194 1311180441 PL/SQL compilation elapsed time 0
194 751169994 Java execution elapsed time 0
194 1159091985 repeated bind elapsed time .0000001
194 2411117902 RMAN cpu time (backup/restore) 0
19 rows selected.
Elapsed: 00:00:01.61
SQL> select (sysdate-logon_time)*24*60 minutes, username,machine,sid,serial#,event,p1,p2,p3
2 from v$session where sid=194;
MINUTES USERNAME MACHINE SID SERIAL# EVENT P1 P2 P3
---------- -------------------- -------------------- ---------- ---------- -------------------- ---------- ---------- ----------
34.4833333 WHSUSRGL NA/BDC-KALIDO001 194 28391 direct path read tem 202 89709 1
p
Elapsed: 00:00:01.15
Session 194连接到数据库已经34.4833333分钟,DB time=33.5681982 , CPU time=32.9633455
现在来查询一下ASH,我们知道ASH是每隔1秒钟采样一次
SQL> select count(*)
2 from v$active_session_history ash, v$event_name enm
3 where ash.event#=enm.event#
4 and SESSION_ID=194;
COUNT(*)
----------
47
那么194这个session大概等待了47秒,也就是等待了大约。78333333分钟
SQL> select 47/60 from dual;
47/60
----------
.783333333
SQL> select 32.9633455+.783333333 from dual;
32.9633455+.783333333
---------------------
33.7466788
DB CPU + 等待时间=33.7466788 ,而 DB time=33.5681982 他们相差不大,基本上可以说明
DB time=DB CPU+ DB waiting time(no-idle time)
注意:AWR中的DB time等于所有session DB time之和(除去后台进程)
SQL> select SESSION_ID,NAME,P1,P2,P3
2 from v$active_session_history ash, v$event_name enm
3 where ash.event#=enm.event#
4 and SESSION_ID=194;
SESSION_ID NAME P1 P2 P3
---------- ----------------------------------- ---------- ---------- ----------
194 db file sequential read 202 106634 1
194 db file sequential read 202 53541 1
194 db file sequential read 202 204387 1
194 db file sequential read 202 242316 1
194 db file sequential read 202 251848 1
194 db file sequential read 202 201689 1
194 db file scattered read 45 480505 16
194 db file scattered read 44 169145 16
194 db file scattered read 45 32489 16
194 db file scattered read 44 316585 16
194 db file scattered read 46 255817 16
194 db file scattered read 47 204105 16
194 db file scattered read 44 236633 16
194 db file scattered read 46 222761 16
194 db file scattered read 44 232969 16
194 db file scattered read 44 230489 16
194 db file scattered read 44 227673 16
194 db file scattered read 45 177785 16
194 db file scattered read 44 292121 16
194 db file scattered read 45 136041 16
194 db file scattered read 28 17177 16
194 db file scattered read 47 233513 16
194 db file scattered read 44 222233 16
194 db file scattered read 46 211289 16
194 db file scattered read 47 152889 16
194 db file scattered read 45 164921 16
194 db file scattered read 47 149609 16
194 db file scattered read 44 312873 16
194 db file scattered read 45 157289 16
194 db file scattered read 45 155225 16
194 db file sequential read 50 30400 1
194 db file sequential read 43 8763 1
194 db file scattered read 36 518441 16
194 db file scattered read 37 504992 4
194 db file scattered read 38 195257 16
194 db file scattered read 37 209776 9
194 db file scattered read 41 119561 16
194 db file scattered read 39 62041 16
194 db file scattered read 41 4765 2
194 db file scattered read 38 438857 16
194 read by other session 38 335097 1
194 latch: cache buffers chains 1.3835E+19 122 0
194 db file sequential read 27 52122 1
194 db file sequential read 41 140105 1
194 read by other session 36 97230 1
194 db file scattered read 38 17369 16
194 db file sequential read 37 1889 1
47 rows selected.
看AWR的时候,可以拿DB time和采样时间间隔做比较,如果DB time高出采样时间间隔很多,说明数据库负载很大
同样,可以拿DB time和DB CPU比较,如果 DB time高出DB CPU很多,说明数据库出现了显而易见的性能问题
因为等待时间过多。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战