Optimum Value for db_file_multiblock_read

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 Received on Mon Aug 11 2003 - 03:30:45 CDT