Shulou(Shulou.com)06/01 Report--

Tuning it through case study-- simulating buffer busy waits events

Buffer busy waits wait event

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 an 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

Case study:

1. Set up a test table

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 row created.13:36:35 SCOTT@ prod > insert into tb1 values); 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 grant all on tb1 to tom;Grant succeeded.2 3725 SCOTT@ prod > grant all on tb1 to tom;Grant succeeded.2, session 1 for transaction processing

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 do transaction

13:40:48 SYS@ prod > conn tom/tomConnected.13:40:52 TOM@ prod > select sid from v$mystat where rownum=1; SID- 4313 select sid from v$mystat where rownum=1; SID- 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. View session wait events

If the transaction can access v$session_wait while running, and if the transaction is finished, it can access v$session_wait_history.

13:44:55 SYS@ prod > select event,sid,p1,p2 P3 from v$session_wait_history where sid in (37 from v$session_wait_history where sid in 43) and event like'% buffer%'EVENT SID P1 P2 P3 Murray- -- log buffer space 37 00 0log buffer space 37 0 0 0log buffer space 43 0 0 0log buffer space 43 0 0 0log buffer space 43 00013 SYS@ prod 4456 SYS@ prod > select event Sid,p1,p2 P3 from v$session_wait_history where sid in (37 from v$session_wait_history where sid in 43) and event like'% buffer%'EVENT SID P1 P2 P3 Murray- -- 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. You can see that the block: file# that caused the 'buffer busy waits'' is 6 and the block # is 2035, and the type of block waiting for event access is determined.

13:45:17 SYS@ prod > select * from v$waitstat where count > 0bot class COUNT TIME- data block 144754file header block 9 44undo header 7 16, view the sql statement that caused the 'buffer busy waits' event

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=27, View the block type that caused the 'buffer busy waits' event

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 TB18, View the segment that caused the 'buffer busy waits' event

13:57:25 SYS@ prod > col segment_name for a3013 SYS@ prod 5738 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. Since the block#, of the table TB1 is close to the block# (203) that caused the "buffer busy waits" event, it is determined that the hot block is on the table tb1.

9. Query the sql accessing tb1

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.