Comments in-line.
--
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.
Howard J. Rogers wrote in message
<3c197f11$0$7497$afc38c87_at_news.optusnet.com.au>...
>
>Furthermore, since DBWR does writes in the background, I'd be interested to
>know the actual performance impact of this particular bit of extra I/O. I
>guess you wouldn't agree with Steve's assertion that "It is not critical to
>optimize rollback segment writes because they are performed in the
>background by DBWn."? Or that "Rollback segment I/O is normatively
>write-intensive and largely logically sequential. If the rollback segment
>requirements are large enough, it is possible to obtain largely physically
>sequential I/O by placing each rollback segment in its own tablespace and
on
>dedicated disks."?
The 'done in the background' is a good indication of the potential
variation in the problem. There are people who swear by async I/O,
there are people who swear AT async I/O. Under low stress async
can help, at high stress async can be a nasty overhead.
Note, too, that Steve does also give some indications that his
thinking is biased towards very large systems - which always
require special thinking. In particular the massive separation
into separate tablespaces and multiple dedicated disks for
rollback.
I have experienced of systems where 30% of the I/O was on
the rollback files - because the rollback segments were too
big. On a large-scale, busy, system that volume DID have
an impact on all I/O performed by DBWn. (I nearly put DOES,
but I guess is may not be so true on late 8 and early 9)
>
>As for Steve's statement that "There is no overhead in using large rollback
>segment extents instead of small ones. " -well, this can only be explained,
>given your figures, if he's assuming that the additional writes are
>insignificant in the scheme of things. Agree/Disagree?
>
I agree with your assessment. I assume that he is making the
point that the size of the rollback segment does not affect
the 'logical' mechanisms used to process them.
>I presume from all this that you would hate the 9i algorithm for undo
>segments, and particularly the undo_retention idea (on the grounds, I am
>assuming, that the need to retain undo for, say, three hours, would force
>such undo to be written to disk instead of being merely overwritten in the
>buffer cache???).
In theory, and for a simple, vanilla system, the concept of the automatic
undo fits perfectly with my argument that you should "size the undo as
small as possible but then allow for the longest time required for
read-consistency".
The nice thing about automatic undo is that you set the 'longest time' as a
genuine time estimate; whereas the manual method requires you to set the
'longest time' as an estimate of a number of blocks.
The problem I have (or perhaps had) with automatic undo is that on first
tests it seemed to be very agressive about dropping rollback extents
which had expired so on a busy system with no requirement for long
read-consistency, I found it to be dropping and adding extents with
extreme frequency - which is where you have previously pointed out
your diskloke of OPTIMAL of course - excessive I/O at both operations.
However that was on 9.0.1.0.0, on HP, and I have not observed exactly
the same behaviour on 9.0.1.1.1 on NT. I have yet to discover whether
this is a code difference, or a side-effect of the test not quite behaving
in the same way.
Received on Fri Dec 14 2001 - 03:51:46 CST
