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.

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

Its irrelevent.

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

Quote:

Originally posted by abhaysk
Its irrelevent.

I meant for the statement below...

Quote:

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.

Quote:

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.

Quote:

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.

Quote:

Originally posted by jmodic
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 :

Ofcourse OS will fetch the extra 4K for single block I/O request, but I intended to say "Irrelevent" with respect to Performance Issue, because OS always fetechs in multilples of it Block Size limited my Max I/O per fetch.

So have DB Block_Size 8K or 4K, you will get 8K blocks per request (considering 8K block size as said by poster).
So, the Q of performance is Irrelevent, coz in any case you will get non requested data as well.

Abhay.

Could it be that your extent sizes are limiting the size of the reads?

UNIFORM Initial/Next sizes for both tablespace, and therefore segments, are 4MB.

PCTFREE 0

32K (128 Blocks per extent)
8K (512 Blocks per extent)

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

alter session set db_file_multiblock_read_count=64;

Session altered.

set timi on
Alter session set events '10046 trace name context forever, level 12';

Session altered.

8K Default Tablespace

SQL> select count(*) from test
where snapshot_id_c = 'test';

Elapsed: 00:00:29.86

WAIT #1: nam='db file scattered read' ela= 14379 p1=18 p2=200913 p3=64
WAIT #1: nam='db file scattered read' ela= 14212 p1=18 p2=200977 p3=64
WAIT #1: nam='db file scattered read' ela= 17312 p1=18 p2=201041 p3=64

32K non-Default Tablespace

SQL> select count(*) from test
where snapshot_id_c = 'test';

Elapsed: 00:00:30.01

WAIT #1: nam='db file scattered read' ela= 11546 p1=11 p2=87701 p3=16
WAIT #1: nam='db file scattered read' ela= 11580 p1=11 p2=87717 p3=16
WAIT #1: nam='db file scattered read' ela= 11550 p1=11 p2=87733 p3=16

To find out OS block size, login as sys and run the following query:
select max(lebsz) from sys.x$kccle;

If Veritas is used and block size is 8k, then it is advisable to set your DB_BLOCK_SIZE to 8K. The reason is simple: no extra IO.
When they are NOT equal then OS has to do reconstruct the data block in the memory. This will impair performance.

Tamil

Hmmm.

Do the "elapsed" times not suggest that you are spending less time waiting on the 32K reads than on the 8k reads? How does that correlate to the longer elapsed time for the 32k read?

Confused.

Quote:

Originally posted by tamilselvan
To find out OS block size, login as sys and run the following query:
select max(lebsz) from sys.x$kccle;

If Veritas is used and block size is 8k, then it is advisable to set your DB_BLOCK_SIZE to 8K. The reason is simple: no extra IO.
When they are NOT equal then OS has to do reconstruct the data block in the memory. This will impair performance.

Tamil

I'd like to see some supporting evidence -- i don't believe that any overhead that might be associated with this block reconstruction would be enough to offset the loss in i/o performance from larger block sizes.

Quote:

Originally posted by tamilselvan
When they are NOT equal then OS has to do reconstruct the data block in the memory. This will impair performance.

Tamil

Reconstruct?? Why and How?

BTW, where do we get info of the X$ tables?

Abhay.

Quote:

Originally posted by slimdave
Hmmm.

Confused.

Retested... on a quieter DB

SQL> Alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> select count(*) from test
where snapshot_id_c = 'test';

Elapsed: 00:00:31.40

