Oracle DB time 详解
DB TIME是所有前台session花费在database调用上的总和时间,microsecond(微妙),AWR展示DBTIME单位为分钟(MINS),是查看数据库系统总体负载的主要参考参数,数值主要来自视图DBA_HIST_SYS_TIME_MODEL(历史db time数据来源)、v$sys_time_model、DBA_HIST_SYSSTAT(不准确)、V$SYSSTAT
一、DBTime计算公式:
<span "="" style="word-wrap: break-word; font-size: 10.5pt;">DB TIME= DB CPU + Non-Idle Wait + Wait on CPU queue
(Non-Idle Wait= enq:TX + cursor pin S on X + latch : xxx + db file sequential read + ………)
<span "="" style="word-wrap: break-word; font-size: 10.5pt;">DB TIME= DB CPU + Non-Idle Wait + Wait on CPU queue
(Non-Idle Wait= enq:TX + cursor pin S on X + latch : xxx + db file sequential read + ………)
二、最近7天的DBTime查看脚本
(主要从DBA_HIST_SYS_TIME_MODEL 和dba_hist_snapshot取历史数据,默认每条记录为1小时快照数据)
set linesize 200 ;
set pagesize 20000 ;
col DATE_TIME for a45 ;
col STAT_NAME for a10 ;
WITH sysstat AS (
SELECT
sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.
VALUE
e_value,
lag (ss. VALUE, 1) over (ORDER BY ss.snap_id) b_value
FROM
DBA_HIST_SYS_TIME_MODEL ss,
dba_hist_snapshot sn
WHERE
trunc (sn.begin_interval_time) >= sysdate - 7
AND ss.snap_id = sn.snap_id
AND ss.dbid = sn.dbid
AND ss.instance_number = sn.instance_number
AND ss.dbid = (SELECT dbid FROM v$database)
AND ss.instance_number = (
SELECT
instance_number
FROM
v$instance
)
AND ss.stat_name = 'DB time'
) SELECT
to_char (
BEGIN_INTERVAL_TIME,
'yyyy-mm-dd hh24:mi'
) || to_char (
END_INTERVAL_TIME,
' hh24:mi'
) date_time,
stat_name,
round(
(e_value - nvl(b_value, 0))/60/1000/1000 ,
2
) dbtime_value
FROM
sysstat
WHERE
(e_value - nvl(b_value, 0)) > 0
AND nvl (b_value, 0) > 0 ;
三、实时查看DBTime脚本
当前dbtime数值查询:
select value/1000000/60 now_dbtime from v$SYS_TIME_MODEL where STAT_NAME = 'DB time';
最近的历史dbtime基准数值查询:
select t.snap_id,to_char(t.begin_interval_time,'yyyy-mm-dd hh24:mi:ss') btime,to_char(t.end_interval_time ,'yyyy-mm-dd hh24:mi:ss') etime ,e.value/1000000/60 latest_dbtime from DBA_HIST_SYS_TIME_MODEL e,dba_hist_snapshot t
WHERE
e.STAT_NAME = 'DB time'
and t.snap_id=e.snap_id
and e.snap_id=(select max(snap_id) from dba_hist_snapshot) ;
实时查看dbtime负载脚本:
col STAT_NAME for a35;
col END_INTERVAL_TIME for a35;
col BEGIN_INTERVAL_TIME for a35;
col NOW_DBTIME for 9999999.9999;
col LATEST_DBTIME for 9999999.9999;
col DB_WORKLOAD for a15;
set line 200;
set pagesize 20000;
with tmp as (select t.snap_id,t.begin_interval_time btime,t.end_interval_time etime ,e.value/1000000/60 latest_dbtime from DBA_HIST_SYS_TIME_MODEL e,dba_hist_snapshot t
WHERE
e.STAT_NAME = 'DB time'
and t.snap_id=e.snap_id
and e.snap_id=(select max(snap_id) from dba_hist_snapshot))
select tmp.latest_dbtime,value/1000000/60 now_dbtime,(((systimestamp+0)-(etime+0 ))*24*60) Elapsed ,round(value/1000000/60-tmp.latest_dbtime,2) real_dbtime ,round((value/1000000/60-tmp.latest_dbtime)/(((systimestamp+0)-(etime+0 ))*24*60)*100,2)||'%' db_workload
from v$SYS_TIME_MODEL sm ,tmp where STAT_NAME = 'DB time';
四 、DBTime附录
1)相关重要视图、表
DBA_HIST_SYS_TIME_MODEL
dba_hist_snapshot
DBA_HIST_SYSSTAT
v$sys_time_model
V$SYSSTAT
2)平均负载计算(上面的db_workload 为负载百分比)
Average Active Session AAS= DB time/Elapsed Time
如果仅有2个逻辑CPU,而3个session在60分钟都100%仅消耗CPU,那么总有一个要wait on queue
DB CPU = 2* 60 mins ,wait on CPU queue= 60 mins
AAS= (120+ 60)/60=3
主机load 也为3,此时vmstat 看waiting for run time
DBA_HIST_SYS_TIME_MODEL
DBA_HIST_SYS_TIME_MODEL displays historical system time model statistics. This view contains snapshots ofV$SYS_TIME_MODEL.
Column | Datatype | NULL | Description |
---|---|---|---|
SNAP_ID | NUMBER | Unique snapshot ID | |
DBID | NUMBER | Database ID for the snapshot | |
INSTANCE_NUMBER | NUMBER | Instance number for the snapshot | |
STAT_ID | NUMBER | Statistic ID | |
STAT_NAME | VARCHAR2(64) | Statistic name | |
VALUE | NUMBER | Statistic value |
DBA_HIST_SYSSTAT
DBA_HIST_SYSSTAT displays historical system statistics information. This view contains snapshots of V$SYSSTAT.
Column | Datatype | NULL | Description |
---|---|---|---|
SNAP_ID | NUMBER | Unique snapshot ID | |
DBID | NUMBER | Database ID for the snapshot | |
INSTANCE_NUMBER | NUMBER | Instance number for the snapshot | |
STAT_ID | NUMBER | Statistic identifier | |
STAT_NAME | VARCHAR2(64) | Statistic name | |
VALUE | NUMBER | Statistic value |
DBA_HIST_SNAPSHOT
DBA_HIST_SNAPSHOT displays information about the snapshots in the Workload Repository.
Column | Datatype | NULL | Description |
---|---|---|---|
SNAP_ID | NUMBER | NOT NULL | Unique snapshot ID |
DBID | NUMBER | NOT NULL | Database ID for the snapshot |
INSTANCE_NUMBER | NUMBER | NOT NULL | Instance number for the snapshot |
STARTUP_TIME | TIMESTAMP(3) | NOT NULL | Startup time of the instance |
BEGIN_INTERVAL_TIME | TIMESTAMP(3) | NOT NULL | Time at the beginning of the snapshot interval |
END_INTERVAL_TIME | TIMESTAMP(3) | NOT NULL | Time at the end of the snapshot interval; the actual time the snapshot was taken |
FLUSH_ELAPSED | INTERVAL DAY(5) TO SECOND(1) | Amount of time to perform the snapshot | |
SNAP_LEVEL | NUMBER | Snapshot level | |
ERROR_COUNT | NUMBER | Number of errors occurring in the tables for the particular snapshot | |
SNAP_FLAG | NUMBER | Condition under which the snapshot was inserted. Possible values are: 0 - Snapshot was taken automatically by the Manageability Monitor Process (MMON process) 1 - Manual snapshot created using a PL/SQL package 2 - Imported snapshot 4 - Snapshot taken while Diagnostic Pack or Tuning Pack was not enabled | |
SNAP_TIMEZONEFoot 1 | INTERVAL DAY(0) TO SECOND(0) | Snapshot time zone expressed as offset from UTC (Coordinated Universal Time) time zone |