Re: Optimum Value for db_file_multiblock_read

Neil Phillips wrote:

> Oracle 9.2.0.1.0 Enterprise Edition on SuSe Linux 8
>
> db block size = 8k
>
> Hi
>
> I read in an article on the net that the setting you give the
> parameter db_file_multiblock_read tells Oracle what it thinks it can
> achieve when, amongst other things, it full table scans. I also read
> that what Oracle can actually achieve in terms of multiblock reads can
> be quite different from the value assigned to the multiblock reads
> parameter in v$parameter. I, therefore, want to find out how many
> multiblock reads Oracle can actually achieve in my environment.
>
> The same article went on to say that the way in which to determine the
> number of multiblock reads Oracle can actually achieve is to perform a
> full table scan on a large table and to look at the trace stats;
> paying particular attention to p3 values for db file scattered reads.
> To test this I created a locally managed tablespace with uniform
> extents of 128k (i.e. 16 blocks per extent). I then set my
> db_file_multiblock_read value to 16, switched on event tracing (event
> 10046, level8), ran a sql statement to generate a table scan (SELECT
> /*+ FULL (T) */ COUNT(*) FROM TEST_5 T <the table TEST_5 holds the
> contents of sys.source$>) and looked at the p3 values for
> db_file_scattered_reads in the trace file which was 16, as expected.
>
> I know that multiblock reads can only read data from within a single
> extent, so I wondered if the size of my extents were limiting the
> amount of multiblock reads Oracle was able to do. I wanted to see if
> using larger extent sizes with a greater number of blocks would allow
> Oracle to read in more blocks per read. So, I created a new TS with a
> uniform extent size of 1024k (ie 128 * db_block_size), set the
> db_file_multiblock_read value to 128 (the max value even Oracle will
> allow for this param), switched tracing on and re-ran my sql
> statement. When I looked at the p3 values for db_file_scattered_read
> in the new trace file the value was now 128. Can that be right? Can I
> achieve 128 multiblock reads??? A lot of things I have read seem to
> suggest not.
>
> I have read a bit about this on the net and a lot of the examples
> given by people to determine an optimum size for the multiblock read
> value do not mention the impact small extent sizes will have on the
> number of blocks Oracle can read per extent. Even if you set your
> multiblock read values to 16 and you know (somehow) your system is
> capable of delivering this, if your extents only have 8 blocks in them
> the p3 value reported in the trace file is only ever 8. In which case
> how can you determine the true value for db_file_multiblock_reads.
>
> The issue I have is that many examples used by people to determine the
> optimum value for db_file_multiblock_read don't seem to discuss the
> impact small extent sizes have on the value of p3 reported in a trace
> file. Is there a particular extent size you should use when trying to
> calculate the optimum multiblock read value?
>
> Do p3 values within the trace file show how many reads Oracle was
> actually able to do?
>
> How can I tell exactly how many multiblock reads Oracle is capable of
> so that I can set this parameter correctly? (I have had exactly the
> same results for Oracle 9.2.0.1.0 on Win XP).
>
> Apologies in advance if I have made a stupid mistake in doing this but
> any advice you can give will be greatly appreciated.
>
> Many thanks
> Neil

You can tell Oracle to do lots of things. But almost all operating systems limit a read to 64K.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)