WAIT #1: nam='db file scattered read' ela= 50510 p1=9 p2=379403 p3=128
WAIT #1: nam='db file scattered read' ela= 50602 p1=9 p2=379531 p3=128
WAIT #1: nam='db file scattered read' ela= 37283 p1=9 p2=379659 p3=128
WAIT #1: nam='db file scattered read' ela= 37624 p1=9 p2=379787 p3=126
WAIT #1: nam='db file scattered read' ela= 56332 p1=10 p2=418315 p3=128
WAIT #1: nam='db file scattered read' ela= 30759 p1=10 p2=418443 p3=128
WAIT #1: nam='db file scattered read' ela= 37364 p1=10 p2=418571 p3=128
WAIT #1: nam='db file scattered read' ela= 30100 p1=10 p2=418699 p3=126
WAIT #1: nam='db file scattered read' ela= 68661 p1=9 p2=379915 p3=128
WAIT #1: nam='db file scattered read' ela= 41933 p1=9 p2=380043 p3=128
WAIT #1: nam='db file scattered read' ela= 42122 p1=9 p2=380171 p3=128
WAIT #1: nam='db file scattered read' ela= 39265 p1=9 p2=380299 p3=126
WAIT #1: nam='db file scattered read' ela= 56861 p1=10 p2=418827 p3=128
WAIT #1: nam='db file scattered read' ela= 31159 p1=10 p2=418955 p3=128
WAIT #1: nam='db file scattered read' ela= 36711 p1=10 p2=419083 p3=128
WAIT #1: nam='db file scattered read' ela= 32579 p1=10 p2=419211 p3=126
.
.
.

STAT #1 id=2 cnt=0 pid=1 pos=1 obj=30464 op='TABLE ACCESS FULL OBJ#(30464) (cr=82547 r=82527 w=0 time=30658401)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.34 30.65 82527 82547 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.34 30.65 82527 82547 0 1

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 58 (FRED)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=82547 r=82527 w=0 time=30658418 us)
0 TABLE ACCESS FULL OBJ#(30464) (cr=82547 r=82527 w=0 time=30658401 us)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: FIRST_ROWS
1 SORT (AGGREGATE)
0 TABLE ACCESS (FULL) OF 'TEST'

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file scattered read 650 0.07 28.21
SQL*Net message from client 2 52.87 52.87

SQL> exit

SQL> Alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> select count(*) from test
where snapshot_id_c = 'test';

Elapsed: 00:00:26.72

WAIT #1: nam='db file scattered read' ela= 34521 p1=12 p2=2949 p3=32
WAIT #1: nam='db file scattered read' ela= 31547 p1=12 p2=2981 p3=32
WAIT #1: nam='db file scattered read' ela= 22113 p1=12 p2=3013 p3=32
WAIT #1: nam='db file scattered read' ela= 23875 p1=12 p2=3045 p3=32
WAIT #1: nam='db file scattered read' ela= 52675 p1=11 p2=2566 p3=32
WAIT #1: nam='db file scattered read' ela= 38911 p1=11 p2=2598 p3=32
WAIT #1: nam='db file scattered read' ela= 35585 p1=11 p2=2630 p3=32
WAIT #1: nam='db file scattered read' ela= 36678 p1=11 p2=2662 p3=31
WAIT #1: nam='db file scattered read' ela= 37421 p1=11 p2=2693 p3=32
WAIT #1: nam='db file scattered read' ela= 36737 p1=11 p2=2725 p3=32
WAIT #1: nam='db file scattered read' ela= 40435 p1=11 p2=2757 p3=32
WAIT #1: nam='db file scattered read' ela= 33564 p1=11 p2=2789 p3=32
WAIT #1: nam='db file scattered read' ela= 47267 p1=11 p2=2822 p3=32
WAIT #1: nam='db file scattered read' ela= 39201 p1=11 p2=2854 p3=32
WAIT #1: nam='db file scattered read' ela= 34944 p1=11 p2=2886 p3=32
.
.
.
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=30464 op='TABLE ACCESS FULL OBJ#(30464) (cr=20097 r=20078 w=0 time=26080640)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 4.05 26.08 20078 20097 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 4.05 26.08 20078 20097 0 1

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 58 (FRED)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=20097 r=20078 w=0 time=26080652 us)
0 TABLE ACCESS FULL OBJ#(30464) (cr=20097 r=20078 w=0 time=26080640 us)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: FIRST_ROWS
1 SORT (AGGREGATE)
0 TABLE ACCESS (FULL) OF 'TEST

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file scattered read 631 0.06 24.22
SQL*Net message from client 2 47.37 47.37

