Thursday 22 March 2018

Understanding Log Writer Process LGWr in Oracle Database

Quick Overview to Log Writer Process LGWr- Oracle Database

What is LGWr process in Oracle Database? What is the use of LGWr process and how it works?

Log Writer Process LGWr in Oracle Architecture

LGWr process writes the contents of log buffers present inside SGA(shared global area) to the logfiles present on the disk. The logfile marked as current will be used for the same in real time. The main focus is to capture all the change vectors that will change the database structure like DML operations (INSERT, UPDATE, DELETE). Unlike DBWr process, Log writer process is quite active and it makes sure that all the changes getting made in the database are recorded and so it works in nearly real time.

Due to it's nature, this process consumes more I/O which is a bottleneck when it comes to performance because it always keeps on writing the change vectors from memory to disk. Also normally the size of log buffer is small as compared to Database Buffers Cache which acts as a 50-50 thing. If the buffer size is big, it will take more time to complete as the data to write will be more. If the buffer is small, the frequency of it's run will increase but will take less time to complete the writing.

There are normally 3 circumstances when LGWr process is called:

- On Commit
- If the log buffer is 1/3rd full
- Before DBWr process performs its task

Whenever a user commits the data, LGWr process will always run. Like I said, Oracle Architecture is designed to perform this way. Also when the buffer is one third full, it also provokes the LGWr to perform it's duty. And the same goes with the situation where DBWr process is about to call, LGWr process will run first before DBWr performs it's task.

Note: Most of the people assumes that after commit, their data is permanently saved in the database. That is not 100% true. Data gets copied to the Database memory buffers and is written to disk whenever DBWr process runs. Commit operation does not calls the DBWr process to run. 

No comments:

Post a Comment