SASEPFUNC Table Function

The SASEPFUNC table function is the interface for running the scoring model inside Oracle with the SAS Embedded Process. The SASEPFUNC table function performs the scoring based on the parameters that are passed to it. It uses the .ds2 and .ufmt XML files that are stored in the model table.

This function is created by the SASADMIN user when the in-database deployment package is installed and configured. For more information, see the SAS In-Database Products: Administrator's Guide.

You can use the SASEPFUNC table function using explicit pass-through and PROC SQL or you can use other Oracle query tools such as SQLPlus. Use the SASEPFUNC function in the FROM clause in any SQL expression to run the scoring model.

The syntax of the SASEPFUNC table function is as follows.

Here is an example using PROC SQL.

proc sql;
connect to oracle (user=userid password=xxxx path=mydatabase);
create table work.sas_score_out1 as select * from connection to oracle
   (SELECT * from table(SASEPFUNC(CURSOR(select * from intrtree), 
      'myschema.SAS_PUBLISH_MODEL', 'INTRREG1', 'null',
      'select * from intrtree'));
disconnect from oracle;quit;

The performance of the Scoring Accelerator for Oracle can be affected by altering the Degree of Parallelism (DOP) setting. In a Real Application Clusters (RAC) environment the parallel processes are distributed among the available database instances when the chosen DOP exceeds the expected capabilities of a single node. In environments with mixed workloads and multi-concurrency, you should rely on the parallelism provided by the Oracle database. However, to achieve maximum throughput for dedicated Scoring Accelerator operations, you might consider adjusting the DOP setting.

Because Oracle and SAS use separate threads during execution, improvements throughput diminish for DOP values that are greater than half the total number of cores available. For example, if you have 128 cores total available for all instances, a DOP greater than 64 is not likely to yield improved performance. Setting the DOP up to this maximum level assumes that the system is solely dedicated to running the SAS Scoring Accelerator. For a mixed load system, a lower DOP value might be more appropriate.

Along with the DOP, adjusting the value of the internal parameter _parallel_load_bal_unit can also be beneficial to distributed performance. This parameter affects the number of threads allocated for a given RAC instance before a parallel query engages additional instances. Similar to DOP, setting _parallel_load_bal_unit parameter beyond half the core total per instance is not likely to be beneficial.