Re: Question about redo size in dump file and view
From: lsllcm <lsllcm_at_gmail.com>
Date: Wed, 10 Jun 2009 18:22:03 -0700 (PDT)
Message-ID: <4658bdb7-4dfe-4630-8b82-3240e0cd0eae_at_v23g2000pro.googlegroups.com>
On Jun 10, 3:05 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "lsllcm" <lsl..._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 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 -
Date: Wed, 10 Jun 2009 18:22:03 -0700 (PDT)
Message-ID: <4658bdb7-4dfe-4630-8b82-3240e0cd0eae_at_v23g2000pro.googlegroups.com>
On Jun 10, 3:05 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "lsllcm" <lsl..._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 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 -
Thanks Jonathan Received on Wed Jun 10 2009 - 20:22:03 CDT