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.

1. DML Error Logging
2. 11.2.0.2
3. Deferred Segment Creation
4. Partitioning

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.

End to End instrumentation is an extremely powerful feature for investigating or monitoring performance across all layers of a modern multi-tiered application.

To facilitate this, the application is expected to tag each database operation with a MODULE and ACTION string. These are free form and can be populated with whatever is most appropriate for the application, however releases up to and including 11.2.0.1 have limited their lengths to – a restrictive – 48 and 32 bytes respectively.

When I was reviewing our recently upgraded 11.2.0.2 environment I was excited to discover:

SQL> SELECT
 2 column_name
 3 ,data_length
 4 FROM dba_tab_columns
 5 WHERE table_name = 'V_$SESSION'
 6 AND column_name IN ('MODULE','ACTION');

COLUMN_NAME                    DATA_LENGTH
------------------------------ -----------
MODULE                                  64
ACTION                                  64

Confirming this wasn’t simply the view definition, I checked the package specification for DBMS_APPLICATION_INFO:

procedure set_module(module_name varchar2, action_name varchar2);

-- Input arguments:
-- module_name
-- The name of the module that will now be running. The maximum
-- length of the module name is 64 bytes. Longer names will be
-- truncated.
-- action_name
-- The name of the action that will now be running. The maximum
-- length of the action_name is 64 bytes. Longer names will be
-- truncated. If the action name is not being specified, then null
-- should be passed for this value.

Excellent! That gives us a bit more to work with! Let’s try it…

SQL>
SQL> select lengthb('Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do') AS module_length_bytes
 2 ,lengthb('Ut enim ad minim veniam, quis nostrud exercitation ullamco labor') AS action_length_bytes
 3 from dual;

MODULE_LENGTH_BYTES ACTION_LENGTH_BYTES
------------------- -------------------
                 64                  64

SQL> begin
 2 dbms_application_info.set_module('Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do'
 3 ,'Ut enim ad minim veniam, quis nostrud exercitation ullamco labor');
 4 end;
 5 /

PL/SQL procedure successfully completed.

SQL> SELECT module
 2 ,length(module)
 3 ,action
 4 ,length(action)
 5 FROM v$session
 6 WHERE sid = (SELECT sid
 7 FROM v$mystat
 8 WHERE rownum = 1)
 9 /

MODULE                                                           LENGTH(MODULE) ACTION                                                           LENGTH(ACTION)
---------------------------------------------------------------- -------------- ---------------------------------------------------------------- --------------
Lorem ipsum dolor sit amet, consectetur adipisic                             48 Ut enim ad minim veniam, quis no                                             32

SQL> BEGIN
 2 dbms_monitor.serv_mod_act_trace_enable('RAC1'
 3 ,'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do'
 4 ,'Ut enim ad minim veniam, quis nostrud exercitation ullamco labor');
 5 END;
 6 /
BEGIN
*
ERROR at line 1:
ORA-12899: value too large for column "SYS"."WRI$_TRACING_ENABLED"."QUALIFIER_ID1" (actual: 64, maximum: 48)
ORA-06512: at "SYS.DBMS_MONITOR", line 78
ORA-06512: at "ORACLE.PK_DBMS_MONITOR", line 13
ORA-06512: at line 2

Oh well.. Guess it’s sticking to 48 and 32 bytes for now then.

UPDATE: Oracle support have pointed out the, rather odd, “_module_action_old_length” parameter:

SQL> alter session set "_module_action_old_length" = false;

Session altered.

SQL> begin
  dbms_application_info.set_module('Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do'
  ,'Ut enim ad minim veniam, quis nostrud exercitation ullamco labor');
end;
/

SQL> SELECT module
  ,length(module)
  ,action
  ,length(action)
  FROM v$session
  WHERE sid = (SELECT sid
  FROM v$mystat
  WHERE rownum = 1)
/

MODULE                                                           LENGTH(MODULE) ACTION                                                           LENGTH(ACTION)
---------------------------------------------------------------- -------------- ---------------------------------------------------------------- --------------
Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do             64 Ut enim ad minim veniam, quis nostrud exercitation ullamco labor             64

So you can take advantage of the larger fields on 11.2.0.3. However, the usefulness of doing so is questionable so far as you still can’t enable tracing for values longer than the “old lengths”

SQL> BEGIN
  dbms_monitor.serv_mod_act_trace_enable('RAC1'
  ,'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do'
  ,'Ut enim ad minim veniam, quis nostrud exercitation ullamco labor');
END;
/  2    3    4    5    6  
BEGIN
*
ERROR at line 1:
ORA-12899: value too large for column "SYS"."WRI$_TRACING_ENABLED"."QUALIFIER_ID1" (actual: 64, maximum: 48)
ORA-06512: at "SYS.DBMS_MONITOR", line 78
ORA-06512: at line 2


SQL> set lin 100
SQL> desc "SYS"."WRI$_TRACING_ENABLED"
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 TRACE_TYPE                                            NOT NULL NUMBER
 PRIMARY_ID                                                     VARCHAR2(64)
 QUALIFIER_ID1                                                  VARCHAR2(48)
 QUALIFIER_ID2                                                  VARCHAR2(32)
 INSTANCE_NAME                                                  VARCHAR2(16)
 FLAGS                                                          NUMBER