Oracle Study之--Oracle等待事件（3）
1、关闭ASYNC I/O:11:25:47 SYS@ prod>show parameter sync
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
disk_asynch_io boolean FALSE
tape_asynch_io boolean TRUE
11:25:53 SYS@ prod>select event,TOTAL_WAITS,AVERAGE_WAIT from v$system_event
11:25:58 2where upper(event) like 'DB FILE%';
EVENT TOTAL_WAITS AVERAGE_WAIT
---------------------------------------------------------------- ----------- ------------
db file sequential read 2036 .02
db file scattered read 844 .01
db file single write 27 .69
db file parallel write 38 1.69
2、用户进行事务操作
11:24:02 SCOTT@ prod>conn scott/tiger
Connected.
11:26:19 SCOTT@ prod>begin
11:26:38 2 for i in 1..100000 loop
11:26:38 3 execute immediate 'insert into t1 values ('||i||')';
11:26:38 4 end loop;
11:26:38 5 end;
11:26:38 6 /
11:24:06 TOM@ prod>conn tom/tom
Connected.
11:26:47 TOM@ prod>begin
11:26:57 2 for i in 1..100000 loop
11:26:57 3 execute immediate 'insert into scott.t1 values ('||i||')' ;
11:26:57 4 end loop;
11:26:57 5 end;
11:26:57 6 /
11:22:41 SYS@ prod>select event,TOTAL_WAITS,AVERAGE_WAIT from v$system_event
2* where upper(event) like 'DB FILE%'
EVENT TOTAL_WAITS AVERAGE_WAIT
---------------------------------------------------------------- ----------- ------------
db file sequential read 3216 .02
db file scattered read 846 .01
db file single write 27 .69
db file parallel write 67 2.78
Elapsed: 00:00:00.02
3、解决方法
11:22:42 SYS@ prod>show parameter sync
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
disk_asynch_io boolean TRUE
tape_asynch_io boolean TRUE
11:22:43 SYS@ prod>select event,TOTAL_WAITS,AVERAGE_WAIT from v$system_event
2* where upper(event) like 'DB FILE%'
EVENT TOTAL_WAITS AVERAGE_WAIT
---------------------------------------------------------------- ----------- ------------
db file sequential read 11801 .05
db file scattered read 1920 .03
db file single write 54 .35
db file async I/O submit 266 7.78
db file parallel read 39 .19
'db file parallel write ' 等待事件消失 ！
页:
[1]