Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 26 August 2012

Redolog Performance Tuning

The redo log plays a prime role in Oracle database’s core functionality. However it imposes disk I/O for the redo log’s inherent functionality, increased I/O can highly affect the Oracle performance as we know each Oracle database has a redo log. This redo log records all changes made in datafiles and makes it possible to replay SQL statements. Before Oracle changes data in a datafile it writes these changes to the redo log. If something happens to one of the datafiles, a backed up datafile can be restored and the redo that was written since, replied, which brings the datafile to the state it had before it became unavailable.
As Oracle rotates through its redo log groups, it will eventually overwrite a group which it has already written to. Data that is being overwritten would of course be useless for a recovery scenario. In order to prevent that, a database can (and for production databases should) be run in archive log mode. Simply stated, in archive log mode, Oracle makes sure that online redo log files are not overwritten unless they have been safely archived somewhere as we know a database can only be recovered from media failure if it runs under archive log.
Determining amount of generated redolog:
Select n.name, t.value
from v$mystat t join v$statname n
on t.statistic# = n.statistic#
where n.name = ‘redo size’;
Determining optimal redo log size
select optimal_logfile_size from v$instance_recovery;
Oracle database contains redo log groups which in turn hold redo log members. There could be multiple members which belong to a group. When oracle writes data to the group it can parallelize writing operation to the members, failure to write in one of the members of the group does not cause failure in the database as long as one of the members is available for writing.
An archived redo log file is a copy of one of the filled members of a redo log group. The archived log contains a copy of every group created since archiving was enabled.
In 10g the LOG_ARCHIVE_FORMAT : %t_%s_%r stands for %t=thread, %s=log sequence, %r=database incarnation
Redolog main Issues:
- Excessive redo log generation
- Disk Performance.
Oracle background process LGWR is responsible for writing the redolog buffers to disk. All changes that are covered by redo are first written into the log buffer (RAM). Of-course, when a transaction commits, the redo log buffer must be flushed to disk, otherwise the recovery for that commit could not be guaranteed.
Impact of Issues:
§   Higher CPU usage generating redo records and copying them to log buffer consumes CPU resources
§   LGWR process need to work hard if the redo log generation rate is very high.
§   High redo log generation resulted into very frequent log switches and might increase the checkpoint frequency which in turn slow down the overall disk performance
§   In case of archive log mode: 
§   Arch process leads to generate more archive log files. This again introduces additional CPU and disk usage.
§   Even backup of these archive log files uses more CPU, disk and possibly tape resources.
§   Redo entries are copied in to redo buffer under the protection of various latches, and thus excessive redo log leads to increase in contention for redo latches
§   Another potential issue is the disk performance, which is related to the above Issues, if database generates excessive redo logs, it will slowdown the disk performance drastically.
Factors which affect redolog size:
- the number of index
- the number of threads
- the type of SQL statement 
- number of columns
- commit frequency
- cache size 
- Primary key and unique constraint enforcement.
- The referencial integrity constraint.
Solution:
In order to reduce the redo log generation, DBAs can increase the main memory cache size, one should also increase the commit frequency to reduce the disk
i/o.  However, for the faster i/o, DBAs can add more disk chains, for an economic solution, if cost is not the factor then it is suggested to use SSD disks. It is also
recommended that one should not use the RAID 5 for  system reliability as it will marginally lower the disk performance.
References:
Shamsudeen.A.(2008) redo internals and tuning by redo reduction[Electronic version]. retrieved
Emrick.S.E.(2006) The Oracle Redo Generation [Electronic version]. Retrieved May 25,2010 from http://hosteddocs.ittoolbox.com/EE010306.pdf

1 comments:

  1. Thanks brother Shahid for this well knitted article.

    ReplyDelete