Oracle Database 10g time models

When you have a performance problem, what comes to mind first to reduce the response time? Obviously, you want to eliminate (or reduce) the root cause of the factor that adds to the time. How do you know where the time was spent--not waiting, but actually doing the work?

Oracle Database 10g introduces time models for identifying the time spent in various places. The overall system time spent is recorded in the view V$SYS_TIME_MODEL. Here is the query and its output.

STAT_NAME                                     VALUE
-------------------------------------         --------------
DB time                                       58211645
DB CPU                                        54500000
background cpu time                           254490000
sequence load elapsed time                    0
parse time elapsed                            1867816
hard parse elapsed time                       1758922
sql execute elapsed time                      57632352
connection management call elapsed time       288819
failed parse elapsed time                     50794
hard parse (sharing criteria) elapsed time    220345
hard parse (bind mismatch) elapsed time       5040
PL/SQL execution elapsed time                 197792
inbound PL/SQL rpc elapsed time               0
PL/SQL compilation elapsed time               593992
Java execution elapsed time                   0
bind/define call elapsed time                 0

Note the statistic named DB Time, which represents the time spent in the database since the instance startup. Run the sample workload and select the statistic value from the view again. The difference should represent the time spent in the database for that workload. After another round of tuning, perform the same analysis and that difference will show the change in DB Time after the tuning, which can be compared to first change to examine the effect of the tuning exercise on the database time.

In addition to the database time, the V$SYS_TIME_MODEL view shows a whole lot of other statistics, such as time spent in different types of parsing and even PL/SQL compilation.

This view shows the overall system times as well; however, you may be interested in a more granular view: the session level times. The timing stats are captured at the session level as well, as shown in the view V$SESS_TIME_MODEL, where all the stats of the current connected sessions, both active and inactive, are visible. The additional column SID specifies the SID of the sessions for which the stats are shown.

In previous releases, this type of analysis was impossible to get and the user was forced to guess or derive from a variety of sources. In Oracle Database 10g, getting this information is a snap.