Re: Calculations in maximizing rows per block

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Mon, 14 Aug 2017 17:07:29 -0400
Message-ID: <fc2a3711-03b8-b1ba-cfe0-6d906d0346f0_at_gmail.com>



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

https://jonathanlewis.wordpress.com/2010/06/08/continued-rows/

https://jonathanlewis.wordpress.com/2017/05/19/255-columns-2/

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1830023856761

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1606678460320

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:
> 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
> <mailto: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
> <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

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


--
http://www.freelists.org/webpage/oracle-l
Received on Mon Aug 14 2017 - 23:07:29 CEST

Original text of this message