--查看一个sql的等待具体的过程，就是执行这个sql等待了什么
--v$active_session_history 这个视图告诉我们全过程

--开始一个session
SQL> set timing on
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YYYYMMDDHH24:MI:S
----------------------------------
20100824 11:26:16

已用时间:  00: 00: 00.10
SQL> select * from mystat;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       142          0          1

已用时间:  00: 00: 00.88

先记录session以前的信息看看
SQL> col event for a30
SQL> col wait_class for a20
SQL> SELECT * FROM V$SESSION_WAIT_CLASS WHERE SID=142;
 
       SID    SERIAL# WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS           TOTAL_WAITS TIME_WAITED
---------- ---------- ------------- ----------- -------------------- ----------- -----------
       142         23    4217450380           1 Application                    6           0
       142         23    3875070507           4 Concurrency                    2           0
       142         23    2723168908           6 Idle                          81      435826
       142         23    2000153315           7 Network                       82           0
       142         23    1740759767           8 User I/O                  109726       17163
SQL> select to_char(sample_time,'yyyymmdd hh24:mi:ss.ffff') s_time,session_id,sql_id,session_state,event,wait_class,wait_time,time_waited from v$active_session_history
  2  where session_id=142 and sample_time>to_date('20100824 11:26:16','yyyymmdd hh24:mi:ss') order by sample_time;
 
S_TIME                               SESSION_ID SQL_ID        SESSION_STATE EVENT                          WAIT_CLASS            WAIT_TIME TIME_WAITED
------------------------------------ ---------- ------------- ------------- ------------------------------ -------------------- ---------- -----------
20100824 11:26:20.790790                    142 3fpk43uzyc0vv WAITING       db file sequential read        User I/O                      0       24273
SQL> select session_state,event,wait_class,sum(wait_time),sum(time_waited) from v$active_session_history
  2  where session_id=142 and sample_time>to_date('20100824 11:26:16','yyyymmdd hh24:mi:ss')  group by session_state,event,wait_class;
 
SESSION_STATE EVENT                          WAIT_CLASS           SUM(WAIT_TIME) SUM(TIME_WAITED)
------------- ------------------------------ -------------------- -------------- ----------------
WAITING       db file sequential read        User I/O                          0            24273
SQL> select session_state,event,sum(wait_time),sum(time_waited) from v$active_session_history where session_id=142 group by session_state,event;
 
SESSION_STATE EVENT                          SUM(WAIT_TIME) SUM(TIME_WAITED)
------------- ------------------------------ -------------- ----------------
WAITING       db file sequential read                     0            24273
WAITING       db file scattered read                      0          5212823
ON CPU                                              7325447                0

--session1 执行一个比较大的查询
SQL> select count(*) from a;

  COUNT(*)
----------
  75497474

已用时间:  00: 00: 29.81

--重新查看sql执行的过程
SQL> col event for a30
SQL> col wait_class for a20
SQL> SELECT * FROM V$SESSION_WAIT_CLASS WHERE SID=142;
 
       SID    SERIAL# WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS           TOTAL_WAITS TIME_WAITED
---------- ---------- ------------- ----------- -------------------- ----------- -----------
       142         23    4217450380           1 Application                    6           0
       142         23    3875070507           4 Concurrency                    2           0
       142         23    2723168908           6 Idle                          83      440403
       142         23    2000153315           7 Network                       84           0
       142         23    1740759767           8 User I/O                  124110       19788
SQL> select to_char(sample_time,'yyyymmdd hh24:mi:ss.ffff') s_time,session_id,sql_id,session_state,event,wait_class,wait_time,time_waited from v$active_session_history
  2  where session_id=142 and sample_time>to_date('20100824 11:26:16','yyyymmdd hh24:mi:ss') order by sample_time;
 
