Hi Rich,
I've described Intra-block row chainging in details here:
http://orasql.org/2017/02/12/intra-block-row-chaining/
That's better to migrate on 12.2:
http://orasql.org/2017/04/21/intra-block-row-chaining-optimization-in-12-2/
On Mon, Aug 14, 2017 at 10:23 PM, Rich J <rjoralist3@xxxxxxxxxxxxxxxxxxxxx>
wrote:
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
