> 데이터 베이스 > MySQL 튜토리얼 > 深入解析bufferbusywaits

深入解析bufferbusywaits

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
풀어 주다: 2016-06-07 16:12:28
원래의
1239명이 탐색했습니다.

在写一个培训ppt的时候,为了深入理解buffe busy waits这个等待事件,做了一个仔细的测试,对大家也有帮助,经过测试,发现我个人以前的认识都有一点问题。大家一起探讨! 1. 创建测试表 www.killdb.comconn roger/rogerConnected.www.killdb.comcreate tabl

在写一个培训ppt的时候,为了深入理解buffe busy waits这个等待事件,做了一个仔细的测试,对大家也有帮助,经过测试,发现我个人以前的认识都有一点问题。大家一起探讨!

1. 创建测试表

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

www.killdb.com>conn roger/roger

Connected.

www.killdb.com>create table t_buffer_busy_waits as select * from dba_objects where rownum < 10000;

Table created.

www.killdb.com> create index t on t_buffer_busy_waits(object_id);

Index created.

www.killdb.com>select dbms_rowid.rowid_relative_fno(rowid) fn#,

2         dbms_rowid.rowid_block_number(rowid) blk#,

3         object_id

4    from t_buffer_busy_waits

5   where object_id = 100

6  /

FN#       BLK#  OBJECT_ID

---------- ---------- ----------

5      28909        100

www.killdb.com>select dbms_rowid.rowid_relative_fno(rowid) fn#,

2         dbms_rowid.rowid_block_number(rowid) blk#,

3         object_id

4    from t_buffer_busy_waits

5   where object_id = 101

6  /

FN#       BLK#  OBJECT_ID

---------- ---------- ----------

5      28909        101

www.killdb.com>c/101/99

5*  where object_id = 99

www.killdb.com>/

FN#       BLK#  OBJECT_ID

---------- ---------- ----------

5      28909         99

www.killdb.com>c/99/111

5*  where object_id = 111

www.killdb.com>/

FN#       BLK#  OBJECT_ID

---------- ---------- ----------

5      28909        111

로그인 후 복사

注意,我这里就拿这几条在同一个block内的数据,来进行测试模拟 2. 模拟高并发读取 模拟高并发读取的测试过程中,我同时开了3个窗口,进行测试,另外还开了第4个窗口进行检测event,如下:

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

--session 1

www.killdb.com>select sid from v$Mystat where rownum < 2;

SID

----------

523

www.killdb.com>declare

2    c number;

3  begin

4    for i in 1 .. 1000000 loop

5      select count(*) into c from t_buffer_busy_waits where object_id = 99;

6    end loop;

end;

8  /

PL/SQL procedure successfully completed.

--session 2

www.killdb.com>select sid from v$Mystat where rownum < 2;

SID

----------

534

www.killdb.com>www.killdb.com>declare

2    c number;

3  begin

4    for i in 1 .. 1000000 loop

5      select count(*) into c from t_buffer_busy_waits where object_id = 100;

6    end loop;

end;

8  /

PL/SQL procedure successfully completed.

--session 3

www.killdb.com>select sid from v$mystat where rownum < 2;

SID

----------

520

www.killdb.com>declare

2    c number;

3  begin

4    for i in 1 .. 1000000 loop

5      select count(*) into c from t_buffer_busy_waits where object_id = 111;

6    end loop;

end;

8  /

PL/SQL procedure successfully completed.

www.killdb.com>

로그인 후 복사

通过第4个会话窗口的检测,我发现,并没有buffer busy waits等待出现,但是出现了大家非常熟悉的latch:cache buffer chains.
监控会话(session 4):

1

2

3

4

5

6

7

8

9

10

ww.killdb.com>select event,count(1) from v$session where wait_class#<>6 group by event;

EVENT                                                              COUNT(1)

---------------------------------------------------------------- ----------

SQL*Net message to client                                                 1

latch: cache buffers chains                                               1

www.killdb.com>/

EVENT                                                              COUNT(1)

---------------------------------------------------------------- ----------

SQL*Net message to client                                                 1

latch: cache buffers chains                                               1

로그인 후 복사

从第一个测试来看,并发读取实际上并不会产生buffer busy waits。
3. 模拟2个并发会话,分别为读与写

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

--session 1

www.killdb.com>declare

2    c number;

3  begin

4    for i in 1 .. 1000000 loop

5      select count(*) into c from t_buffer_busy_waits where object_id = 99;

6    end loop;

end;

8  /

PL/SQL procedure successfully completed.

