概述

很多朋友问如果想看Oracle各个时间段的整体负载情况，可以怎么看呢？这里主要分享一个实用脚本来观察，通过DB_TIME的时间来把握负载情况。

实用脚本

下面的sql会列出每个时段具体情况

ALTER session SET nls_date_format='yyyy-mm-dd hh24:mi:ss';
SELECT *
 FROM ( SELECT A.INSTANCE_NUMBER,
 A.SNAP_ID,
 B.BEGIN_INTERVAL_TIME + 0 BEGIN_TIME,
 B.END_INTERVAL_TIME + 0 END_TIME,
 ROUND(VALUE - LAG( VALUE, 1 , '0')
 OVER(ORDER BY A.INSTANCE_NUMBER, A.SNAP_ID)) "DB TIME"
 FROM (SELECT B.SNAP_ID,
 INSTANCE_NUMBER,
 SUM(VALUE ) / 1000000 / 60 VALUE
 FROM DBA_HIST_SYS_TIME_MODEL B
 WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
 AND UPPER (B.STAT_NAME) IN UPPER(('DB TIME' ))
 GROUP BY B.SNAP_ID, INSTANCE_NUMBER) A,
 DBA_HIST_SNAPSHOT B
 WHERE A.SNAP_ID = B.SNAP_ID
 AND B.DBID = (SELECT DBID FROM V$DATABASE)
 AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER)
 WHERE TO_CHAR(BEGIN_TIME, 'YYYY-MM-DD') = TO_CHAR(SYSDATE , 'YYYY-MM-DD')
 ORDER BY BEGIN_TIME;

可以通过DBTIME来对数据库的整体负载做评估。

上面的脚本比较适合对系统做个大概的了解，判断哪个时间段比较繁忙，然后拿对应的awr报告去具体分析。后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~