Oracle等待事件之01(Buffer busy waits)
2019-09-26 本文已影响0人
轻飘飘D
- 創建測試用表
[oracle@DB01 bin]$ sql xag/123456@127.0.0.1:1521/MPTEST;
SQL> set sqlformat ansiconsole;
create table test_wait(id int,name varchar2(10));
begin
for i in 1..20 loop
insert into test_wait values (i,'usr'||i);
end loop;
commit;
end;
/
SQL> select id,rowid,
dbms_rowid.rowid_object(rowid) as "object_id(数据对象号)",
dbms_rowid.rowid_relative_fno(rowid) as "file_id(相对文件号)",
dbms_rowid.rowid_block_number(rowid) as "block_id(在第几个块)",
dbms_rowid.rowid_row_number(rowid) as "num(在block中的行数)"
from test_wait;
ID ROWID object_id(数据对象号) file_id(相对文件号) block_id(在第几个块) num(在block中的行数)
1 AAAUKcAFyAAAACjAAA 82588 370 163 0
2 AAAUKcAFyAAAACjAAB 82588 370 163 1
3 AAAUKcAFyAAAACjAAC 82588 370 163 2
4 AAAUKcAFyAAAACjAAD 82588 370 163 3
5 AAAUKcAFyAAAACjAAE 82588 370 163 4
6 AAAUKcAFyAAAACjAAF 82588 370 163 5
7 AAAUKcAFyAAAACjAAG 82588 370 163 6
8 AAAUKcAFyAAAACjAAH 82588 370 163 7
9 AAAUKcAFyAAAACjAAI 82588 370 163 8
10 AAAUKcAFyAAAACjAAJ 82588 370 163 9
11 AAAUKcAFyAAAACjAAK 82588 370 163 10
12 AAAUKcAFyAAAACjAAL 82588 370 163 11
13 AAAUKcAFyAAAACjAAM 82588 370 163 12
14 AAAUKcAFyAAAACjAAN 82588 370 163 13
15 AAAUKcAFyAAAACjAAO 82588 370 163 14
16 AAAUKcAFyAAAACjAAP 82588 370 163 15
17 AAAUKcAFyAAAACjAAQ 82588 370 163 16
18 AAAUKcAFyAAAACjAAR 82588 370 163 17
19 AAAUKcAFyAAAACjAAS 82588 370 163 18
20 AAAUKcAFyAAAACjAAT 82588 370 163 19
- 測試等待事件
#會話1
#查看当前用户的sid和serial#:
SQL> select sid, serial#, status from v$session where audsid=userenv('sessionid');
SID SERIAL# STATUS
782 15440 ACTIVE
begin
for i in 1..5000000 loop
update test_wait set name='session 1' where id=1;
commit;
end loop;
end;
/
#新開會話2
[oracle@DB01 XAG]$ sql xag/123456@127.0.0.1:1521/MPTEST;
SQL> set sqlformat ansiconsole;
#查看当前用户的sid和serial#:
SQL> select sid, serial#, status from v$session where audsid=userenv('sessionid');
SID SERIAL# STATUS
1199 5993 ACTIVE
begin
for i in 1..5000000 loop
update test_wait set name='session 2' where id=2;
commit;
end loop;
end;
/
#新開會話3
[oracle@DB01 XAG]$ sql xag/123456@127.0.0.1:1521/MPTEST;
SQL> set sqlformat ansiconsole;
#查看当前用户的sid和serial#:
SQL> select sid, serial#, status from v$session where audsid=userenv('sessionid');
SID SERIAL# STATUS
113 22734 ACTIVE
#查看全部會話
SQL> select username,sid,serial# from v$session where username is not null;
USERNAME SID SERIAL#
SYS 3 10073
XAG 113 22734
XAG 782 15440
XAG 1199 5993
#查詢等待事件
SQL> select event,sid,p1,p2,p3 from v$session_wait where sid in (782,1199) and "WAIT_CLASS#"!=6;
EVENT SID P1 P2 P3
buffer busy waits 1199 370 163 1
SQL> SELECT name,parameter1,parameter2,parameter3 FROM v$event_name WHERE name='buffer busy waits';
NAME PARAMETER1 PARAMETER2 PARAMETER3
buffer busy waits file# block# class#
产生buffer busy waits的对象在file#为370，block_id为163上
#data block产生大量的等待 （8311厘秒=83秒）
SQL> select * from v$waitstat where count>0;
CLASS COUNT TIME CON_ID
data block 351448 8311 7
segment header 29 0 7
1st level bmb 5 0 7
3rd level bmb 37 1 7
file header block 10 158 7
undo header 57533 92 7
undo block 415 35 7
#在test_wait 表上产生了热块
SQL> 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
begin for i in 1..5000000 loop update test_wait set name='session 1' where id=1; commit; end loop; end;
begin for i in 1..5000000 loop update test_wait set name='session 2' where id=2; commit; end loop; end;
SQL> select sql_text from v$sqlarea where sql_text like '%test_wait%';
SQL_TEXT
begin for i in 1..5000000 loop update test_wait set name='session 1' where id=1; commit; end loop; end;
begin for i in 1..5000000 loop update test_wait set name='session 2' where id=2; commit; end loop; end;
#查看XAG用户对象TEST_WAIT所在的块：（160-167） 包含上面的 163塊
SQL> select owner,SEGMENT_NAME,SEGMENT_TYPE,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where segment_name='TEST_WAIT';
OWNER SEGMENT_NAME SEGMENT_TYPE FILE_ID BLOCK_ID BLOCKS
XAG TEST_WAIT TABLE 370 160 8
