Re: Checkpoints and Rollback Segments

If you are running tests with large number of serial inserts, with sequential keys, using a single row insert in a pl/sql loop, then you could find that 50 inserts affect a total of two 'data' blocks (one table, one index leaf', whereas 50 inserts could affect half a dozen rollback blocks. Doing insert, delete, commit in the loop would then result in much more rollback being generated, as the rollback entry for a delete is the whole row. Finally, if you have multiple concurrent processes inserting, you may find that you managed to stop Oracle from being able to re-acquire rollback blocks to fill them - leading to very large numbers of nearly empty rollback blocks being generated.

Alternatively, it is also possible that you are observing a side-effect of the 'hot block' mechanism. Rollback blocks may be re-used two or three times in quick succession, resulting in them being promoted to the hot portion of the buffer; so it takes longer for them to age out of the buffer than data blocks. This means that highly scattered data blocks may be written out much more quickly than their rollback blocks by DBWR's 'idle write' activity.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Ethan Post wrote in message ...


>During a very intense checkpoint (after lots of changes) iostat shows rbs

>volume going nuts for a long time then just a blip on the datafiles. I know

>that datafile headers are frozen so I assume changed blocks during

>checkpoint would be written to rbs but thier doesn't seem to be that much

>activity in the db when I am doing this.  If only new changes are being

>written to the rbs what is the long delay in the write to the datafiles?

>One more thought, the changes actually pretty much zero out (i.e. insert

>then delete) so this might be the reason so little is actually written to

>the datafile.

>

>Thanks,

>Ethan

>

>