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

Hi,

This post might best be posted to an Oracle forum, but I thought I'd test the waters here.  Also, I hope this is the correct SAS Communities forum - please advise if you disagree.  I don't see a forum for "access to external database" questions.

I'm modifying an existing SQL pass through query to Oracle.  The previous query returned either one, or only a small number, of rows from a large Oracle table.  The query was coded as a code generated where clause and then passed to Oracle.  There are two variables in the query (where) criteria, and Oracle is indexed on these variables.

I have to modify the query to get the list of records to return from another SAS dataset.  I've chosen to upload that dataset to Oracle temp space, then use an inner query to filter the data (via Oracle pass through).

In the previous query, even though the code generation looked "klunky", it would return say 5 records in say 1-2 seconds.  My query is now returning about 1000 records in say 20 minutes.

Some example code may help to illustrate the issue.  You'll have to use your imagination for the Oracle pass through bit, but this will run on your system and hopefully this helps explain things better:

* simulate Oracle temporary space, which I can write to via SAS/Access to Oracle ;

libname tmpora "%sysfunc(pathname(work))";

* this simulates the large table in Oracle ;

data work.class;

  set sashelp.class;

run;

* this simulates the upload of the filter criteria to Oracle temp space ;

data tmpora.filter;

  set sashelp.class;

  where name le "L";

run;

* option 1- this simulates the existing query ;

* this actual code is a bit klunkier than this, but the same approach applies ;

* however, this approach is actually quite performant ;

* my query could generate 1000's of name/age pairs - I'd have to worry about hitting the macro variable length limit ;

* I'd need a different code generation approach. ;

proc sql noprint _method;

  select catx(" AND ", "(" || cats("NAME=",quote(strip(name))), cats("AGE=",age) || ")") into :where separated by " OR "

  from tmpora.filter

  ;

  %put &where;

  create table test1 as

  select *

  from work.class

  where &where

  ;

quit;

* option 2 - this simulates my query rewrite ;

* this approach performs much worse than the first approach ;

* I suspect, but can't be sure, that Oracle is sorting the large table ;

* in order to do the inner join. ;


* Is there a "hint" I can give to force Oracle to use the existing index ;

* on the large table (name+age)??? ;


* Also, is there the equivalent of _method in Oracle SQL to get diagnostic ;

* information about how the query is executed??? ;

proc sql noprint _method;

  create table test2 as

  select a.*

  from work.class a

  inner join

tmpora.filter b

  on a.name=b.name and a.age=b.age

  ;

quit;

* the two approaches yield the same results ;

proc compare base=test1 compare=test2;

run;

As above, two questions:

1) Is there a "hint" I can give to force Oracle to use the existing index (name+age) on the large table, rather than sorting it?

2) Is there the equivalent of _method in Oracle SQL to get diagnostic information about how the query is executed?

Thanks,

Scott


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
1 ACCEPTED SOLUTION

Accepted Solutions
FriedEgg
SAS Employee

In Oracle, the syntax equivalant of the _method option is referred to as an explain plain and requested as follows:

EXPLAIN PLAN FOR

  SQL_Statement;



proc sql;

connect to oracle();

execute by oracle( EXPLAIN PLAN SET statement_id = 'my_explain_plan1' FOR

SQL_Statement;

);

disconnect from oracle;

quit;


Another step will be to analyze not your written sql, but the sql which SAS will actually generate to execute within oracle.  To get this information you will need to run it first with the sastrace system options set:


options sastrace=',,,d' sastraceloc=saslog nostsuffix;


In the log, you can grab the SQL executed inside Oracle based on your implicit SAS SQL statement, paste it into the explicit sql above and get your explain plan returned.


One you have run your explain plan, you will need to collect and print it from the plan table.


proc print data=ora.plan_table; where statement_id='my_explain_plan1'; run;

*then cleanup the plan table;

proc sql;

connect to oracle();

execute by oracle( delete from plan_table where statement_id='my_explain_plan1'; commit;);

disconnect from oracle;

quit;




So, clearly not as straight forward or simple, but possible...






In response to your inquiry about Oracle hint, there is a index hint, which you can use.  Given the specific circumstances at hand you will need to determine the best optimization plan for your query.  Here is a link for reference the this:  Oracle Hints SQL PL/SQL

View solution in original post

2 REPLIES 2
FriedEgg
SAS Employee

In Oracle, the syntax equivalant of the _method option is referred to as an explain plain and requested as follows:

EXPLAIN PLAN FOR

  SQL_Statement;



proc sql;

connect to oracle();

execute by oracle( EXPLAIN PLAN SET statement_id = 'my_explain_plan1' FOR

SQL_Statement;

);

disconnect from oracle;

quit;


Another step will be to analyze not your written sql, but the sql which SAS will actually generate to execute within oracle.  To get this information you will need to run it first with the sastrace system options set:


options sastrace=',,,d' sastraceloc=saslog nostsuffix;


In the log, you can grab the SQL executed inside Oracle based on your implicit SAS SQL statement, paste it into the explicit sql above and get your explain plan returned.


One you have run your explain plan, you will need to collect and print it from the plan table.


proc print data=ora.plan_table; where statement_id='my_explain_plan1'; run;

*then cleanup the plan table;

proc sql;

connect to oracle();

execute by oracle( delete from plan_table where statement_id='my_explain_plan1'; commit;);

disconnect from oracle;

quit;




So, clearly not as straight forward or simple, but possible...






In response to your inquiry about Oracle hint, there is a index hint, which you can use.  Given the specific circumstances at hand you will need to determine the best optimization plan for your query.  Here is a link for reference the this:  Oracle Hints SQL PL/SQL

ScottBass
Rhodochrosite | Level 12

Thanks Fried Egg, much appreciated.

My code is something like:

proc sql;

  connect to oracle (user=&ora_userid password="&ora_passwd" path=&ora_path);

  create table workspde.my_sas_table as

  select * from connection to oracle

    (

      select

        blah, blah, blah

      from

        foo a

      left join

        bar b

      on

        a.key1=b.key1

        and

        a.key2=b.key2

      left join

        blah c

      on

        c.key3=b.key3

        and

        c.key4=b.key4

      where

        c.somedate >= a.anotherDate

    );

  disconnect from oracle;


quit;


I assume in this scenario SAS is not generating the SQL for Oracle, but rather passing the code as written directly to Oracle?

Nevertheless, I'll try what you've suggested and see if I can track down the performance issue.  You've also given me some good searches for Google.

Regards,

Scott


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 4365 views
  • 0 likes
  • 2 in conversation