Toad World® Forums

Redo Writes IO/sec

On the main dashboard for Spotlight on Oracle RAC there is a Section for Redo Logs, and under it Redo Writes with IO/Sec.

Recently my application performance went way down and the circular arrows were flashing red with high numbers.
However, the number of Redo Log Switches in my Database Alert was normal (3-5 per hour).
So, what does Redo Writes IO/sec on Spotlight really mean?

Per the AWR, Wait Time for Commit (log_file_sync) was very high.
Had a very large shrink table operation in progress at the time, but even when I killed the session, performance did not improve.

  1. Which Oracle version you have (exactly)

  2. could you post somehow AWR?

Hi,

“Redo Writes IO/sec” is the rate of redo writes performed to the Log Writer (LGWR) in an Oracle RAC cluster. It is the rate of total redo writes from all instances participating in the cluster. You can see the details from the balloon help.

By default, the value on this Spotlight control is showing the average over 30 seconds – NOT the current value.

But you can change this setting as below:

  • Right click on this control, and select “Metrics…” to open the Metrics setting form
    redoWriter1.png

  • Select the Metric item which you want to change in the Metric Name list, and select “Don’t average this metric” on the tab page “Metric”

Besides, you can also see the history of the I/O –

Right click on this control, and select “Show History”, you can move and resize the Redo writes window:

redoWriter3.png

redoWriter4.png

What has me confused is if the IO/sec represents the rate of total redo writes from all instances, then when the circular arrows are flashing red and the number is high, then that should translate to lots of redo log switches. Right? But it didn’t in my case. I’m wondering if there was a large amount of redo from my shrink operation that flooded the log_buffer and the flashing red IO/sec relates to the log_buffer, and not redo logs?

Oracle 11.2.0.3

Can’t post AWR

@brian

Thank you…there were a lot of bugs in 10 and 11.1 version in redolog…

@Hightch.Wu ,

are you saying that Spotlight is supporting what Brian ask?

Hi Brian,

As you have noticed Spotlight shows you the number of redo writes per second as a value in this spinner. When an alarm is being raised (causing the control to change color) this means there is a problem in that area of the database. If you click the control it will bring up the help for that particular alarm. Otherwise if you bring up the metrics page as Hightch demonstrated you can see all the alarms defined for a particular control.

In regards to redo writes Spotlight has the following three alarms:

  • Average time to sync a redo log entry
  • Log buffer wait percentage
  • Log switch wait time
    You said that AWR was pointing to a high value of the “log file sync” wait event. Spotlight would raise the “Average time to sync a redo log entry” alarm if this was the case. You also mentioned the value was quite high, so we can assume the reason for this was due to a high amount of redo writes. If there was a large number of log switches occurring you would have instead got the “Log switch wait time” alarm. So depending on which alarm is raised the alarm help will tell you where the problem is. If you see a control flashing red you need to hover over it or click on it to find out exactly what the problem is.

The shrink table operation would have been moving a lot of data around, which would have caused a lot of redo writes. When you killed the session it would have then had to roll back the transaction, which would again led to a lot of redo writes. Unfortunately there is not much you can do in this situation; you need to wait for Oracle to finish rolling back the transaction before performance will return to normal.

David

Thank you David and Hightch.Wu for the excellent explanations. I have a much better understanding of how Spotlight works now.