如何给Large Delete操作提速近千倍?(二)

本文已经发表在ITPUB优化技术丛书,未经许可,不得转载。

本文已经发表在ITPUB优化技术丛书,未经许可,不得转载。

1. 优化过程详解

1.1. 第一次优化——处理庞大的 IN-LIST 操作

回到我们最初的需求,首先看看这个删除工作的基本语句,请注意这个 IN 操作,由于这个 TEMP_MID_HUBEI 表有 130 多万行记录,这将是一个多么庞大的 IN-LIST!

DELETE from SSF

WHERE mid IN (SELECT mid

FROM TEMP_MID_HUBEI);


下面,我们在测试环境下看看,系统机器空闲,资源极大丰富,数量比真实环境小很多的情况下,这条删除语句的执行效率:

SQL> DELETE from SSF WHERE mid IN (SELECT mid FROM TEMP_MID_HUBEI);

18 rows deleted.

Elapsed: 00:05:35.79

SQL>rollback;


我你们看到原始的删除语句需要大约 5 分半钟。

现在,我们使用 RBO (加上 RULE HINT ),观察一下语句的执行情况:

SQL> DELETE /*+ RULE */ from SSF WHERE mid IN (SELECT mid FROM TEMP_MID_HUBEI);

18 rows deleted.

Elapsed: 00:00:47.27

SQL>rollback;


我们看到,这条语句的执行时间缩短到只需要 47 秒钟,单条语句的执行效率提高了 7 倍以上,这也就是我们常说的,对付 IN-LIST 或者大量 OR 条件的一大有利武器。

好了,我们现在如果在生产库上直接运行修改后的 delete 语句来删除数据性能会提高多少呢?会是我们看到的 7 倍么?

我将删除操作所涉及到的所有表的操作写成一个脚本,首先使用少量行删除的操作做测试,得到如下的测试结果:

oracle@db02:/oracle/lunar > cat nohup.out

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Mar 7 12:37:56 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production

With the Partitioning and Real Application Clusters options

JServer Release 9.2.0.4.0 - Production

37 rows deleted. henan_SUBSO

Elapsed: 00:06:39.81

Commit complete.

Elapsed: 00:00:00.01

4 rows deleted. hubei_SUB

Elapsed: 00:02:42.71

Commit complete.

Elapsed: 00:00:00.01

1 row deleted. hubei_SUBSC

Elapsed: 01:31:51.25

Commit complete.

Elapsed: 00:00:00.01

18727 rows deleted. hubei_SUBNFO

Elapsed: 00:01:35.55

Commit complete.

Elapsed: 00:00:00.01

21 rows deleted. fujian_SUINFO

Elapsed: 00:00:15.49

Commit complete.

Elapsed: 00:00:00.01

35423 rows deleted. GINFO

Elapsed: 00:02:19.80

Commit complete.

Elapsed: 00:00:00.04

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production

With the Partitioning and Real Application Clusters options

JServer Release 9.2.0.4.0 - Production

oracle@db02:/oracle/lunar >


嗯,看到上面可喜的结果,我决定进行第二次优化,分段处理,也就是将所有超过 10000 条以上的删除操作拆分成小段操作(比如,以 10000 条记录为标准删除记录,然后提交)。

Published At
Categories with 数据库类
Tagged with
comments powered by Disqus