Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: DB_FILE_MULTIBLOCK_READ_CNT (Problem restated)
jim nash <jimnash_at_sprintmail.com> wrote in article
<3352EAD5.294D_at_sprintmail.com>...
> The most critical aspect of this database is I/O to a single, large
> (3GB) table. Most queries to this table are SELECT statments involving
> perhaps 25% of the rows. The goal is to increase throughput to this
> table. For the time being, OPS and PQO are not options, but disk
> striping is.
>
> The table will be striped at the file system level (Veritas), assume
> among three disks. Assume a stripe width of 64K. This implies that a
> 192K
> byte read request from Oracle will be transferred by the file system
> into 3 separate 64K byte read requests directed to each of the disks in
> the logical volume.
But because your multiblock read is only 64K, a 192K read request will
translate to 3 separate physical I/Os. Because each 64K read is unlikely
to align exactly with your stripe boundary each 64K request will actually
need to be read from 2 disks. So I think that to scan 192K there will be 6
separate I/O operations across the three disks.
>
> Existing parameters of note are as follows:
>
> * OS max I/O size = 64K bytes
> * DB_BLOCK_SIZE = 4096
> * DB_FILE_MULTIBLOCK_READ_CNT = 16
>
> The question is how DB_FILE_MULTIBLOCK_READ_CNT should be changed, if at
> all, to make best use of striping.
As I understand it, trying to set
DB_FILE_MULTIBLOCK_READ_CNT*DB_BLOCK_SIZE > 64K usually has no effect (an
exception is when you set USE_READV under some OSs). If it were me, I'd
be thinking of increasing my stripe size so that each 64K read would
usually be satisfied by a single disk. A single I/O operation should
return fastest if it only involves a single disk. If more than one disk is
involved then the time taken for the overall IO will be the time taken by
the slowest of the disks. Striping works better at optimising concurrent
I/O rather than for optimising a single IO.
Regards,
-- Guy Harrison gharriso@werple.net.au || http://werple.net.au/~gharriso || 613 419377964Received on Tue Apr 15 1997 - 00:00:00 CDT