Oracle Performance Tuning of Redo Log

by / June, 2012 / Published in Oracle DBA Tips

oracle redo log

Avoid Disk I/O of Redo Log and Archives:

Log Writer process and ARCH background processes are working on same redo-log group sometimes and due to this reason, heavy disk I/O is occurring. To avoid this situation, it is necessary to keep both separately.

Typically, as soon as a log switch happens and LGWR begins writing to the next redo-log group, ARCH jumps into act and begins reading from the prior redo-log group to generate the archived redo-logs. Thus, LGWR is writing and ARCH is interpreting parallel. Obviously, it is necessary to make sure that the Archive log directory is preserved on separate disks than the online redo-logs to avoid controversy between LGWR and ARCH as they concurrently engrave to the online and archived redo-logs, in that order. Additionally, it helps to place redo-log groups onto dissimilar disks in such a fashion that LGWR and ARCH do not compete with each other at any given spot in time.

Oracle DBA Interview Questions

You may have extra of less components options in your situation, and you will most definitely have more than one redo-log participant per team if you have chased the recommendation of Oracle certification. However, prevent carelessness with a bit of brains, you can create certain that LGWR and ARCH live out of each other person's place in your 24/7 managing database environment.

As you conclude the best possible assignment scenario for the redo logs in your surroundings, it is crucial to remember that ARCH is a slower background process than Log Writer process. Throughout peak hours, when there are explodes of write movement, ARCH may be likely to drop very much behind of LGWR. Thus, Log writer may complete multiple log switches, whereas ARCH may still be creating on an earlier redo-log. Even the best possible configuration is not guaranteed to prevent log writer from traversing all redo-log groups and knocking into its own tail, while Archiver process is still working on the tail (here tail is older redo-log group and called as un-archived log group). Total database activity needs to be kept in mind prior to deciding upon the number of redo-log groups to have and the number of disks to spread them across. The main idea is to alternate consecutive redo-log groups across different disks or controllers, so that conflict is minimized in most cases. The greater is the write movement in the database, the greater the number of redo-log groups to introduce, and the greater the number of disks and controllers to swap them across.

Remember one thing more that, Log writer process writes data from redo-log buffer where size absolutely depends on operating system. In Oracle there is no another component depending on operating system block. Only Red-log buffer has dependency of operating system block size. Log writer always copies operating system blocks from buffers and writes those buffers to disk.

If you place redo log and archives in same disk then database will be encountering with heavy disk I/O contention. Every database changes are being logged in redo logs (which is same kind of transaction logs in SQL server) and archives are copy and replica of redo logs. Once single redo log filled up then archiver process is creating replica/copy of redo as archive log. Large database with high OLTP databases are badly impacted of redo-archive disk I/O. To avoid this circumstances and obtain good performance of OLTP database, you need to place redo log and archives in separate disks.

Hiring senior Oracle DBA process is time consuming and costly. After hiring senior remote dba experts, there is no guarantee of stability of him or her. Due to this reason, nowadays company prefers remote DBA services. Company would get professional expert remote DBA support in lowest cost and without risk of stability. Dbametrix is worldwide leader remote DBA experts team and offering uninterrupted stable professional remote dba support to worldwide clients. You can trust on our remote dba work. Contact our sales department for more query without technical specification and requirement SLA.