Oracle等待事件之02(control file paral
2019-09-26 本文已影响0人
轻飘飘D
1.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
#查看当前用户的sid和serial#:
[oracle@DB01 ~]$ sql xag/123456@127.0.0.1:1521/MPTEST;
SQL> set sqlformat ansiconsole;
SQL> select sid, serial#, status from v$session where audsid=userenv('sessionid');
SID SERIAL# STATUS
427 56220 ACTIVE
#會話2
#查看当前用户的sid和serial#:
[oracle@DB01 ~]$ sql xag/123456@127.0.0.1:1521/MPTEST;
SQL> set sqlformat ansiconsole;
SQL> select sid, serial#, status from v$session where audsid=userenv('sessionid');
SID SERIAL# STATUS
1175 13563 ACTIVE
#會話3
#查看当前用户的sid和serial#:
[oracle@DB01 ~]$ sql xag/123456@127.0.0.1:1521/MPTEST;
SQL> set sqlformat ansiconsole;
SQL> select sid, serial#, status from v$session where audsid=userenv('sessionid');
SID SERIAL# STATUS
1164 24870 ACTIVE
#查看全部會話
SQL> select username,sid,serial# from v$session where username is not null;
USERNAME SID SERIAL#
SYS 3 5156
XAG 427 56220
XAG 1164 24870
XAG 1175 13563
#清楚以前的測試數據
SQL> truncate table test_wait;
#會話1
begin
for i in 1..1000000 loop
execute immediate 'insert into test_wait(id) values ('||i||')';
commit;
end loop;
end;
/
#會話2
begin
for i in 1..1000000 loop
execute immediate 'insert into test_wait(id) values ('||i||')';
commit;
end loop;
end;
/
#會話3
SQL> select sid,event,p1,p2,p3,WAIT_TIME from v$session_wait where sid in (427,1175);
SID EVENT P1 P2 P3 WAIT_TIME
427 SQL*Net message from client 1413697536 1 0 0
1175 SQL*Net message from client 1413697536 1 0 0
SQL> select sid,event,p1,p2,p3,WAIT_TIME from v$session_wait where sid in (427,1175);
SID EVENT P1 P2 P3 WAIT_TIME
427 library cache: mutex X 709908139 0 354712759107586 -1
1175 cursor: pin S wait on X 2949091299 1833951035392 21474836480 0
SQL>
SQL> select sid,event,p1,p2,p3,WAIT_TIME from v$session_wait where sid in (427,1175);
SID EVENT P1 P2 P3 WAIT_TIME
427 db file sequential read 259 13213 1 -1
1175 cursor: pin S wait on X 1822509350 1833951035392 21474836480 0
SQL> select sid,event,p1,p2,p3,WAIT_TIME from v$session_wait where sid in (427,1175);
SID EVENT P1 P2 P3 WAIT_TIME
427 db file sequential read 259 13377 1 -1
1175 db file sequential read 259 12799 1 -1
SQL> select sid,event,p1,p2,p3,WAIT_TIME from v$session_wait where sid in (427,1175);
SID EVENT P1 P2 P3 WAIT_TIME
427 cursor: pin S wait on X 2670162255 5046586572800 21474836480 -1
1175 kksfbc child completion 0 0 0 0
SQL> select sid,event,p1,p2,p3,WAIT_TIME from v$session_wait where sid in (427,1175);
SID EVENT P1 P2 P3 WAIT_TIME
427 kksfbc child completion 0 0 0 0
1175 latch: shared pool 1613797920 551 0 -1
#查看关于controlfile的事件（字段描述如下）【如产生大量的控制文件读和写則需檢查，如日誌文件大小等，本次測試未產生大量】
EVENT:等待事件名称
TOTAL_WAITS：此项事件总等待次数
TIME_WAITED：此项事件的总等待时间(单位：百分之一秒)
SQL> select EVENT ,TOTAL_WAITS,TIME_WAITED from v$system_event where event like '%control%';
EVENT TOTAL_WAITS TIME_WAITED
control file sequential read 42205 25
control file parallel write 16673 5645
SQL> select EVENT ,TOTAL_WAITS,TIME_WAITED from v$system_event where event like '%control%';
EVENT TOTAL_WAITS TIME_WAITED
control file sequential read 42211 25
control file parallel write 16677 5647
SQL> select EVENT ,TOTAL_WAITS,TIME_WAITED from v$system_event where event like '%control%';
EVENT TOTAL_WAITS TIME_WAITED
control file sequential read 42218 25
control file parallel write 16677 5647
SQL> select EVENT ,TOTAL_WAITS,TIME_WAITED from v$system_event where event like '%control%';
EVENT TOTAL_WAITS TIME_WAITED
control file sequential read 42447 26
control file parallel write 16805 5687
SQL> select EVENT ,TOTAL_WAITS,TIME_WAITED from v$system_event where event like '%control%';
EVENT TOTAL_WAITS TIME_WAITED
control file sequential read 42230 25
control file parallel write 16685 5650
#查看日志组信息
SQL> select group#,sequence#,status,bytes/1024/1024 from v$log;
GROUP# SEQUENCE# STATUS BYTES/1024/1024
1 7926 ACTIVE 200
2 7927 ACTIVE 200
3 7928 CURRENT 200
#查看警告日誌
[oracle@DB01 trace]$ pwd
/u01/app/oracle/diag/rdbms/mpcdb01/MPCDB/trace
[oracle@DB01 trace]$ tail -30f alert_MPCDB.log
Checkpoint not complete
Current log# 2 seq# 7930 mem# 0: /u02/oradata/MPCDB/redo02.log
2019-09-10T10:10:54.253084+08:00
LGWR: Standby redo logfile selected to archive thread 1 sequence 7931
LGWR: Standby redo logfile selected for thread 1 sequence 7931 for destination LOG_ARCHIVE_DEST_2
2019-09-10T10:10:54.336747+08:00
Thread 1 advanced to log sequence 7931 (LGWR switch)
Current log# 3 seq# 7931 mem# 0: /u02/oradata/MPCDB/redo03.log
2019-09-10T10:10:55.365997+08:00
Archived Log entry 15816 added for T-1.S-7930 ID 0xc37388c4 LAD:1
2019-09-10T10:11:54.504720+08:00
Thread 1 cannot allocate new log, sequence 7932
Checkpoint not complete
Current log# 3 seq# 7931 mem# 0: /u02/oradata/MPCDB/redo03.log
2019-09-10T10:12:00.667531+08:00
LGWR: Standby redo logfile selected to archive thread 1 sequence 7932
LGWR: Standby redo logfile selected for thread 1 sequence 7932 for destination LOG_ARCHIVE_DEST_2
2019-09-10T10:12:00.773931+08:00
Thread 1 advanced to log sequence 7932 (LGWR switch)
Current log# 1 seq# 7932 mem# 0: /u02/oradata/MPCDB/redo01.log
2019-09-10T10:12:01.463719+08:00
Archived Log entry 15818 added for T-1.S-7931 ID 0xc37388c4 LAD:1
2019-09-10T10:20:50.365148+08:00
LGWR: Standby redo logfile selected to archive thread 1 sequence 7933
LGWR: Standby redo logfile selected for thread 1 sequence 7933 for destination LOG_ARCHIVE_DEST_2
2019-09-10T10:20:50.445373+08:00
Thread 1 advanced to log sequence 7933 (LGWR switch)
Current log# 2 seq# 7933 mem# 0: /u02/oradata/MPCDB/redo02.log
2019-09-10T10:20:51.785213+08:00
Archived Log entry 15820 added for T-1.S-7932 ID 0xc37388c4 LAD:1
