BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hw
Pyrite | Level 9 hw
Pyrite | Level 9

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;

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20
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

3 REPLIES 3
JBailey
Barite | Level 11

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

Patrick
Opal | Level 21

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

 

 

LinusH
Tourmaline | Level 20
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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