Re: Calculations in maximizing rows per block
- From: Kurt Van Meerbeeck <kurtvm@xxxxxxxxxx>
- To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 16 Aug 2017 08:34:38 +0200 (CEST)
ITL size is 0x18 (24b) - tested from 7 to 12
Only exception I ever encountered was on Novell (Oracle7) were ITL size is 0x17
(23b).
cheers
K
----- Oorspronkelijk bericht -----
Van: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
Aan: "oracle-l" <oracle-l@xxxxxxxxxxxxx>, rjoralist3@xxxxxxxxxxxxxxxxxxxxx
Verzonden: Dinsdag 15 augustus 2017 23:33:02
Onderwerp: Re: Fw: Calculations in maximizing rows per block
ITLs are 24 bytes? Some online resources say 23, others say 24.
In very old versions of Oracle the size of an ITL was platform dependent and
there were some platforms where it was 23 bytes.
I think 24 was standard across all platforms by version 7 - but I'm not at all
sure about that.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Rich J <rjoralist3@xxxxxxxxxxxxxxxxxxxxx>
Sent: 15 August 2017 22:06:43
To: oracle-l@xxxxxxxxxxxxx
Subject: 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
0x01 0x000f.004.0000433c 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
ITLs are 24 bytes? Some online resources say 23, others say 24.
Thanks,
Rich
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
