Re: Full table scan and select count(*)

Rick Denoire wrote:
>
> 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

An index fast scan is often quicker than a full table scan assuming the table rows are largish (relatively of course to the index).

On Solaris, (I think) the default max OS read allowed is 256k (or 128k). You would need to tweak some OS parameters to increase this. In /etc/system you may need to set:

• the max OS read size
• the max read size for volumes (if using a volume manager)

and if you're using plain old UFS file systems, they probably need to created with a non-default maxcontig parameter to increase the max IO supported.

Others can probably provide the specifics

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"