Re: Calculations in maximizing rows per block

Rich,

Not sure if your table of 268 columns might be sparsely-populated or not, but if the possibility exists, you might be interested in verifying using the "carl.sql" procedure from this page HERE <http://evdbt.com/scripts/> and referencing this presentation HERE <http://evdbt.com/download/presentation-three-types-of-table-compression/> and this white paper HERE
<http://evdbt.com/download/paper-three-types-of-table-compression/>. CARL (calculate average row length) will attempt to recalculate how you can optimize your AVG_ROW_LEN by re-organizing the table so that all non-null columns are first in each row, leaving any null columns to trail. Along the way, it will also attempt to calculate AVG_ROW_LEN for the present organization. In order for CARL to work properly, it is best to have analyzed the table for column-level statistics. The presentation and white paper will also have passing mention of intra-block chaining, as well as other types of chaining/row-migration.

With more than 255 columns, you can't take advantage of BASIC or OLTP compression, but any version of Oracle and any wide table can take advantage of "trailing NULLCOLS" compression.

Hope this helps...

-Tim

On 8/14/17 21:09, Mark W. Farnham wrote:
>
> 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
> <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 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