Superb. Thanks very much for that.
Best regards
HJR
--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:1008323382.5819.0.nnrp-14.9e984b29_at_news.demon.co.uk...
>
> 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 - 14:34:29 CST
