Re: Calculations in maximizing rows per block

From: Tim Gorman <tim.evdbt_at_gmail.com>
Date: Mon, 14 Aug 2017 15:48:28 -0600
Message-ID: <4dc0feb5-c676-52ab-47dd-c3e0ac9e41d3_at_gmail.com>



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:
>
>
> 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:48:28 CEST

Original text of this message