32K block size performed slightly quicker. But DMBC still as expected.

Incidently, for default Buffer_cache we can look at v$bh table for objects in the cache, how can we see what's in the non default cache?

it looks like you might get something from upping the DMBC there. What value were you using? Might like to try raising it by a factor of 4 and see if it eliminates some of those waits.

I'm giving all it's got captain... (in my worst scotty impersonation) :D

DB_FILE_MULTIBLOCK_READ_COUNT = 128 (is the maximum my os can handle)

Incidently, I just re-ran the test's again and they return in the following:

8K - Elapsed: 00:00:31.17
32K - Elapsed: 00:00:40.37

The DB is pretty quiet, but the server get's a bit of a hammering from time to time from other DB's. So, I'm still not convinced of a better read performance on larger non-default blocks sizes.

I was also testing indexes and tables in larger non-default DB's this morning... which was also eyebrow raising, with the blocks of an index scan going into the default db_cache... not the db_32k_cache as I'd have expected.... more testing to go on that one.

Hello grjohnson,

When file system is used, and settung a higher value for DB_FILE_MULTIBLOCK_READ_COUNT will duplicate the IO, because the OS has already read many blocks even though the request is one or 2 blocks.

More over, the wait event WAIT #1: nam='db file scattered read' can not be completely eliminated during a full table scan.
The proper test is reboot the system for every run.

Tamil

Quote:

Originally posted by tamilselvan
When file system is used, and settung a higher value for DB_FILE_MULTIBLOCK_READ_COUNT will duplicate the IO, because the OS has already read many blocks even though the request is one or 2 blocks.

What point are you making? How does this affect what value should be chosen for DB_FILE_MULTIBLOCK_READ_COUNT?

What I meant is duplication of read ached mechanism adopted by both file system and DB_FILE_MULTIBLOCK_READ_COUNT.
The read mechanism available under file system already caches some data in the file system memory, thinking that it can serve to the next request very quickly without reading from the disk, but in reality it is a waste in a OLTP environment where the request for new blocks are at random. Even in DW environment this wastage is very high. Second, by setting the DB_FILE_MULTIBLOCK_READ_COUNT to a higher value, the OS reads more unwanted data than what is required. In both cases, the assumption is subsequent requests may be served if the data requested is available in the cache. That is why first time the user process will wait longer.

Are you saying that the DB_FILE_MULTIBLOCK_READ_COUNT should be set low to avoid this effect, or that it should still be set high.

Quote:

Originally posted by slimdave
I'd like to see some supporting evidence -- i don't believe that any overhead that might be associated with this block reconstruction would be enough to offset the loss in i/o performance from larger block sizes.

If you've got Jonathan Lewis's book "Practical Oracle 8i" somewhere near by, open it on page 192-193 (Chapter 10: "Files, Raw and RAID"). You'll see there in Table 10-1 the performance impact of block size missmatch. In short, with 8K OS block size, the time required to do 1,000 random writes to a file with DB_BLOCK_SIZE=4K was 25.7 seconds, while with DB_BLOCK_SIZE=8K it only took 15.6 seconds for the same operation. I think the points made there by Jonathan are quite sufficient supporting evidence that choosing DB_BLOCK_SIZE smaller than OS block size certanly bears some performance degradation for writes.

For those of you that don't have that book available, let me just quote Jonathan explanation what happens when DBWR has to write a block (let's say 4K database block) to the file (that resides on a file system that has 8K OS block size):

Quote:

1. DBWR passes the Oracle block to the file system.
2. The file system calculates where to write it, but it can't write just 4K.
3. The filesystem therefore reads the surrounding 8k file-system block from disk.
4. Once the file-system block is in memory, the 4K from Oracle is coppied into it.
5. The file system then writes the updated 8K block to disk - whoops! The disk has moved on while the memory update was taking place
6. The file system has to wait for (at least) nother rotation to complete.

