AWR 报告深度解读:Time Model Statistics 信息的计算和获取
导读:在 AWR 报告中,Time Model Statistics 记录了数据库用户维度(User Calls)的总时间消耗分布。这部分信息来自:SYS.DBA_HIST_SYS_TIME_MODEL ,是通过针对前后两个采样点的差值计算得来的。
在 AWR 报告中,Time Model Statistics 记录了数据库用户维度(User Calls)的总时间消耗分布。
这部分信息来自:SYS.DBA_HIST_SYS_TIME_MODEL ,是通过针对前后两个采样点的差值计算得来的。
计算的SQL如下:
SQL> SELECT a.STAT_NAME,
2 ROUND((b.VALUE -a.VALUE)/1000000,2) "Time(s)"
3 FROM SYS.DBA_HIST_SYS_TIME_MODEL a,
4 SYS.DBA_HIST_SYS_TIME_MODEL b
5 WHERE a.snap_id = &start_snap_id
6 AND b.snap_id = &end_snap_id
7 AND a.STAT_NAME = b.STAT_NAME
8 AND ROUND((b.VALUE -a.VALUE)/1000000,2)>0
9 ORDER BY 2 DESC;
Enter value for start_snap_id: 34987
Enter value for end_snap_id: 34988STAT_NAME Time(s)
---------------------------------------------------------------- ----------
background elapsed time 2720.51
background cpu time 2550.64
RMAN cpu time (backup/restore) 2518.73
DB time 53.21
DB CPU 48.83
sql execute elapsed time 33.66
connection management call elapsed time 10.67
parse time elapsed 2.26
PL/SQL execution elapsed time 1.01
inbound PL/SQL rpc elapsed time .96
hard parse elapsed time .7
hard parse (sharing criteria) elapsed time .68
PL/SQL compilation elapsed time .0213 rows selected.
把这个结果和 AWR 报告中的相关部分对比,可以看到是完全吻合的(这个测试数据来自 11.2.0.4 版本):
这个SQL的语句如下,缺省的时间记录值是微秒,计算结果转换为秒显示:
SELECT a.STAT_NAME,
ROUND((b.VALUE -a.VALUE)/1000000,2) "Time(s)"
FROM SYS.DBA_HIST_SYS_TIME_MODEL a,
SYS.DBA_HIST_SYS_TIME_MODEL b
WHERE a.snap_id = &start_snap_id
AND b.snap_id = &end_snap_id
AND a.STAT_NAME = b.STAT_NAME
AND ROUND((b.VALUE -a.VALUE)/1000000,2)>0
ORDER BY 2 DESC;
了解 AWR 各部分的指标算法,非常有助于我们理解报告的真实表达。