These answers are incredible. I learned a lot and got some great ideas.
Stefan, the inserts are not parallel. They come from different sessions,
one at a time.
Jonathan, the primary key is fom a sequence and, to date, I have not
witnessed any problems with waits. However, if you have something in
particular I should be cautious of please let me know and I'll look into it.
I'll move the partitions with pctfree 0 initrans 2, and look into using
basic compression, but I'll leave the last partition at pctfree 10. I'll
also look at sorting the data in each partition. In fact, I'll use Stefan's
idea of dumping some of the latest blocks to see the ITL list count. It
seems that pctfree 10 leaves room for 34 ITL's (24 bytes each). The inserts
are not parallel so I'll assume they stay at 24 bytes. I have a couple days
before all the partitions are moved until I get to the latest partition
where I'll be using dbms_redefinition.
Thanks for all the info, if there is any more, I'll take it.
Michael
On Wed, May 4, 2016 at 3:45 AM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
wrote:
Stefan,
But now he knows that "alter table move" allocates 3 ITLs and "create
empty / insert " allocates 2 ITLs he may choose to save 24 bytes per block
;)
Realistically, he may also think about using dbms_redefinition to do an
online move - though there are licencing considerations to worry about when
combining compression and online moves.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________________
From: Stefan Koehler [contact@xxxxxxxx]
Sent: 04 May 2016 11:43
To: Jonathan Lewis; oracle-l@freelists org
Subject: RE: PCTFREE and ITL space on high insert table
Hi Jonathan,
thanks for verifying the version.
The three ITL slots appears for "create table as select", and "altertable move".
I may misunderstood Michael, but doesn't he want to move his partitions?
Quote: "I am moving the table partitions to another tablespace and am
wondering if I can change to PCTFREE 0". So finally he will end up with at
least 3 ITL slots.
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> hat am 4. Mai 2016 um11:35 geschrieben:
table move".
Stefan,
The three ITL slots appears for "create table as select", and "alter
If you create an empty table and insert into it you get 2 ITL slots perblock. 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
//www.freelists.org/webpage/oracle-l