On average, a write to the file system that works the first time has to wait for half rotation for the right bit of disk to come under the write heads. If the Oracle block is smaller than the file-system block, a write to the file system has to work in two passes and has to wait on avarage 1.5 rotations.

P.S. It's not very clear from the original poster's question whether he uses "normal" block-based UFS, or maybe some newer extent-based Fast File System, like VxFS. I'm writing this with the assumption that he is talking about normal Unix File System,

Quote:

Originally posted by jmodic
If you've got Jonathan Lewis's book "Practical Oracle 8i" somewhere near by, open it on page 192-193 (Chapter 10: "Files, Raw and RAID"). You'll see there in Table 10-1 the performance impact of block size missmatch. In short, with 8K OS block size, the time required to do 1,000 random writes to a file with DB_BLOCK_SIZE=4K was 25.7 seconds, while with DB_BLOCK_SIZE=8K it only took 15.6 seconds for the same operation. I think the points made there by Jonathan are quite sufficient supporting evidence that choosing DB_BLOCK_SIZE smaller than OS block size certanly bears some performance degradation for writes.

For those of you that don't have that book available, let me just quote Jonathan explanation what happens when DBWR has to write a block (let's say 4K database block) to the file (that resides on a file system that has 8K OS block size):

P.S. It's not very clear from the original poster's question whether he uses "normal" block-based UFS, or maybe some newer extent-based Fast File System, like VxFS. I'm writing this with the assumption that he is talking about normal Unix File System,

That makes sense.

I think the issue that Tamil was raising was the opposite though, wasn't it? I believe he was addressing the situation of DB block size being bigger than os block size.

Ah! Me and my sloppy reading. I thought Tamilselvan was addressing the original question, ie OS block size > DB block size, not the other way around.

With DB block size being larger than OS block size (multiplicated OS block size) I can see no performance drawbacks whatsoever.

Quote:

Originally posted by jmodic
With DB block size being larger than OS block size (multiplicated OS block size) I can see no performance drawbacks whatsoever.

I think a larger drawback would be if DMBC * Block_Size was not a multiple of the segment's EXTENT. Since a read CANNOT cross an extent's boundary. The DB would therfore perform additional reads. e.g table with 3 extents of 128K (total size 384K) with an default block size, on DB configured with an 8k block with 128 DBMC would still require 3 i/o's to perform a FTS. Each read, reading the MAXIMUM, in this case, of 128K, and not 1024K which'd be (DMBC * Block_Size )

Quote:

Originally posted by tamilselvan
Hello grjohnson,

More over, the wait event WAIT #1: nam='db file scattered read' can not be completely eliminated during a full table scan.
The proper test is reboot the system for every run.

Tamil

I'm not trying to "eliminate" 'db file scattered read' WAITS. Actually I think that'd be virtual impossible on a large FTS.

WAIT #1: nam='db file scattered read' ela= 42122 p1=9 p2=380171 p3=128
WAIT #1: nam='db file scattered read' ela= 39265 p1=9 p2=380299 p3=126
WAIT #1: nam='db file scattered read' ela= 56861 p1=10 p2=418827 p3=128

The second line indicates that 2 of the blocks were already found in memory and not scattered into the LRU Cache blocks.

"The proper test is reboot the system for every run." would be a correct but would also be slightly unpredicable, since the first FTS after a DB restart will scatter the table blocks in any previously unused Cache blocks and all subsequent FTS's will use the LRU algorith.

I understand what you are saying regarding testing, although I don't think restarting after each test is a true reflection of a running environment, in which I think the test should be simulated against.

