oracle时间模型

When tuning an Oracle database, each component has its own set of statistics. To lookat the system as a whole, it is necessary to have a common scale for comparisons. Forthis reason, most Oracle Database advisories and reports describe statistics in terms oftime. In addition, the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views provide timemodel statistics. Using the common time instrumentation helps to identify quantitativeeffects on the database operations.

当我们对数据库进行优化时，数据库组件向我们提供了有关自身的统计信息。为了站在整体系统的角度来看待问题，我们需要一套通用的度量方法来比较各种统计信息。出于这个原因，大部分的oracle优化建议器和报告以时间的方式来描述各类统计信息。视图V$sess_time_model和V$SYS_TIME_MODEL为我们提供了基于时间模型的统计信息。

The most important of the time model statistics is DB time. This statistics represents thetotal time spent in database calls and is an indicator of the total instance workload. It iscalculated by aggregating the CPU and wait times of all sessions not waiting on idlewait events (non-idle user sessions).

在时间模型统计信息中，DB TIME占用非常重要的地位。DB TIME表示所有花费在数据库调用上的时间总和，可以作为数据库实例负载的度量工具。DB TIME 是所有用户进程花费在cpu和非空闲等待事件上的时间总和。

DB time is measured cumulatively from the time of instance startup. Because DB time itis calculated by combining the times from all non-idle user sessions, it is possible thatthe DB time can exceed the actual time elapsed after the instance started. For example,an instance that has been running for 30 minutes could have four active user sessionswhose cumulative DB time is approximately 120 minutes.

DB TIME的统计是从实例启动的那一刻开始的。因为db time是所有非空闲用户进程的时间总和，因此其值可能会超过实例运行时间。例如，一个运行30分钟的实例,其DB TIME可能会是120分钟，因为该实例具有4个活动用户进程。

The objective for tuning an Oracle system could be stated as reducing the time thatusers spend in performing some action on the database, or simply reducing DB time.
Other time model statistics provide quantitative effects (in time) on specific actions,such as logon operations and hard and soft parses.

数据库调优的目的便是降低用户执行某些操作所花费的时间，简单一点来说，就是降低DB TIME。除DB TIME 之外，oracle还为我们提供了其他的时间模型信息，例如，用来记录用户登录花费时间的统计信息和记录软硬解析花费时间的统计信息。

在某种意义上，我们可以认为db time为oracle数据库对用户请求的响应时间。但是db time可以在不同的级别进行累计操作（在session级别，service 级别，instance级别），此时再称之为响应时间就不太合适了。

响应时间一般可以分为服务时间和等待时间，即R=S+W。服务时间指进程占用cpu的时间，等待时间包括很多类型，如IO等待和并非等待。

基于响应时间的Oracle优化原则：尽量减少等待时间（Wait time），提高服务时间（Service time）。这也是基于Oracle等待事件的分析方法的基本原则：尽量消除各种等待事件对系统的影响，从而提高系统性能和响应时间。

如果数据库系统除了CPU和IO以外的等待时间超过DB time的5%以上的话，可能存在某些性能问题，需要DBA采用等待事件的分析方法，对系统或应用进行优化。

V$SESS_TIME_MODELdisplays the session-accumulated time for various operations. The time reported is the total elapsed or CPU time (in microseconds). Any timed operation will buffer at most 5 seconds of time data. Specifically, this means that if a timed operation (such as SQL execution) takes a long period of time to perform, the data published to this view is at most missing 5 seconds of the time accumulated for the operation.

v$sess_time_model显示了session级别的时间统计信息，以微秒为单位。任何操作最多缓存5秒时间数据，因此在特定情况下，如果某个操作持续了很长时间，那么最多会存在5秒的时间误差。

The time values are 8-byte integers and can therefore hold approximately 580,000 years of time before wrapping. Background process time is not included in a statistic value unless the statistic is specifically for background processes.

视图中的统计指标不包含后台进程的时间信息，除非某项指标是专门为后台进程准备的。

Column Datatype Description

SID	NUMBER	Session ID (same as inV$SESSION)
STAT_ID	NUMBER	Statistic identifier for the time statistic
STAT_NAME	VARCHAR2(64)	Name of the statistic
VALUE	NUMBER	Amount of time (in microseconds) that the session has spent in this operation

