通过案例学调优之--模拟buffer busy waits事件
来源:互联网 发布:js代码混淆加密工具 编辑:程序博客网 时间:2024/06/16 15:06
通过案例学调优之--模拟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;
本文出自 “天涯客的blog” 博客,请务必保留此出处http://tiany.blog.51cto.com/513694/1546187
0 0
- 通过案例学调优之--模拟buffer busy waits事件
- buffer busy waits等待事件案例-vage
- 模拟buffer busy waits等待事件
- oracle 模拟出 buffer busy waits 事件
- 等待事件 - buffer busy waits
- Buffer busy waits事件,整个过程
- 等待事件buffer busy waits
- buffer busy waits等待事件
- AWR实战分析之----buffer busy waits
- Buffer busy waits
- Oracle buffer busy waits
- oracle buffer busy waits
- buffer busy waits
- buffer busy waits
- buffer busy waits
- buffer busy waits
- buffer busy waits
- buffer busy waits
- 通过案例学调优之--跨库建立物化视图(Materialized View)
- Oracle RAC错误之--oifcfg错误案例
- 通过案例学调优之--JOB管理
- 通过案例学调优之--10046事件
- 通过案例学调优之--Oracle数据块(block)
- 通过案例学调优之--模拟buffer busy waits事件
- 语音识别之找出字符串的最短距离
- Oracle参数open_cursors和session_cached_cursor详解
- 通过案例学调优之--IOSTAT(查看磁盘I/O)
- 通过案例学调优之--分区表基本概念
- mac系统怎样使用xcode从svn服务器签出(checkout)项目源代码
- Java-Collections Framework学习与总结-LinkedHashMap
- 通过案例学调优之--分区表基本管理
- Oracle GoldenGate学习之--基本概念和配置(1)