The problem with a sequence (or time) based primary key is that all the inserts
are to a new high-value (plus or minus a little jitter, possibly, dependent on
how you wrote the code) - this opens the opportunity for buffer busy waits
because the top (high value) block of the index is subject to lots of
concurrent change; it also means you may find a high number of ITL entries
demanded to cope with the concurrency. Worst, though, is a side effect of
index leaf block splits which I wrote about a few years ago (2009) - there have
been a couple of fixed put in to recent versions of Oracle to address this, but
I still got a suprisingly large ITL from a small number of concurrent
transactions in a test of 12c. More details here:
https://jonathanlewis.wordpress.com/2009/09/19/index-itls/
In my testing it was possible for an index to end up running with 25% usage in
the leaf blocks because half the block was taken up by (unused) ITL entries and
half of the reminder was left empty due to concurrent demand during leaf block
splits. This does not go away, and rebuilding the index will only make the
problem disappear until the next busy moment.
Thanks to ASSM - and large extents - you can get a fairly high degree of
concurrent inserts on a table without getting a high degree of concurrency on
any individual block, so the size of the ITL in the table blocks may stay very
small, and you may see very few buffer busy waits and ITL waits on table blocks
even when the rate of insertion is at a peak. A possible downside to this is
that two rows with adjacent sequence numbers could be end up being inserted at
opposite ends of an extent - which could matter if you were hoping to keep
time-related data well clustered or, at least, to make the optimizer believe it
was well clustered (so setting the table prefs for table_cached_blocks may be
important).
I should mention that when you said 400 inserts per second, I imagined this
would be a couple of hundred session doing one or two inserts per second and
committing very quickly. If it's more like a couple of dozen sessions doing
small array inserts of 10 to 20 rows then there is some variation in the
balance between the various concurrency threats you have to meet.
I mentioned the idea of using two tables with a partition view for history and
current - an alternative, if you find you have too much contention on index
leaf blocks, is to consider creating the table as a composite partitioned
table, subpartitioned by hash, with only one subpartition for history but 2^N
subpartitions for the current partition - where 2^N is somewhere between 16 and
128.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: Michael Cunningham [napacunningham@xxxxxxxxx]
Sent: 04 May 2016 15:59
To: Jonathan Lewis
Cc: Stefan Koehler; oracle-l@freelists org
Subject: Re: PCTFREE and ITL space on high insert table
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<mailto: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<mailto: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 "alter table
move".
Jonathan Lewis--
<jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>> hat am 4.
Mai 2016 um 11:35 geschrieben:
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
