通过案例学调优之--模拟buffer busy waits事件

buffer busy waits等待事件

    Wait occurs when a session attempts to access a block in memory, is denied and must wait until the buffer becomes available. This event happens because a buffer is either being read into the buffer cache by another session (and the session is waiting for that read to complete) or the buffer is in the buffer cache, but in a incompatible mode (that is, some other session is changing the buffer).

Solutions：

    Buffer busy waits often occur in the following cases:
Inefficient SQL statements read more blocks than necessary. If there are many sessions running these statements, they will attempt to read the same blocks and possibly wait on this event.
If the FREELISTS parameter for a table is too low, multiple sessions that are attempting to insert rows in the same table and end up waiting for freelists. This problem shows up as contention for the segment header of the table.
Multiple sessions are attempting to change an index block (possibly do to an insert).
The INITRANS parameters is too low for a segment. Any DML operation that needs to go into the block needs to lock an Interested Transaction List (ITL). If INITRANS parameter is set too low, then there will be less number of ITLs allocated originally. Oracle can only allocate more ITLs if there is space in PCTFREE area of the block. If, however, there is no space to increase the ITL, the transactions that cannot lock ITLs will have to wait until the previous transactions have completed operations on the block. The waiter will register ‘buffer busy wait’ for the block

案例分析：

1、建立测试表

13:35:51 SCOTT@ prod >create table tb1 (id int ,name varchar2(10));
Table created.

13:36:16 SCOTT@ prod >insert into tb1 values (1,'scott');
1 row created.

13:36:35 SCOTT@ prod >insert into tb1 values (2,'tom');
1 row created.

13:36:47 SCOTT@ prod >commit;
Commit complete.

13:37:09 SCOTT@ prod >select sid from v$mystat where rownum=1;
       SID
----------
        37

13:37:25 SCOTT@ prod >grant all on tb1 to tom;
Grant succeeded.

2、session 1做事务处理

13:40:24 SCOTT@ prod >begin
13:41:18   2  for i in 1..100000 loop
13:41:29   3  update tb1 set name='rose' where id=2;
13:41:56   4  commit;
13:41:58   5  end loop;
13:42:02   6  end;
13:42:04   7  /

3、session 2做事务处理

13:40:48 SYS@ prod >conn tom/tom
Connected.
13:40:52 TOM@ prod >select sid from v$mystat where rownum=1;
       SID
----------
        43

13:41:08 TOM@ prod >begin
13:43:15   2  for i in 1..100000 loop
13:43:22   3  update scott.tb1 set name='john' where id=1;
13:43:43   4  commit;
13:43:45   5  end loop;
13:43:49   6  end;
13:43:50   7  /

4、查看会话等待事件

如果事务在运行过程中可以访v$session_wait,如果事务运行结束可以访问v$session_wait_history

13:44:55 SYS@ prod > select event,sid,p1,p2,p3 from v$session_wait_history where sid in (37,43) and event like '%buffer%'
EVENT                                                                   SID         P1         P2         P3
---------------------------------------------------------------- ---------- ---------- ---------- ----------
log buffer space                                                         37          0          0          0
log buffer space                                                         37          0          0          0
log buffer space                                                         43          0          0          0
log buffer space                                                         43          0          0          0
log buffer space                                                         43          0          0          0

13:44:56 SYS@ prod >select event,sid,p1,p2,p3 from v$session_wait_history where sid in (37,43) and event like '%buffer%'
EVENT                                                                   SID         P1         P2         P3
---------------------------------------------------------------- ---------- ---------- ---------- ----------
buffer busy waits                                                        37          6        203          1
buffer busy waits                                                        37          6        203          1
buffer busy waits                                                        37          6        203          1
buffer busy waits                                                        37          6        203          1
latch: cache buffers chains                                              37  372066908        150          0
buffer busy waits                                                        37          6        203          1
buffer busy waits                                                        43          6        203          1
latch: cache buffers chains                                              43  372066908        150          0
buffer busy waits                                                        43          6        203          1
buffer busy waits                                                        43          6        203          1
latch: cache buffers chains                                              43  372066908        150          0
latch: cache buffers chains                                              43  372066908        150          0
12 rows selected.

可以看到引起‘buffer busy waits’的block: file#为6，block#为203

5、判断等待事件访问的块类型

