Heat map for Redo Size in SQL Developer...

Submitted by dave on Tue, 03/08/2016 - 11:23

I've gotten a lot of mileage out of my old SQL Developer Archive Log Count Heat Map report that I created in SQL Developer. I was recently asked to estimate the amount of redo that might be sent to a DataGuard standby. Searching The Google for this and I found some methods for calculating Network Bandwidth but I wanted to start with raw redo generated from an instance using information from v$archived_log.

This PL/SQL script can be run on SQLPLUS or SQL Developer to show the last 31 days of Redo generated on your database summarized by hour and day. It uses the highest amount of redo generated per hour as 'pure red' and everything below that is a gradient down to 'pure white' for no redo generated during that hour. I'm not necessarily saying red is bad... I will leave that judgement up to the DBA and folks that know the workload better... But this will give you a way to quickly see peaks and valleys of your log generation. There is also a column on this report for a daily total. This also shows the max value in pure red and gradients down to zero.

Screen shot (click to open in new window):

Source Code
The Source Code is available at this link. To set up in SQL Developer, create a User Defined Report of type "PL/SQL DBMS_OUTPUT". When UDRs of this kind are executed, SQL Developer captures the DBMS_OUTPUT output and renders it as HTML.

Implementation Ideas
For this report I modified my existing "Count" report. I added calculation for Redo Log in GB and updated some formatting as well. I also added a Total column which shows total redo for the day.

-Dave

Tags: 
SQL Developer
Redo
visualization
Heat Map

Comments

Submitted by David Fitzjarrell (not verified) on Tue, 04/05/2016 - 10:51.

Thank you for this, it provides a great way to monitor redo generation and shows the peaks very clearly. I'm using it on production systems as we speak.

Submitted by dave on Tue, 04/05/2016 - 11:30.

Glad it is helpful!

My original version just counted the log switches which was useful to a point... But as you know log switches can (and do!) occur without a completely full log so it could be misleading if wanting to know more about the volume of redo generated on your DB.

Submitted by Rahul Dere (not verified) on Sun, 08/22/2021 - 05:14.

Sir can you please send the archivelog heat map script on email id rahuldere1988@gmail.com

Submitted by dave on Fri, 11/19/2021 - 11:39.

Hi Rahul,

I fixed the source code link, you should be able to access now!

Add new comment