Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Mar 5, 2009

Modeling Oracle Time

By James Koopmann

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date