一． 等待事件的相关知识
1.1 等待事件主要可以分为两类：
即空闲(IDLE)等待事件和非空闲(NON-IDLE)等待事件。
1). 空闲等待事件指ORACLE正等待某种工作，在诊断和优化数据库的时候，不用过多注意这部分事件。
2). 非空闲等待事件专门针对ORACLE的活动，指数据库任务或应用运行过程中发生的等待，这些等待事件 是在调整数据库的时候需要关注与研究的。
在Oracle 10g中的等待事件有874个，11g中等待事件1118个。 我们可以通过v$event_name 视图来查看等待事件的相关信息。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
10gR2：
[oracle@aix211 ~]$sqlplus
'/as sysdba'
SQL*Plus: Release
10.2.
0.1.
0
- Production
on
Thu Jul
24
14
:
44
:
43
2014
Copyright (c)
1982
,
2005
, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release
10.2.
0.1.
0
- 64bit Production
With the Partitioning, OLAP
and
Data Mining options
SQL> desc v$event_name
Name Null? Type
----------------------------------------- -------- ----------------------------
EVENT# NUMBER
EVENT_ID NUMBER
NAME VARCHAR2(
64
)
PARAMETER1 VARCHAR2(
64
)
PARAMETER2 VARCHAR2(
64
)
PARAMETER3 VARCHAR2(
64
)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(
64
)
SQL> select count(*)
from
v$event_name;
COUNT(*)
----------
874
11gR2：
[oracle@rh6 ~]$sqlplus
'/as sysdba'
SQL*Plus: Release
11.2.
0.1.
0
Production
on
Thu Jul
24
14
:
54
:
35
2014
Copyright (c)
1982
,
2009
, Oracle. All rights reserved.
Connected to an idle instance.
14
:
55
:
04
SYS@ prod>select count(*)
from
v$event_name;
COUNT(*)
----------
1118
Elapsed:
00
:
00
:
00.0
|
1.2 查看等待事件的分类
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
14
:
55
:
18
SYS@ prod> SELECT wait_class#,
14
:
58
:
50
2
wait_class_id,
14
:
58
:
50
3
wait_class,
14
:
58
:
50
4
COUNT ( * ) AS
"count"
14
:
58
:
50
5
FROM v$event_name
14
:
58
:
50
6
GROUP BY wait_class#, wait_class_id, wait_class
14
:
58
:
50
7
ORDER BY wait_class#;
WAIT_CLASS# WAIT_CLASS_ID WAIT_CLASS count
----------- ------------- ------------------------------ ----------
0
1893977003
Other
719
1
4217450380
Application
17
2
3290255840
Configuration
24
3
4166625743
Administrative
54
4
3875070507
Concurrency
32
5
3386400367
Commit
2
6
2723168908
Idle
94
7
2000153315
Network
35
8
1740759767
User I/O
45
9
4108307767
System I/O
30
10
2396326234
Scheduler
7
11
3871361733
Cluster
50
12
644977587
Queueing
9
13
rows selected.
|
1.3 相关的视图
V$SESSION：代表数据库活动的开始，视为起源,连接断开后消失。
V$SESSION_WAIT：视图用以实时记录活动SESSION的等待情况，是当前信息,断开后消失(等待会话生命周期最后1次等待)。
V$SESSION_WAIT_HISTORY：是对V$SESSION_WAIT的简单增强，记录活动SESSION的最近10次等待。
V$SQLTEXT：当数据库出现瓶颈时，通常可以从V$SESSION_WAIT找到那些正在等待资源的SESSION，
通过SESSION的SID，联合V$SESSION和V$SQLTEXT视图就可以捕获这些SESSION正在执行的SQL语句。
V$ACTIVE_SESSION_HISTORY:是ASH的核心，用以记录活动SESSION的历史等待信息，每秒采样一次，这部分内容记录在内存中，期望值是记录一个小时的内容。
WRH#_ACTIVE_SESSION_HISTORY: 是V$ACTIVE_SESSION_HISTORY在AWR的存储地。
V$ACTIVE_SESSION_HISTORY中 的信息会被定期(每小时一次)的刷新到负载库中，并缺省保留一个星期
用于分析。
DBA_HIST_ACTIVE_SESS_HISTORY:视图是WRH#_ACTIVE_SESSION_HISTORY视图和其他几个视图的联合展现，通常通过这个视图进行历史数据的访问。
V$SYSTEM_EVENT: 由于V$SESSION记录的是动态信息，和SESSION的生命周期相关，而并不记录历史信
息，所以ORACLE提供视图V$SYSTEM_EVENT来记录数据库自启动以来所有等待事件的汇总信息。通过这个视图，用户可以迅速获得数据库运行的总体概况。
二． 常见的等待事件
2.1. Buffer busy waits
从本质上讲，这个等待事件的产生仅说明了一个会话在等待一个Buffer（数据块），但是导致这个现象的原因却有很多种。
常见的两种是：
· 当一个会话试图修改一个数据块，但这个数据块正在被另一个会话修改时。
· 当一个会话需要读取一个数据块，但这个数据块正在被另一个会话读取到内存中时。
Oracle 操作的最小单位是块（Block），即使你要修改一条记录，也需要对这条记录所在的这个数据块做操作。 当你对这个数据块做修改时，其他的会话将被阻止对这个数据块上的数据做修改（即使其他用户修改的不是当前用户修改的数据），但是可以以一致性的方式读取这个数据块（from undo）。当前的用户修改完这个数据块后，将会立即释放掉加在这个数据块上的排他锁，这样另一个会话就可以继续修改它。修改操作是一个非常短暂的时间，这种加锁的机制我们叫Latch。
当一个会话修改一个数据块时，是按照以下步骤来完成的：
· 以排他的方式获得这个数据块（Latch）
· 修改这个数据块。
· 释放Latch。
Buffer busy waits等待事件常见于数据库中存在热块的时候，当多个用户频繁地读取或者修改同样的数据块时，这个等待事件就会产生。 如果等待的时间很长，我们在AWR或者statspack 报告中就可以看到。
这个等待事件有三个参数。查看有几个参数我们可以用以下SQL:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
15
:
08
:
43
SYS@ prod>col name
for
a30
15
:
08
:
51
SYS@ prod>col PARAMETER1
for
a20
15
:
08
:
58
SYS@ prod>col PARAMETER2
for
a20
15
:
09
:
05
SYS@ prod>col PARAMETER3
for
a20
15
:
09
:
12
SYS@ prod>r
1
SELECT name,
2
parameter1,
3
parameter2,
4
parameter3
5
FROM v$event_name
6
* WHERE name =
'buffer busy waits'
NAME PARAMETER1 PARAMETER2 PARAMETER3
------------------------------ -------------------- -------------------- ----------------
buffer busy waits file# block#
class
#
Elapsed:
00
:
00
:
00.02
|
在下面的示例中，查询的方法和这个一样，所以其他事件对参数的查询将不做过多的说明。
File#: 等待访问数据块所在的文件id号。
Blocks： 等待访问的数据块号。
ID： 在10g之前，这个值表示一个等待时间的原因，10g之后则表示等待事件的类别。
Buffer Busy Waits 案例：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
|
1
)创建对象
15
:
55
:
32
SCOTT@ test1>create table t1 (id int,name varchar2(
10
));
Table created.
Elapsed:
00
:
00
:
00.13
15
:
55
:
54
SCOTT@ test1>begin
15
:
55
:
56
2
for
i
in
1.
.100
loop
15
:
56
:
09
3
insert
into
t1 values (i,
'usr'
||i);
15
:
56
:
27
4
end loop;
15
:
56
:
31
5
end;
15
:
56
:
32
6
/
PL/SQL procedure successfully completed.
Elapsed:
00
:
00
:
00.28
15
:
56
:
33
SCOTT@ test1>commit;
Commit complete.
Elapsed:
00
:
00
:
00.04
15
:
56
:
36
SCOTT@ test1>grant all
on
t1 to tom;
Grant succeeded.
Elapsed:
00
:
00
:
00.12
2
）多个事务同时访问同一个对象
scott 用户做DML操作：
15
:
58
:
18
SYS@ test1>conn scott/tiger
Connected.
15
:
58
:
22
SCOTT@ test1>begin
15
:
59
:
36
2
15
:
59
:
36
3
for
i
in
1.
.100000
loop
15
:
59
:
36
4
update t1 set name=
'scott'
where
id=
1
;
15
:
59
:
36
5
commit;
15
:
59
:
36
6
end loop;
15
:
59
:
36
7
end;
15
:
59
:
38
8
/
tom用户做DML操作：
15
:
58
:
28
SYS@ test1>conn tom/tom
Connected.
15
:
58
:
32
TOM@ test1>begin
15
:
59
:
52
2
15
:
59
:
52
3
for
i
in
1.
.100000
loop
15
:
59
:
52
4
update scott.t1 set name=
'tom'
where
id=
2
;
15
:
59
:
52
5
commit;
15
:
59
:
52
6
end loop;
15
:
59
:
52
7
end;
15
:
59
:
53
8
/
3
）查看会话产生的waits
15
:
56
:
52
SCOTT@ test1>conn /
as
sysdba
Connected.
15
:
58
:
54
SYS@ test1>select username,sid,serial#
from
v$session
where
username is
not
null
;
USERNAME SID SERIAL#
------------------------------ ---------- ----------
SYS
1
28
SCOTT
29
9
TOM
41
50
Elapsed:
00
:
00
:
00.24
16
:
12
:
48
SYS@ test1>select event,sid,p1,p2,p3
from
v$session_wait
where
sid
in
(
29
,
41
)
EVENT SID P1 P2 P3
---------------------------------------- ---------- ---------- ---------- ----------
log file switch (archiving needed)
29
0
0
0
buffer busy waits
41
4
173
1
Elapsed:
00
:
00
:
00.04
产生buffer busy waits的对象在file#为
4
，block_id为
173
上
16
:
00
:
57
SYS@ test1>select *
from
v$waitstat
where
count>
0
;
CLASS COUNT TIME
------------------ ---------- ----------
data block
67
6516
segment header
1
0
undo header
6
1
undo block
1
1
Elapsed:
00
:
00
:
00.03
data block产生大量的等待 ！
16
:
01
:
00
SYS@ test1>select sql_text
from
V$sqlarea
where
(address,hash_value)
in
(select sql_address,sql_hash_value
from
v$session
where
event like
'%buffer busy%'
);
SQL_TEXT
----------------------------------------------------------------------------------------
UPDATE SCOTT.T1 SET NAME=
'tom'
WHERE ID=
2
Elapsed:
00
:
00
:
00.44
在scott的t1表上产生了热块 ！
16
:
04
:
12
SYS@ test1>select sql_text
from
v$sqlarea
where
sql_text like
'%t1%'
;
SQL_TEXT
-------------------------------------------------------------------------------------------
begin
for
i
in
1.
.100000
loop update t1 set name=
'scott'
where
id=
1
; commit; end loop; end;
begin
for
i
in
1.
.100000
loop update scott.t1 set name=
'tom'
where
id=
2
; commit; end loop; end;
16
:
05
:
07
SYS@ test1>select sql_text
from
v$sqlarea
where
sql_text like
'%t1%'
;
SQL_TEXT
-------------------------------------------------------------------------------------------
begin
for
i
in
1.
.100000
loop update t1 set name=
'scott'
where
id=
1
; commit; end loop; end;
begin
for
i
in
1.
.100000
loop update scott.t1 set name=
'tom'
where
id=
2
; commit; end loop; end;
查看scott用户对象t1所在的块：（
168
-175
）
16
:
16
:
36
SYS@ test1>select owner,SEGMENT_NAME,SEGMENT_TYPE,FILE_ID,BLOCK_ID,BLOCKS
from
dba_extents
16
:
16
:
40
2
where
segment_name=
'T1'
and
owner=
'SCOTT'
;
OWNER SEGMENT_NAME SEGMENT_TYPE FILE_ID BLOCK_ID BLOCKS
------------------------------ -------------------- ------------------ ---------- ---------- ----------
SCOTT T1 TABLE
4
168
8
Elapsed:
00
:
00
:
00.80
|
2. Buffer latch
内存中数据块的存放位置是记录在一个hash列表（cache buffer chains）当中的。当一个会话需要访问某个数据块时，它首先要搜索这个hash 列表，从列表中获得数据块的地址，然后通过这个地址去访问需要的数据块，这个列表Oracle会使用一个latch来保护它的完整性。 当一个会话需要访问这个列表时，需要获取一个Latch，只有这样，才能保证这个列表在这个会话的浏览当中不会发生变化。
产生buffer latch的等待事件的主要原因是：
· Buffer chains太长，导致会话搜索这个列表花费的时间太长，使其他的会话处于等待状态。
· 同样的数据块被频繁访问，就是我们通常说的热块问题。
产生buffer chains太长，我们可以使用多个buffer pool的方式来创建更多的buffer chains，或者使用参数DB_BLOCK_LRU_LATCHES来增加latch的数量，以便于更多的会话可以获得latch，这两种方法可以同时使用。
这个等待事件有两个参数：
Latch addr： 会话申请的latch在SGA中的虚拟地址。
通过以下的SQL语句可以根据这个地址找到它对应的Latch名称：
select * from v$latch a,v$latchname b where
addr=latch addr -- 这里的latch addr 是你从等待事件中看到的值
and a.latch#=b.latch#;
chain#： buffer chains hash 列表中的索引值，当这个参数的值等于s 0xfffffff时，说明当前的会话正在等待一个LRU latch。
案例：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
|
16
:
56
:
47
SYS@ test1>select *
from
v$latchname
where
upper(name) like
'%BUFFER%'
;
LATCH# NAME HASH
---------- ---------------------------------------------------------------- ----------
33
SGA IO buffer pool latch
2719726273
63
IPC stats buffer allocation latch
1449990452
106
KJC global post event buffer
3098969798
145
cache buffers lru chain
3559635447
146
buffer pool
510014793
150
cache buffers chains
3563305585
151
cache buffer handles
892398878
196
media recovery process out of buffers
2731251867
197
mapped buffers lru chain
93631960
208
lock DBA buffer during media recovery
3620457631
350
virtual circuit buffers
1577520421
378
parallel query alloc buffer
291345605
416
image handles of buffered messages latch
3223585260
441
KWQMN to-be-Stopped Buffer list Latch
3064249245
476
buffer pin latch
3925519355
15
rows selected.
Elapsed:
00
:
00
:
00.02
16
:
57
:
39
SYS@ test1>desc v$latch
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
ADDR RAW(
8
)
LATCH# NUMBER
LEVEL# NUMBER
NAME VARCHAR2(
64
)
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
16
:
58
:
18
SYS@ test1>select addr,name,hash,gets,misses
from
v$latch
where
hash=
'3559635447'
;
ADDR NAME HASH GETS MISSES
---------------- ---------------------------------------------------------------- -------
00000000600188A8 cache buffers lru chain
3559635447
359628
36
Elapsed:
00
:
00
:
00.03
16
:
59
:
03
SYS@ test1>select addr,name,hash,gets,misses
from
v$latch
where
hash
in
(
'3559635447'
,
'3563305585'
,
'892398878'
);
ADDR NAME HASH GETS MISSES
---------------- ---------------------------------------------------------------- -------
00000000600188A8 cache buffers lru chain
3559635447
359652
36
000000006001A620 cache buffers chains
3563305585
43311894
21
000000006001A6C8 cache buffer handles
892398878
368
0
Elapsed:
00
:
00
:
00.03
|
====未完，待续===
