DB Block Size
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: DB Block Size

  1. #1
    Join Date
    Aug 2001
    Posts
    134

    Thumbs down

    What are the Advantages and Disadvantages of SMALL BLOCK SIZE (4096) ?
    What are the Advantages and Disadvantages of LARGE BLOCK SIZE (16384) ?

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Advantages of large block size:
    1 Space wastage is very minimum.
    2 Insert will be faster.
    3 Fast Full table/index scan can be done in a less time.

    Disadvantages of large block size:
    1 Unwanted data/index will be cached in SGA.




  3. #3
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330
    Chances for row chaining is more in smaller block size than larger block size..
    Thanigaivasan

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    You are also more likely to do a full table scan for small tables because the entire table might fit into one block. Which I suppose isn't entirely bad.

  5. #5
    Join Date
    Aug 2001
    Posts
    134

    Thanx tamilselvan

    Which one block size 4096/8192/16384 is best option for 1GB physical Ram and 4 processors machine on oracle8i/oracle9i with windows 2000 Adv.Server

  6. #6
    What KIND of database do you have, I mean: what kind of data are there, how many columns are usually in a table (aprox.), do you have large tables (no of rows etc.), how critical is your storage-space (do you can afford some wastage) ... ?
    Obviously, the equilibrium is 4K, but your database is almost never "a typical one", no matter who you are...
    At least tell us how large do you expect to be your database over 1 month, aver 6 months, and over 1 year. This could be the minimal condition to make a vague recomandation. Though not the most proffesional point of start...
    ovidius over!

  7. #7
    Tamilselvan, I do NOT agree with all you did say there, you know?
    Think taking some risks and imprecissions in tuning process! Think you could wrongly set PCTUSED and/or PCTFREE on a large tablespace with a large block size. Do you think it would be "minimum wastage of space" ?
    ovidius over!

  8. #8
    Join Date
    Feb 2002
    Posts
    267
    hi,
    If ur database is in OLTP environment, it is recommended to have small block size. Becoz the queries will extract only small amount of data and it is advisable to keep the data in small blocks. If u have a big block size, unnecessariy u will be reading unwanted data into the cache.

    If u have a DSS environment(Data whearhousing), where ur database is query intensive, it is recommended to have big block size, so that oracle can read more amount of data into the cache in one shot.
    And again if u r table contain objects of big size like graphics files, it is recommended to have a big block size
    Keeping small blocks in this case would mean lot of I/O and CPU overheads.


    As a DBA, it is very important to decide abt the block size when u create the database; since this directly influences the performance. Once u have created the database u cant change the size of the block unless u recreate the database.

    Regards
    Sonia

    [Edited by sonia on 04-08-2002 at 12:37 AM]

  9. #9
    Join Date
    Dec 2001
    Posts
    221
    Ya!! what sonia says is the perfect answer to decide the size of data block.

    For OLTP - the small transactions are more in nos as compared to large blocks requested by queries. and for DSS system - no of transactions are less but the queries requesting the large data are more.

    It is recommended that if you have a VLDB and a HYBRID system , then you should keep two databases on for OLTP ( Small data block size ) which is used for small transactions and other for DSS (Large data block size ) which is only for large requests, i mean only for reports used for MIS or so.

    Even the rest of the answers are correct.
    Santosh Jadhav
    8i OCP DBA

  10. #10
    Join Date
    Aug 2001
    Posts
    134

    Thanx All

    My database is an OLTP , how i set the other SGA parameteres including db_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