1、 v$event_name 查看所有等待事件
SQL> SELECT wait_class#, wait_class_id, wait_class, COUNT (*) AS "count" 
  2   FROM v$event_name  GROUP BY wait_class#, wait_class_id, wait_class
  3   ORDER BY wait_class#;


WAIT_CLASS# WAIT_CLASS_ID WAIT_CLASS      count
----------- ------------- ---------- ----------
          0    1893977003 Other             590
          1    4217450380 Applicatio         12
                          n

          2    3290255840 Configurat         23
                          ion

          3    4166625743 Administra         46
                          tive

          4    3875070507 Concurrenc         24
          5    3386400367 Commit              1
          6    2723168908 Idle               62       ---空闲等待
          7    2000153315 Network            26
          8    1740759767 User I/O           17
          9    4108307767 System I/O         24
         10    2396326234 Scheduler           2
         11    3871361733 Cluster            47

2  V$session_wait获取各进程的等待事件（这些信息是随着session消失而消失的）
SQL> select sid,event,p1,p1text from v$session_wait;
SID EVENT P1 P1TEXT
---------- ------------------------------ ---------- ----------------------------------------------------------------
124 latch free 1.6144E+10 address
1 pmon timer 300 duration
2 rdbms ipc message 300 timeout
-。。。。
140 buffer busy waits 17 file#
66 buffer busy waits 17 file#
10 db file sequential read 17 file#
18 db file sequential read 17 file#
54 db file sequential read 17 file#
49 db file sequential read 17 file#
48 db file sequential read 17 file#
46 db file sequential read 17 file#
45 db file sequential read 17 file#
。。。
244 rows selected


3 通过相关脚本捕获sql
SELECT sql_text
 FROM v$sqltext a
 WHERE a.hash_value = (SELECT sql_hash_value
 FROM v$session b
 WHERE b.SID = '&sid')
ORDER BY piece ASC
/

SQL> @/home/oracle/getsql
Enter value for sid: 18

SQL_TEXT
----------------------------------------------------------------
select i.vc2title,i.numinfoguid from hs_info i where i.intenab
ledflag = 1 and i.intpublishstate = 1 and i.datpublishdate <=
sysdate and i.numcatalogguid = 2047 order by i.datpublishdate d
esc, i.numorder desc

4 分析sql的执行计划并优化





总体思路：通过v$session_wait获得sid号；
              通过v$session和v$sqltext获得对应的sid号所对应的sql语句；
              分析sql语句并优化；
              再次检查v$session_wait