AKA
Ask The Oracle Masters (AskTOM)
From: Tim Gorman [mailto:tim.evdbt@xxxxxxxxx] ;
Sent: Monday, August 14, 2017 5:48 PM
To: gogala.mladen@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxx"rjoralist3@xxxxxxxxxxxxxxxxxxxxx>
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
