Re: When many disks are involved in a physical database layout

"Dino Hsu" <dino1_at_ms1.hinet.net> wrote in message news:gu22ht04frm33j5989rqt0kvssn6u2om3e_at_4ax.com...
> Dear all,
>
> In Ch4 Physical Database Layouts of Kevin Loney's book, an ideal
> 22-disk model is presented as:
>
> Disk Contents
> 1 Oracle software
> 2 SYSTEM tablespace
> 3 RBS tablespace
> 4 DATA tablespace
> 5 INDEXES tablespace
> 6 TEMP tablespace
> 7 TOOLS tablespace
> 8 Online Redo log 1
> 9 Online Redo log 2
> 10 Online Redo log 3
> 11 Control file 1
> 12 Control file 2
> 13 Control file 3
> 14 Application software
> 15 RBS_2
> 16 DATA_2
> 17 INDEXES_2
> 18 TEMP_[USER]
> 19 TOOLS_I
> 20 USERS
> 21 Archived redo log destination disk
> 22 Export dump file desination disk
>
> He then tries in successive iterations to reduce the number of disks
> to 17-disk, 15-disk, 12-disk, 9-disk and 7-disk solutions.
>
> The goals for the disk layout, he defines, are as follows:
> 1.The database must be recoverable.
> 2.The online redo log files must be mirrored via the system or the
> database.
> 3.The database file I/O weights must be estimated.
> 4.Contention between DBWR, LGWR, and ARCH must be minimized.
> 5.Contention between disks for DBWR must be minimized.
> 6.The performance goals of the system must be defined.
> 7.The disk hardware options must be known.
> 8.The disk mirroring architecture must be known.
> 9.Disks must be dedicated to the database.
>
> I think the author is trying to cover all kinds of situations, if
> there are less than 7 disks involved, more compromises have to be
> made, and this model would become less useful. Unfortunately, the
> current databases we have all reside on only one disk; control files,
> data files, online redo log files,... everything lives on the same
> disk. It seems (I might be wrong) that on Windows NT there seldom are
> more than 5 disks, and when there are, they could be combined into one
> by RAID. From the practical point of view, I have questions:
> 1.Do you usually use 7 or more 'dedicated' disks for an Oracle
> database?

Not quite sure why the word dedicated is in inverted commas there. Most (like 99% of) production Oracle Servers would be housed on machines that are solely acting as, er, Oracle Servers. The entire machine is dedicated to doing nothing but running Oracle. The worst implementations arise when some spare capacity on a file server or domain controller is hijacked for Oracle's use.

> 2.Do you prefer the disks to be RAID'ed or not?

Depends whether you can afford the extra disks for mirroring etc. If it was important data, you'd splash the dosh for the extra reliability of RAID (of all flavours -except 0 of course) offers -and then Loney's layout kind of goes out of the window (physically, at least).

> 3.Do you DBA's get involvied in the matter of server purchase so that
> hardware spec. are good for the physical layout?

If they are good DBAs in a good shop, of course. No-one in their right mind slaps a machine on a DBAs desk and says 'here, make do with this... oh and by the way, I want 99.999% uptime, and performance of 10,000 transactions per second'.

> 4.The issue of file sizes is not really taken into account in the
> decisions about which tablespaces should be placed on which disks by
> the author. He focuses on reducing and balancing the contentions most
> of the time. This might imply there are a lot of wasted disk space on
> disks where data are small, and because disks must be dedicated to the
> database (rule 9), they will not be used to accomodate other files.

You will notice in his gold-plated 22-disk solution that each of the control files are on their own disk. Now, I may happen to have shares in Seagate, but even I baulk at housing a (at most) 12Mb file on a 9Gb disk!! Which is why I'd suggest looking at his 12-disk compromise instead. His point is sound though: Control Files are written to. If housed on their own disk (and potentially their own controller), those writes will not affect anything else. The rest of his file distribution is trying to achieve the same thing. Introduce the real world, with its budgetary and hardware constraints, and compromises have to be made.

You seem to be reading a lot of books lately! And it's good to seek some mapping of book stuff to real world experience. I can only suggest that the epitome of that sort of process is Jonathan Lewis' 'Practical Oracle 8i' -read that, and you'll see how idealistic theory maps to practical reality. Thoroughly recommended. He has a good discussion on RAID, incidentally -and has even persuaded me that RAID 5 is not the unmitigated disaster it is often portrayed as.

Regards
HJR
>
> Thanks, Dino
>
Received on Sun May 27 2001 - 16:02:16 CDT