Oracle Study之--Oracle等待事件（9）
16:01:25 SYS@ prod>select event,TOTAL_WAITS,AVERAGE_WAIT,EVENT_ID from v$system_event2*where event like '%log%'
EVENT TOTAL_WAITS AVERAGE_WAIT EVENT_ID
---------------------------------------------------------------- ----------- ------------ ----------
log file sequential read 184 .8549236675
log file single write 51 .45215477332
log file parallel write 5318 1.39 3999721902
log buffer space 17 21.07 3357856061
log file switch (private strand flush incomplete) 4 7.92114164561
switch logfile command 3 9.06 3845123846
log file switch completion 9 13.97 3834950329
log file sync 336 3.41 1328744198
ARCH wait for archivelog lock 18 .01 2370101988
9 rows selected.
16:03:06 SYS@ prod>show parameter log_buffer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 2236416
16:01:10 SYS@ prod>select group#,sequence#,status,bytes/1024/1024 from v$log;
GROUP#SEQUENCE# STATUS BYTES/1024/1024
---------- ---------- ---------------- ---------------
4 31 INACTIVE 4
5 32 CURRENT 4
Elapsed: 00:00:00.02
事务操作；
16:01:53 SCOTT@ prod>select count(*) from t1;
COUNT(*)
----------
700000
Elapsed: 00:00:00.06
16:02:02 SCOTT@ prod>insert into t1 select * from t1;
700000 rows created.
16:09:20 SYS@ prod>select event,TOTAL_WAITS,AVERAGE_WAIT,EVENT_ID from v$system_event
2 where event like '%log%'
3*
EVENT TOTAL_WAITS AVERAGE_WAIT EVENT_ID
---------------------------------------------------------------- ----------- ------------ ----------
log file sequential read 506 .41549236675
log file single write 143 .42215477332
log file parallel write 6606 1.36 3999721902
log buffer space 18 20.05 3357856061
log file switch (checkpoint incomplete) 78 58.69 2867289651
log file switch (private strand flush incomplete) 4 7.92114164561
switch logfile command 3 9.06 3845123846
log file switch completion 51 8.32 3834950329
log file sync 344 3.36 1328744198
ARCH wait for archivelog lock 64 .01 2370101988
告警日志：
Fri Aug 08 16:07:38 2014
Thread 1 advanced to log sequence 77 (LGWR switch)
Current log# 4 seq# 77 mem# 0: /dsk1/oradata/prod/redo04a.log
Fri Aug 08 16:07:38 2014
Archived Log entry 133 added for thread 1 sequence 76 ID 0xfc8aa16 dest 2:
Thread 1 cannot allocate new log, sequence 78
Checkpoint not complete
Current log# 4 seq# 77 mem# 0: /dsk1/oradata/prod/redo04a.log
Thread 1 advanced to log sequence 78 (LGWR switch)
Current log# 5 seq# 78 mem# 0: /dsk1/oradata/prod/redo05a.log
Fri Aug 08 16:07:46 2014
Archived Log entry 134 added for thread 1 sequence 77 ID 0xfc8aa16 dest 2:
Fri Aug 08 16:07:46 2014
ORA-1653: unable to extend table SCOTT.T1 by 128 in tablespace USERS
Fri Aug 08 16:08:11 2014
Thread 1 advanced to log sequence 79 (LGWR switch)
Current log# 4 seq# 79 mem# 0: /dsk1/oradata/prod/redo04a.log
Fri Aug 08 16:08:11 2014
Archived Log entry 135 added for thread 1 sequence 78 ID 0xfc8aa16 dest 2:
Fri Aug 08 16:08:56 2014
Thread 1 advanced to log sequence 80 (LGWR switch)
Current log# 5 seq# 80 mem# 0: /dsk1/oradata/prod/redo05a.log
Fri Aug 08 16:08:56 2014
Archived Log entry 136 added for thread 1 sequence 79 ID 0xfc8aa16 dest 2:
Fri Aug 08 16:09:37 2014
Thread 1 advanced to log sequence 81 (LGWR switch)
Current log# 4 seq# 81 mem# 0: /dsk1/oradata/prod/redo04a.log
Fri Aug 08 16:09:37 2014
Archived Log entry 137 added for thread 1 sequence 80 ID 0xfc8aa16 dest 2:
@由于日志组size较小，日志组数量少，在做事务处理时，日志切换频繁，发生大量关于redo log的等待事件
解决方法：
1、调整日志组的个数
2、增加日志组size
3、将redo log存储到I/O较快的磁盘上（RAID 10)
4、增大log buffer的size
页:
[1]