等待事件_buffer_busy_waits_and_read_by_other_session(1)

1,buffer lock相關等待
Oracle是以塊為單位進行IO的,為了避免多個程式對同一塊同時修改,引入了buffer lock.獲取buffer lock有兩種模式,shared和exclusive,分別對應查詢和修改操作.
不過,buffer lock不是Oracle的正式詞彙,但在轉儲出來的buffer header中可以看到user list和waiter list.

為獲取buffer lock而發生的等待,在10g上,共有4種:
*buffer busy waits
*read by other session
*write complete waits
*gc buffer busy

以下查詢,列出了這4個等待事件的引數及所屬等待型別,其中class#給出了具體原因:
SELECT NAME, parameter1, parameter2, parameter3, wait_class
  FROM v$event_name
 WHERE NAME IN ('buffer busy waits',
                'read by other session',
                'gc buffer busy',
                'write complete waits');
NAME                     PARAMETER1 PARAMETER2  PARAMETER3 WAIT_CLASS
------------------------ ---------- ----------- ---------- ---------------
write complete waits     file#      block#                 Configuration
buffer busy waits        file#      block#      class#     Concurrency
gc buffer busy           file#      block#      id#        Cluster
read by other session    file#      block#      class#     User I/O

為了修改行，需要進行如下操作：
(1)為了查詢要修改的塊存在的位置(hash chain),請求cache buffer chains latch；
(2)如果沒有找到，需要查詢空閒塊，請求cache buffer lru chain latch，對載入塊請求buffer lock,讀入快取；如果該塊在快取中，對該塊請求塊請求buffer lock；
(3)請求行上的TX鎖，修改行，釋放buffer lock。如果沒有獲得TX鎖，則釋放buffer lock，進入等待狀態。之後重複上面過程。

2,select/select引起的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;
--直接IO,x$bh中只有一行被快取,是段頭塊,sys使用者預設表空間SYSTEM,是FLM表空間.

SQL> show parameter multiblock
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16
SQL> show parameter block_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

SQL> SELECT segment_name, bytes/1024/1024, bytes / 1024 / 8 / 16
  2    FROM user_segments
  3   WHERE segment_name = 'TEST';
SEGMENT_NAME BYTES/1024/1024 BYTES/1024/8/16
------------ --------------- ---------------
TEST                    1152            9216
 
SQL> show sga
Total System Global Area 1073741824 bytes
Fixed Size                  1223540 bytes
Variable Size             142607500 bytes
Database Buffers          922746880 bytes
Redo Buffers                7163904 bytes

從以上查詢結果可知:
該表共分配了1152M空間,比buffer cache大;全表掃描共需要9216次multiblock IO.

set serveroutput on
exec system_pkg.get_snap;

create or replace procedure do_select is
l_cursor sys_refcursor;
l_value number;
begin
  for x in (select * from test) loop
    null;
  end loop;
end;
/

declare
  l_job number;
begin
  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:7893667
 
