What is DB time in AWR?
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很多,说明数据库出现了显而易见的性能问题
因为等待时间过多。
最新文章
- ElasticSearch 入门笔记1
- 小printf的故事(未完待续)
- 用vue实现简单实时汇率计算功能
- PHP 类的封装和使用
- java 快速排序
- Leetcode 4.28 string
- Python SQLalchemy的学习与使用
- Python学习之路基础篇--08Python基础+ 文件的基本操作和 注册小作业
- openwrt添加自动启动项
- Elasticsearch学习笔记(二)Search API 与 Query DSL
- 20145208 蔡野 《网络对抗》免考项目 MSF学习
- SQLServer代理新建或者编辑作业报错
- CSS-3 box-shadow 的使用
- codeforces Round #259(div2) C解题报告
- (笔记)Mysql命令rename:修改表名
- mysql 随机获取数据并插入到数据库中
- HDU 4026 Unlock the Cell Phone 状压dp(类似TSP)
- 动态规划入门-01背包问题 - poj3624
- Tarjan的强连通分量算法
- 最短路径——Bellman-Ford算法以及SPFA算法
热门文章
- HDU - 59562016ACM/ICPC亚洲区沈阳站I - The Elder 树上斜率优化dp
- nyoj1007——欧拉求和
- POJ 3167 Cow Pattern ★(KMP好题)
- JS中将对象转化为数组
- Mycat跨分片Join
- kappa系数在大数据评测中的应用
- 关于Qt中使用线程的时候函数具体在哪个线程中运行的问题
- ansible配置文件 ansible.cfg的一点说明
- C++面向对象高级编程(六)转换函数与non-explicit one argument ctor
- 6个变态的C语言程序