redo entries ordering
Date: Mon, 11 Oct 2010 21:50:57 +0200
I recently came across a strange behaviour of redo log dumps. I think it is connected to multi-threaded redo, but I'm still missing some pieces to understand it properly.
I have a redo log, filled essentially by one transaction:
insert into scott.test1(i,col900) select rownum, rownum from dual
connect by level<=1000;
NB. that the table has actually 1000 columns, with defaults. So the result of every row insert are 4 DRP/IRP pairs (255 being the limit in one single row piece). Supplemental logging is enabled. It's an otherwise idle test database.
So far, so good.
I dump the redo log (alter system dump logfile) and look into the contents. From time to time, however, a strange thing happens: A few redo records are placed out-of-order according to their block address (e.g. a 13.22 Redo on Level1 Bitmap Block + one 5.1+11.2 = DRP+IRP pair). As I understand, this is caused by multi-threaded redo, when Oracle fills multiple buffers at the same time, but does not order the records them when writing them to redo. (It should not(?) be private strands, as they are disabled by the supplemental logging.) And alter system dump logfile is so clever that it reorders redo records in to follow the SCN order.
So the questions are:
1. it seems to strange to me, that one SQL statement can generate such situation - and it's only one (or none at all) DRP/IRP pair in whole redo log. Any clue?
2. and more important: how Oracle knows that there is such situation - in the test redo, the position difference was about 1/3 of the file(!). Some Oracle features need the redo in proper order, but work with online redo as well (Streams, logical standby, Golden Gate) - how do they know they have to wait for more data to get a "misplaced" redo entry?
Thanks for any ideas!