Re: PCTFREE and ITL space on high insert table

From: Stefan Koehler <contact_at_soocs.de>
Date: Wed, 4 May 2016 10:49:07 +0200 (CEST)
Message-ID: <832610843.1069107.1462351747729.JavaMail.open-xchange_at_app04.ox.hosteurope.de>

Hello Michael,
just in addition to Jonathan's reply. You will get 3 ITL slots for tables (at least since 12.1.0.1), even if you do not specify an ITL value. Here is a block dump of a short demo on 12.1.0.1, that i did for verification as i was not sure when this change was introduced.

TEST_at_T12DB:186> select INI_TRANS from user_tab_partitions;  INI_TRANS

---

	 1
	 1
	 1

Block dump
…

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.001ece8b

0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 …

> I may have misunderstood what this means, but if PCTFREE 0 fills the block will there still be enough room in the block header for the ITL lists?

Each ITL slot needs 24 bytes. PCTFREE 0 means that INSERTs can fully fill up the block, but if parallel INSERTs are happening and there are more than 24 bytes (+ data) left you can still allocate more ITL slots if needed.

> I am moving the table partitions to another tablespace and am wondering if I can change to PCTFREE 0. My concern is if there will be enough space in
> the block header for the required number of ITL entries.
 

How many of these 400 INSERTs per second in peak are really parallel? I mean in theory you can still have no parallel INSERTs at all if each INSERT is spread equally over 1 sec and it takes only 2 ms. Just dump some blocks for verification and check how many ITL slots are allocated.

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK

> Michael Cunningham <napacunningham_at_gmail.com> hat am 4. Mai 2016 um 01:17 geschrieben:
>
> Hello list,
>
> I have a table that receives a relatively high number of INSERTs (no updates or deletes). They come in at a peak rate of about 400 per second on a
> partitioned table with local indexes. Only the last partition receives inserts and there is a total of 2B rows per partition. My question is for
> both the PCTFREE on both table and index.
>
> I am moving the table partitions to another tablespace and am wondering if I can change to PCTFREE 0. My concern is if there will be enough space
> in the block header for the required number of ITL entries.
>
> The source for this question is from Jonathan Lewis' book Oracle Core on page 37 where it says "but the ITL in any block can grow dynamically if it
> needs to, provided there is enough free space available in the block". I may have misunderstood what this means, but if PCTFREE 0 fills the block
> will there still be enough room in the block header for the ITL lists?
>
> Thanks,
> --
> Michael Cunningham

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

Received on Wed May 04 2016 - 10:49:07 CEST