Log File wait caused by too small online log files

Oracle online redo log files record all changes to the database (DML, DDL, or structural changes made to the data by the administrator ), it is used for accidental deletion or downtime to use log files for data recovery to ensure data integrity. However, improper online Log File planning will lead to log-related wait events. The following is an example from the production environment.

 

1. fault description

-- The customer described that the database was used for data synchronization and summarization at night. It has been working well before. As the number of synchronization requests increases, the database has become slower and slower recently. -- Next, we first take the AWR report from the customer's PM to PM the next day. Workload weekly report fordb name dB ID instance inst num release RAC host ------------ hour -------------- hour --- ------------ st990 2152526631 st990 1 10.2.0.3.0 no hour snap ID snap time sessions curs/SESS --------- hour -------- --------- begin snap: 21787 21-feb-13 20:00:22 50 19.5 end snap: 21798 22-feb-13 07:00:47 44 20.0 elapsed: 660.42 (mins) dB Time: 928.06 (mins) -- From the AWR report above, single instance, version 10.2.0.3, there are not many sessions in the period -- elapsed <dB time -- elapsed time = (20130222 07:00:00-20130221 20:00:00) ≈ 660--db time = 928.06, And the runtime environment is a 16-core CPU, 660*16 = 10560, the CPU took 928.06 minutes to process oralce non-idle waiting and computing-as shown in the preceding figure, the entire system is still relatively idle. Let's take a look at top eventtop 5 timed events AVG % Total ~~~~~~~~~~~~~~~~~~ Wait callevent waits time (s) (MS) time wait class hour ------------ ----------- ------ ---------- CPU time 20,673 37.1log file parallel write 27,399 4,797 175 8.6 system I/ocontrol file parallel write 13,428 4,688 349 8.4 system I/olog file sync 19,564 3,795 6.8 commitdb file scattered read 26,651,537 3,439 0 6.2 user I/O -- from the top event, log File-related wait events have obvious performance-Log File parallel write wait events Total wait times 27,399 Total wait time 4,797/60 = 79.95 (min), more than an hour, considerable-followed by the wait related to the control file parallel write and Log File sync events-the following is the detail information of the wait event wait events dB/Inst: st1200/st1200 snaps: 21787-21798-> S-second-> CS-centisecond-100th of a second-> MS-millisecond-1000th of a second-> US-microsecond-limit 00th of a second-> ordered wait time DESC, waits DESC (idle events last) % time Total wait waitsevent waits-outs time (s) (MS)/txn bytes -------------- ------------- --------- Log File parallel write 27,399. 0 4,797 175 1.1 control file parallel write 13,428. 0 4,688 349 0.5log file sync 19,564 10.6 3,795 194 0.8db file scattered read 26,651,537. 0 3,439 0 1, 049.4db file sequential read 6,682,373. 0 1,567 0 263.1log file switch (checkpoint 1,091 92.9 1,019 934 DataPump dump file I/O 0.0. 0 286 0 24.9log file switch completion 332 31.6 183 552 0.0log buffer space 255 47.8 155 608 0.0 free buffer waits 2,409 99.5 120 50 0.1 buffer busy waits 145 62.8 96 664 0.0

2. Fault Analysis

-- The customer described that the database was used for data synchronization and summarization at night. It has been working well before. As the number of synchronization requests increases, the database has become slower and slower recently. -- Next, we first take the AWR report from the customer's PM to PM the next day. Workload weekly report fordb name dB ID instance inst num release RAC host ------------ hour -------------- hour --- ------------ st990 2152526631 st990 1 10.2.0.3.0 no hour snap ID snap time sessions curs/SESS --------- hour -------- --------- begin snap: 21787 21-feb-13 20:00:22 50 19.5 end snap: 21798 22-feb-13 07:00:47 44 20.0 elapsed: 660.42 (mins) dB Time: 928.06 (mins) -- From the AWR report above, single instance, version 10.2.0.3, there are not many sessions in the period -- elapsed <dB time -- elapsed time = (20130222 07:00:00-20130221 20:00:00) ≈ 660--db time = 928.06, And the runtime environment is a 16-core CPU, 660*16 = 10560, the CPU took 928.06 minutes to process oralce non-idle waiting and computing-as shown in the preceding figure, the entire system is still relatively idle. Let's take a look at top eventtop 5 timed events AVG % Total ~~~~~~~~~~~~~~~~~~ Wait callevent waits time (s) (MS) time wait class hour ------------ ----------- ------ ---------- CPU time 20,673 37.1log file parallel write 27,399 4,797 175 8.6 system I/ocontrol file parallel write 13,428 4,688 349 8.4 system I/olog file sync 19,564 3,795 6.8 commitdb file scattered read 26,651,537 3,439 0 6.2 user I/O -- from the top event, log File-related wait events have obvious performance-Log File parallel write wait events Total wait times 27,399 Total wait time 4,797/60 = 79.95 (min), more than an hour, considerable-followed by the wait related to the control file parallel write and Log File sync events-the following is the detail information of the wait event wait events dB/Inst: st1200/st1200 snaps: 21787-21798-> S-second-> CS-centisecond-100th of a second-> MS-millisecond-1000th of a second-> US-microsecond-limit 00th of a second-> ordered wait time DESC, waits DESC (idle events last) % time Total wait waitsevent waits-outs time (s) (MS)/txn bytes -------------- ------------- --------- Log File parallel write 27,399. 0 4,797 175 1.1 control file parallel write 13,428. 0 4,688 349 0.5log file sync 19,564 10.6 3,795 194 0.8db file scattered read 26,651,537. 0 3,439 0 1, 049.4db file sequential read 6,682,373. 0 1,567 0 263.1log file switch (checkpoint 1,091 92.9 1,019 934 DataPump dump file I/O 0.0. 0 286 0 24.9log file switch completion 332 31.6 183 552 0.0log buffer space 255 47.8 155 608 0.0 free buffer waits 2,409 99.5 120 50 0.1 buffer busy waits 145 62.8 96 664 0.0

3. Several Log File events
Log File parallel write

The log file parallel write wait event has three parameters: files, blocks, and requests. in Oracle Database 10g, this wait event falls under the system I/O wait class. keep the following key thoughts in mind when dealing with the log file parallel write wait event.

The log file parallel write event belongs only to the lgwr process.
A slow lgwr can impact foreground processes commit time.
Significant Log File parallel write wait time is most likely an I/O issue

 

Log File sync

The log file sync wait event has one parameter: Buffer #. in Oracle Database 10g, this wait event falls under the commit wait class. keep the following key thoughts in mind when dealing with the log file sync wait event.

The log file sync wait event is related to transaction terminations (commits or rollbacks ).

When a process happens DS a lot of time on the log file sync event, it is usually indicative of too commit commits or short transactions.

 

The log file switch (checkpoint incomplete) Wait event has no wait parameters.

In Oracle Database 10g, this wait event falls under the configuration wait class. Keep the following key thought in mind when dealing with the log file switch (checkpoint incomplete) Wait event.

Excessive log switches caused by small log files and a high transaction Rate

For more information, see Oracle wait interface: A Practical Guide to performance diagnostics & tuning.

 

4. Suggestions and Solutions
A. From the above analysis and log-related wait events, the first thing is to increase the log file size (200-250 MB ). For details, see change redo log size)
B. Too many log file groups. We recommend that you reduce the number to 4-5.
C. store logs to a high-speed disk (currently RAID 5), such as RAID 1.
D. Submit transactions in batches.
E. We recommend that you increase the number of dbwn instances.

 

For more information, see:

For more information about Oracle RAC, see
Use crs_setperm to modify the resource owner and permissions of RAC.
Use crs_profile to manage RAC resource configuration files
RAC database startup and Shutdown
Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Connect Oracle RAC to a specified instance
Oracle RAC load balancing test (combined with server and client)
Oracle RAC server connection Load Balance)
Load Balance)
Non-Default port listening configuration in Oracle RAC (listener. ora tnsnames. ora)
Oracle RAC Listener Configuration (listener. ora tnsnames. ora)
Configure RAC load balancing and Failover
CRS-1006, CRS-0215 fault case
Installing Oracle 10g RAC Based on Linux (RHEL 5.5)
Use runcluvfy to verify the Oracle RAC installation environment

For more information about the basics and concepts of Oracle network configuration, see:
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle listener password (listener)
Configure the Oracle client to connect to the database

For more information about user-managed backup and recovery, see
Oracle cold backup
Oracle Hot Backup
Concept of Oracle backup recovery
Oracle instance recovery
Oracle recovery based on user management
System tablespace management and Backup Recovery
Sysaux tablespace management and recovery
Oracle backup control file recovery (unsing backup controlfile)

For information on RMAN backup recovery and management, see
RMAN overview and architecture
RMAN configuration, Monitoring and Management
Detailed description of RMAN backup
RMAN restoration and recovery
Create and use RMAN catalog
Create RMAN storage script based on catalog
Catalog-based RMAN backup and recovery
RMAN backup path confusion
Use RMAN for recovery from different machine backups (WIN platform)
Use RMAN to migrate a file system database to ASM
Linux RMAN backup shell script
Use RMAN to migrate the database to a different machine

For the Oracle architecture, see
Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle online redo log file)
Oracle Control File)
Oracle archiving logs
Oracle rollback and undo)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)