Archivelog volume by day

This is a tiny SQL statement that I use every now and then to check the amount of redo generated daily. I like this as some sort of indicator of how much work an OLTP system is actually doing. Not in absolute terms but relative to an earlier point in time. Like when the average amount of archivelogs being generated this month is twice as much as at the same point last year, this would be a starting point in establishing that the system is doing roughly twice as much work now. There are a lot of other (propably more meaningful) metrics like transactions per second. The nice thing about looking at archivelogs is that you don’t need to look at statspack snapshots or AWR data.
Of course, knowing how much redo you generate is also essential when planning for the storage needs of archivelog destinations.

SELECT ROUND(SUM(blocks*block_size)/1024/1024/1024) arc_size,
  TRUNC(first_time) arc_date
FROM v$archived_log
WHERE dest_id=10
GROUP BY TRUNC(first_time)
ORDER BY 2 DESC

One thought on “Archivelog volume by day

  1. Pingback: portrix systems

Leave a Reply

Your email address will not be published. Required fields are marked *