Re: Raw partitions / cooked files

In <1812@rand.mel.cocam.oz.au> shaneb@mentat.mel.cocam.oz.au (Shane Booth) writes: >We have OnLine databases (version 5.0) on a Sun SPARCstation 10, one on a >raw partition and one on a cooked file. >For a query involving a join on two tables, where one of the tables is very >small (~10 rows) and we do a sequential scan through the second table >(~40Mb and ~50000 rows), the raw partition takes 2.6 times as long (on >average) as the cooked file to execute the query. I just can't work out why? >It's supposed to be the other way 'round isn't it? Certainly with Oracle what happens when full table scans occur, is that rows only get entered into the database cache if fewer than 8 blocks of data are involved. In all other cases, non-database cache reads are performed, in other words that means perform a physical I/O. This is designed to stop any full table scans from flushing out all that useful high cache hit ratio information. I imagine Informix does something very similar. With raw disk files, this will always result in a physical I/O to the disk device, whereas with a database on a UNIX file system device, there is a random effect due to the UNIX buffer cache. To detect whether the UNIX buffer cache is having an "unintended" effect, monitor the number of physical I/O's that are being performed and compare the results when using cooked and raw files. You could also monitor UNIX buffer cache hit ratios, monitor size of UNIX buffer cache (if dynamic), etc, etc. It is also possible, dependant on the order in which the above query is executed, that even your 10 row table is not in the database cache. This would result in an additional 50000 I/O's being performed, which could relate to the difference in overall execution times. Jon -- -m------- Jon Scott Tel : +44 252 373035 ---mmm----- Pre Sales Support -----mmmmm--- Pyramid Technology Ltd. jscott@pyra.co.uk -------mmmmmmm- Farnborough GU14 7PL, England.