oracle模拟出bufferbusywaits事件

浏览： ℃

字体：大中小

发布时间：2013-12-13 14:31:50

来源：

实验内容

模拟出 buffer busy waits 等待事件

实验总结

在同一个块同时进行DML操作时会产生 buffer busy waits 事件

有些等待时间非常短几乎可以忽略不计

但是如果在AWR报告排名很靠前就需要想办法减少buffer busy waits 等待事件

尽量避免buffer busy waits 事件解决办法

1、prcfree（10%）使用这个参数预留一定空间

2、修改块的大小

alter system set db_4k_cache_size=5M;

create tablespace tablepack10 datafile '/u01/app/oracle/oradata/ocm/tablepack10.dbf' size 10M blocksize 4k;

alter table gyj_t2 move tablespace tp10;

3、HASH分区表

4、反向索引 (不能排序)

实验开始

测试表test_1的内容:

SQL> select rowid,a.* from test_1 a;

ROWID ID_A NAME_A
------------------ ---------- --------------------
AAAEMvAABAAAJ5hAAA 1 session1
AAAEMvAABAAAJ5hAAB 2 session2

查看test_1的两行数据是不是同一个块上面

SQL> select id_a,name_a,dbms_rowid.rowid_relative_fno(rowid) file# ,
dbms_rowid.rowid_block_number(rowid) block# from test_1;
ID_A NAME_A FILE# BLOCK#
---------- -------------------- ---------- ----------
1 session1 1 40545
2 session2 1 40545

新开两个会话窗口分别是32号会话和40号会话

SQL> select distinct sid from v$mystat;

SID
----------
32

SQL> select distinct sid from v$mystat;

SID
----------
40

在32号会话中执行大量查询操作

declare
vid number;
begin
for i in 1 .. 5000000 loop
select id_a into vid from test_1 where rowid='AAAEMvAABAAAJ5hAAA';
end loop;
end;
/

同时在40号会话中执行更新操作

declare
begin
for i in 1 .. 200000 loop
update test_1 set id_a=id_a+0 where rowid='AAAEMvAABAAAJ5hAAB';
end loop;
commit;
end;
/

之后查看事件内容:

SQL> col EVENT for a35;
SQL> select SID,EVENT,TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED_MICRO from v$session_event where sid in(32,40);

SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED_MICRO
---------- ----------------------------------- ----------- -------------- -----------------
32 Disk file operations I/O 2 0 258
32 latch: cache buffers chains 1 0 104436
32 buffer busy waits 11 0 1570217
32 log file sync 1 0 378
32 SQL*Net message to client 11 0 35
32 SQL*Net message from client 10 0 758283567
32 SQL*Net break/reset to client 5 0 1583
32 events in waitclass Other 2 2 10
40 Disk file operations I/O 2 0 440
40 latch: cache buffers chains 5 0 213828
40 log file switch completion 1 0 107532
40 log file sync 4 0 130952
40 SQL*Net message to client 15 0 105
40 SQL*Net message from client 14 0 811794952
40 SQL*Net break/reset to client 2 0 667
40 events in waitclass Other 3 3 11

TOTAL_WAITS 会话总数的等待次数
TOTAL_TIMEOUTS 该事件的会话总数超时
TIME_WAITED_MICRO 会话等待时间总量（以微秒为单位）

下面是官方文档原文

V$SESSION_EVENT

This view lists information on waits for an event by a session.
Note that the TIME_WAITED and AVERAGE_WAIT columns will contain a value of zero on those platforms that do not support a fast timing mechanism.
If you are running on one of these platforms and you want this column to reflect true wait times,
you must set TIMED_STATISTICS to true in the parameter file.
Please remember that doing this will have a small negative effect on system performance.

See Also:
"TIMED_STATISTICS"
Column DatatypeDescription
SID NUMBER ID of the session
EVENT VARCHAR2(64)Name of the wait event
See Also: Appendix C, "Oracle Wait Events"
TOTAL_WAITS NUMBERTotal number of waits for the event by the session
TOTAL_TIMEOUTS NUMBERTotal number of timeouts for the event by the session
TIME_WAITED NUMBERTotal amount of time waited for the event by the session (in hundredths of a second)
AVERAGE_WAIT NUMBERAverage amount of time waited for the event by the session (in hundredths of a second)
MAX_WAIT NUMBERMaximum time waited for the event by the session (in hundredths of a second)
TIME_WAITED_MICRO NUMBERTotal amount of time waited for the event by the session (in microseconds)
EVENT_ID NUMBERIdentifier of the wait event
WAIT_CLASS_ID NUMBERIdentifier of the class of the wait event
WAIT_CLASS# NUMBERNumber of the class of the wait event
WAIT_CLASS VARCHAR2(64)Name of the class of the wait event

>更多相关文章