Re: Question about redo size in dump file and view
Date: Mon, 8 Jun 2009 09:01:34 -0700 (PDT)
Message-ID: <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
Received on Mon Jun 08 2009 - 11:01:34 CDT