Re: separate data/inidex

In article <1020683797.19441.0.nnrp-08.9e984b29_at_news.demon.co.uk>, you said (and I quote):
>
> MFT - is a TLA which is a DKI for me.

A bit more than that. It actually can store entire small files. This is where things get very messy in mixed volumes. You can end up with an MFT that fragments itself into the middle of data file fragments, particularly when the free space is short. And then causes all sorts of little messes and keeps compounding the problem as files go volatile.

>
> Even with 'file 1 interleaved with file 2' should the
> performance impact be so great ? Presumably
> the chunks used by NT are a reasonable size
> on a clean file system ?

No major performance impact if the disk is dedicated to Oracle. That is rarely the case in most sites: admins just keep using any extra free disk for temp stuff and that really mucks things up. A full NTFS partition is a sight to behold in terms of fragmentation! Even the MFT itself becomes heavily fragmented as the disk fills up. Remember the old Unix "rule" of always leaving 5% of a file system free? In NTFS we're talking 25%...

>
> I guess if your Oracle blocksize were larger than
> the O/S block size you could find that one split
> Oracle block in 32 (say) could have quite an impact
> on performance.

And why it's so relevant to use a block size that matches the sector size the partition was formatted with. NTFS will never fragment less than that.

> But how much of the performance impact might be
> due to NT systems being used in a somewhat
> cavalier fashion - lots of little files get created and
> dropped over time leaving lots of file fragments,
> and then Oracle files get extended, moved,
> recreated in the gaps. As per your comments
> about sharing partitions with other folders
> and moving big data files.

Absolutely. Safest solution IMHO is to go for raw partitions. That way you're guaranteed no cowboy sysadmin is gonna stuff up an entire partition by using the free space as a temp download for a patch, or something!

> In fact, given that a large fraction of I/O is single
> (Oracle) block - why should it matter if the underlying
> O/S has scattered its blocks completely randomly -
> (assuming the Oracle and O/S blocks align ?)
> (After all, how many people now agree "one extent
> isn't necessary" about data segments - the same
> arguments seem to make sense at the file system
> level).
>

Well, no problems really if you get a nice spread and the disk is guaranteed to be dedicated. IME, that is almost always not the case.

The problems kick in when things start to get split by the NTFS allocation mechanism as the disk gets mix use or re-used and it fills up. A full disk is virtually guaranteed to have a fragmented MFT. Once the MFT itself starts to fragment, you may incur some heavy I/O when not expected at all. IIRC, NT does not cache the MFT separately. It has to contend for system cache with everything else.

So if the MFT itself frags heavily due to mix use and a full disk, you may end up with a serious problem that not even a defragmenter may fix: most of them don't touch the MFT itself. The only solution is to reformat the partition.

But you are correct: assuming you can ensure no one else will use a partition for anything other than Oracle datafiles, then no problems should develop. Provided you don't constantly resize or reallocate data files. And you don't fill up the partition. And you use a sector size that matches the db block. Not a major list, really. Sounds worse than it is.

Of course, keeping other folders out of the partition is the biggest problem there. I vote for raw partitions: guaranteed by "design" not to be interfered with.
:-)

-- 
Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam