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.
What should be my block size, when OS block size is 8k.
I agree with you, but, when we create a file system with "mkfs" or through any GUI utility like Veritas. The default file system type is 4k or 8k.This is not the issue.
If i have 8k as OS block size and 4k is the Data block size, what could the performance ? Will it go down or not?
Hm, I wouldn't say it is irrelevant. It would be (more or less) irrelevant if you had OS block size of 4K and DB_BLOCK_SIZE of 8K, but not the other way arround as the original poster asked.
If you have 8K OS block size and 4K database block size, then for every single block I/O operation the database requests, the OS will read an extra 4K of data the database hasn't asked for.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
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;
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.
Reply With Quote
Bookmarks