How to find amount of redo generated per a SQL statement?

[Gat%Way]

Hi,

What is the best way to find how much redo has been generated per a SQL
statement?

I know about "redo size" statistics in v$sesstat, but that is redo size
per session, not redo size per a statement.

Thanks.

[hrishy]

Hi

From sql plus you cna do that

sql>set autotrace on

run your sql statement

in the trace you can see that

regards
Hrishy

[Gat%Way]

OK, let have an example: there is a frequent log switching in a
database. A session is generating an excessive amount of redo. I can
easily find which session it is by looking into v$sesstat and "redo
size" statistics.

But, I would like to be able to see for each SQL statement that session
is running the amount of redo entries (in bytes) it generates. Even
approximate amount would be fine.
For example:
SQL STATEMENT REDO SIZE
----------------------- ------------
UPDATE ACC SET A=3; 8460 bytes
SELECT .A FROM DUAL; 0 bytes
DELETE NOM; 469000 bytes
.... etc

Regards,
Gate%way........

[dbaxchangedba]

One way to get the redo sizes at the detail you are looking for is to do a dump on the redo log or the archive log files and look for the following details within the trace files:

A transaction with multiple statements are bundled within the same controlling SCN and so look for the matching SCN values within the redo change vectors. The "KDO Op code:" within the trace file gives the type of operation performed:

IRP / QMI => Insert Operation
URP => Update operation
DRP / QMD => Delete operation

"siz:" entry within the "ktudb redo:" and "ktudh redo:" sections in the trace file gives the actual redo size for the operation.

You can arrive at individual redo sizes at the transaction level by using the "objn:" / "objd:" (object ids) entries in conjunction with the entries mentioned above (SCN, KDO Op code and siz) from the redo log or archive log dump.

Good luck!

http://www.dbaxchange.com