Re: Unix File System versus ROW device

In article <19970625224401.SAA07195_at_ladder02.news.aol.com>, MarkP28665 wrote:

>In a note on this thread was this quote from an Oracle rep>
>given by a guy from Oracle US who
>specialized in storage-related issues. He said that full table scans
>perform significantly less well on raw devices because they don't write
>to the oracle buffer cache - which normally acts as a cache for raw
>device access. <<
>
>This is not exactly true. Oracle data blocks read via a full table scan
>are read into the Oracle buffer pool (cache)! However, they are placed on
>the least recently used end of the LRU chain so they are flushed first
>under the assumption that they are unlikely to be reused. If the query
>will need to access the data block only once then this will not harm
>performance, but where Oracle will need to access the block again later
>(like in a series of nested loops) then this hurts performance big time.
>There is a cache hint that can be used in these situations.
>
>Data blocks read by randon read and index blocks are normally placed on
>the most recently used end of the LRU chain. A physical raw data block
>read is often 50% faster than a standard Unix read. And if you are using
>OS level stripping which tends to distribute your system i/o to appear to
>be randon i/o then the difference can be noticable.

*
This is not exactly true either. I am getting a bit picky here but this is what happens (as far as I remember):

A number of DB_BLOCK_BUFFERS are reserved equal to the size of your DB_FILE_MULITBLOCK_READ_COUNT. These are effectivley LRU as they will be wiped for the next read, but they are only used for table scans. This is to prevent a scan of a large table wiping out all of your DB_BLOCK_BUFFERS.

The only RAW vs LVM test that I have tried were a couple of years ago using AIX, and I discovered that the buffering given by the LVM offset the increase in speed gained by using RAW devices and therefore accessing directly.

The exception to this was the REDO log files. These performed significantly better than their LVM equivalents. I suspect this is due to the fact that they are written sequentially. Placng REDO on raw, stand-alone volumes can give a considerable performance gain.
*

>
>Still, ever system needs to be judged on its own merits.
>
>Just more stuff to complicate our decisions, but I felt the clarification
>was important.

*
One question you must ask youself is, does the extra effort/cost/complexity really produce the benefits I think I need, or can I achieve them by improving the application or some of the aspect of the database. *

>
>
>Mark Powell -- The only advise that counts is the advise that you follow
>so follow your own advise

Neil Chandler -
It is your decision, not mine. Do you own thing. Received on Thu Jun 26 1997 - 00:00:00 CDT