性能诊断与调优之V$--V$SYSTEM_WAIT_CLASS

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:

V$SYSTEM_WAIT_CLASS 保存了自实例启动以来的实例级的不同等待类别的总等待时间，列的信息如下：

SQL> desc V$SYSTEM_WAIT_CLASS
Name          Type         Nullable Default Comments 
------------- ------------ -------- ------- -------- 
WAIT_CLASS_ID NUMBER       Y                         
WAIT_CLASS#   NUMBER       Y                         
WAIT_CLASS    VARCHAR2(64) Y                         
TOTAL_WAITS   NUMBER       Y                         
TIME_WAITED   NUMBER       Y                         
 

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.

和V$SYSTEM_VEVENT一样，V$SYSTEM_WAIT_CLASS视图中的信息可以帮助我们快速的对数据库实例进行整体的性能检查，而不是注重在具体的事件上，而是整体从事件类别上的诊断，可以知道自实例启动以来的主要等待事件，该视图最多只保存12条记录，所以通过一个简单的查询通过TIME_WAITED列排序就能知道自实例启动以来的主要等待事件的类别。

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.