Time and Wait Model

We briefly mentioned the Time and Wait model earlier, while introducing the performance-reporting philosophy new to Oracle Database 10g. In essence, in this version, metrics and statistics are classified by type of usage of CPU for the Time component and by type of waiting for the Wait component. In other words, the previous equation for response time can now be restated in its two component forms as follows:

Response Time = CPU Consumption by Class + Wait Time by Class

The difference is that now you can determine CPU time spent in various subcomponents (class of consumption) as well as time spent waiting for various classes of events. A quick look at the V$SYS_TIME_MODEL and V$SYSTEM_WAIT_CLASS views helps explain this in Listing 10.9.

Listing 10.9. Snapshot of V$SYS_TIME_MODEL

 SQL> column STAT_NAME format a50 SQL> set numwidth 12 SQL> select stat_name, value      from v$sys_time_model; STAT_NAME                                                 VALUE -------------------------------------------------- ------------ DB time                                               635611982 DB CPU                                                485511901 background elapsed time                             28179121230 background cpu time                                  1785120249 sequence load elapsed time                                49429 parse time elapsed                                     90176368 hard parse elapsed time                                61418093 sql execute elapsed time                              544301874 connection management call elapsed time                  707888 failed parse elapsed time                                191650 failed parse (out of shared memory) elapsed time              0 hard parse (sharing criteria) elapsed time               478684 hard parse (bind mismatch) elapsed time                  295962 PL/SQL execution elapsed time                          93880446 inbound PL/SQL rpc elapsed time                               0 PL/SQL compilation elapsed time                         4219341 Java execution elapsed time                             8220017 

We need to clarify at this time that some of the lines seen in V$SYS_TIME_MODEL are actually subcomponents of other lines. For example, the background cpu time is part of background elapsed time and can never be larger than the former. Similarly parse time elapsed is a rollup of the other parse components. The DB Time is the most valuable of these and is the total time spent by the foreground sessions performing useful work. In other words, it is a combination of CPU spent parsing and executing SQL, PL/SQL, and Java, as well other overheads such as process setup and management.

In many ways, this view is similar to the V$SYSSTAT view from earlier (as well as this) versions. This view displays some CPU-related statistics, among many others. However, this is presented in a classified and compact manner in this new view. And just as V$SESSTAT describes the same statistics at the session level, Oracle Database 10g provides the V$SESS_TIME_MODEL to determine the same components at a session level. Using the latter, you can quickly drill down to determine individual CPU consumption by session.

Having dealt with the service component, or time spent on CPU, we will now look at the wait component in the form of the V$SYSTEM_WAIT_CLASS view. A sample is shown in Listing 10.10.

Listing 10.10. Wait Components from V$SYSTEM_WAIT_CLASS

 SQL> column WAIT_CLASS format a30 SQL> select wait_class, total_waits, time_waited      from v$system_wait_class; WAIT_CLASS                      TOTAL_WAITS  TIME_WAITED ------------------------------ ------------ ------------ Other                                 10959      2595609 Application                              82            3 Configuration                           317          340 Concurrency                              74           15 Commit                                 9941         4224 Idle                                2122944    823048107 Network                                7988            9 User I/O                              82315        13057 System I/O                           356979        85897 

This view breaks down exactly how much time was spent by all sessions waiting in different types of waits. For example, if you see a large number of waits and time spent waiting under Concurrency, you might want to consider redesigning your application to minimize the time that rows are kept locked because locking is detrimental to concurrency. And after performing I/O tuning, you might want to monitor the time waited under System I/O and User I/O to verify that you did the right thing.

The Concurrency Wait Class

Although we seem to imply that concurrency has to do with row locking (which ensures transaction consistency and is seen via the enqueue event and its subevents, such as enq: TX row lock contention), it can also be caused by excessive latching. Transaction-type locks are placed on data elements to make sure that concurrent sessions don't change them simultaneously, but are performed in a serial (one after another) manner on a first-come, first-served basis. Latching, on the other hand, protects internal structures from being accessed or modified but does not generally follow the first-come, first-served rule. A simple example of latching is the holding of the shared pool latch by a process that wants to de-allocate some space from one element in the shared pool and allocate it to another element. Locks, latches, and handling of other internal structures are vast subjects by themselves, and hence, we can mention them here in passing only.

Just as we compared V$SYSSTAT and V$SYS_TIME_MODEL, we can compare V$SYSTEM_WAIT_CLASS to the V$SYSTEM_EVENT view available in older versions.

The time components for all these views are always displayed in microseconds (1/1000th of a second), and the counters are large enough to hold 580,000 years worth of statistics!

Another view, V$SERVICE_WAIT_CLASS, breaks out waits by service and wait class within the service since database restart. A comparison of statistics from this view before and after a performance event of interest can reveal much. A sample snapshot is shown in Listing 10.11.

Listing 10.11. Snapshot of V$SERVICE_WAIT_CLASS

 SQL> set numwidth 12 SQL> column SERVICE_NAME format a20 SQL> column WAIT_CLASS format a20 SQL> select service_name, wait_class, total_waits, time_waited   2  from v$service_wait_class   3  order by service_name, time_waited desc; SERVICE_NAME         WAIT_CLASS            TOTAL_WAITS  TIME_WAITED -------------------- -------------------- ------------ ------------ SYS$BACKGROUND       System I/O                8103534  72417515074 SYS$BACKGROUND       Idle                      1459745    508654694 SYS$BACKGROUND       Other                        8999      2158769 SYS$BACKGROUND       User I/O                    26733         3790 SYS$BACKGROUND       Commit                        383          315 SYS$BACKGROUND       Configuration                 254          224 SYS$BACKGROUND       Concurrency                    46            3 SYS$USERS            Idle                       300152     95045746 SYS$USERS            User I/O                    36505         6394 SYS$USERS            Commit                       7910         3178 SYS$USERS            Other                          16          443 SYS$USERS            Configuration                  28           53 SYS$USERS            System I/O                   2361           17 SYS$USERS            Network                      6820            9 SYS$USERS            Concurrency                    21            6 SYS$USERS            Application                    64            2 TS10G                Idle                           11          165 TS10G                User I/O                       18            6 TS10G                Commit                          2            0 TS10G                Network                        11            0 

Summarized this way, it is easy to see classified wait statistics for both system and user I/O load as well as any concurrency issues at the system level.

In summary, interpretation of statistics that was previously based on experience and specialized knowledge is now available in a very intuitive, easy-to-understand, and classified format.