Modeling Oracle Time

Oracle’s Time Model – a thousand foot view

It is often difficult to zero in on where
to spend time tuning an Oracle database. From a very high level, Oracle Time
Model can provide direction and offer feedback for tuning.

When database performance deteriorates,
one plan of action is to check where time is spent in the databases. Disk, CPU,
and memory are the only three resources that a database consumes and at a high level,
we should be looking at these levels if we have no idea where to begin. CPU
usage used to be something we had to try and guess at—using operating system
commands and accumulating statistics across a variety of V$ views. Oracle has
introduced time modeling that helps in checking to see if the database server
is constrained for CPU resources—looking at both the system and session levels
to see how CPU consumption is distributed, either at the O/S level or within
the database. This can be done through Oracle’s V$SYS_TIME_MODEL and
V$SESS_TIME_MODEL views.

Both V$SYS_TIME_MODEL and
V$SESS_TIME_MODEL are very similar, with the exception of SID being added to
the session time model.

The V$SYS_TIME_MODEL,
when queried will display system level accumulated times for various tracked
statistics—the total elapsed time or CPU time depending on the statistic.


SQL> desc v$sys_time_model
Name
—————————-
STAT_ID
STAT_NAME
VALUE

The V$SESS_TIME_MODEL,
when queried will display session level accumulated times for various tracked
statistics—the total elapsed time or CPU time depending on the statistic.


SQL> desc v$sess_time_model
Name
—————————-
SID
STAT_ID
STAT_NAME
VALUE

The key point to take away is
that all time values are accumulated for system or session respectively. So
when we query the V$SYS_TIME_MODEL with the following SQL, the time spent for
each statistic tells us where Oracle is spending time since it was first
started, and potentially where we could spend some time tuning. However, it really
doesn’t tell us about peaks or valleys in time spent to effectively pinpoint
where to spend our tuning efforts.

Realizing the time model
tracks time in microseconds (one millionth of a second), we could write a SQL
query to look at these timings. Take into consideration any other statistic
values you may want to compare these to as Oracle often tracks time in various
ticks of seconds.


SQL> select stat_name, trunc(value/1000000,2) seconds
2 from v$sys_time_model
3 order by 2 desc;

STAT_NAME SECONDS
———————————————————— ———-
DB time 242.28
sql execute elapsed time 238.64
background elapsed time 153.43
DB CPU 78.69
parse time elapsed 65.82
hard parse elapsed time 62.79
background cpu time 29.46
Java execution elapsed time 20.58
PL/SQL execution elapsed time 17.79
PL/SQL compilation elapsed time 12.42
hard parse (sharing criteria) elapsed time .45
repeated bind elapsed time .35
connection management call elapsed time .16
hard parse (bind mismatch) elapsed time .03
failed parse elapsed time .03
sequence load elapsed time .01
inbound PL/SQL rpc elapsed time 0
RMAN cpu time (backup/restore) 0
failed parse (out of shared memory) elapsed time 0

The beauty of
the time model is that it gives a high-level overview and hierarchy of where
time is being spent. For instance, DB Time is the total amount of elapsed time
that was spent performing user-level calls. DB Time is then made up of
additional timings such as DB CPU, connection management call elapsed time,
sequence load elapsed time, etc. – providing a nice look deep into where time
is spent. While the following SQL looks a little rough, it will produce a
hierarchical time model, such as is suggested in the Oracle manuals, showing
the relationship tree for the cumulative times. Keep in mind when looking at
the timings reported by Oracle that the children times do not necessarily add
up to the total time reported by the parents, there might be overlap in
reporting time between multiple children, and there may be other timings not
included.


