|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
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 22th 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;