【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 = 最後等待持續時間)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23577591/viewspace-687331/，如需轉載，請註明出處，否則將追究法律責任。