Oracle Study之--Oracle等待事件(2)
来源:互联网 发布:丹尼格兰杰数据 编辑:程序博客网 时间:2024/06/02 03:51
Oracle Study之--Oracle等待事件(2)
Control file parallel write
当数据库中有多个控制文件的拷贝时,Oracle 需要保证信息同步地写到各个控制文件当中,这是一个并行的物理操作过程,因为称为控制文件并行写,当发生这样的操作时,就会产生control file parallel write等待事件。
控制文件频繁写入的原因很多,比如:
· 日志切换太过频繁,导致控制文件信息相应地需要频繁更新。
· 系统I/O 出现瓶颈,导致所有I/O出现等待。
当系统出现日志切换过于频繁的情形时,可以考虑适当地增大日志文件的大小来降低日志切换频率。
当系统出现大量的control file parallel write 等待事件时,可以通过比如降低控制文件的拷贝数量,将控制文件的拷贝存放在不同的物理磁盘上的方式来缓解I/O 争用。
这个等待事件包含三个参数:
Files: Oracle 要写入的控制文件个数。
Blocks: 写入控制文件的数据块数目。
Requests: 写入控制请求的I/O 次数。
Control file sequential read
当数据库需要读取控制文件上的信息时,会出现这个等待事件,因为控制文件的信息是顺序写的,所以读取的时候也是顺序的,因此称为控制文件顺序读,它经常发生在以下情况:
备份控制文件
RAC 环境下不同实例之间控制文件的信息共享
读取控制文件的文件头信息
读取控制文件其他信息
这个等待事件有三个参数:
File#: 要读取信息的控制文件的文件号。
Block#: 读取控制文件信息的起始数据块号。
Blocks: 需要读取的控制文件数据块数目。
模拟案例:
1、模拟事务处理
16:59:48 SYS@ test1>select username,sid,serial# from v$session where username is not null;USERNAME SID SERIAL#------------------------------ ---------- ----------SCOTT 1 7SYS 34 4TOM 41 316:59:57 SCOTT@ test1>begin17:04:05 2 for i in 1..1000000 loop17:04:05 3 execute immediate 'insert into t1(id) values ('||i||')';17:04:05 4 end loop;17:04:05 5 end;17:04:05 6 /17:00:00 TOM@ test1> begin17:04:15 2 for i in 1..1000000 loop17:04:15 3 execute immediate 'insert into scott.t1(id) values ('||i||')';17:04:15 4 end loop;17:04:15 5 end;17:04:15 6 /
2、查看等待事件:
17:05:40 SYS@ test1>select sid,event,p1,p2,p3,WAIT_TIME from v$session_wait where sid in (1,41); SID EVENT P1 P2 P3 WAIT_TIME---------- -------------------------------------------------- ---------- ---------- ----- 1 latch: redo allocation 1993106152 182 0 -1 41 latch: cache buffers chains 2011026752 150 0 -1Elapsed: 00:00:00.0117:06:11 SYS@ test1>r 1* select sid,event,p1,p2,p3,WAIT_TIME from v$session_wait where sid in (1,41) SID EVENT P1 P2 P3 WAIT_TIME---------- -------------------------------------------------- ---------- ---------- ------- 1 log file switch (checkpoint incomplete) 0 0 0 3 41 buffer busy waits 4 4048 1 94Elapsed: 00:00:00.0117:06:16 SYS@ test1>r 1* select sid,event,p1,p2,p3,WAIT_TIME from v$session_wait where sid in (1,41) SID EVENT P1 P2 P3 WAIT_TIME---------- -------------------------------------------------- ---------- ---------- ----- 1 log file switch (checkpoint incomplete) 0 0 0 0 41 log file switch (checkpoint incomplete) 0 0 0 0Elapsed: 00:00:00.0217:06:18 SYS@ test1>r 1* select sid,event,p1,p2,p3,WAIT_TIME from v$session_wait where sid in (1,41) SID EVENT P1 P2 P3 WAIT_TIME---------- -------------------------------------------------- ---------- ---------- ---------- ---------- 1 log buffer space 0 0 0 0 41 log buffer space 0 0 0 0Elapsed: 00:00:00.0117:06:20 SYS@ test1>r 1* select sid,event,p1,p2,p3,WAIT_TIME from v$session_wait where sid in (1,41) SID EVENT P1 P2 P3 WAIT_TIME---------- -------------------------------------------------- ---------- ---------- ----- 1 log file switch (checkpoint incomplete) 0 0 0 134 41 log file switch (checkpoint incomplete) 0 0 0 3Elapsed: 00:00:00.0117:06:22 SYS@ test1>r 1* select sid,event,p1,p2,p3,WAIT_TIME from v$session_wait where sid in (1,41) SID EVENT P1 P2 P3 WAIT_TIME---------- -------------------------------------------------- ---------- ---------- ---------- ---------- 1 latch: cache buffers chains 2011026752 150 0 -1 41 latch: cache buffers chains 2011026752 150 0 -1Elapsed: 00:00:00.01
3、查看关于controlfile的事件
17:06:24 SYS@ test1>select EVENT ,TOTAL_WAITS,TIME_WAITED from v$system_event where event like '%control%';EVENT TOTAL_WAITS TIME_WAITED-------------------------------------------------- ----------- -----------control file sequential read 51623 326control file parallel write 6327 5102control file heartbeat 1 400Elapsed: 00:00:00.0217:07:31 SYS@ test1>r 1* select EVENT ,TOTAL_WAITS,TIME_WAITED from v$system_event where event like '%control%'EVENT TOTAL_WAITS TIME_WAITED-------------------------------------------------- ----------- -----------control file sequential read 52314 329control file parallel write 6410 5164control file heartbeat 1 400Elapsed: 00:00:00.0317:07:41 SYS@ test1>r 1* select EVENT ,TOTAL_WAITS,TIME_WAITED from v$system_event where event like '%control%'EVENT TOTAL_WAITS TIME_WAITED-------------------------------------------------- ----------- -----------control file sequential read 52597 330control file parallel write 6433 5177control file heartbeat 1 400Elapsed: 00:00:00.02
4、查看告警日志:
Beginning log switch checkpoint up to RBA [0x438.2.10], SCN: 1762466Thread 1 advanced to log sequence 1080 (LGWR switch) Current log# 3 seq# 1080 mem# 0: /u01/app/oracle/oradata/redo03a.logFri Jul 25 17:10:26 2014Archived Log entry 1065 added for thread 1 sequence 1079 ID 0x4906548b dest 1:Thread 1 cannot allocate new log, sequence 1081Checkpoint not complete Current log# 3 seq# 1080 mem# 0: /u01/app/oracle/oradata/redo03a.logCompleted checkpoint up to RBA [0x438.2.10], SCN: 1762466Beginning log switch checkpoint up to RBA [0x439.2.10], SCN: 1762522Thread 1 advanced to log sequence 1081 (LGWR switch) Current log# 4 seq# 1081 mem# 0: /u01/app/oracle/oradata/redo04a.logFri Jul 25 17:10:29 2014Archived Log entry 1066 added for thread 1 sequence 1080 ID 0x4906548b dest 1:Thread 1 cannot allocate new log, sequence 1082Checkpoint not complete Current log# 4 seq# 1081 mem# 0: /u01/app/oracle/oradata/redo04a.logCompleted checkpoint up to RBA [0x439.2.10], SCN: 1762522Fri Jul 25 17:10:32 2014Beginning log switch checkpoint up to RBA [0x43a.2.10], SCN: 1762575Thread 1 advanced to log sequence 1082 (LGWR switch) Current log# 3 seq# 1082 mem# 0: /u01/app/oracle/oradata/redo03a.logArchived Log entry 1067 added for thread 1 sequence 1081 ID 0x4906548b dest 1:
5、查看日志组信息
17:16:22 SYS@ test1>select group#,sequence#,status,bytes/1024/1024 from v$log; GROUP# SEQUENCE# STATUS BYTES/1024/1024---------- ---------- ---------------- --------------- 3 1082 CURRENT 4 4 1081 INACTIVE 4Elapsed: 00:00:00.03日志组size太小,日志组数量较少,日志切换太频繁,导致产生大量的控制文件读和写,调整日志组的size,并增加日志组的个数。
本文出自 “天涯客的blog” 博客,请务必保留此出处http://tiany.blog.51cto.com/513694/1530190
- Oracle Study之--Oracle等待事件(2)
- Oracle Study之--Oracle等待事件(1)
- Oracle Study之--Oracle等待事件(3)
- Oracle Study之--Oracle等待事件(4)
- Oracle Study之--Oracle等待事件(5)
- Oracle Study之--Oracle等待事件(6)
- Oracle Study之--Oracle等待事件(7)
- Oracle Study之--Oracle等待事件(8)
- Oracle Study之--Oracle等待事件(9)
- Oracle Study之--resmgr:cpu quantum等待事件
- Oracle Study之-- enq:SQ contention等待事件
- Oracle等待事件之四---log相关等待事件
- oracle之 等待事件LOG FILE SYNC (awr)优化
- Oracle Study之--Oracle 触发器(Trigger)
- ORACLE等待事件
- ORACLE等待事件 [转]
- oracle等待事件介绍
- oracle的等待事件
- Oracle 学习之--Buffer Cache深入解析
- Oracle ASM错误之--ORA-15031、ORA-15014
- Oracle ASM错误之--ORA-15033
- Oracle Study之--Oracle等待事件(1)
- Oracle非归档模式Media Recovery错误之--ORA-26040
- Oracle Study之--Oracle等待事件(2)
- Oracle Study之--Oracle等待事件(3)
- Oracle Study之--Oracle等待事件(4)
- Oracle Study之--Oracle等待事件(5)
- AIX HACMP系统错误--NODE同步配置错误
- AIX RAC构建错误之--未配置Default Gateway
- AIX RAC配置Flashback Recovery Area
- Oracle Study之--Oracle等待事件(6)
- Oracle Study之--Oracle等待事件(7)