Re: Fw: Calculations in maximizing rows per block

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 15 Aug 2017 21:33:02 +0000
Message-ID: <MMXP123MB1037D804DF779B300019E228A58D0_at_MMXP123MB1037.GBRP123.PROD.OUTLOOK.COM>

> 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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Rich J <rjoralist3_at_society.servebeer.com> Sent: 15 August 2017 22:06:43
To: oracle-l_at_freelists.org
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

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

Received on Tue Aug 15 2017 - 23:33:02 CEST