Logfile wait caused by too small online log files

Oracle online redo log files record all database changes (DML, DDL, or structural changes made to the data by the Administrator) for accidental deletion or downtime Using Log Files

Oracle online redo log files record all database changes (DML, DDL, or structural changes made to the data by the Administrator) for accidental deletion or downtime Using 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 repository report

DB Name DB Id Instance Inst Num Release RAC Host
---------------------------------------------------------------------
ST990 2152526631 ST990 1 10.2.0.3.0 NO v2011db02p

Snap Id Snap Time Sessions Curs/Sess
---------------------------------------------
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)

-- We can see from the awr report that the number of sessions in a single instance, 10.2.0.3, is small.
-- Elapsed <DB Time
-- Elapsed Time = (20130222 07:00:00-20130221 20:00:00) ≈ 660
-- DB Time = 928.06, the runtime environment is a 16-core CPU, 660*16 = 10560, And the cpu spends 928.06 minutes processing Oralce non-idle waiting and computation.
-- We can see that the entire system is still relatively idle.

-- Next let's take a look at the top event
Top 5 Timed Events Avg % Total
~~~~~~~~~~~~~~~~~~ Wait Call
Event Waits Time (s) (MS) Time Wait Class
---------------------------------------------------------------------------
CPU time 20,673 37.1
Log file parallel write 27,399 4,797 175 8.6 System I/O
Control file parallel write 13,428 4,688 349 8.4 System I/O
Log file sync 19,564 3,795 194 6.8 Commit
Db file scattered read 26,651,537 3,439 0 6.2 User I/O

-- From the top event, log file-related wait events are obviously displayed.
-- Log file parallel write wait event total wait times 27,399 Total wait time 4,797/60 = 79.95 (min), more than an hour, considerable
-- The second is 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-000000th of a second
-> Ordered by wait time desc, waits desc (idle events last)

% Time Total Wait wait Waits
Event Waits-outs Time (s) (MS)/txn
---------------------------------------------------------------------------
Log file parallel write 27,399. 0 4,797 175
Control file parallel write 13,428. 0 4,688 349
Log file sync 19,564 10.6 3,795 194 0.8
Db file scattered read 26,651,537. 0 3,439 0 1,049.4
Db file sequential read 6,682,373. 0 1,567 0 263.1
Log file switch (checkpoint 1,091 92.9 1,019 934 0.0
Datapump dump file I/O 633,458. 0 286 0 24.9
Log file switch completion 332 31.6 183 552
Log 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