Hey all,
I've got a table of 268 columns with a mix of NCHAR and NUMBER columns
in 11.2 (moving to 12.1 soonish?). I'm having issues determining why I
can only fit 2 rows in an 8K block with a PCTFREE of 1, with only 1/4 of
rows packed 3 per block with a PCTFREE of 0. Stats collection says the
average row size is 2667 bytes.
My own sizing calculations based on the dated information from
http://docs.oracle.com/cd/A58617_01/server.804/a58397/apa.htm has some
problems. Among them, it seems that there may be ASSM overhead in the
block that could not have been accounted for in that pre-ASSM doc. This
leads to a few questions:
1) Are the block and record layouts documented in newer versions of
Oracle? Where? The 10g docs mention "intra-block chaining" for rows
with more than 255 columns, but no detail. Nothing even in MOS that I
can see. Seems this is getting _less_ documented with each new
version...
2) With a PCTFREE of 0, does Oracle still use the remaining space in
the block for row mods? It seems that a 4K row in an 8K block leaves a
lot of room for row mods, but again I can't find where or if this is
documented. Yes, I may be able to see this empirically, but I prefer to
use that to back up (or refute!) the docs.
If, after all things considered, packing 3 rows into each block instead
of 2 of our largest physical table should have a decent positive impact
on the business.
Thanks!
Rich