V$SESS_TIME_MODEL and V$SYS_TIME_MODEL Statistics

Statistic Name Description

DB Time	Amount of elapsed time (in microseconds) spent performing Database user-level calls. This does not include the elapsed time spent on instance background processes such as PMON.不包括后台进程数据
DB CPU	Amount of CPU time (in microseconds) spent on database user-level calls. This does not include the CPU time spent on instance background processes such as PMON.
background elapsed time	Amount of elapsed time (in microseconds) consumed by database background processes.专门为后台进程准备
background CPU time	Amount of CPU time (in microseconds) consumed by database background processes.专门为后台进程准备
sequence load elapsed time	Amount of elapsed time spent getting the next sequence number from the data dictionary. If a sequence is cached, then this is the amount of time spent replenishing the cache when it runs out. No time is charged when a sequence number is found in the cache. For non-cached sequences, some time will be charged for every nextval call.
parse time elapsed	Amount of elapsed time spent parsing SQL statements. It includes both soft and hard parse time.
hard parse elapsed time	Amount of elapsed time spent hard parsing SQL statements.
SQL execute elapsed time	Amount of elapsed time SQL statements are executing. Note that for select statements this also includes the amount of time spent performing fetches of query results.
connection management call elapsed time	Amount of elapsed time spent performing session connect and disconnect calls.
failed parse elapsed time	Amount of time spent performing SQL parses which ultimately fail with some parse error.
failed parse (out of shared memory) elapsed time	Amount of time spent performing SQL parses which ultimately fail with errorORA-04031.
hard parse (sharing criteria) elapsed time	Amount of elapsed time spent performing SQL hard parses when the hard parse resulted from not being able to share an existing cursor in the SQL cache.
hard parse (bind mismatch) elapsed time	Amount of elapsed time spent performing SQL hard parses when the hard parse resulted from bind type or bind size mismatch with an existing cursor in the SQL cache.
PL/SQL execution elapsed time	Amount of elapsed time spent running the PL/SQL interpreter. This does not include time spent recursively executing/parsing SQL statements or time spent recursively executing the Java VM.
PL/SQL compilation elapsed time	Amount of elapsed time spent running the PL/SQL compiler.
inbound PL/SQL rpc elapsed time	Time inbound PL/SQL remote procedure calls have spent executing. It includes all time spent recursively executing SQL and JAVA, and therefore is not easily related to "PL/SQL execution elapsed time".
Java execution elapsed time	Amount of elapsed time spent running the Java VM. This does not include time spent recursively executing/parsing SQL statements or time spent recursively executing PL/SQL.
RMAN cpu time (backup/restore)	Amount of CPU time (in microseconds) spent in RMAN backup and restore operations.
repeated bind elapsed time	Amount of elapsed time spent giving new values to bind variables (rebinding).

The relationships between the statistics listed in form two trees in which all the time reported by a child in the tree is contained within the parent in the tree. The following are the relationship trees; the number is the level in the given tree.

我们可以将各类时间统计信息归纳为如下的树形结构：

1) background elapsed time
    2) background cpu time
          3) RMAN cpu time (backup/restore)
1) DB time
    2) DB CPU
    2) connection management call elapsed time
    2) sequence load elapsed time
    2) sql execute elapsed time
    2) parse time elapsed
          3) hard parse elapsed time
                4) hard parse (sharing criteria) elapsed time
                    5) hard parse (bind mismatch) elapsed time
          3) failed parse elapsed time
                4) failed parse (out of shared memory) elapsed time
    2) PL/SQL execution elapsed time
    2) inbound PL/SQL rpc elapsed time
    2) PL/SQL compilation elapsed time
    2) Java execution elapsed time
    2) repeated bind elapsed time

The relationship between a parent and a child in the tree indicates containment only. Keep the following in mind with regard to the tree:

• Children do not necessarily add up to the parent.

• Children are not necessarily exclusive (that is, it is possible that they overlap).（各个子条目之间可能存在交叉）

• The union of children does not necessarily cover the whole of the parent.