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 ClassAlthough 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.