Shulou(Shulou.com)06/01 Report--

Oracle Study-- Oracle wait event (2)

Control file parallel write

When there are multiple copies of control files in the database, Oracle needs to ensure that the information is written to each control file synchronously, which is a parallel physical operation process, because it is called control file writing in parallel. When such an operation occurs, a control file parallel write wait event occurs.

There are many reasons why control files are written frequently, such as:

Log switching is so frequent that the control file information needs to be updated frequently accordingly.

There is a bottleneck in the system IPUBO, causing all IPUBO to wait.

When the system switches logs too frequently, you can consider increasing the size of log files appropriately to reduce the frequency of log switching.

When there are a large number of control file parallel write waiting events in the system, the contention can be alleviated by reducing the number of copies of control files and storing the copies of control files on different physical disks.

This wait event contains three parameters:

Files: the number of control files to be written by Oracle.

Blocks: the number of blocks written to the control file.

Requests: the number of times the control request was written.

Control file sequential read

This wait event occurs when the database needs to read the information on the control file, because the information of the control file is written sequentially, so it is also read sequentially, so it is called the sequential read of the control file. it often occurs in the following situations:

Backup control file

Information sharing of control files between different instances in RAC environment

Read the header information of the control file

Read other information from the control file

This wait event has three parameters:

The file number of the control file that File#: wants to read information.

Block#: reads the starting block number of the control file information.

Blocks: the number of control file blocks that need to be read.

Simulation case:

1. Simulate transaction processing

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 for i in 59 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. View waiting events:

17:05:40 SYS@ test1 > select sid,event,p1,p2,p3,WAIT_TIME from v$session_wait where sid in (1cm41) SID EVENT P1 P2 P3 WAIT_TIME -- 1 latch: redo allocation 1993106152 182 20-1 41 latch: cache buffers chains 2011026752 150-1Elapsed: 0000 latch 00.0117 latch 06 11 SYS@ test1 > r 1* select sid Event,p1,p2,p3 WAIT_TIME from v$session_wait where sid in (1p341) SID EVENT P1 P2 P3 WAIT_TIME -1 log file switch (checkpoint incomplete) 00 0 3 41 buffer busy waits 4 4048 1 94Elapsed: 00 SYS@ test1 00 17 14 14 16 SYS@ test1 > r 1 * select sid Event,p1,p2,p3 WAIT_TIME from v$session_wait where sid in (1p341) SID EVENT P1 P2 P3 WAIT_TIME -1 log file switch (checkpoint incomplete) 00 00 41 log file switch (checkpoint incomplete) 00 0Elapsed: 00 SYS@ test1 00.0217 log file switch 0618 SYS@ test1 > r 1* select sid Event,p1,p2,p3 WAIT_TIME from v$session_wait where sid in (1p341) SID EVENT P1 P2 P3 WAIT_TIME -1 log buffer space 0 000 0 41 log buffer space 0 00 0Elapsed: 00 0Elapsed 0015 00.0117 15 0615 20 SYS@ test1 > r 1* select sid Event,p1,p2,p3 WAIT_TIME from v$session_wait where sid in (1p341) SID EVENT P1 P2 P3 WAIT_TIME -1 log file switch (checkpoint incomplete) 00 0 134 41 log file switch (checkpoint incomplete) 00 3Elapsed: 00 SYS@ test1 00.0117 17 log file switch 06 22 SYS@ test1 > r 1* select sid Event,p1,p2,p3 WAIT_TIME from v$session_wait where sid in (1p341) 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 latch 00.01

3. View events about 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: 0000 400Elapsed 00.021714 07 SYS@ test1 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: 000015 00.0317 5164control file heartbeat 0715 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. Check the alarm log:

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. View log group information

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: 0015 00.03 log group size is too small The number of log groups is small, and log switching is too frequent, resulting in a large number of read and write control files, adjust the size of log groups, and increase the number of log groups.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.