等待事件_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大小限制，等待事件和统计数据与第一次类似。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-713227/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-713227/