本贴是四篇系列中的第三篇，涵盖表碎片的内容。完整的系列如下所示：

1、介绍 – （含2-4的链接）
2、磁盘和表空间碎片
3、表碎片 - 本篇
4、索引碎片

三、表“碎片”

在引言中，我们讨论了一种不是（通常）问题的表碎片类型——将表分散成多个extent。这是个有趣的想法——ASSM（自动段空间管理）引入了另一种通常无关紧要的表碎片形式。
当向一个对象中插入新数据，但进程不能从可用空间位图上找到有足够可用空间的块时，它会通过格式化当前extent中（如有必要，分配新的extent)的16个块来获得可用空间。这16个块的位置可以是extent中的任何地方（事实上的限制是其起点必须是16的倍数）。块组的位置和最初选择使用哪个数据块类似，是由进程ID（PID）指定的。这意味着，你在使用1MB统一extent大小的表空间上创建了一个表，并且你发现你插入的第一行，进入到了第一个分区中已被格式化的16个块的最后一个块中。通常，这并不是什么大不了的事情，这是因为:（a)大多数的IO访问，是基于块而不是表来扫描的，所以，在extent中的位置并不是什么问题。(b）“混乱”通常仅限于表中的最后一个extent；而且（c）如果你在做表扫描，Oracle有一个依赖于使用LHWM（低-高水位线）和HHWM（高-高水位线）的机制，可以最大限度地减少检查哪些16个块的块组需要特殊处理的开销。

更重要、更常见的表碎片类型是由于数据可以被删除，然后我们担心“填充稀疏的块”而带来的。创建表时，你指定每个块中应为需要更新的行保留多少空间，以及（显式的freelist管理或在ASSM下的隐式freelist的管理）块中的可用空间变为多少时，它可以做为要插入的新数据的目标块。

当您考虑那些必须通过数据插入、更新和删除来解决问题的可能场景时，您会意识到Oracle编写及时高效地处理块级可用空间管理的代码是多么困难；您还意识到指定合理的空间管理限制（作为开发人员或DBA），以最大限度地减少极端情况下的性能问题是多么困难。最终，一个表可能在每个块中都有大量的空闲空间，然后你必须开始问这样的问题：空闲空间是如何到达那里的，它的存在是否会导致性能问题，你是否能够重新使用它，你是否能够及时地重新使用它，如果您尝试重新使用它，将导致另一种类型的性能问题。

考虑这样一个场景：在运行系统五年后删除第一年的数据，这可能会导致表中前20%的块完全空了。它们都将出现在freelist中，或者（除bug外）在ASSM位图中被标记为具有空闲空间，因此您最终将重用该空间；此外，如果您的应用程序性能取决于您的数据按到达时间进行聚集，则重新填充空块空间的方式也会这样（译者注：先空的块会被先重新填充），一般来说，这会保持聚集的完整性。通过一个奇怪的实现，你可能会在自由列表管理下以相反的顺序使用这些块，在ASSM下以正常的顺序使用这些块（当时我脑子里闪过一个想法，想知道前者对“低基数”非唯一索引的影响）。但是，在相当长的一段时间内，您所做的任何表扫描都将比需要的时间长20%左右，而且您会备份比实际需要的更大的数据文件：这些考虑因素可能会使您决定在新的表空间中重建表（及其索引）–它们也可能会使您考虑分区的好处。

想象一下这样一个场景：你有一个基于WEB的销售系统，在这个系统中，用户可以填满购物筐，并最终成为订单。一些用户不可避免地会填写购物篮，然后不下订单，这样您就可以运行一些后台代码，查找尚未转变为订单的旧购物筐，并将其从数据库中删除。（这种情况下有两个设计缺陷，但这样的问题经常在生产系统中发生。）在这种情况下，您可能最终得到一个表，该表在最近的一段时间内不断地被删除，以致当您检查该表时，您会发现表中的每个块都平均有（比方说）20%的可用空间，除了保存最近购物筐数据的块（的可用空间）相对较少。如果运行ASSM，则20%的可用空间无法重用（除非重建表），因为ASSM的粒度在块的状态从“full”更改为“0%–25%free”之前，需要至少25%的可用空间；但是，如果您使用的是freelist空间管理，那么您可能已经预测到了影响，并将表存储参数pctused设置为略高于80的值。对于pctused超过80的，当块的可用空间回到20%，它将回到空闲列表中，并且空间将会被重用。这听起来是件好事，只是有一个新的问题需要考虑：由于过去几天的购物筐（的数据）存储在表的最后N个块中，您的应用程序会运行效率很高，那么现在您已经有了一个表，其中删除和重用的方式意味着数据现在分布在5*N个块上，因为您不断从过去释放20%的块，这意味着如果你想保持同样的效率，您不得不找一些额外的内存来缓存数据。

一般的结论是：一旦您解决过（一次）为表设置extent大小的问题，任何关于“碎片化”的想法都应该被认为是“稀疏填充的块”–而且有一些数据删除方式可能导致表块的利用率不太理想。在某些情况下，不重建表就不可能回收空空间（在所有情况下，分区表可能是一个可行的选项），但是，在某些情况下，删除的方式可以允许空间被重用，然后您可能会发现，您并不总是希望重用该空间，因为使用它可能会带来另一种问题。

更新：我看到Nigel Noble写了一篇文章（文章链接），是关于ASSM检查空闲空间映射的一个BUG，它会导致在特定情况下浪费大量的空间。

原文的链接和内容：
https://jonathanlewis.wordpress.com/2010/07/19/fragmentation-3/

Fragmentation 3
Filed under: ASSM,fragmentation,Infrastructure,Oracle — Jonathan Lewis @ 7:05 pm BST Jul 19,2010
This note is part three of a four-part series, and covers Table fragmentation. The whole series is as follows

Introduction – with links to parts 2 – 4
Disk and Tablespace Fragmentation
Table Fragmentation – this bit
Index Fragmentation
3. Table “fragmentation”.
In the introduction we discussed one type of table fragmentation that doesn’t (usually) matter – the fragmentation of a table into multiple extents. Here’s a funny thought – ASSM (automatic segment space management) introduces another form of table fragmentation that usually doesn’t matter.

When a process checks the freespace bitmaps for an object and can’t find a block with enough free space for inserting new data, it will make some space available by formatting 16 blocks somewhere in the current extent (allocating a new extent if necessary). The location of those 16 blocks may be almost anywhere in the extent (limited by the fact that the starting point within the extent has to be based on multiples of 16). The location of the chunk, like the initial choice of block to use, is dictated by the process id (pid). This means you could create a table in a tablespace using 1MB uniform extents and find that the first row you inserted formatted the last 16 blocks of the first extent and the row went into the last block of that extent. This doesn’t really matter very much in general because (a) most I/O access is by block rather than tablescan so the location within extent doesn’t really matter much; (b) the “messiness” is generally restricted to just the last extent in the table; and © if you are doing tablescans Oracle has a mechanism dependent on using the “LHWM (low high water mark) and HHWM (high high water mark), for minimising the overhead of checking which 16 block chunks need special treatment.

The more significant, and common, type of table fragmentation comes about because data can be deleted and then we can worry about “sparsely populated blocks” . When you create a table you specify how much space should be left in each block for rows to be updated and (explicitly under freelist management or implicitly under ASSM) how much space should become free in a block before it should be considered as a target for new data to be inserted.

When you consider the possible scenarios that have to be addressed with data inserts, updates and deletes, you realise how difficult it can be for Oracle to write code that handles free space management at the block level in a timely and efficient manner; you also realise how difficult it can be (as a developer or DBA) to specify sensible space management limits to minimise performance problems in extreme cases. Ultimately it is possible for a table to end up with a significant amount of free space in each block – and then you have to start asking question like: how did that free space get there, is its presence causing a performance problem, are you going to be able to re-use it, are you going to be able to re-use it in a timely fashion, if you do manage to re-use it will that introduce a different type of performance problem.

Consider the scenario where you delete one year’s worth of data after running your system for five years – this probably results in the first 20% of the blocks in a table becoming completely empty. They will all go on the freelist or (bugs excepted) be flagged in the ASSM bitmaps as having free space, so you will reuse the space eventually; moreover if your application performance depends on your data being clustered by time of arrival the way the empty space refills will, broadly speaking, keep that clustering intact. By a quirk of implementation you will probably use the blocks in reverse order under freelist management and in forward order under ASSM (and a thought crosses my mind at that point to wonder about the impact the former will have on “low-cardinality” non-unique indexes). But, for quite some time, any tablescans you do will take about 20% longer than they need to, and you could be backing up larger data files than you really need to: these considerations might make you decide to rebuild the table (and its indexes) in a new tablespace – they might also make you consider the benefits of partitioning.

Imagine a scenario where you have an web-based sales system where users can fill baskets which ultimately become orders. Some users inevitably will fill baskets and then not place orders so you could have some background code running that looks for old baskets that have not turned into orders and delete them from the database. (There are two design flaws in this scenario – but accidents like this often happen in production systems.) In this case you could end up with a table that suffers a constant stream of deletions in the recent past so that when you check the table you find that on average every block in the table has (say) 20% free space, except for the relatively small number of blocks that hold data about recent baskets.

If you are running ASSM the 20% of free space cannot be reused (unless you recreate the table) because the granularity of ASSM requires a minimum of 25% free space in a block before its status changes down from “full” to “0% – 25% free”; but if you are using freelist space management then you may have predicted the effect and set the pctused table storage parameter to a value a little above 80. With the pctused above 80, blocks would go back on the freelist as their free space climbed to 20% and the space would be reused. This sounds like a good thing, except there’s a new problem to consider.

If your application used to run efficiently because the baskets for the last few days were stored in the last N blocks of the table, you’ve now got a table where the pattern of deletes and re-use mean that that data is now spread over 5 * N blocks (because you keep freeing up 20% of blocks from the past – which means you’re going to have to find some extra memory for buffering the data if you want to keep the same level of efficiency.

The general conclusion is this: once you’ve got past the issues of dealing (once) with setting extent sizes for a table, any thoughts of “fragmentation” should be thought of as “sparsely populated blocks” – and there are patterns of data deletion that can result in table blocks being less than perfectly utilised. In some cases it is not possible to reclaim the empty space without rebuilding the table (and in all cases, partitioned tables may make this a viable option), but there are cases where the pattern of deletion can allow space to be reused – and then you may find that you don’t always want to re-use that space because using it can introduce a different kind of problem.

Update: I see that Nigel Noble has written an article about a bug in the way that ASSM checks free space maps that can result in significant space wastage in particular special cases.