Re: Question about redo size in dump file and view
From: joel garry <joel-garry_at_home.com>
Date: Mon, 8 Jun 2009 10:05:32 -0700 (PDT)
Message-ID: <d71b37dc-19fb-44f8-967c-ca2870eda8a6_at_j32g2000yqh.googlegroups.com>
On Jun 7, 12:10 pm, "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.
Date: Mon, 8 Jun 2009 10:05:32 -0700 (PDT)
Message-ID: <d71b37dc-19fb-44f8-967c-ca2870eda8a6_at_j32g2000yqh.googlegroups.com>
On Jun 7, 12:10 pm, "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.
The TYP:2 (in CHANGE #1 TYP:2 CLS: 1 AFN:4 DBA:0x0100090f OBJ:70582 SCN: ...) means delayed logging?
If I'm understanding this (unlikely :-) the difference would be previous testing on the block.
jg
-- _at_home.com is bogus. "You should use the Web 3.0 app called going outside and investigating the world for yourself" - Marlon BainReceived on Mon Jun 08 2009 - 12:05:32 CDT