提高 Oracle DELETE 性能的策略 [英] Strategy to improve Oracle DELETE performance

问题描述

我们有一个开始变大的 Oracle 11g 安装.该数据库是在集群上运行的并行优化系统的后端.流程的输入与优化步骤的输出一起包含在数据库中.输入包括死记硬背的配置数据和一些二进制文件(使用 11g 的 SecureFiles).输出包括当前存储在 DB 中的 1D、2D、3D 和 4D 数据.

We've got an Oracle 11g installation that is starting to get big. This database is the backend to a parallel optimization system running on a cluster. Input to the process is contained in the database along with output from the optimization steps. The input includes rote configuration data and some binary files (using 11g's SecureFiles). The output includes 1D, 2D, 3D, and 4D data currently stored in the DB.

数据库结构:

/* Metadata tables */
Case(CaseId, DeleteFlag, ...) On Delete Cascade CaseId
OptimizationRun(OptId, CaseId, ...) On Delete Cascade OptId
OptimizationStep(StepId, OptId, ...) On Delete Cascade StepId

/* Data tables */
Files(FileId, CaseId, Blob) /* deletes are near instantateous here */

/* Data per run */
OnedDataX(OptId, ...)
TwoDDataY1(OptId, ...) /* packed representation of a 1D slice */

/* Data not only per run, but per step */
TwoDDataY2(StepId, ...)  /* packed representation of a 1D slice */
ThreeDDataZ(StepId, ...) /* packed representation of a 2D slice */
FourDDataZ(StepId, ...)  /* packed representation of a 3D slice */
/* ... About 10 or so of these tables exist */

收割者脚本每天都会出现并查找具有 DeleteFlag = 1 的案例，然后继续使用 DELETE FROM Case WHERE DeleteFlag = 1，允许级联继续.

A reaper script comes around daily and looks for cases with the DeleteFlag = 1 and proceeds with the DELETE FROM Case WHERE DeleteFlag = 1, allowing the cascades to continue.

这个策略非常适合读/写，但现在当我们想要清除数据时超出了我们的能力！问题是删除一个案例需要大约 20-40 分钟，具体取决于大小，并且经常使我们的存档空间过载.该产品的下一个主要版本将采用从头开始"的方法来解决问题.下一个次要版本需要保持在数据库中存储的数据范围内.

This strategy works great for read/write, but is now outstripping our capabilities when we want to purge data! The rub is deleting a Case takes ~20-40 minutes depending on the size and often overloads our archiver space. The next major version of the product will take a "from the ground up" approach to solving the problem. The next minor release needs to stay within the confines of data stored in the database.

因此，对于次要版本，我们需要一种可以提高删除性能并且最多需要对数据库进行适度更改的方法.

So, for the minor release we need an approach that can improve delete performance and at most require moderate changes to the database.

1. REF 分区，但问题是如何?我很乐意在 Case 上做 INTERVAL，其余做 REF，但不支持.是否有某种方法可以通过触发器按 CaseId 手动分区 OptimizationRun?
2. 禁用归档/重做日志以进行删除?找不到与此搭配的提示.不确定它是否可行.
3. 截断?这可能需要一些复杂的表格设置.但也许我没有考虑我所有的选择. (每个答案，受打击)

1. REF Partitioning, but the question is HOW? I would love to do INTERVAL on Case and REF on the rest, but that isn't supported. Is there some way to manually partition OptimizationRun by CaseId through a trigger?
2. Disable archiving/redo logs for deletes? Couldn't find a HINT to go with this one. Not sure it is even feasible.
3. Truncate? This likely would need some sorta complicated table setup. But maybe I'm not considering all of my option. (per answer, stricken)

为了帮助说明这个问题，每个案例的相关数据范围从 15MiB 到 1.5GiB，行数从 20k 到 2M 不等.

To help illustrate the issue, the data in question per case ranges from 15MiB to 1.5GiB with anywhere from 20k to 2M rows.

更新:数据库的当前大小约为 1.5TB.

Update: Current size of the DB is ~1.5TB.

推荐答案

删除数据对于数据库来说是一项艰巨的工作.它必须先创建映像、更新索引、写入重做日志并删除数据.这是一个缓慢的过程.如果您可以有一个窗口来执行此任务，最简单和最快的方法是构建包含所需数据的新表.删除旧表并重命名新表.这需要一些设置工作，这是显而易见的，但很可能做到.不那么激烈的一步是在删除之前删除索引.我的投票将投给 CTAS(根据选择创建表)并构建新表.一个好的分区模式肯定会有所帮助，也许在下一个版本中 Oracle 可以结合区间和参考分区.要是有就好了.

Deleting data is a hell of a job, for the database. It has to create before images, update indexes, write redo logs and remove the data. This is a slow process. If you can have a window to perform this task, easiest and fastest is to build new tables, containing the wanted data. Drop the old tables and rename the new tables. This requires some setup work, that is obvious but is very well possible to make. One step less drastic is to drop the indexes before the delete takes place. My vote would go for CTAS (Create Table As Select from) and build the new tables. A nice partitioning schema would certainly be helpful, maybe in the next release Oracle can combine interval and reference partitioning. It would be very nice to have.

禁用日志记录.... 无法删除，但 CTAS 可以使用 nologging.准备好后进行备份，并确保将数据文件传输到备用数据库(如果有).

Disabling logging .... can not be done for deletes but CTAS can use nologging. Make a backup when ready and make sure to transfer the datafiles to the standby database, if you have one.

这篇关于提高 Oracle DELETE 性能的策略的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！