实验内容
模拟出 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
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 Datatype Description
SID NUMBER ID of the session
EVENT VARCHAR2(64) Name of the wait event
See Also: Appendix C, "Oracle Wait Events"
TOTAL_WAITS NUMBER Total number of waits for the event by the session
TOTAL_TIMEOUTS NUMBER Total number of timeouts for the event by the session
TIME_WAITED NUMBER Total amount of time waited for the event by the session (in hundredths of a second)
AVERAGE_WAIT NUMBER Average amount of time waited for the event by the session (in hundredths of a second)
MAX_WAIT NUMBER Maximum time waited for the event by the session (in hundredths of a second)
TIME_WAITED_MICRO NUMBER Total amount of time waited for the event by the session (in microseconds)
EVENT_ID NUMBER Identifier of the wait event
WAIT_CLASS_ID NUMBER Identifier of the class of the wait event
WAIT_CLASS# NUMBER Number of the class of the wait event
WAIT_CLASS VARCHAR2(64) Name of the class of the wait event
模拟出 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号会话
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 Datatype Description
SID NUMBER ID of the session
EVENT VARCHAR2(64) Name of the wait event
See Also: Appendix C, "Oracle Wait Events"
TOTAL_WAITS NUMBER Total number of waits for the event by the session
TOTAL_TIMEOUTS NUMBER Total number of timeouts for the event by the session
TIME_WAITED NUMBER Total amount of time waited for the event by the session (in hundredths of a second)
AVERAGE_WAIT NUMBER Average amount of time waited for the event by the session (in hundredths of a second)
MAX_WAIT NUMBER Maximum time waited for the event by the session (in hundredths of a second)
TIME_WAITED_MICRO NUMBER Total amount of time waited for the event by the session (in microseconds)
EVENT_ID NUMBER Identifier of the wait event
WAIT_CLASS_ID NUMBER Identifier of the class of the wait event
WAIT_CLASS# NUMBER Number of the class of the wait event
WAIT_CLASS VARCHAR2(64) Name of the class of the wait event