Enable Microsoft Transaction Server in ODBC client side cause direct path load not work

(Doc ID 2353035.1)

Last updated on FEBRUARY 02, 2018

Applies to:

Oracle Database - Standard Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

Symptoms

New Environment:

insert /*+ append */ into Z10_XXXXXXXXX nologging select
 W1.PRTNID, W1.VRSNNO, W1.COMPCD, W1.SGMTCD, W1.JOBID, W1.RAWID, W1.DATAID,
... ...
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          2          0           0
Execute      1     45.80      59.74          2     138988    1357035      415263
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     45.81      59.76          2     138990    1357035      415263

 Old Environment:

insert /*+ append */ into Z10_XXXXXXXXX nologging select
 W1.PRTNID, W1.VRSNNO, W1.COMPCD, W1.SGMTCD, W1.JOBID, W1.RAWID, W1.DATAID,
... ...
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          4          1           0
Execute      1     14.00      14.17      13805      15084      76678      415263
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     14.02      14.19      13805      15088      76679      415263

New Environment:

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        0          0          0  LOAD TABLE CONVENTIONAL  (cr=138991 pr=2 pw=0 time=59746508 us) *<<<<--------------------------
   415263     415263     415263   HASH JOIN  (cr=14817 pr=0 pw=0 time=2056588 us cost=437943 size=16715006255 card=128671)
     1705       1705       1705    NESTED LOOPS  (cr=366 pr=0 pw=0 time=6525 us cost=85 size=129403603 card=1997)
        1          1          1     NESTED LOOPS OUTER (cr=4 pr=0 pw=0 time=43 us cost=2 size=58 card=1)
        1          1          1      TABLE ACCESS BY INDEX ROWID SMD_M_ALCTPTRN (cr=2 pr=0 pw=0 time=21 us cost=1 size=52 card=1)
        1          1          1       INDEX UNIQUE SCAN PK_SMD_M_ALCTPTRN (cr=1 pr=0 pw=0 time=12 us cost=0 size=0 card=1)(object id 57667)
        1          1          1      TABLE ACCESS BY INDEX ROWID D_JRNLTYPE (cr=2 pr=0 pw=0 time=13 us cost=1 size=6 card=1)
        1          1          1       INDEX UNIQUE SCAN PK_D_JRNLTYPE (cr=1 pr=0 pw=0 time=5 us cost=0 size=0 card=1)(object id 55797)
     1705       1705       1705     TABLE ACCESS FULL Z10_2016208_SMD_W_ALCT0030 (cr=362 pr=0 pw=0 time=4535 us cost=83 size=129287777 card=1997)
   114373     114373     114373    TABLE ACCESS FULL Z10_2016208_SMD_W_ALCT0020 (cr=14451 pr=0 pw=0 time=284570 us cost=3346 size=9061778610 card=139185)

 Old Environment:

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        0          0          0  LOAD AS SELECT  (cr=15087 pr=13805 pw=71776 time=14175939 us) *<<<<--------------------------  
   415263     415263     415263   HASH JOIN  (cr=14659 pr=13803 pw=0 time=2244500 us cost=381423 size=5631251845 card=43349)
     1705       1705       1705    NESTED LOOPS  (cr=351 pr=0 pw=0 time=6051 us cost=82 size=110158300 card=1700)
        1          1          1     NESTED LOOPS OUTER (cr=4 pr=0 pw=0 time=39 us cost=2 size=58 card=1)
        1          1          1      TABLE ACCESS BY INDEX ROWID SMD_M_ALCTPTRN (cr=2 pr=0 pw=0 time=20 us cost=1 size=52 card=1)
        1          1          1       INDEX UNIQUE SCAN PK_SMD_M_ALCTPTRN (cr=1 pr=0 pw=0 time=11 us cost=0 size=0 card=1)(object id 57667)
        1          1          1      TABLE ACCESS BY INDEX ROWID D_JRNLTYPE (cr=2 pr=0 pw=0 time=12 us cost=1 size=6 card=1)
        1          1          1       INDEX UNIQUE SCAN PK_D_JRNLTYPE (cr=1 pr=0 pw=0 time=5 us cost=0 size=0 card=1)(object id 55797)
     1705       1705       1705     TABLE ACCESS FULL Z10_2016208_SMD_W_ALCT0030 (cr=347 pr=0 pw=0 time=4080 us cost=80 size=110059700 card=1700)
   114373     114373     114373    TABLE ACCESS FULL Z10_2016208_SMD_W_ALCT0020 (cr=14308 pr=13803 pw=0 time=559098 us cost=3182 size=7889024232 card=121172)

Changes

 In the new environment, the Microsoft Transaction Server (MTS) in ODBC client side was enabled.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms