Author: Jose Nishith

Page: 1 2

Almost all database administrators pull their hair trying to solve the issue of excessive redo log generation in Oracle. In this article we will discuss the secrets of finding out the sessions responsible for generating lots of redo.

Solution I – AWR Report:

The first solution that comes in our mind is to go through AWR report for DBA_HIST_SESSMETRIC_HISTORY view. Oracle DBA_HIST_SESSMETRIC_HISTORY displays the history of several important session metrics and we hope to get clue to our problem by analyzing the metrics of past sessions. Unfortunately sometimes we find no rows after querying the view.

Solution II – Enabling Oracle Session History Metrics in DBCONSOLE:

This solution suggests you to enable Oracle Session History Metrics in DBCONSOLE. You can set a small value of Redo Writes per second.

Solution III – Calculating Metric for Redo Generated:

If you are not using Enterprise Manager then you will have to calculate the metric information manually. You can calculate the metric for Redo Generated per second with the formula DeltaRedoSize / Seconds where DeltaRedoSize is the difference in select value from v$sysstat where name='redo size” between end and start of sample period and Seconds is the number of seconds in sample period.

Solution IV – Querying Oracle V$SESS_IO View:

When undo is generated in any transaction then it will automatically generate redo as well. This solution examines the amount of undo generated in order to find the sessions that are generating lots of redo.

Oracle V$SESS_IO view lists I/O statistics for each user session. The column BLOCK_CHANGES shows the number of blocks changed by the session. High value for this column means your session is generating lots of redo. You will have to run below query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. If you get large deltas then it shows high redo generation by the session. You use this solution to check for programs that are generating lots of redo when these programs activate more than one transaction.

Solution V – Querying Oracle V$TRANSACTION View:

Oracle V$TRANSACTION is a Data Dictionary view that lists the active transactions in the system. This view can be used to track undo by session. The USED_UBLK column of this view shows the number of undo blocks used and the USED_UREC column shows the number of undo records used by the transaction.

Below query can help you find out the particular transactions that are generating redo. Running the query multiple times and analyzing the delta between each occurrence of USED_UBLK and SED_UREC will help you infer that large deltas indicate high redo generation by the session.

Solution VI – Tracking Undo Generated By All Sessions:

The following statement displays a record for all sessions that have generated undo. It shows both how many undo blocks and undo records a session made.

Solution VII – Collecting Statistics from V$SESSTAT to AWR:

Oracle V$SESSTAT view records the statistical data about the session that accesses it. You will have query the V$STATNAME view in order to find the name of the statistic associated with each statistic number. In this solution we will collects statistics from V$SESSTAT view into our private AWR views.

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Beware of Worse Oracle performance after Migration!!
   Effective Tips on Oracle Flashback!!
   Efficiently Load data using Oracle External Tables!!
   Tips for Oracle 11g SQL Execution Plan Management!!