topic slow SAS query reading Oracle in SAS Data Management

slow SAS query reading Oracle

hw — Fri, 10 Feb 2017 14:03:56 GMT

I use SAS 9.3 pass-through to send SQL to an Oracle 10g database to read data from a view.

Usually the query runs just fine.

Sometimes the query runs extremely slow.

Are there SAS options that I can turn on to try to understand what the issue is?

(Note: I don't believe it is the load on the server nor running during a database maintenance window).

Thanks.

Type of query shown below:

proc sql noprint;
connect to oracle as myconn (user=x password=y path=z);
create table test as select *
from connection to myconn(
select * from T.V
where my_condition = 'TRUE');
disconnect from myconn;
quit;

Re: slow SAS query reading Oracle

JBailey — Mon, 13 Feb 2017 13:43:46 GMT

Hi @hw

There is a SAS Global Forum paper from a few years back that covers how to approach the exact type of problem you are facing.

https://support.sas.com/resources/papers/proceedings13/080-2013.pdf

The fullstimer SAS option will show you where your job is spending its time. There are SAS papers that describe this option in great detail.

Your code uses explicit pass-through so you know exactly what is being sent to Oracle. The question is where is the time being spent: writing by SAS, network traffic, or Oracle query. Since the query runs fine most of the time it means that some "special" something is happening to change this. It could be increased, sporatic, activity on any, or all, of the three items mentioned above.

Your first step is to show your query to the Oracle DBA and ask them if they see a problem. Start keeping a record of the times that the program runs slowly. This information can help.

Best wishes,
Jeff

Re: slow SAS query reading Oracle

Patrick — Sun, 12 Feb 2017 22:51:21 GMT

This is not an explanation for the performance fluctuation you're observing, but setting an explicit value to something higher than the default for BUFFSIZE could improve your end-to-end performance in general.

http://support.sas.com/documentation/cdl/en/acreldb/65247/HTML/default/viewer.htm#n05b4mygsvt845n1vnr6r5kchbjf.htm

Re: slow SAS query reading Oracle

LinusH — Mon, 13 Feb 2017 06:58:02 GMT

SAS options like the ones suggested only shows what's going on at the SAS host. Any network or Oracle problems doesn't get analyzed. I suggest that you work close to your DBA as an initial effort.