Re: Question about redo size in dump file and view

"lsllcm" <lsllcm_at_gmail.com> wrote in message news:538e7ed2-bd51-4c08-979f-b330721e61a1_at_r37g2000yqd.googlegroups.com... On Jun 8, 3:10 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "lsllcm" <lsl..._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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle:
> Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users'
> FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html- Hide quoted text -
>
> - Show quoted text -

Hi Jonathan,

I tested it again. When I first test below command

insert into t4 values (1);
commit;

The redo size from view is 96.

When I run second time and third time

The redo size from view is nearby 488. It is very strange. I will give detail test case and environment.

I have another question, how to get the size from dump file in your reply?

Record header: ------how to get it

    12 byte
Change vector 1 ------how to get it

    140 bytes redo for table insert
Change vector 2

    132 bytes of undo header transaction table slot acquisition Change vector 3 ------how to get it

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

    132 bytes for recording the undo vector for the table change

Thanks
Jacky

Change vectors 2 and 4 have a size report.

CHANGE #2 TYP:0 CLS:25 AFN:3 DBA:0x00c00049 OBJ:4294967295 SCN: 0x0000.004cdfd7 SEQ: 2 OP:5.2
ktudh redo: slt: 0x001f sqn: 0x00000655 flg: 0x0012 ---> siz: 132 <---- fbi: 0

Change vector 3 is the commit which I happen to know is about 80 bytes, from previous raw hex dumps, and there's only about 140 bytes left for the insert which (a) sounds reasonable, according to previous experience and (b)
matches your report from v$sessstat.

-- 
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