Re: SQL Statements responsible for huge redo generation

This has been a long time coming, but the answer is "not really"; but you can get some clues.
In most cases redo tends to be generated by inserts, updates and deletes. (Special cases are creating or moving tables/indexes). If the volume of redo for a given statement is so large that it's interesting it tends to do one or more of the following

a) run for a long time
b) run a  large number of times
c) change a lot of data blocks (db block changes)
d) do a lot of physical reads of a table AND all (or most of) its indexes
e) affects a lot of rows

So a starting point would be a scan (through time) of SQL ordered by elapsed time (looking only at insert, update, delete) SQL ordered by executions (ditto)
segments by db block changes
segments by physical read requests
SQL ordered by physical reads (ditto)
SQL ordered by rows_processed -- not reported as such in the AWR, but dba_hist_sqlstat.rows_processed_delta holds the information

Cross checking between the SQL and segments might give you some clues.

Regards
Jonathan Lewis

On Tue, 5 Oct 2021 at 12:55, Goti <aryan.goti_at_gmail.com> wrote:

> Hi All,
>
> Is there a way from AWR historic views we can identify the SQL statements
> /SQL_ID causing huge redo generation?
>
> The database is of version 11.2.0.4.
>
> Thanks,
>
> Goti
>

--
http://www.freelists.org/webpage/oracle-l