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

When the user (foreground process) commits or rolls back the transaction, oracle needs to write all the redo entry information related to the transaction to red logfile. The foreground process tells lgwr to write out the redo log entries in redo log buffer to redo logfile. Prior to 11.2.0.3, lgwr would notify the foreground process after the write was completed, and the user process would wait until the lgwr notification information was obtained, ensuring that all redo information had been safely written to disk. In 11.2.0.3 and later, by default, the lgwr no longer actively notifies the foreground user process when the log writing is completed, and the foreground process polls the writes of the query log at intervals. The foreground process may see log file parallel write, log file sync wait events in the database before waiting for the write notification message.

From the above process, the Log file sync wait event may be caused by the following reasons:

1. Commit

2. Rollback

3. DDL operation

4. Data dictionary update (seq get)

Oracle recommends that the average wait time for log file sync wait events should be kept within 20ms. In general, the average wait time for this wait event is generally below 5ms. 10ms is not necessarily a problem, it depends on the overall operation of the system.

Usually log file sync events can be adjusted in the following ways:

1. Don't put Redo log on raid5.

two。 Merge small and short transactions and execute them in batches (reduce commit)

3. Use the COMMIT NOWAIT option (provided by 10.2, asynchronous commit, which may result in log loss)

4. Use the NOLOGGING / UNRECOVERABLE option

5. Ensure that the log switching time interval is 15-20 minutes.

The whole process of log file sync can be subdivided into the following stages:

1) user submission

2) notify LGWR to write log and LGWR to wake up

3) LGWR collects redo information and writes it.

4) LGWR write completed

5) LGWR notifies the foreground process

6) receive notification and wake up at the front desk

The write time of steps 2 and 3 can be found in the Instance Activity Stats-redo write time section of awr. Step 3 is accompanied by the log file parallel write wait event. Steps 5 and 6 may cause delays when the system load is high.

The whole process may be represented by the following figure (Master tanel poder's):

About the log file sync discussion:

Fully uncover the log file sync waiting event: http://www.itpub.net/thread-1777234-1-1.html

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.