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
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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29096438/viewspace-1464624/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29096438/viewspace-1464624/