S_TIME                               SESSION_ID SQL_ID        SESSION_STATE EVENT                          WAIT_CLASS            WAIT_TIME TIME_WAITED
------------------------------------ ---------- ------------- ------------- ------------------------------ -------------------- ---------- -----------
20100824 11:26:20.790790                    142 3fpk43uzyc0vv WAITING       db file sequential read        User I/O                      0       24273
20100824 11:27:07.596596                    142 68bgnf5vcx08y ON CPU                                                                 92213           0
20100824 11:27:08.674674                    142 68bgnf5vcx08y WAITING       db file scattered read         User I/O                      0         828
20100824 11:27:09.929929                    142 68bgnf5vcx08y WAITING       db file scattered read         User I/O                      0         847
20100824 11:27:11.179179                    142 68bgnf5vcx08y WAITING       db file scattered read         User I/O                      0      248439
20100824 11:27:12.279279                    142 68bgnf5vcx08y WAITING       db file scattered read         User I/O                      0      100467
20100824 11:27:13.403403                    142 68bgnf5vcx08y WAITING       db file scattered read         User I/O                      0         822
20100824 11:27:14.495495                    142 68bgnf5vcx08y WAITING       db file scattered read         User I/O                      0       90926
20100824 11:27:15.614614                    142 68bgnf5vcx08y ON CPU                                                                116397           0
20100824 11:27:16.709709                    142 68bgnf5vcx08y WAITING       db file scattered read         User I/O                      0       95088
20100824 11:27:17.795795                    142 68bgnf5vcx08y ON CPU                                                                 84193           0
20100824 11:27:18.901901                    142 68bgnf5vcx08y WAITING       db file scattered read         User I/O                      0         863
20100824 11:27:19.987987                    142 68bgnf5vcx08y WAITING       db file scattered read         User I/O                      0         713
20100824 11:27:21.131131                    142 68bgnf5vcx08y ON CPU                                                                143090           0
20100824 11:27:22.244244                    142 68bgnf5vcx08y WAITING       db file scattered read         User I/O                      0         706
20100824 11:27:23.312312                    142 68bgnf5vcx08y ON CPU                                                                 67502           0
20100824 11:27:24.421421                    142 68bgnf5vcx08y WAITING       db file scattered read         User I/O                      0      112388
20100824 11:27:25.563563                    142 68bgnf5vcx08y WAITING       db file scattered read         User I/O                      0         828
20100824 11:27:26.685685                    142 68bgnf5vcx08y WAITING       db file scattered read         User I/O                      0         828
20100824 11:27:27.801801                    142 68bgnf5vcx08y ON CPU                                                                115872           0
20100824 11:27:28.898898                    142 68bgnf5vcx08y WAITING       db file scattered read         User I/O                      0        1294
20100824 11:27:30.018018                    142 68bgnf5vcx08y WAITING       db file scattered read         User I/O                      0         823
20100824 11:27:31.151151                    142 68bgnf5vcx08y ON CPU                                                                131627           0
20100824 11:27:32.273273                    142 68bgnf5vcx08y ON CPU                                                                118813           0
20100824 11:27:33.378378                    142 68bgnf5vcx08y WAITING       db file scattered read         User I/O                      0       35661
20100824 11:27:34.450450                    142 68bgnf5vcx08y WAITING       db file scattered read         User I/O                      0       10474
20100824 11:27:35.532532                    142 68bgnf5vcx08y WAITING       db file scattered read         User I/O                      0       76911
20100824 11:27:36.669669                    142 68bgnf5vcx08y WAITING       db file scattered read         User I/O                      0      138514
 
28 rows selected
SQL> select session_state,event,wait_class,sum(wait_time),sum(time_waited) from v$active_session_history
  2  where session_id=142 and sample_time>to_date('20100824 11:26:16','yyyymmdd hh24:mi:ss')  group by session_state,event,wait_class;
 
SESSION_STATE EVENT                          WAIT_CLASS           SUM(WAIT_TIME) SUM(TIME_WAITED)
------------- ------------------------------ -------------------- -------------- ----------------
WAITING       db file scattered read         User I/O                          0           917420
ON CPU                                                                    869707                0
WAITING       db file sequential read        User I/O                          0            24273
SQL> select session_state,event,sum(wait_time),sum(time_waited) from v$active_session_history where session_id=142 group by session_state,event;
 
SESSION_STATE EVENT                          SUM(WAIT_TIME) SUM(TIME_WAITED)
------------- ------------------------------ -------------- ----------------
WAITING       db file sequential read                     0            24273
WAITING       db file scattered read                      0          6130243
ON CPU                                              8195154                0
SQL> select t.SQL_TEXT,t.EXECUTIONS,t.ELAPSED_TIME,t.CPU_TIME,t.USER_IO_WAIT_TIME,APPLICATION_WAIT_TIME,CONCURRENCY_WAIT_TIME from v$sqlarea t where sql_text='select count(*) from a';
 
SQL_TEXT                                                                         EXECUTIONS ELAPSED_TIME   CPU_TIME USER_IO_WAIT_TIME APPLICATION_WAIT_TIME CONCURRENCY_WAIT_TIME
-------------------------------------------------------------------------------- ---------- ------------ ---------- ----------------- --------------------- ---------------------
select count(*) from a                                                                    1     29776572    4867278          26251098                     0                     0
 

我们看到这个
select count(*) from a  执行过程中
有多次的db file scattered read等待和cpu的等待
其中cpu等待时间为 869707 ，而User I/O 等待时间为 917420
而从v$sqlarea 厘米看到cpu消耗的总时间 为4867278，user  I/O 消耗的总时间为 26251098
从v$active_session_history里看到 User I/O  消耗的总时间为 (19788-171630)/100*1000000= 26250000，两者越相等

 
 
 
备注：
V$SESSION SECONDS_IN_WAIT(秒，每三秒更新一次）
V$SESSION wait_time（微妙,1/1,000,000)
V$SESSION_WAIT_CLASS  TIME_WAITED(厘秒，100分之一秒)，是整个执行的时间，而非等待的时间，比如说user i/o 读取io花了这么多时间，而非读io等待了多少时间
cpu记录的是微妙级别的
v$active_session_history wait_time  微妙
v$active_session_history time_waited 微妙