If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Oracle Block size Vs OS block size

Hi Friends,

I have few doubts about Oracle Block size and OS block size.

Typically, we will set the OS block size as Oracle block size. Solaris supports two block sizes. One is 4k another is 8k, default is 8k. If i go for Transaction processing database, i will have to set db_block_size as 4k. When OS is 8k size. For one OS write, Oracle will have to write twice. There will be performance issue on this.

If, this is the case. Generally, any Unix installation will go with default BLOCK size. SUN and Oracle is working closer. Suppose, i want to go for TRANSACTION processing type.

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.

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.

Originally posted by grjohnson 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.

That's the correct conclusion with respect to default TS and DMRC, but consider what happens if you "up" the multiblock read count -- then you will start leveraging the larger non-default block size.

For a db with system TS of 8k blocks and a DMRC of 32, operations on a TS with 32k blocks are disadvantaged because they are effectively operating with a DMRC of 8. If much of your io is coming from the 32k TS you would do better to raise your DMRC -- say to 64.