RE: Calculations in maximizing rows per block

AKA Ask The Oracle Masters (AskTOM)

From: Tim Gorman [mailto:tim.evdbt_at_gmail.com] Sent: Monday, August 14, 2017 5:48 PM
To: gogala.mladen_at_gmail.com; oracle-l_at_freelists.org 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:

HYPERLINK "https://urldefense.proofpoint.com/v2/url?u=https-3A__jonathanlewis.wordpress.com_2010_06_08_continued-2Drows_&d=DwMDaQ&c=RoP1YumCXCgaWHvlZYR8PQcxBKCX5YTpkKY057SbK10&r=Z7TxYgTRa8b2Qmi2Gp6fvdZIJHBIir8RWfL4YTvXYOM&m=TphpddPdAYuj3IGGja3ONYc9wYANWAS4lJJOAua_m0s&s=ORNjphfU8Cezh38P4-uJ4DrfazwGkosh9OfyKSlb-LE&e="https://jonathanlewis.wordpress.com/2010/06/08/continued-rows/

HYPERLINK "https://urldefense.proofpoint.com/v2/url?u=https-3A__jonathanlewis.wordpress.com_2017_05_19_255-2Dcolumns-2D2_&d=DwMDaQ&c=RoP1YumCXCgaWHvlZYR8PQcxBKCX5YTpkKY057SbK10&r=Z7TxYgTRa8b2Qmi2Gp6fvdZIJHBIir8RWfL4YTvXYOM&m=TphpddPdAYuj3IGGja3ONYc9wYANWAS4lJJOAua_m0s&s=dBYEKrozLfOE1pjMwptCtFw484DBFKbo2OomON5_0ko&e="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: HYPERLINK "https://urldefense.proofpoint.com/v2/url?u=http-3A__orasql.org_2017_02_12_intra-2Dblock-2Drow-2Dchaining_&d=DwMDaQ&c=RoP1YumCXCgaWHvlZYR8PQcxBKCX5YTpkKY057SbK10&r=Z7TxYgTRa8b2Qmi2Gp6fvdZIJHBIir8RWfL4YTvXYOM&m=TphpddPdAYuj3IGGja3ONYc9wYANWAS4lJJOAua_m0s&s=myhSJhKV1FQZE8Iv305b4oYi9xkR4z-9DehjQyHCQd4&e="http://orasql.org/2017/02/12/intra-block-row-chaining/

That's better to migrate on 12.2: HYPERLINK "https://urldefense.proofpoint.com/v2/url?u=http-3A__orasql.org_2017_04_21_intra-2Dblock-2Drow-2Dchaining-2Doptimization-2Din-2D12-2D2_&d=DwMDaQ&c=RoP1YumCXCgaWHvlZYR8PQcxBKCX5YTpkKY057SbK10&r=Z7TxYgTRa8b2Qmi2Gp6fvdZIJHBIir8RWfL4YTvXYOM&m=TphpddPdAYuj3IGGja3ONYc9wYANWAS4lJJOAua_m0s&s=ec75KcB5elr3ZBgGY94VQcLlo2-TbJ-713jnvavdCkQ&e="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 <HYPERLINK "mailto:rjoralist3_at_society.servebeer.com"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
HYPERLINK "https://urldefense.proofpoint.com/v2/url?u=http-3A__orasql.org&d=DwMDaQ&c=RoP1YumCXCgaWHvlZYR8PQcxBKCX5YTpkKY057SbK10&r=Z7TxYgTRa8b2Qmi2Gp6fvdZIJHBIir8RWfL4YTvXYOM&m=TphpddPdAYuj3IGGja3ONYc9wYANWAS4lJJOAua_m0s&s=LldaGM89xF-ZySRanBUsEhjnUBxQ_RfGRcRjTtMmjeQ&e="http://orasql.org

--

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

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