Oracle性能视图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 = 最后等待持续时间)