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/24558279/viewspace-756377/，如需轉載，請註明出處，否則將追究法律責任。