改善Oracle DELETE性能的策略 [英] Strategy to improve Oracle DELETE performance

问题描述

我们已经开始安装Oracle 11g.该数据库是在集群上运行的并行优化系统的后端.该过程的输入与优化步骤的输出一起包含在数据库中.输入内容包括死记硬背配置数据和一些二进制文件(使用11g的SecureFiles).输出包括当前存储在数据库中的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屋！