ORACLE DBTime详解

    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计算公式：
    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 of V$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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27067062/viewspace-2127828/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27067062/viewspace-2127828/