【Oracle九大性能视图】之5.v$session_wait_session的等待情况及热点块

1、表结构
SQL> desc v$session_event
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
SID NUMBER
EVENT VARCHAR2(64)
TOTAL_WAITS NUMBER
TOTAL_TIMEOUTS NUMBER
TIME_WAITED NUMBER
AVERAGE_WAIT NUMBER
MAX_WAIT NUMBER
TIME_WAITED_MICRO NUMBER
EVENT_ID NUMBER
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)

SQL> desc v$session_wait
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
SID NUMBER
SEQ# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P1RAW RAW(8)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(8)
P3TEXT VARCHAR2(64)
P3 NUMBER
P3RAW RAW(8)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_TIME NUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)

查v$session_wait:

SQL> select event,count(*),p1,p2,p3 from v$session_wait group by event,p1,p2,p3;

EVENT COUNT(*) P1 P2 P3
1 rdbms ipc message 6 300 0 0
2 smon timer 1 300 0 0
3 rdbms ipc message 1 500 0 0
4 Streams AQ: waiting for time management or cleanup tasks 1 0 0 0
5 Streams AQ: qmn slave idle wait 1 0 0 0
6 rdbms ipc message 1 180000 0 0
7 SQL*Net message from client 2 1413697536 1 0
8 Streams AQ: qmn coordinator idle wait 1 0 0 0
9 jobq slave wait 1 0 0 0
10 rdbms ipc message 1 100 0 0
11 SQL*Net message from client 1 1111838976 1 0
12 pmon timer 1 300 0 0

2、检查数据库的等待事件

select event,count(*) from v$session_event group by event;
select event,count(*) from v$session_wait group by event;
v$session_wait

p1、 p2、p3告诉我们等待事件的具体含义，如果wait event是db file scattered read，p1=file_id/p2=block_id/p3=blocks，然后通过dba_extents即可确定出热点对象；如果是latch free的话，p2为闩锁号，它指向v$latch。
P1RAW,P2RAW,P3RAW列对应P1,P2,P3的十六进制值；P1TEXT,P2TEXT,P3TEXT列对应P1,P2,P3列的解释。
v$event_name 可以看到
PARAMETER1 - address
PARAMETER2 - number
PARAMETER3 - tries
关联到如下：
PARAMETER1 - 等待的LATCH地址
PARAMETER2 - latch号
PARAMETER3 - 进程试图获取lanch的次数

3、关于热点块dba_extents
表结构：
SQL> desc dba_extents;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
EXTENT_ID NUMBER
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
如果wait event是db file scattered read，p1=file_id/p2=block_id/p3=blocks，然后通过dba_extents即可确定出热点对象

先查
select sid,event,p1 as file_id, p2 as "block_id/latch", p3 as blocks,l.name
from v$session_wait sw,v$latch l
where event not like '%SQL%' and event not like '%rdbms%'
and event not like '%mon%' and sw.p2 = l.latch#(+);

-----查热点块的SQL：
--求等待事件及其热点对象

select owner, segment_name, segment_type
from dba_extents
where file_id = &file_id
and &block_id between block_id and block_id + &blocks - 1;

查结果如下：

OWNER SEGMENT_NAME SEGMENT_TYPE
1 SYS SYSTEM ROLLBACK
3、v$latch
表结构
SQL> desc v$latch
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ADDR RAW(8)
LATCH# NUMBER
LEVEL# NUMBER
NAME VARCHAR2(50)
HASH NUMBER
GETS NUMBER
MISSES NUMBER
SLEEPS NUMBER
IMMEDIATE_GETS NUMBER
IMMEDIATE_MISSES NUMBER
WAITERS_WOKEN NUMBER
WAITS_HOLDING_LATCH NUMBER
SPIN_GETS NUMBER
SLEEP1 NUMBER
SLEEP2 NUMBER
SLEEP3 NUMBER
SLEEP4 NUMBER
SLEEP5 NUMBER
SLEEP6 NUMBER
SLEEP7 NUMBER
SLEEP8 NUMBER
SLEEP9 NUMBER
SLEEP10 NUMBER
SLEEP11 NUMBER
WAIT_TIME NUMBER

如果是latch free的话，p2为闩锁号，它指向v$latch。
P1RAW,P2RAW,P3RAW列对应P1,P2,P3的十六进制值；P1TEXT,P2TEXT,P3TEXT列对应P1,P2,P3列的解释。
--求等待事件及其对应的latch
col event format a32
col name format a32
select sid,event,p1 as file_id, p2 as "block_id/latch", p3 as blocks,l.name
from v$session_wait sw,v$latch l
where event not like '%SQL%' and event not like '%rdbms%'
and event not like '%mon%' and sw.p2 = l.latch#(+);

查得的结果如下：
SID EVENT FILE_ID block_id/latch BLOCKS NAME
1 154 Streams AQ: qmn coordinator idle wait 0 0 0 event range base latch
2 153 jobq slave wait 0 0 0 event range base latch
3 150 Streams AQ: qmn slave idle wait 0 0 0 event range base latch
4 149 Streams AQ: waiting for time management or cleanup tasks 0 0 0 event range base latch

--综合以上两条sql，同时显示latch及热点对象(速度较慢)
select sw.sid,event,l.name,de.segment_name
from v$session_wait sw,v$latch l,dba_extents de
where event not like '%SQL%' and event not like '%rdbms%'
and event not like '%mon%' and sw.p2 = l.latch#(+) and sw.p1 = de.file_id(+) and p2 between de.block_id and de.block_id + de.blocks - 1;

--如果是非空闲等待事件，通过等待会话的sid可以求出该会话在执行的sql
select sql_text
from v$sqltext_with_newlines st,v$session se
where st.address=se.sql_address and st.hash_value=se.sql_hash_value
and se.sid =&wait_sid order by piece;
其中 STATE 列 的描述如下(参考 Oracle Database Reference 10g Release 1 Part Number B10755-01):
? 0 - WAITING (当前等待的 Session)
? -2 - WAITED UNKNOWN TIME (最后等待持续时间未知)
? -1 - WAITED SHORT TIME (最后的等待 <1/100 秒) ? >0 - WAITED KNOWN TIME (WAIT_TIME = 最后等待持续时间)