Re: Fw: Calculations in maximizing rows per block

On 2017/08/15 12:04, Jonathan Lewis wrote:

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

The row length is definitely variable, likely due to the range of values in the NUMBER columns (since NULLs are not normally used by the app, zeros are the placeholder), judging by a random sampling of data blocks and some extrapolation.

Your part about initrans being 3 for appended rows made me curious, since I loaded this particular table from a DataPump export using TABLE_EXISTS_ACTION=APPEND. Sure enough, if I decoded this part of a block dump correctly, there's 3 ITLs:

Block header dump: 0x067653d2
 Object id on Block? Y

 seg/obj: 0x28fe8  csc: 0x10.100a97bb  itc: _3_  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x6764084 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

ITLs are 24 bytes? Some online resources say 23, others say 24.

Thanks,
Rich

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