This example describes a case scenario of
how to find out the root cause when a large amount of redo (or archived logs) were generated on the database in the past.
The techniques introduced works with Oracle 11g and above.

Step 1 : Identify the time frame when the large amount of redo was generated

At instance level, AWR Instance Load Profile shows the change of redo generation rate over a period of time.
As shown below, instance 1 had the highest redo generation rate in time slot of begin snapshot id =3432 and end snapshot id = 3433.

DBspeed : 'Redo size /S' = 'Redo size per second'

Step 2 : Identify the service which was the top contributor to redo generation

At service level, AWR Service Load Profile shows the change of redo generation rate over a period of time.
If the service is defined at granule level for application programs we can tell which application is the top redo generator.

As shown below, service 'ETL_USER' was found out generating the biggest amount of redo at the problem time slot.

Step 3 : Check dimension 'object'

AWR Top Segments by Block Changes lists the objects according to the number of data block changes during a AWR snapshot time window.
The number of data block changes at object level has the direct relationship with the rate of redo generation.

As shown below, table ‘PRODUCT_DIM’ was listed as the top objects which had the biggest number of data block changes at the problem time slot.

Step 4 : Correlate dimension 'object' to dimension 'SQL'

For the object identified above, we need to find out the SQL statements which ‘touch’ them at the problem time slot.

As shown below, two SQL ID have been found whose SQL Plan involved table ‘PRODUCT_DIM’.
One of the SQL (ID=8x0jwwfpfdq5j) was found out performing ‘delete’ on the full table, which can be concluded
as the root cause of high redo generation rate at the problem time slot.