为什么DMML错误日志记录在并行语句中被忽略 [英] Why dml error logging ignored in parallel statement

问题描述

我有 insert-select 语句正在并行执行。

I have insert-select statement that I'm executing in parallel.

我正在尝试添加 dml错误日志记录来捕获异常。

I'm trying to add a dml error logging for capture the exceptions.

但是当我更改会话以使用并行时，

But when I'm alter the session to use parallel the error logging just ignored.

-- This is my error table:
TRUNCATE TABLE DWH.ERR$_DWH_CONV;

-- This code is finish with no error and insert rows to DWH.ERR$_DWH_CONV
BEGIN
EXECUTE IMMEDIATE
'alter session enable parallel dml';

INSERT
        /*+ monitor parallel(1) */
INTO    DWH.PURCHASE
select *
FROM    DWH.PURCHASE_C LOG ERRORS
INTO    DWH.ERR$_DWH_CONV ('DWH.PURCHASE') REJECT LIMIT UNLIMITED ;

commit;
END;

但是当我运行这段代码（并行6）时，会有一个例外（ORA-12801 ）
并且错误表没有任何变化：

But when I'm running this code (parallel 6) - there is an exception (ORA-12801) and the error table doesn't has any change:

BEGIN
EXECUTE IMMEDIATE
'alter session enable parallel dml';
INSERT
        /*+ monitor parallel(6) */
INTO    DWH.PURCHASE
select *
FROM    DWH.PURCHASE_C LOG ERRORS
INTO    DWH.ERR$_DWH_CONV ('DWH.PURCHASE') REJECT LIMIT UNLIMITED ;
commit;
END;

如果我启用或禁用 alter-session，也会出现这种差异。

我正在使用Oracle 11g。

I'm using Oracle 11g.

根据Oracle文档，我没有看到在 parallel 中使用 dml错误日志记录的任何限制。

Accoring to the Oracle documents, I didn't saw any restriction of using dml error logging with parallel..

推荐答案

根据 error_logging_clause

DML错误记录限制

Restrictions on DML Error Logging

• 以下情况导致语句失败并回滚而未调用错误记录功能：

• The following conditions cause the statement to fail and roll back without invoking the error logging capability:

...

• 任何直接路径的INSERT或MERGE操作引发唯一约束或索引冲突。

默认情况下，并行DML使用直接路径写入，但是您可以使用提示 NOAPPEND 禁用直接路径。这将允许该语句使用并行性，但使用常规写入。它的速度不如并行直接路径写入快，但至少比单线程常规写入要好。

Parallel DML uses direct-path writes by default but you can disable direct-path with the hint NOAPPEND. That will allow the statement to use parallelism but with conventional writes. It's not as fast as parallel direct-path writes, but it's at least better than single-threaded conventional writes.

这篇关于为什么DMML错误日志记录在并行语句中被忽略的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！