Full table scan and select count(*)

I was trying to find the best value of db_file_multiblock_read_count in order to improve full table scans.

So to test the effect of this setting, I started issuing the command select count(*) from tabq;
tabq hast several Million rows.

I got different results that surprise me a lot:

PARAMETER	Command			Time
64		as above		12 sec
64		/*+ FULL(TABQ)*/ 	57 sec
32		"			53 sec
16		"			45 sec
8		"			80 sec

Now it seems that executing without any hint DOES NOT uses any full table scan as I would have expected. Using the hint forces FTS and the time results are different. How is it possible to read all rows from a table faster using a method different than full table scan? I always thought that a count(*) would always force a FTS.

Using iostat -Dnx (Oracle 8.1.7.3, Solaris 2.7) I noticed that one I/O is always about 101 KB in size (why?), no matter what is the value of db_file_multiblock_read_count (which was set via "alter session.."). That explains the value of 16 to be the optimal (16x8=128KB, which is near the 101KB per I/O measured).

Anyway, in order to continue my tests, how would I force other access patterns, for example blockwise?

Thanks
Rick Denoire Received on Sun Apr 20 2003 - 18:51:46 CDT