RE: PCTFREE and ITL space on high insert table
Date: Wed, 4 May 2016 15:54:34 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282D1348_at_EXMBX01.thus.corp>
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
_at_jloracle
From: Michael Cunningham [napacunningham_at_gmail.com] Sent: 04 May 2016 15:59
To: Jonathan Lewis
Cc: Stefan Koehler; oracle-l_at_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_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>> 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
_at_jloracle
From: Stefan Koehler [contact_at_soocs.de<mailto:contact_at_soocs.de>] Sent: 04 May 2016 11:43
To: Jonathan Lewis; oracle-l_at_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".
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: _at_OracleSK
> Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>> 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
> _at_jloracle
-- http://www.freelists.org/webpage/oracle-l -- Michael Cunningham -- http://www.freelists.org/webpage/oracle-lReceived on Wed May 04 2016 - 17:54:34 CEST