Oracle 如何避免写入 UNDO / REDO 日志

Oracle How to Avoid writes to UNDO / REDO log

提问by Tom

I have an Oracle PL / SQL script. It processes about 51 millon registers, and writes results to 5 different tables.

我有一个 Oracle PL/SQL 脚本。它处理大约 5100 万个寄存器，并将结果写入 5 个不同的表。

The problem is that I left the process running last night, and apparently there was an overflow in the UNDO logs.

问题是我昨晚让进程继续运行，显然 UNDO 日志中存在溢出。

Particularly, we are not interested in Rollbacking this script, if it fails, we can run it again.

特别是，我们对回滚这个脚本不感兴趣，如果它失败了，我们可以再次运行它。

Is there any way optimize the usage of the undo / redo logs? Avoid writing them or minimizing those writes?

有什么方法可以优化撤消/重做日志的使用吗？避免写入它们或最小化这些写入？

As far as I understand, setting the NOLOGGING attribute the output tables would help, in addition to using the APPEND insert (as said here).

据我了解，在NOLOGGING属性输出表会有所帮助，除了设置使用追加插入（如说这里）。

回答by Kosi2801

You should not process the 51 million registers in only one batch. Try to split it up into smaller chunks of a few thousands for example. If you perform a COMMIT after each smaller batch (which you would do anyway as you say that you're not going to rollback) the redo/undo logs usage will only be for the uncommitted parts and you will avoid an overflow.

您不应该只处理一批 5100 万个寄存器。例如，尝试将其分成数千个较小的块。如果您在每个较小的批次之后执行 COMMIT（您无论如何都会这样做，因为您说您不会回滚），重做/撤消日志的使用将仅用于未提交的部分，您将避免溢出。

回答by David Aldridge

It is really a matter or reducing the amount of work that you are doing.

这真的是一个问题或减少你正在做的工作量。

Table UNDO on a direct path insert is always small as the system just has to record that certain ranges of blocks should be removed from the segment. Indexes will still require substantial undo though. Nologging on a direct path insert minimises table REDO.

直接路径插入上的表 UNDO 总是很小，因为系统只需要记录应该从段中删除某些范围的块。尽管如此，索引仍然需要大量撤消。在直接路径插入上不记录可以最小化表重做。

回答by Dinesh Bhat

In addition, disabling constraints and indexes may also speed up the inserts. You can rebuild indexes with nologging.

此外，禁用约束和索引也可能会加快插入速度。您可以使用 nologging 重建索引。

回答by Gary Myers

"Particularly, we are not interested in Rollbacking this script, if it fails, we can run it again."

“特别是，我们对回滚这个脚本不感兴趣，如果它失败了，我们可以再次运行它。”

One question is, would you need to, and are you prepared to, go back to a previous backup in order to run the script again ? That is, if the process failed after 10 million rows, would simply re-running the script result in 61 million rows being inserted, or would the 10 million be skipped/ignored or would you have 10 million updated and 41 million inserted.

一个问题是，您是否需要并准备好返回到以前的备份以再次运行脚本？也就是说，如果进程在 1000 万行后失败，只需重新运行脚本会导致插入 6100 万行，或者跳过/忽略 1000 万行，或者更新 1000 万行并插入 4100 万行。

Also, if you do a NOLOGGING insert, it will probably require a fresh backup immediately after the job. You would have problems doing a point-in-time recovery to a time during the script run, so you also need to consider what other activity is happening on the database while the script is running.

此外，如果您执行 NOLOGGING 插入，则可能需要在作业后立即进行新备份。将时间点恢复到脚本运行期间的某个时间时会遇到问题，因此您还需要考虑在脚本运行时数据库上正在发生的其他活动。

Depending on how you've written the PL/SQL script, you may find that using large SQLs, rather than row-by-row processing, can reduce undo (eg by minimising revisits to processed blocks).

根据您编写 PL/SQL 脚本的方式，您可能会发现使用大型 SQL 而不是逐行处理可以减少撤消（例如，通过最小化对已处理块的重新访问）。

Unless you really understand the impact of reducing undo or committing to allow reuse of the undo, my first recommendation would be simply to increase the size of the undo tablespace. Of course you do have the drawback that, if you have generated bucket loads of undo, then a failure will take a LONG time to rollback.

除非您真正了解减少撤消或提交以允许重用撤消的影响，否则我的第一个建议就是增加撤消表空间的大小。当然，您确实有缺点，如果您生成了大量撤消操作，则失败将需要很长时间才能回滚。

回答by Oscar Caraballo

You can also use the hidden parameter _disable_logging = true to reduce redo, but beware that the resulting import will be unrecoverable.

您还可以使用隐藏参数 _disable_logging = true 来减少重做，但要注意生成的导入将无法恢复。