等待事件_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等待.