How much Redo is written by Oracle

To be completed...

Thanks to Judith Haberstock who notified me of an error on this page.

This article tries to show how you can track how much redo is written by particular SQL statements.

Update December 22^th 2006: Amaome points out that this procedure collects system wide stats, not only for the SQL statement. In order to collect redo size for one session, one has to join v$statname and v$mystat. Thanks for correcting this!

Granting select on

First, we need to grant access to x$kcccp and x$kccle. This tables can be joined in order to find out where the redo log write pointer is.

Run the following Statements as SYS

create view x_$kcccp as select * from x$kcccp;
grant select on x_$kcccp to public;
create public synonym x_$kcccp for x_$kcccp;

create view x_$kccle as select * from x$kccle;
grant select on x_$kccle to public;
create public synonym x_$kccle for x_$kccle;

Creating the procedure HOW_MUCH_REDO and the table HOW_MUCH_REDO_STAMP

We create a procedure HOW_MUCH_REDO that, when called, prints the number of bytes of redo written since the last time this function was called. This procedure needs a table (HOW_MUCH_REDO_STAMP) where it stores the cummulative amount of bytes written. Whenever the procedure is called, it will subtract the cummulative amount in the table from the current cummulative amont and then write the current current amount in the table.

Make sure you don't run the following lines as SYS but as an ordinary user.

create table how_much_redo_stamp (bytes number);

insert into how_much_redo_stamp 
 select 
   le.leseq     * le.lesiz * (select max(lebsz) from x_$kccle) +
   cp.cpodr_bno * (select max(lebsz) from x_$kccle)
 from 
   x_$kcccp cp,
   x_$kccle le
  where
 le.leseq = cp.cpodr_seq
 and bitand(le.leflg,24)=8;

create or replace procedure how_much_redo as
 new_redo_bytes number;
 old_redo_bytes number;
 log_block_size     number;
begin
 select max(lebsz) into log_block_size from x_$kccle;
 select
   le.leseq     * le.lesiz * log_block_size +
   cp.cpodr_bno * log_block_size
   into new_redo_bytes
  from
    x_$kcccp cp,
    x_$kccle le
   where
  le.leseq = cp.cpodr_seq
  and bitand(le.leflg,24)=8;

  select bytes into old_redo_bytes from how_much_redo_stamp;
  dbms_output.put_line ('  New Redo Bytes Written: ' || to_char(new_redo_bytes - old_redo_bytes));

  update how_much_redo_stamp set bytes = new_redo_bytes;
end;
/

exec how_much_redo;

exec how_much_redo;

create table tab_nologging nologging as select * from all_objects;

exec how_much_redo;

create table tab_logging   logging   as select * from all_objects;


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger