A client has an ETL process that checks for success by ensuring –
Source Rows = Rows inserted + Rows in DML error logging table.
This is normally fine, however one needs to be aware that rows inserted by DML error logging are done so autonomously, outside the parent transaction. Thus you need to be careful when combining [Rows Inserted] (which is atomic) and [Rows in DML error logging table] (which is not).
As an example, if you combine all four of the below conditions you’ll get a scenario that’ll cause the above check to fail unnecessary.
Create demonstration table with indexes:
SQL> ALTER SESSION SET deferred_segment_creation = true Session altered. SQL> CREATE TABLE DML_DFC (ID VARCHAR2(30) ,NAME VARCHAR2(20) ,ACTIVE varchar2(1 byte)) PARTITION BY LIST (ACTIVE) ( PARTITION P_ACTIVE VALUES ('Y'), PARTITION P_INACTIVE VALUES (NULL) ) Table created. SQL> BEGIN dbms_errlog.create_error_log(dml_table_name => 'DML_DFC', err_log_table_name => 'DML_DFC_ERR'); END; PL/SQL procedure successfully completed. SQL> CREATE UNIQUE INDEX DFX_PK ON DML_DFC(id) Index created. SQL> CREATE UNIQUE INDEX DFC_IX01 ON DML_DFC(NAME) Index created.
Confirm Deferred Segment Creation:
SQL> SELECT count(*) FROM user_extents WHERE segment_name = 'DML_DFC' COUNT(*) ---------- 0 1 row selected.
Capture a few useful statistics before running the test case:
SQL> select name, value FROM v$sesstat a INNER JOIN v$statname b ON a.statistic# = b.statistic# AND b.name like '%rollbacks%' AND a.sid = (select sid from v$mystat where rownum = 1) order by 1 NAME VALUE ---------------------------------------------------------------- ---------- IMU CR rollbacks 0 cleanouts and rollbacks - consistent read gets 0 rollbacks only - consistent read gets 0 transaction rollbacks 0 transaction tables consistent read rollbacks 0 user rollbacks 0
Simulate an ETL load with four source rows:
SQL> MERGE INTO DML_DFC dst USING ( SELECT * FROM ( select '1' AS id ,null AS name ,null as ACTIVE FROM DUAL UNION ALL select '1' AS id ,null AS name ,null as ACTIVE FROM DUAL UNION ALL select '2' AS id ,'hello' AS name ,null as ACTIVE FROM DUAL UNION ALL select '3' AS id ,'hello' AS name ,null as ACTIVE FROM DUAL ) ORDER BY 1,2 ) src ON (dst.id = src.id) WHEN NOT MATCHED THEN INSERT VALUES (src.id ,src.name ,src.active) LOG ERRORS INTO DML_DFC_err('tdh') REJECT LIMIT UNLIMITED
Monitor the changes to statistics captured earlier:
SQL> select name, value FROM v$sesstat a INNER JOIN v$statname b ON a.statistic# = b.statistic# AND b.name like '%rollbacks%' AND a.sid = (select sid from v$mystat where rownum = 1) order by 1 NAME VALUE ---------------------------------------------------------------- ---------- IMU CR rollbacks 0 cleanouts and rollbacks - consistent read gets 0 rollbacks only - consistent read gets 0 transaction rollbacks 1 transaction tables consistent read rollbacks 0 user rollbacks 0
Interesting – At some point during the statement executing, Oracle has performed an implicit rollback and restart. If you weren’t aware that Oracle could do this, see Tom Kyte’s series on Transaction Restarts – Part i is here.
That series, from 2005, predate Deferred Segment Creation so it’s not a scenario that’ll be mentioned there.
Check the inserted and rejected row counts:
SQL> SELECT count(*) FROM DML_DFC COUNT(*) ---------- 2 1 row selected. SQL> SELECT count(*) FROM DML_DFC_err COUNT(*) ---------- 3 1 row selected.
Oh dear – We had 4 source rows, we’ve inserted 2 but we’ve managed to reject 3 rows!
For the moment, the application in question has disabled deferred segment creation but the strategic fix is to find an implementation that doesn’t have an implicit expectation that DML error logging is transactional.