www.killdb.com>

---session 2

www.killdb.com>declare

2    c number;

3  begin

4    for i in 1 .. 1000000 loop

5      update  t_buffer_busy_waits set object_id=111 where object_id=111;

6    end loop;

end;

8  /

declare

*

ERROR at line 1:

ORA-30036: unable to extend segment by 8 in undo tablespace &#39;UNDOTBS2&#39;

ORA-06512: at line 5

로그인 후 복사

大家注意,这里测试的时候必须使用位于同一个数据块内的行,前面我们已经知道object_id=99和111的这2条数据是在
同一个数据块内(实际上,object_id 从99到111都是在同一块内,不用说明了吧?)
ok,下面我们来看下第3个监控会话的信息是什么样的?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

---session 3

www.killdb.com>/

EVENT                                                              COUNT(1)

---------------------------------------------------------------- ----------

SQL*Net message to client                                                 1

control file sequential read                                              1

log file switch completion                                                1

。。。。。

EVENT                                                              COUNT(1)

---------------------------------------------------------------- ----------

SQL*Net message to client                                                 1

latch: cache buffers chains                                               1

log file switch completion                                                1

www.killdb.com>/

EVENT                                                              COUNT(1)

---------------------------------------------------------------- ----------

SQL*Net message to client                                                 1

latch: cache buffers chains                                               2

。。。。。。

www.killdb.com>/

EVENT                                                              COUNT(1)

---------------------------------------------------------------- ----------

SQL*Net message to client                                                 1

로그인 후 복사

比较奇怪,居然没有出现应该出现的buffer busy waits等待事件。难道读和写不会出现这个等待吗 ?
我们先保留这个疑问,继续测试写和写的并发是否会产生buffer busy waits。
4. 模拟2个会话并发同时写

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

---Session 1

www.killdb.com>declare

2    c number;

3  begin

4    for i in 1 .. 500000 loop

5      update  t_buffer_busy_waits set object_id=99 where object_id=99;

6    end loop;

end;

8  /

PL/SQL procedure successfully completed.

--Session 2

www.killdb.com>declare

2    c number;

3  begin

4    for i in 1 .. 500000 loop

5      update  t_buffer_busy_waits set object_id=111 where object_id=111;

6    end loop;

end;

8  /

PL/SQL procedure successfully completed.

로그인 후 복사

ok,下面我们来看下第3个监控会话的结果:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

www.killdb.com>l

1* select event,count(1) from v$session where wait_class#<>6 group by event

www.killdb.com>/

EVENT                                                              COUNT(1)

---------------------------------------------------------------- ----------

SQL*Net message to client                                                 1

buffer busy waits                                                         1

latch: cache buffers chains                                               1

www.killdb.com>/

EVENT                                                              COUNT(1)

---------------------------------------------------------------- ----------

SQL*Net message to client                                                 1

buffer busy waits                                                         1

latch: cache buffers chains                                               1

www.killdb.com>select sid,username,p1,p2,p3,event from v$session where event like &#39;buffer%&#39;;

SID USERNAME           P1         P2         P3 EVENT

---------- ---------- ---------- ---------- ---------- ----------------------------

523 ROGER               5      28909          1 buffer busy waits

로그인 후 복사

ok,终于出现了我们希望的结果了,这个buffer busy waits来的不容易。这里我们需要说明一点,从oracle 10.1版本开始,
这里的p3 不再是像之前的数据库版本中那样,用来表示buffer busy waits的reason了,这里表示的是所等待的block的数据块
类型。很明显,这里的p3为1,那么也就是说block type 为1,表示data block,这一点不用多说了吧!
大家看Oracle AWR,我想也经常看到buffer waits,其实也会出现在其他的block 类型上,不仅仅是data block。
只不过数据块的争用更为常见一些。如果你查询文档你会发现,有下面的一些block type类型。

1

2

3

4

5

6

1 data block        7 extent map    13 file header block

2 sort block        8 1st level bmb 14 unused

