Migrating db block size from 4k to 16k - possible problems?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Migrating db block size from 4k to 16k - possible problems?

  1. #1
    Join Date
    Jul 2003
    Location
    Sofia, Bulgaria
    Posts
    91

    Migrating db block size from 4k to 16k - possible problems?

    We have several large tables with more than 200 columns, in which probably over 80% ot all rows are chained. After reading what I found in the forum and in RTFM, I decided that it is best to make db block size 16k, now it is 4k. The database is OLTP.

    Can you advise me is this a good decision and what other problems can arise from it? I also think making the INITRANS to 4 or 8?

    All advices greatly appreciated.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: Migrating db block size from 4k to 16k - possible problems?

    Originally posted by stancho
    We have several large tables with more than 200 columns, in which probably over 80% ot all rows are chained. After reading what I found in the forum and in RTFM, I decided that it is best to make db block size 16k, now it is 4k. The database is OLTP.

    Can you advise me is this a good decision and what other problems can arise from it?
    16K is a pretty big block size for pure OLTP applications, IMHO. You may encounter more block contention with bigger blocks.

    Anyway, the only way you can change your block size for the entire database is to export, re-create your database, and re-import which will solve most of your chaining problems anyway. If you are on 9i, you can setup different tablespaces with the 16K block size and use it for some tables and keep the 4k blocks for other tables.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: Migrating db block size from 4k to 16k - possible problems?

    Originally posted by stancho
    We have several large tables with more than 200 columns, in which probably over 80% ot all rows are chained. After reading what I found in the forum and in RTFM, I decided that it is best to make db block size 16k, now it is 4k. The database is OLTP.

    Can you advise me is this a good decision and what other problems can arise from it? I also think making the INITRANS to 4 or 8?

    All advices greatly appreciated.
    Have you checked to seewhether they are truly chained (v. long rows) or actually migrated (pctfree set too low)?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  4. #4
    Join Date
    Jul 2003
    Location
    Sofia, Bulgaria
    Posts
    91

    Re: Re: Migrating db block size from 4k to 16k - possible problems?

    Originally posted by slimdave
    Have you checked to seewhether they are truly chained (v. long rows) or actually migrated (pctfree set too low)?
    We will try to do this in the weekend. The database is version 8.1.7, so we can't have tablespaces with different block sizes :(.
    I think that the problem is from the big rows - tables with 200-300 columns, pctfree and pctused are 10/40 (standart ones).

  5. #5
    Join Date
    Oct 2003
    Posts
    38
    Increase the pctfree, if there are large updates.
    Create temp table as select * from main table.
    Delete all rows in main table and then insert rows from temp table.
    This should solve.
    thanks

  6. #6
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    *sniff sniff* smell that??

    yea, it smells like.....reorg
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

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