 |
|
Oracle DB time
tips
Oracle Consulting Tips by Burleson
|
The
DB time Oracle
metric
is 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.
The goal for tuning Oracle process should be to reduce to minimum
CPU time and Wait time so that more transactions can be processed.
This is done by tuning the SQL:
DB Time = CPU time + I/O time +
Non-idle wait time
DB time is total time spent by user
processes either actively working or actively waiting in a database
call.
From this formula we can conclude that database requests are
composed from CPU (service time, performing some work) and wait time
(session is waiting for resources).
select
to_char(begin_time,'dd.mm.yyy hh24:mi:ss')
begin_time,
to_char(end_time,'dd.mm.yyy
hh24:mi:ss') end_time,
intsize_csec interval_size,
group_id,
metric_name,
value
from
v$sysmetric
where
metric_name =
'Database Time Per Sec';
Here is a DBV time query from
v$sysmetric_summary:
select
maxval,
minval,
average,
standard_deviation
from
v$sysmetric_summary
where
metric_name = 'Database Time
Per Sec';
Here is another query for DB time from
the ASH table:
select
count(*) DB_TIME,
from
v$active_session_history
where session_type =
'FOREGROUND'
and sample_time between to_date('30032016 10:00:00','ddmmyyyy
hh24:mi:ss')
and to_date('30032016 10:30:00','ddmmyyyy
hh24:mi:ss');
You can see the current
value of DB time for the entire system by querying the
V$SYS_TIME_MODEL or you can see it for a given session by using the
V$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
See also on "db time":
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

|
|
|
|
|