My Oracle Support Banner

Enable Microsoft Transaction Server in ODBC client side cause direct path load not work (Doc ID 2353035.1)

Last updated on AUGUST 04, 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

To view full details, 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 a vibrant support community of peers and Oracle experts.