Re: Question about redo size in dump file and view

"lsllcm" <lsllcm_at_gmail.com> wrote in message news:163a2a08-c612-45d8-8ddb-76307b238d40_at_e24g2000vbe.googlegroups.com...
> Hi Friends,
>
> I have a question about redo size generated by insert statement.
>
> Here I want to get redo size of insert/update statement, but the size
> from redo dump is different from view.
>
> Below is my test case.
>
> 1. create test table
> create table t4 ( a number) tablespace users;
>
> 2. insert into one row
> alter system switch logfile;
> insert into t4 values (1);
> commit;
> alter system switch logfile;
>
> 4. dump redo file
> alter system dump logfile 'C:\APP\LCM\ORADATA\ORCL\REDO03.LOG'
>
> 5. get redo size from one session
> SELECT n.name, s.value FROM v$sesstat s, v$statname n WHERE
> s.statistic# = n.statistic# AND n.name = 'redo size' and s.sid = 139;
> NAME
> VALUE
> ----------------------------------------------------------------
> ----------
> redo
> size 0
>
> 6. in same session, run insert statement
> insert into t4 values (1);
> commit;
>
> 7. get redo size of the session in step 5/6 from another session
> SELECT n.name, s.value FROM v$sesstat s, v$statname n WHERE
> s.statistic# = n.statistic# AND n.name = 'redo size' and s.sid = 139;
> NAME
> VALUE
> ----------------------------------------------------------------
> ----------
> redo
> size 140
> redo size for lost write
> detection 0
>
> 8. get length of redo in step 4. The length is 488.
> REDO RECORD - Thread:1 RBA: 0x000142.00000002.0010 LEN: 0x01e8 VLD:
> 0x0d
> SCN: 0x0000.004cdff3 SUBSCN: 1 06/07/2009 18:59:50
>
> 9. conclusion, the length from redo dump is different from view.
>
> Thanks at first
> Jacky

This is odd, because your dump file shows you to be using 11.1.0.6 windows - and when I tried to emulate the test on my laptop the session stats showed redo size = 488 bytes.

From the redo record you posted, the 488 is (roughly): Record header:

    12 byte
Change vector 1

    140 bytes redo for table insert
Change vector 2

    132 bytes of undo header transaction table slot acquisition Change vector 3

    80 bytes to clear the undo header transaction table slot Change vector 4

    132 bytes for recording the undo vector for the table change

(I've obviously got a small error somewhere - but without checking a raw dump file I won't be able to spot where.)

It looks as if your version has (for reasons I do not know) only recorded the basic REDO as the redo size. Maybe the anomaly is due to the way (or timing) when you check the redo size.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html