Oracle Metric DB time
Oracle Tips by Burleson Consulting
The DB time Oracle metricis the amount of elapsed time (in microseconds) spent performing Database user-level calls. This does not include the time spent on instance background processes such as PMON.
ADDM’s goal is to improve the value of a statistic called db time. Db time is a value that Oracle calculates to indicate the cumulative time that is spent processing user requests. ADDM’s goal is to reduce the overall db time value, and it does not target individual users or user response times, and thus the db time it uses is an aggregate value of overall system CPU and wait times.
You can see the current value of db time for the entire system by querying theV$SYS_TIME_MODEL or you can see it for a given session by using theV$SESS_TIME_MODEL view as seen here:
select sum(value) "DB time" from v$sess_time_model
where stat_name='DB time';
DB time
----------
109797
v$sys_time_model scripts
Oracle Tips by Burleson Consulting
This chapter covers the new “time model” tuning approach introduced in the Oracle10g database. In plain English, the time model approach allows the DBA to identify where the Oracle database spends its CPU processing time, and the time model method reveals how much CPU time is consumed by each processing component of the database.
The greatest benefit of this approach is that every statistic is measured in terms of time, so that the DBA can evaluate CPU consumers on an equal basis. This approach is best suited for situations in which there is a CPU-bound system and the DBA needs to know what steps to take to remove or at least decrease this time dependence.
The Oracle10g database provides two basic v$ time model performance views: v$sys_time_model and v$sess_time_model. These views report on cumulative database processing times for the whole instance, and on a per session basis.
desc v$sys_time_model
Name Null? Type
----------------- -------- ------------
STAT_ID NUMBER
STAT_NAME VARCHAR2(64)
VALUE NUMBER
desc v$sess_time_model
Name Null? Type
----------------- -------- ------------
SID NUMBER
STAT_ID NUMBER
STAT_NAME VARCHAR2(64)
VALUE NUMBER
The v$sys_time_model view display cumulative times, expressed in microseconds, which are collected from all non-idle database sessions. Therefore, these cumulative times could be greater than the total time measured since instance startup.
This cumulative approach is not applicable to the times reported for session time model statistics presented in the v$sess_time_model view. The background process times are not included in the statistic values unless the statistic is specific for background processes. For example, the background elapsed time statistic shows database time spent for the background processes to run.
Use the following script to retrieve a list of time model statistics available in Oracle10g database:
SQL> select
stat_name,
Round(value/1000000) "Time (Sec)"
from v$sys_time_model;
The output of the above query shows many of the most important statistics, looks like:
STAT_NAME Time (Sec)
------------------------------------------ ----------
DB time 515,178
DB CPU 299,352
background elapsed time 583,318
background cpu time 128,953
sequence load elapsed time 59
parse time elapsed 76,889
hard parse elapsed time 73,126
sql execute elapsed time 505,637
connection management call elapsed time 458
failed parse elapsed time 9,503
failed parse (out of shared memory) elapsed time 0
hard parse (sharing criteria) elapsed time 1,382
hard parse (bind mismatch) elapsed time 184
PL/SQL execution elapsed time 36,788
inbound PL/SQL rpc elapsed time 0
PL/SQL compilation elapsed time 4,932
Java execution elapsed time 1,949
The listing above confirms that Oracle computes elapsed times for various parts of both user and background processing. For example, the DBA can see how much time is consumed in the hard parsing of SQL statements. If this time component is large enough, the DBA might wish to consider investigating the application to find inefficient SQL statements that are causing the excessive hard parsing workload and perhaps implement cursor_sharing =force. If a high PL/SQL execution elapsed time is discovered, the system may benefit from optimization of the PL/SQL programs.
The most important time model statistics are DB time and DB CPU. The DB time statistic shows the elapsed processing time accumulated from the elapsed times of non-idle sessions. DB CPU presents the cumulative CPU time from all non-idle sessions. Both statistics directly show database workload and describe overall database response time.
Most of the Oracle advisors use the time model approach to perform their work, and DB time is a statistic used as a criterion for tuning and producing the recommendations. Of course, all database users want to get results as soon as possible, and the goal of tuning for every particular session can be summarized as the minimization of database response time for the session or the whole system.
It is also useful to monitor DB time or DB CPU statistics in real time. This approach allows the DBA to immediately identify possible system overload or stress when an exceptional event occurs. For example, The Ion tool allows the DBA to monitor database time in real time as shown in Figure 9.1 below:
Figure 9.1: Monitoring real-time database processing with the Ion tool.
v$sess_time_model scripts
Oracle Tips by Burleson Consulting
It is possible to write an exception script that will give a warning if the database workload exceeds some predefined threshold. For example, the script below lists sessions that experience high workload and consume significant processing resources:
SELECT
s.sid,
s.username,
s.module,
round(t.value/1000000,2) "Elapsed Processing Time (Sec)"
FROM
v$sess_time_model t,
v$session s
WHERE
t.sid = s.sid
AND
t.stat_name = 'DB time'
AND
s.username IS NOT NULL
AND
t.value/1000000 >= 1;
The output, showing the elapsed time used by specific modules, looks like the following:
SID USER MODULE Elapsed Processing Time (Sec)
---------- ----- ------------ -----------------------------
137 DABR SQL*Plus 5,93
133 SPV spvent.exe 26,47
135 SYS SQL*Plus 6,36
141 DABR SpMon.exe 23,42
150 DABR spvent.exe 8,3
152 SYS SQL*Plus 8,13
156 SYS SQL*Plus 1,43
In the script above, the (WHERE t.value/1000000>1) clause shows user sessions that consume processing time of more than one second.
Once the sessions consuming high resources have been identified, the DBA reviews what particular type of processing causes such a high workload. For example, the query below reveals time model statistics for a particular session:
select
*
from
v$sess_time_model
where
sid = 137
order by
value desc;
The output of the above query, showing all important statistics for a particular session, might look like:
SID STAT_ID STAT_NAME VALUE
--- ------------- ----------------------------------------------- ----------
137 3,649,082,374 DB time 7,244,647
137 2,821,698,184 sql execute elapsed time 7,035,683
137 2,748,282,437 DB CPU 6,827,842
137 1,431,595,225 parse time elapsed 529,294
137 372,226,525 hard parse elapsed time 512,333
137 1,311,180,441 PL/SQL compilation elapsed time 65,472
137 1,990,024,365 connection management call elapsed time 18,180
137 2,643,905,994 PL/SQL execution elapsed time 1,225
137 4,157,170,894 background elapsed time 0
137 751,169,994 Java execution elapsed time 0
137 290,749,718 inbound PL/SQL rpc elapsed time 0
137 268,357,648 hard parse (bind mismatch) elapsed time 0
137 2,451,517,896 background cpu time 0
137 1,824,284,809 failed parse elapsed time 0
137 3,138,706,091 hard parse (sharing criteria) elapsed time 0
137 4,125,607,023 failed parse (out of shared memory) elapsed time 0
From the listing above, it is apparent that a significant part of processing time for this database is consumed by SQL execution. This could clue the DBA that an investigation into SQL statements to identify high CPU consuming SQL statements is required. For example, such SQL statements could have a large number of buffer gets that were taken in consistent mode, and this requires a large amount of CPU processing time to reconstruct data blocks from rollback segments (UNDO logs).