RE: PCTFREE and ITL space on high insert table

Stefan,

The three ITL slots appears for "create table as select", and "alter table move".
If you create an empty table and insert into it you get 2 ITL slots per block.  I think this appeared in 10g at the same time that MAXTRANs ceased to have any effect.



Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

________________________________________
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Stefan Koehler [contact_at_soocs.de]
Sent: 04 May 2016 09:49
To: napacunningham_at_gmail.com; oracle-l_at_freelists org
Subject: Re: PCTFREE and ITL space on high insert table

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


--