大数据量删除的思考-2
在这个简短系列的第1部分中，我提供了两个场景的非正式描述，在这些场景中，我们可以从表中进行大规模删除。没有一个具体的例子,很难想象删除数据的性质和可用的访问路径会产生大量删除操作对系统的性能影响,所以我要把大部分的时间花在本文讨论的两个测试生成的数据集。这篇文章似乎有点长但相当多的空间会被表格占用。
创新互联建站主要从事网站制作、做网站、网页设计、企业做网站、公司建网站等业务。立足成都服务博乐,10多年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:18980820575
简单的数据集
随着硬件的能力和规模的不断增长，我们越来越难以就“大表”或“大规模删除”的含义达成一致——对于一个人来说，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/*+ materialize cardinality(1e4) */
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 /*+ index_ffs(t1(client_ref)) */ 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 /*+ leading(v1 t1) use_hash(t1) */ leaf_block, count(*) rows_per_leaf from ( select /*+ no_merge index_ffs(t1(client_ref)) */ 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/
标题名称：大数据量删除的思考-2
网页网址：http://mswzjz.cn/article/igescc.html