3 save undo block   9 2nd level bmb 15+2*x undo header block(x=usn#)

4 segment header   10 3rd level bmb 16+2*x undo block(x=usb#)

5 save undo header 11 bitmap block

6 free list        12 bitmap index block

로그인 후 복사

既然有可能是其他类型的block也会出现,那么按理说读和写是不兼容的,也应该出现,难道我们的测试有问题?
可能是并发不够,这里我再次测试了第2个测试:

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

Session 1:

www.killdb.com>declare

2    c number;

3  begin

4    for i in 1 .. 500000 loop

5      update  t_buffer_busy_waits set object_id=105 where object_id=105;

6    end loop;

end;

8  /

session 2:

www.killdb.com>declare

2    c number;

3  begin

4    for i in 1 .. 500000 loop

5      update  t_buffer_busy_waits set object_id=106 where object_id=106;

6    end loop;

end;

8  /

session 3:

www.killdb.com>declare

2    c number;

3  begin

4    for i in 1 .. 500000 loop

5      update  t_buffer_busy_waits set object_id=107 where object_id=107;

6    end loop;

end;

8  /

Session 4:

www.killdb.com>declare

2    c number;

3  begin

4    for i in 1 .. 500000 loop

5      update  t_buffer_busy_waits set object_id=108 where object_id=108;

6    end loop;

end;

8  /

Session 5:

www.killdb.com>declare

2    c number;

3  begin

4    for i in 1 .. 1000000 loop

5      select count(*) into c from t_buffer_busy_waits where object_id =109;

6    end loop;

end;

8  /

로그인 후 복사

我这里同时模拟了5个会话进行操作,其中4个是update,一个进行select查询,注意,这里重复操作的5条数据,其实都是在同一个数据块中。
下面我们来看下第6个监控会话的结果是什么呢?

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

session 6:

www.killdb.com>select sid,username,p1,p2,p3,event from v$session where event like &#39;buffer%&#39;;

SID USERNAME           P1         P2         P3 EVENT

---------- ---------- ---------- ---------- ---------- ----------------------------------------

521 ROGER               2       5657         23 buffer busy waits

www.killdb.com>/

SID USERNAME           P1         P2         P3 EVENT

---------- ---------- ---------- ---------- ---------- ----------------------------------------

521 ROGER               2       5657         23 buffer busy waits

www.killdb.com>/

SID USERNAME           P1         P2         P3 EVENT

---------- ---------- ---------- ---------- ---------- ----------------------------------------

521 ROGER               2       5657         23 buffer busy waits

www.killdb.com>/

SID USERNAME           P1         P2         P3 EVENT

---------- ---------- ---------- ---------- ---------- ----------------------------------------

521 ROGER               2       5657         23 buffer busy waits

w.killdb.com>select sid,username,p1,p2,p3,event from v$session where event like &#39;buffer%&#39;;

SID USERNAME           P1         P2         P3 EVENT

---------- ---------- ---------- ---------- ---------- ----------------------------------------

534 ROGER               5      28909          1 buffer busy waits

www.killdb.com>/

SID USERNAME           P1         P2         P3 EVENT

---------- ---------- ---------- ---------- ---------- ----------------------------------------

521 ROGER               5      28909          1 buffer busy waits

523 ROGER               5      29037          1 buffer busy waits

524 ROGER               5      29037          1 buffer busy waits

537 ROGER               5      29037          1 buffer busy waits

www.killdb.com>/

SID USERNAME           P1         P2         P3 EVENT

---------- ---------- ---------- ---------- ---------- ----------------------------------------

521 ROGER               5      28909          1 buffer busy waits

523 ROGER               5      29037          1 buffer busy waits

524 ROGER               5      29037          1 buffer busy waits

537 ROGER               5      29037          1 buffer busy waits

www.killdb.com>/

SID USERNAME           P1         P2         P3 EVENT

---------- ---------- ---------- ---------- ---------- ----------------------------------------

520 ROGER               5      29037          1 buffer busy waits

521 ROGER               5      28909          1 buffer busy waits

534 ROGER               5      29037          1 buffer busy waits

www.killdb.com>/

SID USERNAME           P1         P2         P3 EVENT

---------- ---------- ---------- ---------- ---------- ----------------------------------------

520 ROGER               5      29037          1 buffer busy waits

521 ROGER               5      28909          1 buffer busy waits

534 ROGER               5      29037          1 buffer busy waits

로그인 후 복사

我们可以看到,不仅仅是数据块,这里undo 也出现的buffer busy waits,很明显,这里的file 2是undo datafile。
因此,下面我们来个简单的总结:
1) Buffer busy waits等待的本质是因为写的缘故出现争用.
2) Oracle里面写写是不兼容的,写和读也是不兼容的,有可能出现buffer busy waits等待.
3) 实际上读写并发容易产生回滚段的争用,等待事件也是buffer busy waits.

본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
인기 튜토리얼
더>
최신 다운로드
더>
웹 효과
웹사이트 소스 코드
웹사이트 자료
프론트엔드 템플릿