Long before Oracle Database 10g, 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 10g Release 1 has 58 events listed in wait class #6, the Idle Wait Class.
Table 1 shows an example of some of the non-idle wait events that can be posted by foreground (F) and background (B) processes.
Table 2 shows some of the idle wait events defined in Oracle Database 10g Release 1.
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.
TABLE 1. Non-Idle Wait Events (Not a Complete List)
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.
TABLE 2. Idle Wait Events (Not a Complete List)
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 Oracle9i 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 3, but you should always consult Oracle Support for the right patch, or patchset, for your platform.
TABLE 3. Patch Numbers for Oracle9i Database for Bug #2843192.
Leave a Reply