New OWI Views in Oracle Database 10g Release 1
New OWI Views in Oracle Database 10 g Release 1
Oracle Database 10 g Release 1 contains several nice enhancements to the OWI, such as the wait event classification, the wait event history, and wait event metrics. Several new V$ views are also added to OWI, and the V$SESSION view has been enhanced to include wait event information.
V$SESSION_WAIT_HISTORY View
Historical information has been lacking from the Oracle wait events views. Low-level historical data simply doesn ‚ t exist, and DBAs have to rely on high-level data from V$SESSION_EVENT, V$SYSTEM_EVENT and the Statspack utility for performance diagnosis. This view retains the 10 most recent wait events for each connected session. This is not a complete history, but it is a step in the right direction.
The V$SESSION_WAIT_HISTORY view contains the following columns :
Name Type
-------------------- ---------------
SID NUMBER
SEQ# NUMBER
EVENT# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P2TEXT VARCHAR2(64)
P2 NUMBER
P3TEXT VARCHAR2(64)
P3 NUMBER
WAIT_TIME NUMBER
WAIT_COUNT NUMBER
How to Use V$SESSION_WAIT_HISTORY View
As you noticed, most of the columns in this view are identical to the ones in V$SESSION_WAIT except for the SEQ#, EVENT#, and WAIT_COUNT columns.
The value in column SEQ# indicates the order in which the session encountered the wait events. The most recent event will have a value of 1, while the oldest event will have a value of 10. The column EVENT# identifies the event in the instance. Please note that in Oracle Database 10 g Release 1 there is no EVENT_ID in this view. The WAIT_TIME column shows the session wait time for the event. A value of zero means the session was waiting for the event to complete when this information was captured. A value greater than zero denotes the session ‚ s last wait time.
The WAIT_COUNT column shows the number of times the session waited for this event. However, we failed to notice any value other than 1 in this column in Oracle Database 10 g Release 1.
This view comes in handy when you don ‚ t want to keep querying V$SESSION_WAIT view in quick successions to see what events get posted by the session but would like to quickly get a peek at what events the session has posted recently. The view refreshes the information when the session posts the next event. The oldest event is removed, and the newest one is displayed with a SEQ# of 1.
You can view the history of wait events for a particular session using the following query:
select sid, seq#, event, p1, p2, p3, wait_time
from v$session_wait_history
where sid = <sid>;
Following is a sample output from the above query for SID 20:
SID SEQ# EVENT P1 P2 P3 WAIT_TIME
--- ---- ---------------------------- ---------- ------- ------ ----------
20 1 db file scattered read 6 192033 8 1
20 2 db file scattered read 6 192025 8 0
20 3 db file scattered read 6 192017 8 0
20 4 db file scattered read 6 192009 8 0
20 5 db file scattered read 6 192001 8 0
20 6 SQL*Net message to client 1650815232 1 0 0
20 7 SQL*Net message from client 1650815232 1 0 0
20 8 db file scattered read 6 192057 8 0
20 9 db file scattered read 6 192049 8 0
20 10 db file scattered read 6 192041 8 0
10 rows selected.
This information is good, but 10 slots are too few for an active session because events roll off too quickly. We hope Oracle will provide more slots in future releases. However, as we said earlier, this is the first step in the right direction in providing online historical data for performance diagnosis.
The V$ACTIVE_SESSION_HISTORY view in Oracle Database 10 g Release 1 also offers historical performance data, which we will discuss in Chapter 9. The information is based on 1-second sampling intervals versus the most recent 10 waits, as in the V$SESSION_WAIT_HISTORY view. We prefer the V$ACTIVE_SESSION_HISTORY because it gives more historical data.
V$SYSTEM_WAIT_CLASS View
This V$SYSTEM_WAIT_CLASS shows the instance-level total waits and time waited by wait class since instance startup.
The view V$SYSTEM_WAIT_CLASS has the following columns:
Name Type
-------------------- ---------------
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
TOTAL_WAITS NUMBER
TIME_WAITED NUMBER
How to Use V$SYSTEM_WAIT_CLASS View
As with the V$SYSTEM_EVENT view, the information displayed by V$SYSTEM_WAIT_CLASS view can be used to quickly check the health of the database instance. Instead of focusing on individual events, you are focusing on the class level.
You can find out what wait classes posted the most wait times since the instance startup. The view will have at most 12 rows, so a simple SELECT * order by TIME_WAITED against this view will reveal the class that has the most waits since instance startup. However, sampling the information between two points of time will show you which wait classes are contributing to the wait times for that period.
You can use a similar method to the one discussed in the ‚“V$SYSTEM_EVENT View ‚½ section for computing the delta. Following is a sample output from one of our tests. Ignoring the Idle wait class for the time being, you can see that the System I/O wait class has the most time. You can now focus only on those wait events that are classified as System I/O . You can use this information to track down the sessions that may be contributing wait times to this wait class.
WAIT_CLASS# WAIT_CLASS Delta_Waits Delta_Time_Waited
----------- -------------- ----------- -----------------
6 Idle 15107 4210778
9 System I/O 5116 1926
8 User I/O 3456 1480
0 Other 83 344
7 Network 2038 2
4 Concurrency 3 1
5 Commit 7 1
1 Application 7 0
2 Configuration 0 0
Interestingly enough, User I/O is not far behind System I/O . If I/O related wait classes consistently show high wait times, this may indicate an under performing I/O subsystem or I/O bound application code. As it turned out, the server we were using for our tests had only two internal disk drives , and the I/O subsystem was heavily loaded. It was not surprising to us to see this delta information correctly identifying I/O related classes as the potential hot spots.
V$SESSION_WAIT_CLASS View
The view V$SESSION_WAIT_CLASS is similar to the V$SYSTEM_WAIT_CLASS view, but it gives session-level information for all sessions that are currently connected to the instance.
The view V$SESSION_WAIT_CLASS has the following columns:
Name Type
-------------------- ---------------
SID NUMBER
SERIAL# NUMBER
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
TOTAL_WAITS NUMBER
TIME_WAITED NUMBER
How to Use V$SESSION_WAIT_CLASS View
The V$SESSION_WAIT_CLASS view is useful when you know the SID (and maybe the SERIAL#) of the session that is connected to the instance. You can simply query this view for the SID to quickly find out what wait class has the most waits. Further drilling down using V$SESSION_EVENT view allows you to identify the wait events that may need further investigation.
If you identified a particular wait class as a potential problem by sampling the V$SYSTEM_WAIT_CLASS view as just shown, you can find out all the current sessions contributing to this wait class by querying V$SESSION_WAIT_CLASS view using the WAIT_CLASS#. Once the SID is known, you can find out the user and the SQL that is causing the waits.
V$EVENT_HISTOGRAM View
This is a very interesting and informational view in Oracle Database 10 g Release 1 when it comes to analyzing wait time per wait event over the life of the instance. It shows a histogram of the number of waits, the total wait time, and the maximum wait for each event. The bucket sizes, or the time intervals in this case, for the histograms are predefined. They cannot be changed. The bucket time intervals are from < 1 ms, < 2 ms, < 4 ms, < 8 ms, < 16 ms, and so on, increasing with the power of 2 up to >= 2 ‚ ² ‚ ² ms. The buckets will be populated accordingly and only when TIMED_STATISTICS is set to TRUE.
The view V$EVENT_HISTOGRAM has the following columns:
Name Type
-------------------- ---------------
EVENT# NUMBER
EVENT VARCHAR2(64)
WAIT_TIME_MILLI NUMBER
WAIT_COUNT NUMBER
How to Use V$EVENT_HISTOGRAM View
The event number is shown in the EVENT# column, while the event name is shown in the EVENT column. The column WAIT_TIME_MILLI defines the amount of the time the histogram bucket represents. As the name suggests, the time is in milliseconds . This is similar to how you define the range partitions with a partition key. Still confused ? We ‚ ll illustrate this with an example. Once you understand how to read this information, we are confident that you will like this view a lot.
The following simple query selects only two common wait events from this view. Event number 290 and 291 correspond to the db file sequential read and db file scattered read in our database instance. They may be different in your database instance; check V$EVENT_NAME for the correct numbers in your Oracle version.
select *
from v$event_histogram
where event# in (290,291);
EVENT# EVENT WAIT_TIME_MILLI WAIT_COUNT
---------- ------------------------- --------------- ----------
290 db file sequential read 1 5928
290 db file sequential read 2 6099
290 db file sequential read 4 509
290 db file sequential read 8 640
290 db file sequential read 16 1653
290 db file sequential read 32 2327
290 db file sequential read 64 506
290 db file sequential read 128 67
290 db file sequential read 256 20
290 db file sequential read 512 13
290 db file sequential read 1024 2
EVENT# EVENT WAIT_TIME_MILLI WAIT_COUNT
---------- ------------------------- --------------- ----------
291 db file scattered read 1 4228
291 db file scattered read 2 2230
291 db file scattered read 4 1002
291 db file scattered read 8 2875
291 db file scattered read 16 616
291 db file scattered read 32 1040
291 db file scattered read 64 655
291 db file scattered read 128 144
Let us review the preceding output for event #290, the db file sequential read . The values in column WAIT_TIME_MILLI vary from 1 to 1024, comprising 11 histogram buckets. The values in column WAIT_COUNT vary from 5928 to 2. From the first bucket, you see that there were 5928 waits of duration less than 1ms each; from the second bucket, you see that there were 6099 waits of duration more than 1ms but less than 2ms; and so on. In the last bucket, you see that there were 2 waits of duration greater than 512ms but less than 1024ms.
For event #291, there are only 8 histogram buckets because the longest wait time never exceeded 128ms.
What can you learn from this?
You can tell if there are smaller numbers of long waits or a large number of shorter waits.
For any event, the fewer buckets the better because the wait time they represent is shorter. The bucket boundaries are preset, and you cannot change their values. Also, high WAIT_COUNT numbers should be in the buckets with lower WAIT_TIME_MILLI values. If you have it the other way around, then that ‚ s the event you need to investigate further! Got it?
Another good application of this view is to monitor the SQL*Net message from client event. If the WAIT_COUNT is high for the low-end buckets, it could mean that the application is communicating a lot with the client. This may cause excessive network traffic, which could be reduced with higher ARRAYSIZE or server-side processing. On the other hand, if the WAIT_COUNT is high for the high-end buckets, it is more likely due to user lag time, for example, user think time, coffee time, and so on.
Types of Wait Events
Long before Oracle Database 10 g , DBAs have been classifying wait events into four main categories: Foreground, Background, Idle, and Non-Idle events. Foreground events are posted by sessions that have V$SESSION.TYPE = ‚USER ‚ , otherwise referred to as foreground processes. Background events are posted by sessions that have V$SESSION.TYPE= ‚BACKGROUND ‚ , otherwise known as background processes. Both the foreground and background event categories can have the same wait events. For example, you will find the latch free , direct path read, direct path write, db file sequential read, db file scattered read events, among others, posted by both foreground and background processes. Moreover, foreground and background events can further be classified into subcategories , such as I/O, latency, locks, and so on.
Idle events are normally ignored. There is not an industry standard list of common and idle wait events. Oracle Database 10 g Release 1 has 58 events listed in wait class #6, the Idle Wait Class.
Table 2-1 shows an example of some of the non-idle wait events that can be posted by foreground (F) and background (B) processes.
| db file sequential read (F, B) | db file scattered read (F, B) |
| direct path read (F, B) | direct path write (F, B) |
| db file parallel write (B) | log file parallel write (B) |
| control file parallel write (B) | write complete waits (F, B) |
| free buffer waits (F) | log buffer space (F) |
| latch free (F, B) | log file sync (F) |
| enqueue (F, B) | buffer busy waits (F, B) |
| SQL*Net more data to client (F) | SQL*Net message to client (F) |
| SQL*Net more data from client (F) | ‚ |
Table 2-2 shows some of the idle wait events defined in Oracle Database 10 g Release 1.
| PL/SQL lock timer | all events beginning with PX Deq |
| PX idle wait | SQL*Net message from client |
| SQL*Net message from dblink | dispatcher timer |
| jobq slave wait | pipe get |
| pmon timer | queue messages |
| rdbms ipc message | single-task message |
| smon timer | virtual circuit status |
| wait for activate message | wait for transaction |
| wakeup event for builder | wakeup event for preparer |
| wakeup event for reader | wakeup time manager |
| Null event | ‚ |
| Note ‚ | It is important to note that ‚“idle ‚½ does not mean that the wait can be ignored ‚ it just means that the user session is not doing work in the database instance. |
Whether or not the event SQL*Net message from client (and SQL*Net message from dblink ) should be ignored depends on how the application works. Foreground processes post this event when they wait for instructions from client processes. In other words, the foreground processes are sitting idle waiting for more work to do. For example, a user may run a short query and spend time looking at the result or go out to lunch without logging off. All the while, the foreground process faithfully waits for the user to return, posting the SQL*Net message from client event and increasing the value in the TIME_WAITED column. Therefore, the SQL*Net message from client is the most prevalent event in OLTP systems, and this is why many DBAs choose to ignore this event.
However, this event may provide proof that the bottlenecks are on the client side. Let ‚ s say a job ran for a total of 100 seconds, but every 2 seconds it waited on the SQL*Net message from client event for another 8 seconds. This shows that whenever a command hit the database, it finished in 2 seconds and the client process took 8 seconds to deal with the result before sending another command to the database. In this case, it is clear that the bulk of the processing time belongs to the client. You can show this timing information to your customers and politely state that the performance of the database instance is not a problem but that the client process needs to be reviewed. In a client/server environment, one should not ignore the SQL*Net, Net8, or Oracle Net ‚ related wait events.
You can safely ignore the Null event but you may not want to ignore the null event. No, this is not a typo. Pay attention to the case. The Null event becomes null event starting in Oracle9 i Database, and it is a major nuisance. It not so much because the case is changed; it ‚ s mainly because a bug #2843192 causes many events to be inaccurately reported as the null event in the V$SESSION_WAIT view. This is an important view for root cause analysis. You can quickly find out if this bug affects your version of Oracle. Query the V$SESSION_WAIT view in a few quick successions during active processing. If you see the null event reported in the output, you should obtain and apply the appropriate patch for the bug, or upgrade to Oracle Release 9.2.0.4. Some patch numbers are listed in Table 2-3, but you should always consult Oracle Support for the right patch, or patchset, for your platform.
| Platform | Patch Number |
|---|---|
| IBM AIX (64-bit) 5L | 3073015 |
| IBM RS/6000 AIX 64-bit | 3064001 |
| HP Tru64 UNIX | 3073015 |
| HP 9000 Series HP-UX 64bit | 3055343 |
| Sun Solaris OS (SPARC) 64-bit | 3095277 |
EAN: 2147483647
Pages: 114