SELECT LPAD(‘ ‘, 2*level-1)||stat_name stat_name,
trunc(value/1000000,2) seconds
FROM (
select 0 id, 9 pid, null stat_name, null value from dual union
select decode(stat_name,’DB time’,10) id ,
decode(stat_name,’DB time’,0) pid , stat_name, value
from v$sys_time_model
where stat_name = ‘DB time’ union
select decode(stat_name,’DB CPU’,20) id ,
decode(stat_name,’DB CPU’,10) pid , stat_name, value
from v$sys_time_model
where stat_name = ‘DB CPU’ union
select decode(stat_name,’connection management call elapsed time’,21) id ,
decode(stat_name,’connection management call elapsed time’,10) pid , stat_name, value
from v$sys_time_model
where stat_name = ‘connection management call elapsed time’ union
select decode(stat_name,’sequence load elapsed time’,22) id ,
decode(stat_name,’sequence load elapsed time’,10) pid , stat_name, value
from v$sys_time_model
where stat_name = ‘sequence load elapsed time’ union
select decode(stat_name,’sql execute elapsed time’,23) id ,
decode(stat_name,’sql execute elapsed time’,10) pid , stat_name, value
from v$sys_time_model
where stat_name = ‘sql execute elapsed time’ union
select decode(stat_name,’parse time elapsed’,24) id ,
decode(stat_name,’parse time elapsed’,10) pid , stat_name, value
from v$sys_time_model
where stat_name = ‘parse time elapsed’ union
select decode(stat_name,’hard parse elapsed time’,30) id ,
decode(stat_name,’hard parse elapsed time’,24) pid , stat_name, value
from v$sys_time_model
where stat_name = ‘hard parse elapsed time’ union
select decode(stat_name,’hard parse (sharing criteria) elapsed time’,40) id ,
decode(stat_name,’hard parse (sharing criteria) elapsed time’,30) pid , stat_name, value
from v$sys_time_model
where stat_name = ‘hard parse (sharing criteria) elapsed time’ union
select decode(stat_name,’hard parse (bind mismatch) elapsed time’,50) id ,
decode(stat_name,’hard parse (bind mismatch) elapsed time’,40) pid , stat_name, value
from v$sys_time_model
where stat_name = ‘hard parse (bind mismatch) elapsed time’ union
select decode(stat_name,’failed parse elapsed time’,31) id ,
decode(stat_name,’failed parse elapsed time’,24) pid , stat_name, value
from v$sys_time_model
where stat_name = ‘failed parse elapsed time’ union
select decode(stat_name,’failed parse (out of shared memory) elapsed time’,41) id ,
decode(stat_name,’failed parse (out of shared memory) elapsed time’,31) pid , stat_name, value
from v$sys_time_model
where stat_name = ‘failed parse (out of shared memory) elapsed time’ union
select decode(stat_name,’PL/SQL execution elapsed time’,25) id ,
decode(stat_name,’PL/SQL execution elapsed time’,10) pid , stat_name, value
from v$sys_time_model
where stat_name = ‘PL/SQL execution elapsed time’ union
select decode(stat_name,’inbound PL/SQL rpc elapsed time’,26) id ,
decode(stat_name,’inbound PL/SQL rpc elapsed time’,10) pid , stat_name, value
from v$sys_time_model
where stat_name = ‘inbound PL/SQL rpc elapsed time’ union
select decode(stat_name,’PL/SQL compilation elapsed time’,27) id ,
decode(stat_name,’PL/SQL compilation elapsed time’,10) pid , stat_name, value
from v$sys_time_model
where stat_name = ‘PL/SQL compilation elapsed time’ union
select decode(stat_name,’Java execution elapsed time’,28) id ,
decode(stat_name,’Java execution elapsed time’,10) pid , stat_name, value
from v$sys_time_model
where stat_name = ‘Java execution elapsed time’ union
select decode(stat_name,’repeated bind elapsed time’,29) id ,
decode(stat_name,’repeated bind elapsed time’,10) pid , stat_name, value
from v$sys_time_model
where stat_name = ‘repeated bind elapsed time’ union
select decode(stat_name,’background elapsed time’,1) id ,
decode(stat_name,’background elapsed time’,0) pid , stat_name, value
from v$sys_time_model
where stat_name = ‘background elapsed time’ union
select decode(stat_name,’background cpu time’,2) id ,
decode(stat_name,’background cpu time’,1) pid , stat_name, value
from v$sys_time_model
where stat_name = ‘background cpu time’ union
select decode(stat_name,’RMAN cpu time (backup/restore)’,3) id ,
decode(stat_name,’RMAN cpu time (backup/restore)’,2) pid , stat_name, value
from v$sys_time_model
where stat_name = ‘RMAN cpu time (backup/restore)’)
CONNECT BY PRIOR id = pid START WITH id = 0;

STAT_NAME SECONDS
———————————————————— ———-
background elapsed time 153.53
background cpu time 29.46
RMAN cpu time (backup/restore) 0
DB time 242.29
DB CPU 78.7
connection management call elapsed time .16
sequence load elapsed time .01
sql execute elapsed time 238.64
parse time elapsed 65.82
hard parse elapsed time 62.79
hard parse (sharing criteria) elapsed time .45
hard parse (bind mismatch) elapsed time .03
failed parse elapsed time .03
failed parse (out of shared memory) elapsed time 0
PL/SQL execution elapsed time 17.79
inbound PL/SQL rpc elapsed time 0
PL/SQL compilation elapsed time 12.42
Java execution elapsed time 20.58
repeated bind elapsed time .35

From the results of this query
it is easy to see the top consumers of time. When using the time model to tune
a database, it is best to look at DB time first to get some indication of the
load on the databases. The time accumulated for DB time is considered the
workload of the database as it represents the total time for database calls, an
aggregation of all CPU and wait times for all sessions and excluding idle wait
events. Since DB time is the aggregation of all CPU and wait times, working or
not working, and is for all sessions, this value can be greater than real time
(wall clock time) or any amount of CPU time actually available in the system.
Assume you have 1 CPU and 1,000 users where only 500 users are able to work at
any one time. This leaves the 500 users in a wait for some resource (CPU, I/O,
latch, etc.) but just so long as those idle 500 users are waiting on a non-idle
wait event, their wait times will be added to DB time.

One might wonder if this is a
good thing or a bad thing. If you take a purist definition of the term
workload, you will find that workload is the total requests made by users and
applications of a system. Therefore, when Oracle defines DB time as the amount
of elapsed time spent performing database user-level calls, this seems to fit
right in line. DB time becomes a number we can use to gage the amount of work
requested, at least in respect to time, of the databases regardless of the
database being able to work on it or not. Theoretically, if there were no wait
events, this value would be the total time the database was actively working on
requests.

The goal of a DBA would be to
reduce the DB time number to be as low as possible for any given time period.
Obviously DBAs constantly try and reduce this number by eliminating wait
events, but now we have a bit more incentive to reduce DB time by tuning SQL,
applications, architecture, database design, instance layout, etc. –realizing
that if we can produce a result set faster then DB time will also be reduced.

Granted, the time model does
not give the low level granular detail we are use to seeing in many of the
other V$ views in Oracle. However, it does provide us with a great high-level
view of where to focus our attention. For instance, with the differentiation of
SQL, PL/SQL, parse, and execute timings, we can zero in on where we might want
to focus tuning efforts in applications much easier. The time model is not
meant to provide answers to deep tuning requests, but instead it is designed to
focus our attention to an area, sort of the one thousand foot level approach. Moreover,
if viewed over time, the time model offers a nice method of seeing how overall
tuning efforts are progressing, if DB time has reduced by 10% over a month’s
time then you can effectively say your tuning efforts are well worth it. On the
other hand, if DB time is increasing over time then you might have fouled up
tuning efforts or there are additional applications you are unaware of.

»


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles