The Oracle (tm) Users' Co-Operative FAQ

What's my best strategy for laying out an Oracle database across my available I/O devices ?


Author's name: Jonathan Lewis

Author's Email: Jonathan@jlcomp.demon.co.uk

Date written: 29th Oct 2002

Oracle version(s): 7 - 9

?

Back to index of questions


Historically there have been may directives, hints and tips about Oracle and disc layout like 'separate indexes from data', 'put redo logs on their own discs'. Some suggestions have varied from pointless to dangerous, some are merely optimistic. (How many smaller systems nowadays actually have the nine discs that once was touted as the minimum requirement).

This note is not a 'quick tip' suggestion - it is a generic guideline. You have to think for yourself about spreading I/O in the most effective way to reduce hotspots on your discs - if that happens to contradict one of the old rules of thumb - so be it, there are always special cases.

So - when trying to work out the best (or perhaps least worst) distribution pattern for your database:

a) Be aware of what your database has to do

b) Understand how indexes work and what they cost

c) Understand how UNDO and redo work

d) Understand the possible data access mechanisms that Oracle users

e) Identify the parts of your database that are necessarily read-intensive

f) Identify the parts of your database that are necessarily write-intensive

g.1) For administrative reasons separate parts of your system with different I/O characteristics into separate modules. (One consequence of this is that in general no tablespace will hold tables and indexes because in most cases table access behaviour can be different from index access behaviour)

g.2) For administrative reasons (sub)section your system into units that can be categorised by size.

g.3) For administrative reasons (sub)section your system into units that can be categorised by backup/restore requirements.

g.4) For administrative reasons consider reducing the number of units generated by g1, g2, g3 above.

h) Allocate estimates of physical I/O costs to each unit specified in g4

i) Spread the units from g4 across the available devices with a view to balancing the I/O evenly whilst avoiding significant contention between units of significantly contradictory characteristic behaviour."

.


Further reading: N/A


Back to top

Back to index of questions