等待事件_buffer_busy_waits_and_read_by_other_session(2)
3,select/update引起的read by other session
drop table test;
create table test(id char(1000));
insert into test
select /*+ append */' ' from dual connect by level<=1000000;
commit;
SQL> SELECT segment_name, segment_type, bytes / 1024 / 1024
2 FROM user_segments
3 WHERE segment_name = 'TEST';
SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024
------------------------------ --------------- ---------------
TEST TABLE 1152
create or replace procedure do_update is
begin
update test set id=1;
commit;
end;
/
create or replace procedure do_select is
l_cursor sys_refcursor;
l_value number;
begin
for x in (select t1.id id1,t2.id id2
from test t1,test t2
where rownum<500000) loop
null;
end loop;
end;
/
set serveroutput on
exec system_pkg.get_snap;
declare
l_job number;
begin
dbms_job.submit(l_job,'do_update;');
for x in 1..20 loop
dbms_job.submit(l_job,'do_select;');
end loop;
--commit;
end;
/
commit;
exec do_select;
exec system_pkg.rpt_stat_event(0);
==>
Last sampid:8299638
----system stats---
Name Value
session connect time ############
process last non-idle time ############
physical read total bytes 733,859,840
physical read bytes 586,833,920
physical write total bytes 429,508,608
redo size 171,708,304
physical write bytes 106,471,424
undo change vector size 83,481,096
session pga memory 39,689,872
session pga memory max 36,527,116
table scan rows gotten 10,680,311
session uga memory max 5,350,896
session uga memory 4,498,032
session logical reads 1,708,119
consistent gets from cache 1,611,636
consistent gets 1,611,635
table scan blocks gotten 1,525,551
no work - consistent read gets 1,516,069
Cached Commit SCN referenced 1,515,010
redo blocks written 336,627
db block changes 167,182
recursive calls 109,365
free buffer requested 103,641
db block gets from cache 96,826
db block gets 96,818
consistent gets - examination 82,605
redo entries 82,393
consistent changes 74,695
data blocks consistent reads - 74,668
physical reads 71,635
physical reads cache 71,615
physical reads cache prefetch 67,055
buffer is pinned count 65,659
DB time 65,650
user I/O wait time 40,011
calls to kcmgas 32,061
redo wastage 32,008
physical writes 12,997
physical writes from cache 12,977
DBWR thread checkpoint buffers 12,977
DBWR checkpoint buffers writte 12,977
physical writes non checkpoint 12,959
physical write IO requests 12,165
switch current to new buffer 10,685
CR blocks created 10,676
redo ordering marks 10,626
CPU used by this session 9,779
recursive cpu usage 9,294
free buffer inspected 8,446
cleanout - number of ktugct ca 6,842
immediate (CR) block cleanout 6,840
cleanouts and rollbacks - cons 6,826
active txn count during cleano 6,826
DBWR undo block writes 6,444
workarea memory allocated 6,001
physical read total IO request 4,971
physical read total multi bloc 4,651
physical read IO requests 4,580
concurrency wait time 4,509
rollbacks only - consistent re 3,844
sorts (rows) 3,518
physical write total IO reques 3,488
bytes received via SQL*Net fro 3,018
calls to get snapshot scn: kcm 1,960
buffer is not pinned count 1,885
bytes sent via SQL*Net to clie 1,656
shared hash latch upgrades - n 1,602
index scans kdiixs1 1,570
change write time 1,542
sorts (memory) 1,338
redo write time 1,308
execute count 1,171
enqueue requests 1,144
enqueue releases 1,114
parse count (total) 1,066
opened cursors cumulative 1,016
workarea executions - optimal 853
session cursor cache hits 750
table fetch by rowid 637
CPU used when call started 597
physical write total multi blo 528
redo log space wait time 321
index fetch by key 209
session cursor cache count 178
messages sent 168
messages received 167
pinned buffers inspected 151
redo writer latching time 138
table scans (short tables) 130
redo writes 122
background timeouts 113
commit cleanouts 83
commit cleanouts successfully 79
redo buffer allocation retries 68
user calls 65
rows fetched via callback 63
table scans (long tables) 46
cluster key scan block gets 43
cluster key scans 43
user commits 36
enqueue conversions 29
commit txn count during cleano 24
Commit SCN cached 21
redo synch writes 20
physical reads direct 20
physical writes direct 20
redo log space requests 20
logons cumulative 20
enqueue timeouts 20
parse count (hard) 18
calls to kcmgcs 17
cursor authentications 16
parse time cpu 15
parse time elapsed 14
cleanouts only - consistent re 14
SQL*Net roundtrips to/from cli 13
DBWR transaction table writes 10
deferred (CURRENT) block clean 5
heap block compress 5
immediate (CURRENT) block clea 4
background checkpoints started 4
write clones created in foregr 4
DBWR checkpoints 4
enqueue waits 2
commit cleanout failures: bloc 2
background checkpoints complet 2
commit cleanout failures: cann 2
redo synch time 1
logons current 1
----system events---
Wait_Class Event Total_waits Time_waited
Commit log file sync 22 281
Concurrency os thread startup 22 521
Concurrency buffer busy waits 81 630
Concurrency latch: cache buffers chains 356 3,404
Configuration log file switch (checkpoint in 3 9
Configuration log file switch completion 13 303
Configuration log buffer space 57 887
Idle Streams AQ: qmn slave idle wai 1 2,742
Idle dispatcher timer 1 5,913
Idle Streams AQ: qmn coordinator id 2 2,742
Idle virtual circuit status 2 5,859
Idle SQL*Net message from client 13 14
Idle pmon timer 17 3,824
Idle rdbms ipc message 233 51,901
Network SQL*Net message to client 13 0
Other enq: CF - contention 2 39
Other latch: cache buffers lru chain 17 95
Other LGWR wait for redo copy 87 136
Other latch free 109 937
System I/O log file single write 8 1
System I/O control file parallel write 98 430
System I/O log file parallel write 124 1,300
System I/O Log archive I/O 153 159
System I/O log file sequential read 154 49
System I/O control file sequential read 255 1
User I/O direct path write 20 0
User I/O direct path read 20 0
User I/O db file sequential read 50 3
User I/O db file scattered read 4,513 3,172
User I/O read by other session 7,106 36,836
----v$active_session_history---
Wait_class Event count(*) min_samp max_samp
Concurrency buffer busy waits 7 8299900 8299903
Concurrency latch: cache buffers chains 26 8299900 8299909
Concurrency os thread startup 3 8299900 8299902
Configuration log buffer space 7 8299913 8299925
Configuration log file switch completion 2 8299921 8299926
Other LGWR wait for redo copy 2 8299900 8299901
Other latch free 5 8299902 8299910
Other null event 6 8299900 8299903
System I/O Log archive I/O 3 8299912 8299917
System I/O control file parallel write 4 8299910 8299924
System I/O log file parallel write 10 8299914 8299925
User I/O db file scattered read 28 8299897 8299926
User I/O read by other session 357 8299902 8299926
PL/SQL procedure successfully completed
SELECT event, p1, p2, p3, COUNT(*)
FROM v$active_session_history
WHERE sample_id > 8299638
AND event IN ('buffer busy waits', 'read by other session')
GROUP BY event, p1, p2, p3
ORDER BY event, p1, p2, p3;
event p1 p2 p3 count(*)
buffer busy waits 1 64911 1 1
buffer busy waits 2 57 23 1
buffer busy waits 2 897 24 5
buffer busy waits 2 12042 24 1
read by other session 1 91788 1 1
read by other session 1 92281 1 3
read by other session 1 92553 1 1
read by other session 1 98889 1 8
read by other session 1 102681 1 7
read by other session 1 102921 1 2
read by other session 1 103353 1 1
read by other session 1 105385 1 1
read by other session 1 105689 1 2
read by other session 1 128409 1 2
read by other session 1 130473 1 8
read by other session 1 130809 1 8
read by other session 1 132489 1 19
read by other session 1 132722 1 19
read by other session 1 133017 1 19
read by other session 1 133097 1 19
read by other session 1 133445 1 19
...
46 rows selected
SELECT owner,segment_name,segment_type
FROM dba_extents_snap
WHERE file_id = 1
AND 64911 BETWEEN block_id AND block_id + blocks - 1;
OWNER SEGMENT_NAME SEGMENT_TYPE
---------- ------------------------------ ---------------
SYS TEST TABLE
SELECT owner,segment_name,segment_type
FROM dba_extents_snap
WHERE file_id = 2
AND 57 BETWEEN block_id AND block_id + blocks - 1;
OWNER SEGMENT_NAME SEGMENT_TYPE
---------- ------------------------------ ---------------
SYS _SYSSMU4$ TYPE2 UNDO
SELECT owner,segment_name,segment_type
FROM dba_extents_snap
WHERE file_id = 2
AND 897 BETWEEN block_id AND block_id + blocks - 1;
OWNER SEGMENT_NAME SEGMENT_TYPE
---------- ------------------------------ ---------------
SYS _SYSSMU9$ TYPE2 UNDO
SELECT owner,segment_name,segment_type
FROM dba_extents_snap
WHERE file_id = 2
AND 12042 BETWEEN block_id AND block_id + blocks - 1;
OWNER SEGMENT_NAME SEGMENT_TYPE
---------- ------------------------------ ---------------
SYS _SYSSMU9$ TYPE2 UNDO
SELECT owner,segment_name,segment_type
FROM dba_extents_snap
WHERE file_id = 1
AND 91788 BETWEEN block_id AND block_id + blocks - 1;
OWNER SEGMENT_NAME SEGMENT_TYPE
---------- ------------------------------ ---------------
SYS TEST TABLE
分析:
buffer busy waits事件产生原因如下:
*p3(class#)如果为大于15的奇数,表示该数据块类型为回滚段头块,偶数为回滚数据块.
*针对回滚段头块的buffer lock争用,产生原因为:update会话以exclusive模式请求,而select会话以shared模式请求,回滚数据块上的争用导致buffer busy waits.
read by other session事件产生原因如下:
*为了获取select一致性读数据,需要读取回滚数据块创建CR块,大量会话读取表数据CR块时发生read by other session等待.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/18922393/viewspace-713229/ ，如需转载，请注明出处，否则将追究法律责任。
