大数据量删除的思考 - 2

    在这个简短系列的第1部分中，我提供了两个场景的非正式描述，在这些场景中，我们可以从表中进行大规模删除。没有一个具体的例子,很难想象删除数据的性质和可用的访问路径会产生大量删除操作对系统的性能影响,所以我要把大部分的时间花在本文讨论的两个测试生成的数据集。这篇文章似乎有点长但相当多的空间会被表格占用。

简单的数据集

    随着硬件的能力和规模的不断增长，我们越来越难以就“大表”或“大规模删除”的含义达成一致——对于一个人来说，100万行似乎很大，而对于另一个人来说，1亿行似乎相当普通。

    我将使用一个折中方案，用1000万行表示一个投资系统，该系统10年来以每年100万行的速度增长，并且已经达到了1.6GB的段大小。

    当然，这个表只是组成整个系统的几个表中的一个，在某个时候我们会对所需要的数据担心，但是，目前，我们只考虑这个表，只考虑表本身和表上的4个索引。

下面是生成数据集的代码:

execute dbms_random.seed(0)
create table t1 (
idnot null,
date_open, date_closed,
deal_type,client_ref,
small_vc,padding
)
nologging
as
with generator as (
select
rownumid 
fromdual
connect by
rownum <= 1e4
)
select
1e4 * (g1.id - 1) + g2.idid,
trunc(
add_months(sysdate, - 120) + 
(1e4 * (g1.id - 1) + g2.id)* 3652 / 1e7
)date_open,
trunc(
add_months(
add_months(sysdate, - 120) + 
(1e4 * (g1.id - 1) + g2.id) * 3652 / 1e7,
12 * trunc(dbms_random.value(1,6))
)
)date_closed,
cast(dbms_random.string('U',1) as varchar2(1))deal_type,
cast(dbms_random.string('U',4) as varchar2(4))client_ref,
lpad(1e4 * (g1.id - 1) + g2.id,10)small_vc,
rpad('x',100,'x')padding
from
generatorg1,
generatorg2
where
g1.id <= 1e3
and     g2.id <= 1e4
;
execute dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1');
alter table t1 add constraint t1_pk primary key(id) using index nologging;
create index t1_dt_open on t1(date_open) nologging;
create index t1_dt_closed on t1(date_closed) nologging;
create index t1_client on t1(client_ref) nologging;

上面看起来不是很明显，但是代码生成了10000万行；

date_open：从过去的120个月(10年3652天)开始，用于增加值的算法意味着最近的条目在当前日期。

date_closed：是添加到date_open(该表是记录定期投资的简单模型)的1到5年(包括5年)之间的整数。

deal_type：是随机生成的单个大写字符——生成26个不同的值，这些值具有相同的数据量;

client_ref：是随机生成的一个固定长度的字符串，由4个大写字母组成，每个组合提供大约50万个组合和20行。

    note：作为补充说明-已经生成的数据集没有使用rownum在任何地方的高容量选择;这将使我能够使用并行执行更快地生成数据(“level”和“rownum”伪列都限制了oracle使用并行执行的能力)。但是在本例中，因为我希望id列对按到达顺序存储的按顺序生成的值进行建模，所以我是按顺序运行代码的。

    我的笔记本电脑上，在linux 5 VM上运行了database 12.1.0.2，我得到了创建数据、收集统计数据和创建索引所花费的时间如下:

表创建:7:06.40
数据收集:0:10.54
PK主键:0:10.94
创建索引:0:10.79 (date_open)
创建索引:0:12.17 (date_closed)
创建索引:0:13.65 (client_ref)

   当然，这就要我们开始提一个很现实问题，即不同的系统可能会有不同的时间消耗结果。

虚拟机分配4 gb的内存(1.6 gb是留出memory_target)和一个四核CPU 2.8 ghz 的CPU,但可能最重要的是机器1 tb的固态盘,所以不会失去太多时间在物理I / O。

数据库配置了3个重做日志组，每个重做日志组的大小为200MB(为了日志文件检查点和日志文件切换等待出现一些延迟)，日志是重复的，但是实例没有在arcHivelog模式下运行。

在stats收集之后，大多数块中的表块计数大约为204,000个块，每个块有49行，PK索引和client_ref索引大约有22,000个叶块，两个日期索引大约有26,500个叶块。

Quality

    当使用这样的模型来质疑它们与现实生产中有多接近时是非常重要的。到目前来看，在我所的的准备工作中，你能发现其中存在哪些问题呢？

    首先，表中的Id列太完美了，id列在表中的顺序从小到大排列的非常有序，然而在现实当中，并发性的插入会有一点都抖动，一定范围内连续性的值可能分布在少量的块上，这可能不是很重要，重要的是我是在创建表之后插入数据才创建的索引，这意味着索引在物理上来看是没有什么问题。（每个块中有10%的自由空间），我应该先创建一张空的表，然后在表上建立索引，在这之后再运行几个并发性的脚本使用序列进行单行插入来生成id，但是我上次这样创建的时候，所需要的时间增加了40倍。同样的，这可能也不是很重要，我记得在生产系统中索引的叶块中平均可用空间在任何时候都接近30%。 随着块与块之间明显的变化差异，我想时不时的通过基于叶块状态的检查，尤其是date_open这个索引。

Scenarios（场景）

    尽管任何时间消耗都取决于机器的配置和资源的分配，并且这个模型过于简单化，但是我们任然可以从一些基本的测试当中获取一些有意思的信息。让我们从几个与业务相关的的场景开始：

a、删除所有5年前完成的交易
b、删除client_ref以“A”-“E”开头的所有交易
c、删除所有5年以上的交易

    A 项可能在删除前已经做了一次最基本要求的归档，也可能已经cpye 到另一张表中了。

    B 项可能告诉我们，client_ref已经(ab)用于在第一个字母中为引用编码一些重要的分类，我们将数据分成两个处理集

    C 项可能是按照date_open 对数据进行分区的过程的一部分。（虽然我不确定在这种情况下分区是不是一个好方法），在做任何对于数据库来说影响比较大的操作之前，最好看看时刻能够可视化的知道oracle将要做什么？执行的步骤是什么，以及工作负载会出现在哪里？这些场景都是相同的吗？如果不是，他们有什么不同？如果你不知道你的数据以及你删除数据的影响，你可以从数据库中寻求答案-举个例子：

select
        rows_in_block,
        count(*)                                     blocks,
        rows_in_block * count(*)                     row_count,
        sum(count(*)) over (order by rows_in_block)                 running_blocks,
        sum(rows_in_block * count(*)) over (order by rows_in_block) running_rows
from
        (
        select 
                dbms_rowid.rowid_relative_fno(rowid), 
                dbms_rowid.rowid_block_number(rowid),
                count(*)                                rows_in_block
        from 
                t1
--
--      where   date_open >= add_months(sysdate, -60)
--      where   date_open <  add_months(sysdate, -60)
--
--      where   date_closed >= add_months(sysdate, -60)
--      where   date_closed <  add_months(sysdate, -60)
--
--      where   substr(client_ref,2,1)  >= 'F'
--      where   substr(client_ref,2,1)  < 'F'
--
        group by 
                dbms_rowid.rowid_relative_fno(rowid), 
                dbms_rowid.rowid_block_number(rowid) 
        )
group by
        rows_in_block
order by
        rows_in_block
;

    您将注意到，在这个查询中，我有六个注释谓词(在三个互补对中)。这个查询的基本目的是让我总结一下有多少块可以容纳多少行。但是每对谓词都让我对每种场景的效果有了一些想法-每一对中的一个告诉我关于将要删除的数据量和模式的一些信息。下面是sql*plus中执行如上查询的输出：

                                              Blocks           Rows
Rows per block   Blocks         Rows   Running total   Running total
-------------- -------- ------------   -------------   -------------
            27        1           27               1              27
            49  203,877    9,989,973         203,878       9,990,000
            50      200       10,000         204,078      10,000,000
               --------
sum             204,078

    下面的输出显示了如果删除了5年以上打开的数据行，留下来的数据将会是什么样子?(也就是说，使用谓词date_open >= add_months(sysdate， -60))

                                             Blocks           Rows
Rows per block   Blocks           Rows Running total  Running total
-------------- -------- -------------- ------------- --------------
            27        1             27             1             27
            42        1             42             2             69
            49  102,014      4,998,686       102,016      4,998,755
               --------
