Re: 2k and 8k block size

Raj,

I don't know if you read my earlier response to this issue. The other concern with a larger block size is the possibility of "buffer busy waits". You mentioned it was an OLTP app with pretty much 1 row change per statement. Increasing the block size will mean more rows per a single block. Consider two sessions trying to update two different rows.

  1.. Session1 tries to update Row1
  2.. The server process gets the block from the disk to the buffer.
  3.. At the very same instant Session2 tries to update Row2 (Note Row2, not Row1, so no locking)
  4.. Row2 happens to be on the same block as Row1.
  5.. Session2 also tries to get the block from file to the disk. Since it is being brought over by Session1, Session2 will have to wait. This is called "buffer busy wait".

So how do you resolve it?

Contrary to some of the articles I have see elsewhere, it's not affected by setting FREELISTS and FREELIST GROUPS. You reduce it by reducing the probablity that two rows accessed will be on the same block. This can be done by

  a.. Smaller block size
  b.. Increasing free space inside a block (PCTUSED, PCTFEE)

Setting higher INITRANS, MAXTRANS, FREELISTS and FREELIST GROUPS will increase the freepace inside a block, making it store less rows. So indirectly they help in reducing the wait event; they don't cure it.

So anyway, that was the problem you might face if you go from 2K to 8K block sizes. However, if it was a DSS type application, then it would have been all right.

By the way, the recomemndation to make the database blocks the same size as OS blocks seems incorrect. The OS blocks are typically 512 bytes, Oracle blocks are hardly that small. What it should have been is Oracle block size should be a "multiple" of OS block size.

I don't know the downside of specifying an OS block size of 2K or 8K. But if it can be done without any problems, I supposethat will help in "split-block phenomenon" during hot backup.

Arup Nanda
www.proligence.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, February 14, 2003 12:29 PM

>
> My question was specific to RAC: With a larger block size, is one not
> increasing the chances of pinging for the blocks? Or I dont need to worry
> about it with Cache fusion? A new worry for me now is that, though the
> block size was changed from 2K to 8K, the recommendation was to keep the
> SGA the same?
>
> Anyways, I am still setting up the database, and I plan to create a few
> tables in tablespaces of varied block sizes, and test it myself. During
> stress testing, I expect to see a lot of waits.
>
> A new question: All recommendations that I have read so far is to make the
> datablock block size equal to the file system block size? But what if my
> datafiles are raw disks?
>
> Thanks
> Raj
>
>
>
>
>
> Charlie_Mengler_at_Hom
> eDepot.com To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent by: cc:
> root_at_fatcity.com Subject: Re: 2k and 8k block size
>
>
> February 14, 2003
> 11:09 AM
> Please respond to
> ORACLE-L
>
>
>
>
>
>
>
> There was a presentation at the recent HOTSOS conference which showed
> a performance gain by placing indexes in tablespaces with a "LARGE"
> blocksize.
>
> HTH & YMMV
>
> HAND!
>
>
>
>
>
> Michael Fontana
>
> <mfontana_at_verio.n To: Multiple
> recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> et> cc:
>
> Sent by: Subject: Re: 2k and 8k
> block size
> root_at_fatcity.com
>
>
>
> 02/10/2003 01:59
>
> PM
>
> Please respond to
>
> ORACLE-L
>
>
>
>
>
>
>
> Reading my oracle 9i new features , there is now something called:
> Multiple Database Block Size Support. In other words, it is no longer
> necessary
> to commit to a single block size for your database. I'd suggest looking
> into this, as
> it may now be possible to test using different blocksizes for different
> tablespaces.
>
>
> At 01:04 PM 2/10/2003 -0800, Rajesh.Rao_at_jpmchase.com wrote:
> An 8.0.6 ops database being migrated to 9i RAC database on Solaris 8.
> The
> 8.0 database had an block size of 2k. Multiblock read count is 8.
> This is
> an OLTP database, all transactions almost always work on a single
> row. None
> of the row sizes exceed the block size. Hardly any chained or
> migrated
> rows. Now, during this migration, it was recommended to change the
> block
> size to 8k. And my concerns are:
>
> 1. Would this not increase pinging across the instances? With 9i and
> interconnect and cache fusion, this should be reduced considerably,
> so, is
> it a mute point?
> 2. This could alter the execution plans of some statements. Would
> some,
> maybe a very few of the statements not prefer a full table scan?
>
> Any other positive or negative effects of this increase in block
> size?
>
> Thanks
> One Paranoid DBA
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: Rajesh.Rao_at_jpmchase.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: orarup_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).