RE: Calculations in maximizing rows per block

There is a decent chance you would benefit by physically storing your table as two tables in a cluster and referenced by a view (make sure you can follow the rules for an insertable view if you don’t want to have to change your applications). I only mention this because of your indication your largest table might be worth some effort.  

Since you have existing data, you can also determine the “most null” columns and you may have knowledge of which columns are used least. Putting the “most null” columns last can save a lot of space if you have them and saves some row reading overhead because Oracle does take a short cut when all the rest are null.  

Putting the least frequently used columns late physically *might* pay off if your queries routinely only reference the columns used rather than “*”.  

Even without clustering, that might mean you don’t need the second row piece for a query.  

I mention this because my experience in the field shows that *mostly* tables with over 255 columns have a lot of unpopulated columns and a lot of rarely or never used columns and sometimes columns that are functionally audit trail columns that could be coordinated as a second logical object. IF you can get rid of them to get under 255, that is plan A.  

RE: pctfree 0, yes, that controls whether a new row will be inserted into the block and does not change the mechanics of whether empty space in the block can be used for row updates that lengthen the row.  

The ASSM space records are in their own blocks of the segment (not in a separate segment as I had BEGGED*) and additional space record blocks are allocated as needed.  

(The order in which blocks are made eligible for new non-direct inserts may be different for ASSM and Freelists. I’m not aware of any official documentation on the freelist order of candidates structure and no utility exists to order or manage blocks that have becomes eligible for insertion. This can generate the “empty front” condition if all rows are coincidentally removed from blocks from the physical beginning of the storage allocation. Deletes mixed with only direct inserts tends to systematically generate this condition, since the direct inserts are placed high.)  

*BEGGED: A reliability and simplicity claim was made when ASSM was previewed to me and I suggested this. I was not effective in persuading that having two segments to maintain was worth the possibility of defining the space management blocks as either a cache object or an object on faster storage than the table rows, nor did the argument of not having to read and discard (or interrupt scanning reads) to pass over the space management blocks prevail. Sigh. Discussion time was up, so I didn’t even mention the possibility of content statistics on blocks of the space management records. Besides, that IS complicated.  

All the stuff mentioned earlier in the thread is probably gold.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: Monday, August 14, 2017 5:07 PM
To: oracle-l_at_freelists.org
Subject: Re: Calculations in maximizing rows per block  

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

--

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

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