Oracle Storage - Cooked To RAW - What are the implications to our Backup Strategy?

[A. Clay Stephenson]

This is really one of those "it depends" answers. How your actual database performs depends much more upon the database design than it does upon the hardware. In your case, since you are already bypassing the buffer cache using the vxfs mount options, I'm going to bet that your gains going to raw i/o are going to be difficult to measure. One mistake often made is that if 1GB of buffer cache is great; 4GB will be even better. It's unusual to find gains above about 1600MB.

It's really time to ask yourself "Would a 2X performance gain be enough?" If the answer to that is no then immediately stop looking at raw vs. cooked i/o, kernel tuning, etc. A 2X gain is almost unheard of except on incredibly badly configured systems and a 0.2X gain is about as much as you can realistically expect -- and that's a stretch as well. If your system is underperforming by large margins then there is really only one place to look -- at the SQL itself. The SQL can yield 10X (or better) improvements.

[Steve Lewis]

Raw has less initial configuration.
Raw forces your DBAs to consider actual tablesizes in advance.
Autoextend on next causes filesystem fragmentation which affects read-ahead on sequential access.
Indexed access on raw requires searching the database index then lseeking to the point in the raw volume.
Indexed access on cooked files requires searching the index to find the logical point in the file, then going thru the filesystem potentially walking thru several inodes (if they aren't cached) to find the point on the raw volume. It may require tuning of your inode cache and will definitely require lots of memory.
Since both methods of storage use database indexes but cooked files also go through what is effectively a filesystem index/cache why don't you just add more resources to your DB or make the large indexes memory-resident?

For a database of your size with that i/o rate, I reckon you are getting close to the comfortable limits of your memory i/o for a single server. You also have the cpus and the network to consider, all competing for memory access. You have gone beyond multiple controller cards and multiple busses and are now into the range of clustered / distributed databases if you want large increases in performance. I am well out of my depth on that sort of stuff, but ACS's comments above are useful too, as always.

[Steven E. Protter]

Depending on your actual use and setup, it is possible to get cooked filesystems to exceed the performance of raw. The data that A. Clay collected suggested the costs don't exceed the less than 10% benefit of cooked filesystems.

You can improve performance a lot more by loading your system with more memory, or even additional processors, though processers mean adding huge licensing costs.

There are so many factors that effect oracle performance. You can even use filesystem tuning techniques to provide block sizes and such that match the way oracle wants.

These benchmark tests should not get you terribly excited. They are set up to provide spectacular results to feed into the marketing machinery of the vendor.

In real life application, the peformance of raw versus cooked filesystem is roughly equal. I run both on my systems and there is very little difference when I collect performance data.

The biggest issue with disk is how fast is it. IF you go raid 10 instead of raid 5 for data, you'll get a massive performance improvement on i/o. If your disks are 15,000 rpm versus 10,000 rpm, you'll get better performance. If your disk admin knows the disk system and lays things out intelligently to avoid contention, you'll get better performance.

Remember also that raw filesystems make backup and recovery more burdensome. Is it worth it? I don't think so in most cases.

Side note: Hope you're enjoying the Harley.

SEP

[Jean-Luc Oudart]

I will go with A.Clay, the Physical layout and hardware should be last resource (unless it has been built on a non-sense model !)

Database design : probably to late at this stage
SQL tuning : a few things can be done here.
- track "bad" SQL and transaction with lon g response time (statspack report, Oracle trace + tkprof). Usually, developers use a much "smaller" database and won't see the performance aspect of new transactions.
- missing index
- statistics not updated
Oracle parameters
OS parameters
Datafile layout , stripping
and finally HW (server & storage)

Also, I suppose if you to validate changes (from a performance prospective), benchmarking would be the ultimate answer.
Unfortunately, you won't be given the opportunity (time, resource, cost) to do it all the time

Regards
Jean-Luc
PS : attached the Unix performance cookbook.