Oracle Time Model

When tuning an Oracle database, each component has its own set of statistics. to look at the system as a whole, it is necessary to have a common scale for comparisons. for this reason, most Oracle Database advisories and reports describe statistics in terms of time. in addition, the V $ SESS_TIME_MODEL and V $ SYS_TIME_MODEL views provide time model statistics. using the common time instrumentation helps to identify quantitative effects on the database operations.

When we optimize the database, the database component provides us with statistics about ourselves. To look at the problem from the perspective of the overall system, we need a set of common measurement methods to compare various statistical information. For this reason, most oracle optimizer and reports describe various statistical information over time. Views V $ sess_time_model and V $ SYS_TIME_MODEL provide us with time-based statistical information.

The most important of the time model statistics is DB time. this statistics represents the total time spent in database CILS and is an indicator of the total instance workload. it is calculated by aggregating the CPU and wait times of all sessions not waiting on idle wait events (non-idle user sessions ).

In the statistical information of the TIME model, db time occupies a very important position. Db time indicates the total TIME spent on Database calls. It can be used as a measurement tool for database instance load.Db time is the total TIME spent by all user processes on cpu and non-idle wait events.

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

Db time statistics start from the moment the instance is started. Because db time is the total time of all non-idle user processes, its value may exceed the running time of the instance. For example, if an instance runs for 30 minutes, its db time may be 120 minutes because the instance has four active user processes.

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

The purpose of database optimization is to reduce the TIME it takes for users to execute certain operations. To put it simply, it is to reduce the db time. In addition to db time, oracle also provides us with other TIME model information, such as statistical information used to record the TIME spent on user login and hardware and software parsing.

In a sense, we can consider db time as the response time of the oracle database to user requests. However, the db time can be accumulated at different levels (at the session level, service level, and instance level). In this case, it is not appropriate to call the response time.

Generally, the response time can be divided into service time and wait time, that is, R = S + W. The service time refers to the cpu usage time of the process. The waiting time includes many types, such as IO waiting and not waiting.

Oracle Optimization Principle Based on response time: reduce the Wait time as much as possible and increase the Service time ). This is also the basic principle based on the Oracle wait event analysis method: try to eliminate the impact of various wait events on the system, so as to improve system performance and response time.

If the wait time of the database system besides CPU and IO exceeds 5% of the DB time, there may be some performance problems, and the DBA needs to analyze the wait event, optimize the system or application.