13:45:17 SYS@ prod >select * from v$waitstat where count >0;
CLASS                   COUNT       TIME
------------------ ---------- ----------
data block                144        754
file header block           9         44
undo header                 7          1

6、查看引起‘buffer busy waits’事件的sql语句

13:52:13 SYS@ prod >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
------------------------------------------------------------------------------------------------------------------------
UPDATE TB1 SET NAME='rose' WHERE ID=2

7、查看引起‘buffer busy waits’事件的块类型

14:11:00 SYS@ prod > SELECT 'segment Header' CLASS,
  2  a.Segment_Type,
  3  a.Segment_Name,
  4  a.Partition_Name
  5  FROM Dba_Segments a,
  6  V$session_Wait b
  7  WHERE a.Header_File = b.P1
  8  AND a.Header_Block = b.P2
  9  AND b.Event = 'buffer busy waits'
 10  UNION
 11  SELECT 'freelist Groups' CLASS,
 12  a.Segment_Type,
 13  a.Segment_Name,
 14  a.Partition_Name
 15  FROM Dba_Segments a,
 16  V$session_Wait b
 17  WHERE b.P2 BETWEEN a.Header_Block + 1 AND (a.Header_Block + a.Freelist_Groups)
 18  AND a.Header_File = b.P1
 19  AND a.Freelist_Groups > 1
 20  AND b.Event = 'buffer busy waits'
 21  UNION
 22  SELECT a.Segment_Type || ' Block' CLASS,
 23  a.Segment_Type,
 24  a.Segment_Name,
 25  a.Partition_Name
 26  FROM Dba_Extents a,
 27  V$session_Wait b
 28  WHERE b.P2 BETWEEN a.Block_Id AND a.Block_Id + a.Blocks - 1
 29  AND a.File_Id = b.P1
 30  AND b.Event = 'buffer busy waits'
 31  AND NOT EXISTS (SELECT 1
 32  FROM Dba_Segments
 33  WHERE Header_File = b.P1
 34* AND Header_Block = b.P2)
CLASS                    SEGMENT_TYPE       SEGMENT_NAME         PARTITION_NAME
------------------------ ------------------ -------------------- ------------------------------
TABLE Block              TABLE              TB1

8、查看引起‘buffer busy waits’事件的segment

13:57:25 SYS@ prod >col segment_name for a30
13:57:38 SYS@ prod >select owner,segment_name,segment_type,block_id from dba_extents where file_id=6
OWNER                          SEGMENT_NAME                   SEGMENT_TYPE         BLOCK_ID
------------------------------ ------------------------------ ------------------ ----------
SCOTT                          EMP1                           TABLE                     128
SCOTT                          EMP1                           TABLE                     144
SCOTT                          EMP1                           TABLE                     160
SCOTT                          EMP1                           TABLE                     168
SCOTT                          EMP1                           TABLE                     184
SCOTT                          MLOG$_EMP1                     TABLE                     136
SCOTT                          MLOG$_EMP1                     TABLE                     152
SCOTT                          MLOG$_EMP1                     TABLE                     176
SCOTT                          MLOG$_EMP1                     TABLE                     192
SCOTT                          TB1                            TABLE                     200
10 rows selected.

因为表TB1的block#，接近引起“buffer busy waits”事件的block#（203），从而判断热块是在表tb1上。

9、查询访问tb1的sql

14:01:31 SYS@ prod >select sql_text from v$sqlarea where sql_text like '%tb1%';
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------
 begin  for i in 1..100000 loop  update tb1 set name='rose' where id=2; commit;  end loop;  end;
 select sql_text from v$sqlarea where sql_text like '%tb1%'
select sql_text from v$sqlarea where sql_text like '%tb1%'
 begin  for i in 1..100000 loop  update scott.tb1 set name='rose' where id=2; commit;  end loop;  end;

14:01:45 SYS@ prod >select sql_text from v$sqlarea where sql_text like '%tb1%';
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------
 begin  for i in 1..100000 loop  update tb1 set name='rose' where id=2; commit;  end loop;  end;
 select sql_text from v$sqlarea where sql_text like '%tb1%'
select sql_text from v$sqlarea where sql_text like '%tb1%'
 begin  for i in 1..100000 loop  update scott.tb1 set name='rose' where id=2; commit;  end loop;  end;