• Print |
• E-mail  |
• Bookmark

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

Operating System and Release Information