RE: Calculations in maximizing rows per block

From: Jeff Smith <>
Date: Mon, 14 Aug 2017 14:56:18 -0700 (PDT)
Message-ID: <bc0d3e1f-dcfa-43e9-b4ca-c93326120558_at_default>

AKA Ask The Oracle Masters (AskTOM)



From: Tim Gorman [] Sent: Monday, August 14, 2017 5:48 PM
To:; Subject: Re: Calculations in maximizing rows per block


AskTom is maintained by Connor, Chris, and Maria.

On 8/14/17 15:07, Mladen Gogala wrote:

Of course, it is always good to read the classics:





BTW, are they going to rename AskTom to AskConnor?



On 08/14/2017 04:13 PM, Sayan Malakshinov wrote:

Hi Rich,


I've described Intra-block row chainging in details here: HYPERLINK ""

That's better to migrate on 12.2: HYPERLINK ""


On Mon, Aug 14, 2017 at 10:23 PM, Rich J <HYPERLINK ""> 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 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.




Best regards,
Sayan Malakshinov

Oracle performance tuning engineer

Oracle ACE Associate


Mladen Gogala
Oracle DBA
Tel: (347) 321-1217


Received on Mon Aug 14 2017 - 23:56:18 CEST

Original text of this message