Comments in-line.
--
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Daniel Morgan wrote in message <3CC5B90A.7F56053B_at_exesolutions.com>...
>
>I'm not asking for black and white. Obviously that does not exist here any
more
>than I can look at a SQL statement and guess in advance its cost.
>
I wouldn't claim to be able to guess the CBO-calculated cost - but in lots
of the more straightforward cases I make it my business to estimate
reasonably accurately the real cost in logical and physical I/O of
high-profile
statements in the very early stages of design. That may be why I can appear
to be so casual about the issue of deciding on balancing I/O.
>
>I have worked numerous times with consultants from Oracle's own consulting
>division and not once have I ever seen one of them question the "rule"
about
>separating tables and indexes. And that goes for consultants from lots of
other
>name companies.
But did you ask them every single time if they were doing it because
it improved performance or if they were doing it because it was an
administrative benefit for monitoring activity and trapping errors.
Perhaps if you had asked, some of them would have explained that it
had little impact on performance.
>
>Let me try proposing a rule and I'll let you agree or disagree.
>
>The rule:
>The goal is to balance I/O across as many separate physical devices, hard
disks
>and controller channels, as possible.
Stop here, and you're doing okay.
>One method of doing this is to separate
>system, table, index, temp, and rollback files onto separate physical
devices. If
>performance problems exist it may be due to the way the data is being
utilized.
>Verify the nature of the performance issue and if it relates to I/O
balancing
>you may need to look at modifying where objects and datafiles are placed in
>order to optimize performance.
Another method is to stick files anywhere you fancy, then if
performance problems exist it may be ..... ;)
More significantly: SAME (as previously mentioned) is the
best 'low-skill', 'low-risk' option around.
If you want a simple starting approach for unexceptional
database systems, and you absolutely cannot stripe
your filesystems :-
Reserve drives for redo
Reserve drives for archived redo
Create one rollback tablespace per device
Create a couple of files per drive for a TEMP tablespace
Allow about two to four tablespaces per drive for tables
Allow about two to four tablespaces per drive of indexes
(The separation of indexes and tables is for admin
NOT performance reasons)
Allocate segments to tablespaces in a way that you feel
will balance physical I/O, and map objects of about the
same size to the same tablespace.
Start looking for special cases, e.g. a very large table
with highly random I/O requirements, which may need
its own tablespace spread at one file per device.
And before you do all that, try to work out when you
are going to need to grow into some new disks and
figure out how you can fit them into the pattern
without rebuilding the entire database.
Received on Tue Apr 23 2002 - 16:15:54 CDT
