Re: Does NT write to random locations on disk?

If you created it using the Database Assistant, it created a bunch of strangely small data files with autoextend turned on. When the file autoextends, it grabs the next free hunk of disk, of whatever size you set it to grab. Whether something else is creating ephemeral files that cause these fragments to be separated or what, I can't say -- but all you need to do to defrag is:

1. shutdown the database, so that all file locks are cleared
2. back up :-)
3. run your defrag utility, or format the disk and restore from backup :-)
4. startup the database

Voila, the files are defragged -- for now. If you want to keep them that way you probably want to turn autoextend off and resize the files prudently before taking the time for this procedure, and create new files as needed at a set size, rather than use autoextend.

But it probably doesn't matter -- NT likes files to be defragged, but if you are actually reading random disk-blocks anyway contiguous files won't do a lot of good. If you do a lot of full table scans it will help a bit, if you also defrag your tablespaces. It will also help if you have a lot of free space between the fragments (you won't have to fly over it afterwards), but not much if your disk is mostly full (you didn't say if 1.2G is 80% or 8% :-).

"Boivin, Patrice J" wrote:

> Using a little utility called contig I noticed that the Oracle 8.1.6
> datafiles on my test NT server are quite fragmented, an average of 177
> fragments per file, 118 fragments for the OEM repository datafile. The poor
> utility couldn't do anything with the database files, they are too large
> perhaps.
>
> These were created on an empty server, 8i release 2 went on it after a
> defrag, then the OEM. This is on a hard disk with 1.2G of free space, none
> of the datafiles come close to that.
>
> Why so many fragments? Oracle created those files in one pass, does NT
> write randomly to disk or what?
>
> Won't this have an impact on my NT database's performance?
>
> Oracle says tablespace fragmentation is not a big deal, but fragmentation at
> the OS level matters. Supposedly that's why NT and WndowsXX came with
> defragmentation tools.
>
> ???
>
> Is there a registry setting somewhere to tell NT to write contiguously to
> disk?
>
> TIA
> Patrice Boivin
> Systems Analyst (Oracle Certified DBA)
>
> Systems Admin & Operations | Admin. et Exploit. des systèmes
> Technology Services | Services technologiques
> Informatics Branch | Direction de l'informatique
> Maritimes Region, DFO | Région des Maritimes, MPO
>
> E-Mail: boivinp_at_mar.dfo-mpo.gc.ca <mailto:boivinp_at_mar.dfo-mpo.gc.ca>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Boivin, Patrice J
> INET: BoivinP_at_mar.dfo-mpo.gc.ca
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Mon Mar 12 2001 - 15:49:39 CST