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

通过案例学调优之--模拟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----------        3713:37:25 SCOTT@ prod >grant all on tb1 to tom;Grant succeeded.

2、session 1做事务处理

13:40:24 SCOTT@ prod >begin13:41:18   2  for i in 1..100000 loop13: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/tomConnected.13:40:52 TOM@ prod >select sid from v$mystat where rownum=1;       SID----------        4313:41:08 TOM@ prod >begin13:43:15   2  for i in 1..100000 loop13: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          0log buffer space                                                         37          0          0          0log buffer space                                                         43          0          0          0log buffer space                                                         43          0          0          0log buffer space                                                         43          0          0          013: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          1buffer busy waits                                                        37          6        203          1buffer busy waits                                                        37          6        203          1buffer busy waits                                                        37          6        203          1latch: cache buffers chains                                              37  372066908        150          0buffer busy waits                                                        37          6        203          1buffer busy waits                                                        43          6        203          1latch: cache buffers chains                                              43  372066908        150          0buffer busy waits                                                        43          6        203          1buffer busy waits                                                        43          6        203          1latch: cache buffers chains                                              43  372066908        150          0latch: cache buffers chains                                              43  372066908        150          012 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        754file header block           9         44undo 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 a3013:57:38 SYS@ prod >select owner,segment_name,segment_type,block_id from dba_extents where file_id=6OWNER                          SEGMENT_NAME                   SEGMENT_TYPE         BLOCK_ID------------------------------ ------------------------------ ------------------ ----------SCOTT                          EMP1                           TABLE                     128SCOTT                          EMP1                           TABLE                     144SCOTT                          EMP1                           TABLE                     160SCOTT                          EMP1                           TABLE                     168SCOTT                          EMP1                           TABLE                     184SCOTT                          MLOG$_EMP1                     TABLE                     136SCOTT                          MLOG$_EMP1                     TABLE                     152SCOTT                          MLOG$_EMP1                     TABLE                     176SCOTT                          MLOG$_EMP1                     TABLE                     192SCOTT                          TB1                            TABLE                     20010 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;