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
@jloracle
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Stefan Koehler [contact@xxxxxxxx]
Sent: 04 May 2016 09:49
To: napacunningham@xxxxxxxxx; oracle-l@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@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?
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.
Michael Cunningham <napacunningham@xxxxxxxxx> 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