I got a funny spam/phishing message bounced back at me, so I guess this didn't
get to the list yesterday.
Regards
Jonathan Lewis
________________________________________
I think the main thing to consider is that your rows could be very variable in
length; I doubt if there's anything particularly special about the chained rows
that's making a big difference.
There used to be a problem with insert /*+ append */ averaging one row per
block fewer than you might expect - but I think that was fixed long ago.
The avg_row_len is going to be a bit short - it will be missing 10 bytes from
standard row overhead (two overheads per full row because you have two row
pieces) plus 6bytes "next rowid" for the chaining information. So that takes
you to 2683 bytes per row: 3 rows gives 3 * 2,683 = 8049
The block free space for rows (assuming initrans = 2 (though it will be 3 if
you've done /*+ append */) will be 8,070 which doesn't leave you much room for
rows to be a little over average before you get only 2 rows per block instead
of 3. If you've done /*+ append */ knock 24 bytes off the starting free space:
8046 - and you're often going to have trouble.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Rich J <rjoralist3@xxxxxxxxxxxxxxxxxxxxx>
Sent: 14 August 2017 20:23:36
To: Oracle L
Subject: Calculations in maximizing rows per block
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.
--
//www.freelists.org/webpage/oracle-l
