- 創(chuàng)建測試用表
[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(數(shù)據(jù)對象號(hào))",
dbms_rowid.rowid_relative_fno(rowid) as "file_id(相對文件號(hào))",
dbms_rowid.rowid_block_number(rowid) as "block_id(在第幾個(gè)塊)",
dbms_rowid.rowid_row_number(rowid) as "num(在block中的行數(shù))"
from test_wait;
ID ROWID object_id(數(shù)據(jù)對象號(hào)) file_id(相對文件號(hào)) block_id(在第幾個(gè)塊) num(在block中的行數(shù))
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
- 測試等待事件
#會(huì)話1
#查看當(dāng)前用戶的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;
/
#新開會(huì)話2
[oracle@DB01 XAG]$ sql xag/123456@127.0.0.1:1521/MPTEST;
SQL> set sqlformat ansiconsole;
#查看當(dāng)前用戶的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;
/
#新開會(huì)話3
[oracle@DB01 XAG]$ sql xag/123456@127.0.0.1:1521/MPTEST;
SQL> set sqlformat ansiconsole;
#查看當(dāng)前用戶的sid和serial#:
SQL> select sid, serial#, status from v$session where audsid=userenv('sessionid');
SID SERIAL# STATUS
113 22734 ACTIVE
#查看全部會(huì)話
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#
產(chǎn)生buffer busy waits的對象在file#為370冯遂，block_id為163上
#data block產(chǎn)生大量的等待 （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 表上產(chǎn)生了熱塊
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