----system stats---
Name                                 Value
session uga memory            ############
physical read bytes           ############
physical read total bytes     ############
process last non-idle time    ############
session connect time          ############
session pga memory              15,109,348
session pga memory max          13,864,164
table scan rows gotten          10,354,677
session uga memory max           4,590,656
table scan blocks gotten         1,478,832
no work - consistent read gets   1,477,674
Cached Commit SCN referenced     1,476,686
consistent gets from cache       1,472,752
consistent gets                  1,472,723
session logical reads            1,472,121
physical write total bytes         761,856
free buffer requested              220,856
physical reads cache               220,622
physical reads                     220,607
physical write bytes               172,032
physical reads cache prefetch      168,479
free buffer inspected              147,878
recursive calls                    105,387
redo size                           85,220
physical read IO requests           52,400
physical read total IO request      52,369
undo change vector size             28,888
physical read total multi bloc      14,658
redo wastage                         8,068
workarea memory allocated            6,252
concurrency wait time                5,552
DB time                              4,079
bytes received via SQL*Net fro       3,553
sorts (rows)                         3,002
user I/O wait time                   2,546
bytes sent via SQL*Net to clie       1,885
shared hash latch upgrades - n       1,583
CPU used by this session             1,575
index scans kdiixs1                  1,564
recursive cpu usage                  1,526
buffer is not pinned count           1,521
calls to get snapshot scn: kcm       1,383
sorts (memory)                       1,269
consistent gets - examination        1,123
buffer is pinned count               1,121
execute count                        1,035
parse count (total)                    917
opened cursors cumulative              881
workarea executions - optimal          806
CPU used when call started             743
session cursor cache hits              679
table fetch by rowid                   620
db block changes                       544
parse time elapsed                     539
enqueue requests                       453
enqueue releases                       436
db block gets                          396
db block gets from cache               396
redo blocks written                    384
redo entries                           295
index fetch by key                     193
redo write time                        123
opened cursors current                 114
user calls                              72
background timeouts                     63
rows fetched via callback               60
physical write total IO reques          59
messages received                       55
messages sent                           55
commit cleanouts                        53
commit cleanouts successfully           51
session cursor cache count              47
pinned buffers inspected                44
cluster key scans                       41
cluster key scan block gets             41
calls to kcmgas                         39
redo writes                             31
physical write total multi blo          29
table scans (short tables)              25
user commits                            24
table scans (long tables)               22
cleanouts only - consistent re          21
immediate (CR) block cleanout           21
commit txn count during cleano          21
cleanout - number of ktugct ca          21
Commit SCN cached                       21
logons cumulative                       21
physical writes                         21
physical writes from cache              21
physical writes non checkpoint          20
logons current                          20
deferred (CURRENT) block clean          17
SQL*Net roundtrips to/from cli          15
summed dirty queue length               12
immediate (CURRENT) block clea          12
redo synch writes                       12
dirty buffers inspected                 11
parse count (hard)                       7
enqueue conversions                      7
switch current to new buffer             6
parse time cpu                           5
physical write IO requests               4
heap block compress                      4
redo ordering marks                      4
cursor authentications                   3
redo synch time                          2
commit cleanout failures: bloc           2
change write time                        2
consistent changes                       2
calls to kcmgcs                          1
 
----system events---
Wait_Class     Event                         Total_waits Time_waited
Commit         log file sync                            4           2
Concurrency    os thread startup                       26         945
Concurrency    latch: cache buffers chains            137       4,608
Idle           Streams AQ: qmn slave idle wai           1       2,735
Idle           virtual circuit status                   1       2,953
Idle           Streams AQ: qmn coordinator id           2       2,735
Idle           pmon timer                               9       2,346
Idle           SQL*Net message from client             15          29
Idle           rdbms ipc message                      115      20,747
Network        SQL*Net message to client               15           0
Other          latch: enqueue hash chains               1           4
Other          latch: cache buffers lru chain          24         100 
Other          latch free                              84       2,811
System I/O     control file parallel write              8         132
System I/O     control file sequential read            18           0
System I/O     log file parallel write                 31         118
User I/O       read by other session                  146         657  ==>buffer lock等待
User I/O       db file scattered read              15,161       1,747  ==>由於buffer cache有限,部分塊被多次裝載
User I/O       db file sequential read             39,018         317  ==>由於buffer cache有限,部分塊被擠出記憶體,重新裝載時被分割,不能多塊裝入
 
----v$active_session_history---
Wait_class     Event                         count(*)        min_samp    max_samp   
Concurrency    latch: cache buffers chains   23              7893701     7893704    
Concurrency    os thread startup             5               7893696     7893700    
Other          latch free                    27              7893697     7893704    
Other          null event                    10              7893696     7893699    
User I/O       db file scattered read        10              7893694     7893704    
User I/O       db file sequential read       1               7893703     7893703    
User I/O       read by other session         1               7893704     7893704    
 
PL/SQL procedure successfully completed

測試結果說明:
*查詢程式在裝載資料塊到記憶體時，需要以exclusive模式獲取塊上的buffer lock，其他程式以shared模式請求buffer block，就會出現read by other session等待（類似於硬解析SQL時需要以exclusive模式獲取library cache pin）。
*buffer lock等待一般伴隨物理IO
*重新執行時，雖然部分塊以在第一次執行時載入記憶體，但由於sga大小限制，等待事件和統計資料與第一次類似。