RE: PCTFREE and ITL space on high insert table
Date: Wed, 4 May 2016 07:50:33 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282D11BF_at_EXMBX01.thus.corp>
"... provided there is enough free space ..."
Any free space will be due to the PCTFREE, there is no special "extra" free space in the block header, so if the data is subject to change and you set pctfree to zero you may easily find that a small number of concurrent transactions takes out all the ITL slots (INITRANS) and leaves transactions reporting TX/4 enqueues as they wait for ITL slots.
If only the most recent partition is subject to inserts then when you move all the rest you might as well set initrans to 2 (the minimum allowed) and pctfree to zero - added to which you might as well rebuild the partitions with basic compression. You might also consider sorting the data in each (read-only) partition as you recreate it because this may (a) improve the performance of some particularly important queries and (b) improve the level of compression.
I'd put the live/current partition in its own tablespace, by the way and think very carefully about the rate of inserts, the effects on indexes (particularly sequence or time-based), the level of concurrency, the options for partitioning, extent sizing and the impact of the clustering_factor on the optimizer. I'd also consider the option for using a partition view of partitioned tables with different partitioning options for current and historic. Too many possibilities to summarize usefully in an email, though.
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 Michael Cunningham [napacunningham_at_gmail.com] Sent: 04 May 2016 00:17
To: oracle-l_at_freelists org
Subject: PCTFREE and ITL space on high insert table
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
Received on Wed May 04 2016 - 09:50:33 CEST