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

[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.

[marist89]

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.

[slimdave]

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)?

[stancho]

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).

[prince_jr]

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

[OracleDoc]

*sniff sniff* smell that??

yea, it smells like.....reorg