Changing db_block_size
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Changing db_block_size

  1. #1
    Join Date
    Apr 2003
    Location
    Kuala Lumpur
    Posts
    59

    Changing db_block_size

    I'm running 9i 9.2.0.1 64-bit on Solaris 9. My current parameter is db_block_size=4096.

    My questions:
    1. Can I change the parameter to 8192 in the spfile and restart the DB so that the new value is in effect?
    2. Do my existing datafiles which are created using 4096 bytes block size able to extend to 8192 bytes?
    3. For my environment what is the recommended block size for best performance?

    Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Dublin, Ireland
    Posts
    133

    Re: Changing db_block_size

    Originally posted by richyip
    I'm running 9i 9.2.0.1 64-bit on Solaris 9. My current parameter is db_block_size=4096.

    My questions:
    1. Can I change the parameter to 8192 in the spfile and restart the DB so that the new value is in effect?
    >>> No
    2. Do my existing datafiles which are created using 4096 bytes block size able to extend to 8192 bytes?
    >>> No
    3. For my environment what is the recommended block size for best performance?
    >>> what's your env???? Dataware house?? if not You are on the right track
    Thanks.
    Giani

  3. #3
    Join Date
    Jun 2001
    Posts
    40
    Don't forget that Oracle supports multiple block sizes in the same database in 9i. You have init.ora parameters to specify the size of each buffer cache for blocks of x size e.g. db_2k_cache_size, db_4k_cache_size and so on. Therefore you can create tablespaces that have a different block size to db_block_size.

  4. #4
    Join Date
    Nov 2002
    Location
    Dublin, Ireland
    Posts
    133
    Originally posted by hacketta
    Don't forget that Oracle supports multiple block sizes in the same database in 9i. You have init.ora parameters to specify the size of each buffer cache for blocks of x size e.g. db_2k_cache_size, db_4k_cache_size and so on. Therefore you can create tablespaces that have a different block size to db_block_size.
    Oops.. Thanks, you are right
    We are still fighting for 9i

    Cheers
    Giani

  5. #5
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    We are just about to upgrade to 9i and I'm planning to reconfigure the architecture.
    With variable block sizes I'm right in assumming that tables where many,many rows are required for certain transactions (say a mass update of a certain column) would benefit from having larger Block Sizes? So I'd put these tables in a TBS with a larger block size than the other smaller transaction tables?

    Is there a tried and trusted simple query on the V$views I can run to see if transactions on an object would benefit from a larger block size?

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