Re: File placement ?????

Janet Linsy wrote:
>
> Hi,
>
> I generage a report.txt using utlbstat/utlestat, and
> use www.oraPerf.com to format it. In the formatted
> report, I saw that "Never split index and data files
> to different sets of disks". Does that mean I'd
> better put datafile and index file on the same disk?
> Since data file and index file are very large, many
> times I put them on different disks. Thank you for
> any advice.
>
> Janet
>

Janet,

Generalizations just plain suck.
(which is in itself, a generalization ...)

They would be so much better off using "it depends ..." instead of "never".
I'd be interested in seeing their assumptions, reasoning and results.

If anything, I would tend to think that splitting indexes and data would help to speed access, provided that *all_other_factors_are_equal*.

Maybe they're big fans of clusters, IOTs and object-type data structures, where the index and data are stored together. Maybe they like having everything in RAM, and reading the entire database into memory at startup.
Maybe they like 1 MB stripe sizes on large RAID 0+1 (or RAID 4) volumes.

If you really think about it - an disk read is a disk read, and it is most likely that either set of disk won't have the heads above the track that you want - for a single set of block requests. Maybe you get lucky, and the blocks are in the buffers on the hard drives.

But imagine that the current execution path is nested loops, and an index is driving the query that is accessing the table by rowid. If no other disk read requests are being answered, with 2 different tablespaces, and 2 different RAID volumes (on different physical drives) - neither set of heads are seeking while procesing the query.

Are you running a few massive batch jobs, or hundreds of concurrent transactions?
What's you OS I/O size, your database block size, are you doing mostly full_table_scans and hash_joins?

maybe all of the blocks needed in the report that you analyzed were cached, and physical disk access was not required.

maybe oraperf wants to push SAME with huge write-back caches of NVRAM.

maybe oraperf really likes WAFL and does not care about an OFA-compliant arrangement.

But I cannot see how the statement "never split index and data files to differnet sets of disks" could hold true. If you can find one case where this is not valid, the statement is invalid.

lets see - what if the my current RAID 5 (he he) volume cannot contain all of the datafiles for my data and index tablespaces datafiles - I cannot put them on different disk sets - because oraperf.com told me to never do so? When adding another volume would mean that no rebuild of the single RAID volume would be required?

Do I have to add drives and rebuild the single RAID 5 volume, instead of adding additional RAID vols?

I would say that the statement "Never split index and data files to different sets of disks" invalid.

Of course, additional context is probably needed. further investigation is probably warranted. Maybe they're right, and I'm just being a naysayer.

I wonder what the "Gods of Disks" would have to say?

Yes, the statement is FALSE, if you can find a SINGLE example where it is not TRUE.

time to find my asbestos shorts ...

Paul

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Paul Drake
  INET: paled_at_home.com

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).