| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Placement of Oracle files on cooked file systems - thought experiment
Hi.
Today as I read a spec sheet for Quantum's Atlas V 18.2 GB Ultra 160/m 7200 RPM SCSI drive, I pondered the radial placement of datafiles in relation to access and throughput rates.
Now, this may seem routine for a Unix SysAdmin, having tools for laying out partitions that allow the specification of the starting (physical) block. With NT (NTFS), I believe that a little trickery could get the job done - bogus datafiles.
given: 8 hard drives - just low level formatted or factory seal broken (no
RAID, JBOD)
an arbitrary number of I/O channels - somewhere between 1 and 8 -
lets reduce this to not involving I/O channels
OFA will be used - 1 type of oracle files per physical drive
drive # files 0 C: OS, binaries, swap, disk copy of backups 1 D: online redo logs, exports (should be raw, but lets assume cooked for this discussion) 2 E: archived redo logs 3 F: system 4 G: rbs 5 H: temp 6 I: indexes 7 J: data - X: cdrom (gotta move that CDROM WAY down there - I save V: -DVD,W: for CD-RW, Y: - Jax, Z: - Zip)
take drive # 3 for instance. System might be 256 MB max. The drive is 16.9 GB
formatted.
I believe (flame me if I'm wrong) that the datafile
<drive>:\oracle\oradata\<db_name>\system01.dbf
will have been created on the innermost tracks of the hard drive. this is most
likely not optimal, as sustained throughput is minimized in the innermost
tracks.
for the aforementioned hard drive, the write rates are listed as 17-29 MB/sec
sustained.
so there is a theoretical improvement of ~ 70 % in sustained throughput by
having the datafile exist on the outermost tracks, versus the innermost tracks
of the hard drive.
One method of producing bogus files:
REM ********** TABLESPACE FOR BOGUS_INDX **********
CREATE TABLESPACE BOGUS_INDX DATAFILE 'I:\Oracle\oradata\DEV\bogus_indx01.dbf'
SIZE 1024M REUSE
MINIMUM EXTENT 128K
DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096
PCTINCREASE 0);
alter tablespace bogus_indx add datafile
'I:\Oracle\oradata\DEV\bogus_indx02.dbf' SIZE 1024M;
alter tablespace bogus_indx add datafile
'I:\Oracle\oradata\DEV\bogus_indx03.dbf' SIZE 1024M;
. . .
So you've now chewed through the first 16 GB of storage on the drive. Now create the real tablespace:
REM ********** TABLESPACE FOR INDX **********
CREATE TABLESPACE INDX DATAFILE 'I:\Oracle\oradata\DEV\indx01.dbf' SIZE 512M
REUSE
AUTOEXTEND ON NEXT 16384K
MINIMUM EXTENT 128K
DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096
PCTINCREASE 0);
This datafile should be located on the outer tracks of the hard drive.
As you need space on the drive, drop datafiles in reverse order or creation.
CAUTION: database and services will have to be shutdown in order to delete
the physical datafile.
space freed by droping datafile will not be available
until database shutdwon and NT service is stopped.
You might ask, why would you want to purchase 18 GB drives if you are only using
< 1 GB?
because Sites only want to have 1 type of spare on hand. 18 GB drives are under
$500 from an OEM other than one with RED t-shirts. A company with a blue logo
offers the Quantum Atlas V 18.2 GB 7200 RPM Ultra 160/m drive for $429, 10K RPM
for $549 (not $795 if its red on the outside of the box).
Less $$ per drive makes it easier to justify that 16 drive server ... with lots of I/O channels
for a configuration with many oracle datafiles on a few (full) hard drives, shouldn't you want to place the most frequently accessed partitions in the centermost track, adjacent to the other most frequently accessed tracks, if you are typically only grabbing 64 K reads? (db_block_size * db_file_multiblock_read_count) = 64 KB (8192 bytes * 8)
track - to - track seek times are typically < 1 millisecond. Full stroke seek times are around 15 ms for that model. Received on Fri Sep 22 2000 - 00:49:31 CDT
 |
 |