Anyway, my issue is that testing larger non-default blocks sizes in 9i yeilds no performance benefit when performing large FTS's. And therefore tests performed in this manor are inconclusive. That's what makes me suspect the document referenced in this thread, in particualr the section entitled "I/O Time per Block", as potentially an incorrect observation if tested via this method and not by two separated DB's.

Quote:

Originally posted by grjohnson
I think a larger drawback would be if DMBC * Block_Size was not a multiple of the segment's EXTENT. Since a read CANNOT cross an extent's boundary. The DB would therfore perform additional reads. e.g table with 3 extents of 128K (total size 384K) with an default block size, on DB configured with an 8k block with 128 DBMC would still require 3 i/o's to perform a FTS. Each read, reading the MAXIMUM, in this case, of 128K, and not 1024K which'd be (DMBC * Block_Size )

Sure, but I don't consider this to be a "choosing the correct DB_BLOCK_SIZE" problem - it is "choosing the correct storage parameters" problem (ie segment storage sizing, uniform LMT extent sizing, whatever).

Quote:

Originally posted by jmodic
Sure, but I don't consider this to be a "choosing the correct DB_BLOCK_SIZE" problem - it is "choosing the correct storage parameters" problem (ie segment storage sizing, uniform LMT extent sizing, whatever).

Agreed.

In addition to Jonathan Book, I would recommend to read Rama Velpuri's 24x7 book also. Both are really good.

Tamil

Hi Guys, I sent an email to Eyal Aronoff, author of the article discussed in this thread, inviting him to comment on our discussion. Eyal, replyed to me with the following and gave me authorisation to post the email to this thread, for which, I'm incredibly grateful:

--

Hi Greg,

Wow! I did not realize that people are still using this out dated article. It is true though; the HP box we used was limited to 64k read. I had that confirmed by
the file system guy at HP. This limitation was long removed. Now I believe it is
more like 256k. I am not sure that the tests I did were even on Oracle8i let
alone Oracle9.

I will try to answer as many of the points in that thread as I can.

1) Most Oracle database IO time is influenced more these days by the nature of
the IO sub system than by block size. Disk caching technology, advanced
threading in the operating system, advanced file system all contribute to IO
efficiency (or inefficiency). Hack, even the cheapo disk drives that sit inside
the expensive disk array have onboard cache in addition to the big cache of the
array.

2) The original question was about transaction database. In the case of
transaction database full table scans is not the most important issue. Rather
the ability of the disk cache to securely buffer writes. I still feel that most
new OSs and storage array try to optimize performance for 8k block size. So I
think a unified 8K both for file system and database would yield the best
overall results. Also there is a difference between batch intensive system and
on-line transaction type system. In batch systems there is good chance that
during the times of the run the disk cache will be overrun by data. So more
arrays with more cache would do a better job than one large array. Here the IO
speed will be determined mostly by the database ability to free area in the
buffer cache for new block creation. Once again with 8k block the buffer cache
management system will need half as many buffer scans to for the same amount of
data. On-line transaction system is less sensitive to that because the write IO
are consistent and do not come in big batches.

3) I think that for write intensive application the size of the log file block
may be even more important than the database block size. That size is determined
solely by the size of the underlying OS block size (I think it is 4k in raw but
I am not absolutely sure). Here too the type of processing the application is
doing will determine the optimal strategy. If the application has a lot of large
loads, entire database blocks will be written into the log in each log entry. So
a large log block size will be good. If however, the majority of the write are
small single row transactions, Oracle will still pad the block to a complete
block before dispatching it to the disk. This can be very wasteful if the block
size is too big. Once again the issue here is not so much the IO time but its
affect on overwhelming the write cache on the disk.

4) Last, very large IO request are threaded by most operating systems and are
interruptible. If you have a large IO request on a system that is not busy, a
single request will go faster than many small requests. However, if the system
is busy, the IO can be interrupted and go into a wait queue for small IO to
finish first. This I think explains the result you are showing of the 256k read
chunks.

I hope this helps.

Eyal

PS. You are free to post this on the thread

---