Wrong result when reading data from Oracle using the DBSPLICEPARM option


If you reach the SESSIONS_PER_USER limit in Oracle, SAS® generates a partial result set. SAS/ACCESS® Interface to Oracle opens one connection per "partition" it fetches from the table. If the sessions_per_user limit is reached, an empty result set is returned from that connection. The following options will reveal this behavior:

options sastrace=',,t,' sastraceloc=saslog nostsuffix;

The excerpt from a SAS log demonstrates the problem:

1 proc sql; 
2 create table x as
3 select * from ora.prd_part;

 ORACLE:  DBSLICEPARM option set and 4 threads were requested
 ORACLE:  No application input on number of threads.
 ORACLE:  Thread 2 contains 0 obs.
 ORACLE:  Thread 3 contains 0 obs.
 ORACLE:  Thread 1 contains 1440 obs.
 ORACLE:  Thread 4 contains 1440 obs.
 ORACLE:  Threaded read enabled. Number of threads created: 4
 NOTE: Table WORK.X created, with 2880 rows and 10 columns.

In the above example each thread should have contained 1440 observations resulting in 5760 observations. Increasing SESSIONS_PER_USER or disabling RESOURCE_LIMIT in Oracle solves the problem. The following is executed in SQL*Plus:

SQL> create profile SESSION_LIMITER limit sessions_per_user 100
SQL> alter user SCOTT profile SESSION_LIMITER

or

SQL> alter system set resource_limit = false

Hereafter the above SAS example will show:

1 proc sql;
2 create table x as
3 select * from ora.prd_part;

 ORACLE:  DBSLICEPARM option set and 4 threads were requested
 ORACLE:  No application input on number of threads.
 ORACLE:  Thread 2 contains 1440 obs.
 ORACLE:  Thread 3 contains 1440 obs.
 ORACLE:  Thread 1 contains 1440 obs.
 ORACLE:  Thread 4 contains 1440 obs.
 ORACLE:  Threaded read enabled. Number of threads created: 4
 NOTE: Table WORK.X created, with 5760 rows and 10 columns.

On the UNIX platform no error is written to the log.

On the WINDOWS platform the following errors appear after the SELECT has ended:

ERROR: Thread has been terminated due to an unhandled exception.
ERROR: Address Frame (DBGHELP API Version 4.0 rev 5)
ERROR: 56B72624 0666FBD8 sasora:mcn_main+0x1624
ERROR: 56B715C2 0666FC24 sasora:mcn_main+0x5C2
ERROR: 56B84CB9 0666FC6C sasioora:mcn_main+0x3CB9
ERROR: 56B83E9A 0666FDB0 sasioora:mcn_main+0x2E9A
ERROR: 65A288A4 0666FF1C sasxdbi:mcn_main+0x78A4
ERROR: 65A50E3F 0666FF68 sasxdbi:mcn_main+0x2FE3F
ERROR: 65A50DF3 0666FF84 sasxdbi:mcn_main+0x2FDF3
ERROR: 5CBA1B23 0666FFA8 tkmk:tkBoot+0xAFF
ERROR: 5CBA29DB 0666FFB4 tkmk:tkBoot+0x19B7
ERROR: 7C57438B 0666FFEC KERNEL32:TlsSetValue+0xF0