sum             102,016

    这相当不错--粗略的来说我们已经将表一半的块清空了，另一半没有动。如果我们现在尝试‘收缩空间’，那么我们只需要将表的下半部分复制到表的上半部分。我们会生成大量的undo数据和redo日志。但是任何索引的任何聚簇因子可能没有一点改变。另一种选择是，如果我们决定让空白空间保持原样，那么任何新数据都会非常有效地开始填充空白空间（几乎就想是重新分配区一样），同样的我们也会看到任何聚簇的因子也没有什么改变。将此结果与删除所有5年前关闭的行所带来的结果进行比较，（也就是说，如果我们使用谓词date_closed >= add_months(sysdate， -60)，会看到什么?）这个结果集.会大很多。

Blocks           Rows
Rows per block   Blocks           Rows Running total  Running total
-------------- -------- -------------- ------------- --------------
             1        5              5             5              5
             2       22             44            27             49
             3      113            339           140            388
             4      281          1,124           421          1,512
             5      680          3,400         1,101          4,912
             6    1,256          7,536         2,357         12,448
             7    1,856         12,992         4,213         25,440
             8    2,508         20,064         6,721         45,504
             9    2,875         25,875         9,596         71,379
            10    2,961         29,610        12,557        100,989
            11    2,621         28,831        15,178        129,820
            12    2,222         26,664        17,400        156,484
            13    1,812         23,556        19,212        180,040
            14    1,550         21,700        20,762        201,740
            15    1,543         23,145        22,305        224,885
            16    1,611         25,776        23,916        250,661
            17    1,976         33,592        25,892        284,253
            18    2,168         39,024        28,060        323,277
            19    2,416         45,904        30,476        369,181
            20    2,317         46,340        32,793        415,521
            21    2,310         48,510        35,103        464,031
            22    2,080         45,760        37,183        509,791
            23    1,833         42,159        39,016        551,950
            24    1,696         40,704        40,712        592,654
            25    1,769         44,225        42,481        636,879
            26    1,799         46,774        44,280        683,653
            27    2,138         57,726        46,418        741,379
            28    2,251         63,028        48,669        804,407
            29    2,448         70,992        51,117        875,399
            30    2,339         70,170        53,456        945,569
            31    2,286         70,866        55,742      1,016,435
            32    1,864         59,648        57,606      1,076,083
            33    1,704         56,232        59,310      1,132,315
            34    1,566         53,244        60,876      1,185,559
            35    1,556         54,460        62,432      1,240,019
            36    1,850         66,600        64,282      1,306,619
            37    2,131         78,847        66,413      1,385,466
            38    2,583         98,154        68,996      1,483,620
            39    2,966        115,674        71,962      1,599,294
            40    2,891        115,640        74,853      1,714,934
            41    2,441        100,081        77,294      1,815,015
            42    1,932         81,144        79,226      1,896,159
            43    1,300         55,900        80,526      1,952,059
            44      683         30,052        81,209      1,982,111
            45      291         13,095        81,500      1,995,206
            46      107          4,922        81,607      2,000,128
            47       32          1,504        81,639      2,001,632
            48        3            144        81,642      2,001,776
            49  122,412      5,998,188       204,054      7,999,964
               --------
sum             204,054

    在这种情况下，大约有60%的blocks依然每个块持有原来的49行，但是表中的其他块几乎没有被删除，而是被完全清空。（如果您将第一个输出中的总块数与第一个报告中的总块数进行比较，您会注意到现在肯定有几个块(24个块)是完全空的）现在有多少块可用来插入?这里有一个快速的计算，我们的大部分块有49行，占了90%（default pctree = 10），因此，一个块将下降到75%的标记(即当ASSM将其标记为有空闲空间时)，当它少于41行时(49 * 75 / 90)，在204,000个块中，大约75,000个符合这个标准(检查“运行的块总数”列)

索引空间

    上一节展示了一些简单的SQL，让您了解了表中将如何显示空间(或数据将如何保留)-我们可以对索引做类似的事情吗？答案必然是肯定的。但是，回答“在删除匹配谓词X的数据之后，索引会是什么样子”这个问题的代码运行起来要比运行表的代码开销更大。首先，这里有一段简单的代码来检查索引的当前内容:

select
        rows_per_leaf, count(*) leaf_blocks
from    (
        select
                
                sys_op_lbid(94255, 'L', t1.rowid)       leaf_block,
                count(*)                                rows_per_leaf
        from
                t1
        where
                client_ref is not null
        group by
                sys_op_lbid(94255, 'L', t1.rowid)
        )
