SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

slow SAS query reading Oracle

Accepted Solution Solved
Reply
User hw
User
Posts: 1
Accepted Solution

slow SAS query reading Oracle

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;


Accepted Solutions
Solution
‎03-03-2017 09:22 AM
Super User
Posts: 5,434

Re: slow SAS query reading Oracle

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.
Data never sleeps

View solution in original post


All Replies
SAS Employee
Posts: 215

Re: slow SAS query reading Oracle

[ Edited ]

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

Respected Advisor
Posts: 4,173

Re: slow SAS query reading Oracle

[ Edited ]

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#n05b4mygsvt845n1vn...

 

 

Solution
‎03-03-2017 09:22 AM
Super User
Posts: 5,434

Re: slow SAS query reading Oracle

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.
Data never sleeps
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 396 views
  • 1 like
  • 4 in conversation