Oracle Block size Vs OS block size - Page 3
DBAsupport.com Forums - Powered by vBulletin
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 32

Thread: Oracle Block size Vs OS block size

  1. #21
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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

  2. #22
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #23
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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.

  4. #24
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #25
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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):
    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,
    Last edited by jmodic; 08-12-2003 at 06:02 PM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #26
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #27
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #28
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    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 )


    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.
    Last edited by grjohnson; 08-13-2003 at 04:27 AM.
    OCP 8i, 9i DBA
    Brisbane Australia

  9. #29
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #30
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    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.
    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
  •  



Click Here to Expand Forum to Full Width