group by
        rows_per_leaf
order by
        rows_per_leaf
;

    对于‘SYS_OP_LBID()’的调用将一个表rowid作为它的如数之一，并返回一些类似于块的第一行的rowid的内容，而该块的地址是索引叶块的地址，索引你块持有表rowid所提供的索引条目。另外两个参数是索引object_id(如果索是分区的，则是分区或者是子分区)和一个表示函数的特定用法的标志。在这个例子中是“L”。hint在目标索引上使用快速索引扫描是必要的-任何其他路径都可能返回错误的出结果-‘client_ref’不为空是必要的。以确保查询可以有效的使用index_ffs路径。

    对于我的初始化数据集，索引在每个块中都有448个索引条目，除了一个（大概是最后一个，192行）。即使这是简单的查询也要为了每个索引的要求而精心设计-因为索引快速扫描需要得到正确的结果，这就是我们不得不做一些不同寻常的删除操作，看看我们大量删除会怎么影响索引。下面是一个例子，展示我们如何找出试图删除5年多前打开的行对client_ref索引产生什么影响。

select
        rows_per_leaf,
        count(*)                                    blocks,
        rows_per_leaf * count(*)                    row_count,
        sum(count(*)) over (order by rows_per_leaf)                 running_blocks,
        sum(rows_per_leaf * count(*)) over (order by rows_per_leaf) running_rows
from    (
        select
                
                leaf_block, count(*) rows_per_leaf
        from    (
                select
                        
                        sys_op_lbid(94255, 'L', t1.rowid)       leaf_block,
                        t1.rowid                                rid
                from
                        t1
                where
                        client_ref is not null
                )       v1,
                t1
        where
                t1.rowid = v1.rid
        and     date_open <  add_months(sysdate, -60)
        group by
                leaf_block
        )
group by
        rows_per_leaf
order by
        rows_per_leaf
;

    正如您所看到的，我们从一个内联视（按时不可合并）图开始将索引块id附加每个表的rowid上，然后将这组行id连接回表-通过rowid连接并强制进行散列连接。我已经暗示了散列连接，因为它(可能)是最有效的策略，但是尽管我引入了一个leading()提示，但我没有包含关于交换(或不)连接输入的提示-我将让优化器决定这两个数据集中哪个更小，由此来更适合的构建哈希表。

    在这种特殊的情况下优化器能够使用一个仅索引的访问路径来查找date_open 比五年前跟早行的所有rowid。尽管如此(部分原因是我的pga_aggregate_target相对较小，散列连接溢出到(固态)磁盘)，查询耗时3分15秒，而上一个查询在缓存整个索引时恰好运行了1.5秒。以下是输出的摘录:

                                   Blocks           Rows
Rows_per_leaf   Blocks           Rows Running total  Running total
------------- -------- -------------- ------------- --------------
          181        2            362             3            458
          186        2            372             5            830
          187        2            374             7          1,204
          188        1            188             8          1,392
...
          210      346         72,660         2,312        474,882
          211      401         84,611         2,713        559,493
...
          221      808        178,568         8,989      1,921,410
          222      851        188,922         9,840      2,110,332
          223      832        185,536        10,672      2,295,868
...
          242      216         52,272        21,320      4,756,575
          243      173         42,039        21,493      4,798,614
          244      156         38,064        21,649      4,836,678
...
          265        1            265        22,321      5,003,718
          266        1            266        22,322      5,003,984

    我们要修改22322个叶块——这是索引中的每一个叶块;我们从一个叶块中删除的行数从1到266不等。我一次从83行输出中选择了几行，但是您可能仍然可以看到该模式似乎遵循正态分布，以222(50%)为中心。

    如果这样删除我们应该很清楚，我们将花费大量的精力来更新这个索引；即使这样，“每个叶块删除多少行”这个简单的数字也不能告诉我们要做的工作的全部内容。我们不知道我们是否会(例如)在同一时间删除所有266个索引条目从最后一块上面显示删除完成，我们将非常随机地在索引周围跳跃式来回，并发现自己不断地重新访问该块，以便一次删除一个索引条目。因此在下一期中，我们将研究需要考虑工作负载的哪些方面，以及不同的删除策略如何对工作负载产生重大影响。

译者: 汤建
原作者: Jonathan Lewis
原文地址：https://www.red-gate.com/simple-talk/sql/oracle/massive-deletes-part-2/