Tuesday, June 5, 2012

How to change your db_block_size - NOT

Was reading this white paper from Virident today:

Accelerating Oracle Databases and Reducing Storage Complexity and Costs. Virident FlashMAX SCM as Primary Storage.

Offered at: http://www.bitpipe.com/data/document.do?res_id=1337280483_183&src=5053040&asrc=EM_BRU_17570135&uid=10956921  (as of 6/5/2012)

They are making a point that switching from 8k block size to 4k would reap performance benefits:

"When Oracle data is stored on FlashMAX devices, reducing Oracle database block size from the default value of 8192 bytes (8KB) to 4096 bytes (4KB) can provide substantial performance benefits in many applications. With HDDs, reading/writing 4KB takes essentially the same amount of time as 8KB as most of the time is spent on moving heads. In contrast, FlashMAX can perform 2x the amount of IOPS with 4KB block size compared to 8KB block size, or the same amount of IOPS at lower latencies."

They offered the steps on how to change your block size:

"You can set this parameter in several different ways:
  1. By adding it to initORACLE_SID.ora file (or changing if the parameter already exists)
  2. By setting the parameter in the SPFILE:
    1. SQL>alter system set db_block_size=4096 scope=spfile;
    2. SQL>shutdown immediate
    3. SQL>startup
  3. By setting it on Initialization Parameters -> Sizing tab of the DBCA"

Last I checked, you could only change the default block size at DB creation. Sure you can have multiple block sizes supported at the Tablespace level (must also specify cache sizes) but it's not as simple as changing a init parameter and bouncing your instance – otherwise, you will see:

ORA-00209: control file blocksize mismatch, check alert log for more info

Hard to take this white paper seriously or did I miss something?

Vendor response received 6/5/2012:

Hello Anthony,

You are bringing up a valid point. The block size change needs to be done before creating the DB . We will fix the whitepaper to clarify this.

Thanks a lot you for your feedback!



  1. Hello Anthony/Artem,

    Is there anyway db_block_size can be changed without DB drop and recreate?


  2. The skilled croupier would pay the player 432 chips / $432,000, that is 392 + forty, with the announcement 1xbet that the payout "is with your wager down". To decide the successful number, a croupier spins a wheel in a single course, then spins a ball in the opposite direction|the other way|the incorrect way} around a tilted round track operating across the outer fringe of the wheel. The winnings are then paid to anybody who has placed a profitable wager.