-
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.
What should be my block size, when OS block size is 8k.
Thanks
gandhi
-
Re: Oracle Block size Vs OS block size
Solaris (and most UNIX) OS block size is usually 512 bytes.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Hi Julian,
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?
Thanks
gandhi
-
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
The guys from Quest say big block sizes are better for DBA's . . .
http://www.quest.com/newsletter/v2i5/block.html
-
Lemme be clear with this post
Originally posted by abhaysk
Its irrelevent.
I meant for the statement below...
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?
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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;
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.
Last edited by grjohnson; 08-12-2003 at 08:59 AM.
OCP 8i, 9i DBA
Brisbane Australia
-
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.
-
I ceiling on my OS is 128 * 8192
alter session set db_file_multiblock_read_count = 1000;
Table in 8K Default Block
WAIT #1: nam='db file scattered read' ela= 21993 p1=24 p2=115529 p3=128
WAIT #1: nam='db file scattered read' ela= 21748 p1=24 p2=115657 p3=128
Table n Non-Default 32K tablespace:
WAIT #1: nam='db file scattered read' ela= 18865 p1=11 p2=28807 p3=32
WAIT #1: nam='db file scattered read' ela= 18285 p1=11 p2=28839 p3=32
Exactly the same amount of data returned each read. And teh FTS on the 32K block size table was 6 seconds slower.
OCP 8i, 9i DBA
Brisbane Australia
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|