Customer Portal

Oracle Performance Tuning of Redo Log

Article

Oracle Redo Log Tuning

Most Advisable
Tips and technical articles of Oracle DBA
June, 2012   |  Oracle DBA Tips  |  Author: Gitesh Trivedi

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.


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.


Dbametrix offers high quality of performance tuning results with existing hardware and operating system. We have experts who are very keen to obtain high optimized results within existing hardware to avoid expansion of IT budget. There are lots of another advance methods and techniques available to obtain high performance of Oracle server. Our database dba team provides 24/7 round the clock database services and database troubleshooting using strong response time and affordable remote dba plans. We are offering more than Oracle Support mentioned here. For more details, contact our sales department using contact us and get relax for database issues.


Gitesh Trivedi
- Team Lead
Back to DBA Tips Articles
Copyright Dbametrix Solutions © 2005. All Rights Reserved.
Back to page top