Re: Oracle archive redo log question

Redo contains a before and after image of each transaction. What's the before image of an insert? Practically nothing (the rowid where the insert is about to take place, frankly). What's the after image... er, the entire row. So you should expect redo for inserts to be slightly bigger than the total amount of data inserted for starters. (30 million rowids plus 30 million rows > 30 million rows)

Next, inserts generate some rollback (not much, it's true). But rollback segment updates have to go into the redo stream, too (another 30 million rowids)

Any DML on the data dictionary tables as a result of all that inserting (like acquiring extra extents) also has to be put into the redo stream. And on and on.

As for comparing it to an export, don't. Have you seen the contents of an export file? Open one up in the text editor of your choice (but for God's sake don't save it!). You'll see that most of it is simply text. An export file is a recipe for re-creating things, it's not actually the things themselves. So there's no block headers, no row ids, no rowheaders and so on. Of course, the data is there too, in binary encoded format, but there's none of the physical overhead that Oracle has to employ to store that data meaningfully within the database itself. Exports are always (well, usually) much smaller than the data (as stored in the database) they profess to be exports of. So they are a particularly poor choice of comparison to archives, which have to contain all that overhead.

If you don't like all that redo, may I recommend the hidden parameter that switches redo generation off entirely?! (Tongue firmly in cheek -one day you'll lose the entire database if you do use it, which is why I'm not going to tell you what it is!!)

Regards
HJR <angry_garden_salad_at_yahoo.com> wrote in message news:QG4L6.2504$j65.198392_at_www.newsranger.com...
> HI,
> I'm running oracle 8.1.6 on solaris 7. My database does about 30 million
> inserts a day and if I export all the daily tables the following day, it
 comes
> out to be about 2GB compressed. So, I'm puzzled why if I have my archive
 redo
> logs enabled ( each redo log is about 2GB in size and I have 3 groups
 total )
> how come I produce about 20 or so archive files everyday, each being about
 1GB
> in size after I compress it? So that makes a total of 20GB of data or so
 just
> after 1 day of inserts. I thought maybe the total should be pretty close
 to my
> export of all of the daily tables. What the heck is in these archive redo
 logs
> anyway? A bunch of overhead junk? I thought the redo log switch did not
 happen
> until the redo log filled up. Doesn't say inserting 100M of data into a
> database produce 100M of redo log information? Or does 100M of inserts
 produce
> 1 GB of redo log information. Is there a parameter I have messed up
 somewhere?
> I'm not having any database problems, but it looks like I'm just gonna
 need a
> lot of tapes. thanks in advance....
>
>
Received on Sat May 12 2001 - 02:55:41 CDT