Re: Calculations in maximizing rows per block

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_at_society.servebeer.com> 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
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l