Oracle Block size Vs OS block size

[grjohnson]

Originally posted by DaPi
The guys from Quest say big block sizes are better for DBA's . . .
http://www.quest.com/newsletter/v2i5/block.html

Hmmm... what that article doesn't mention is what version of oracle this was tested on. If there were in fact two different databases with two different blocks sizes I'd have an easier time believing the "I/O Time per Block", section. but if it was performed in 9i using two different blocks sizes on different tablesapces and caches, I'd say it's a potential ill-conceived point and incorrect observation.

I have performing numberous test's on multi-size tablespace in 9i myself, and found the results unusual, which could explain the document by Eyal Aronoff, and therefore his tabled results deemed irrelevant.

IF the DB_FILE_MULTIBLOCK_READ_COUNT = 32 as in the Eyal's test case and this was run on an non default 8K_Tablespace, this scattered_read of 8 would expected. i.e. 8 (reported MBRC) x 8192 = 64K. (32 * 2048 = 64K)

When I performed a FTS large table located in a NON-DEFAULT 32K block tablespace and I got the following result.

alter session set db_file_multiblock_read_count = 32;

WAIT #1: nam='db file scattered read' ela= 7451 p1=11 p2=69735 p3=8
WAIT #1: nam='db file scattered read' ela= 9883 p1=11 p2=69743 p3=8
WAIT #1: nam='db file scattered read' ela= 7587 p1=11 p2=69751 p3=8
WAIT #1: nam='db file scattered read' ela= 7858 p1=11 p2=69759 p3=8


Therefore, you'd assume that my OS is only capable of returning 8 blocks per read... but it's not, cause it is in fact 8 * 32768 = 256K

When I test a FTS on the SAME TABLE on an 8K DEFAULT tablespace... I get..

WAIT #1: nam='db file scattered read' ela= 5335 p1=10 p2=299851 p3=32
WAIT #1: nam='db file scattered read' ela= 5588 p1=10 p2=299883 p3=32
WAIT #1: nam='db file scattered read' ela= 5163 p1=10 p2=299915 p3=32
WAIT #1: nam='db file scattered read' ela= 5827 p1=10 p2=299947 p3=32


Indicating I can actually read 32 Blocks in a read. Which is also still 256K (32 * 8K).

So what should I conclude, my OS is only capable of returning 256K per I/O as indicated in the document.

No, what I concluded running 9i, is that when using different MFBR figures in non_default tablespaces the FTS's scattered read will always (from my testing) perform the same number of "reads" that the DEFAULT tablespaces use.

Therefore, in this case, as stated, there isn't an advantage to large block size from a READ performance point of view in 9i using larger sized NON-DAFUALT tablespaces.

The point in the article "The reason was that in our machine, with an 8K block, we could not read more than 8 blocks (64K bytes), regardless of how the MBRC was actually set." is unsubstantialed and may not be the limitation on the OS, and my simply be what is expect in 9i.