When a database starts to churn more redo then normal, it is handy to be able to extract the amount of redo over time, to be able to plot this on a graph. The below query allows you to extract this info 🙂
Query for redo generation
Query to obtain the amount of redo generation over time by hour and MB:
set pages 999 lines 400 select to_char(trunc(first_time, 'HH24'), 'DD/MM/YYYY HH24:MI:SS') date_by_hour, sum(round(blocks*block_size/1024/1024)) CHURN_IN_MB from v$archived_log group by trunc(first_time, 'HH24') order by 1 /
Output
This is the output you will get from the query:
SQL> set pages 999 lines 400 SQL> select to_char(trunc(first_time, 'HH24'), 'DD/MM/YYYY HH24:MI:SS') date_by_hour, sum(round(blocks*block_size/1024/1024)) CHURN_IN_MB 2 from v$archived_log 3 group by trunc(first_time, 'HH24') 4 order by 1 5 / DATE_BY_HOUR CHURN_IN_MB ------------------- ----------- 07/12/2018 10:00:00 2 07/12/2018 11:00:00 51 07/12/2018 12:00:00 3731 07/12/2018 13:00:00 10857 07/12/2018 14:00:00 12505 07/12/2018 15:00:00 17493 07/12/2018 16:00:00 187 07/12/2018 17:00:00 173 07/12/2018 18:00:00 185 07/12/2018 19:00:00 137 07/12/2018 20:00:00 159 07/12/2018 21:00:00 155 07/12/2018 22:00:00 157 07/12/2018 23:00:00 183 08/12/2018 00:00:00 154 08/12/2018 01:00:00 184 08/12/2018 02:00:00 179 08/12/2018 03:00:00 179 08/12/2018 04:00:00 172 08/12/2018 05:00:00 177 08/12/2018 06:00:00 174 08/12/2018 07:00:00 172 08/12/2018 08:00:00 177 08/12/2018 09:00:00 175 08/12/2018 10:00:00 175 08/12/2018 11:00:00 220 08/12/2018 12:00:00 221 08/12/2018 13:00:00 218 08/12/2018 14:00:00 216 08/12/2018 15:00:00 214 08/12/2018 16:00:00 212 08/12/2018 17:00:00 208 08/12/2018 18:00:00 213 08/12/2018 19:00:00 207 08/12/2018 20:00:00 205 08/12/2018 21:00:00 205 08/12/2018 22:00:00 202 08/12/2018 23:00:00 228 09/12/2018 00:00:00 202 09/12/2018 01:00:00 238 09/12/2018 02:00:00 212 09/12/2018 03:00:00 227 09/12/2018 04:00:00 213 09/12/2018 05:00:00 206 09/12/2018 06:00:00 221 09/12/2018 07:00:00 222 09/12/2018 08:00:00 216 09/12/2018 09:00:00 220 09/12/2018 10:00:00 216 09/12/2018 11:00:00 217 09/12/2018 12:00:00 162 09/12/2018 13:00:00 163 09/12/2018 14:00:00 163 09/12/2018 15:00:00 160 09/12/2018 16:00:00 158 09/12/2018 17:00:00 159 09/12/2018 18:00:00 161 09/12/2018 19:00:00 157 09/12/2018 20:00:00 157 09/12/2018 21:00:00 153 09/12/2018 22:00:00 153 09/12/2018 23:00:00 176 10/12/2018 00:00:00 150 10/12/2018 01:00:00 174 10/12/2018 02:00:00 168 10/12/2018 03:00:00 167 10/12/2018 04:00:00 169 10/12/2018 05:00:00 162 10/12/2018 06:00:00 168 10/12/2018 07:00:00 166 10/12/2018 08:00:00 160 10/12/2018 09:00:00 162 10/12/2018 10:00:00 141 10/12/2018 11:00:00 144 10/12/2018 12:00:00 142 10/12/2018 13:00:00 141 10/12/2018 14:00:00 142 10/12/2018 15:00:00 169 10/12/2018 16:00:00 146 10/12/2018 17:00:00 173 10/12/2018 18:00:00 177 10/12/2018 19:00:00 175 10/12/2018 20:00:00 7278 10/12/2018 21:00:00 12604 10/12/2018 22:00:00 18154 10/12/2018 23:00:00 6844 11/12/2018 00:00:00 1350 11/12/2018 01:00:00 505 11/12/2018 02:00:00 1183 11/12/2018 03:00:00 508 11/12/2018 04:00:00 1488 11/12/2018 05:00:00 7071 11/12/2018 06:00:00 16453 11/12/2018 07:00:00 7076 11/12/2018 08:00:00 17310 11/12/2018 09:00:00 8063 11/12/2018 10:00:00 12681 11/12/2018 11:00:00 3678 11/12/2018 14:00:00 6026 11/12/2018 15:00:00 15569 11/12/2018 16:00:00 7069 11/12/2018 17:00:00 11772 11/12/2018 18:00:00 10167 11/12/2018 19:00:00 6159 11/12/2018 20:00:00 16450 11/12/2018 21:00:00 4106 11/12/2018 22:00:00 10115 11/12/2018 23:00:00 10355 12/12/2018 00:00:00 3203 12/12/2018 01:00:00 5160 12/12/2018 02:00:00 14468 12/12/2018 03:00:00 6591 12/12/2018 04:00:00 1376 12/12/2018 05:00:00 4053 12/12/2018 06:00:00 7947 12/12/2018 07:00:00 12433 12/12/2018 08:00:00 1434 12/12/2018 09:00:00 663 12/12/2018 10:00:00 1511 12/12/2018 11:00:00 654 12/12/2018 12:00:00 5661 12/12/2018 13:00:00 9817 12/12/2018 14:00:00 10148 12/12/2018 15:00:00 372 12/12/2018 16:00:00 1074 12/12/2018 17:00:00 672 12/12/2018 18:00:00 1094 12/12/2018 19:00:00 391 12/12/2018 20:00:00 2403 12/12/2018 21:00:00 827 12/12/2018 22:00:00 1108 12/12/2018 23:00:00 15575 13/12/2018 00:00:00 17219 13/12/2018 01:00:00 8255 13/12/2018 02:00:00 877 13/12/2018 03:00:00 180 13/12/2018 04:00:00 1782 13/12/2018 05:00:00 5284 13/12/2018 06:00:00 16191 13/12/2018 07:00:00 6251 13/12/2018 08:00:00 14533 13/12/2018 09:00:00 8138 13/12/2018 10:00:00 12629 13/12/2018 11:00:00 9701 13/12/2018 12:00:00 9869 13/12/2018 13:00:00 9554 13/12/2018 14:00:00 7106 13/12/2018 15:00:00 15094 13/12/2018 16:00:00 8622 13/12/2018 17:00:00 671 13/12/2018 18:00:00 1094 13/12/2018 19:00:00 370 13/12/2018 20:00:00 2332 13/12/2018 21:00:00 421 154 rows selected. SQL>
The above output can then be used to create a pivot chart in Excel 🙂
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Thanks
Zed DBA (Zahid Anwar)
Zed DBA's Oracle Blog 