We’re smarter together. Learn from this collection of community knowledge and add your expertise.

DBIDIRECTEXEC: GOFAST=YES for Database Processing

by SAS Employee JBailey on ‎03-20-2017 02:27 PM - edited on ‎05-15-2017 02:35 PM by Community Manager (1,036 Views)

DBIDIRECTEXEC - Lets the SQL pass-through facility optimize handling of SQL statements by passing them directly to the database for execution. Our examples will show msglevel=i. This option will print a message stating that the statement was executed by the database (in this post - Oracle).

 

There is another option which turns this off - NODBIDIRECTEXEC.

Enabling DBIDIRECTEXEC can greatly increase the performance of your SAS. This is best shown using examples.

 

Let's setup the examples by creating a simple test table. We can use SASHELP.CARS for this.

 

 

 

1    libname myora oracle path=oraserv user=myuser password=XXXXXX;
NOTE: Libref MYORA was successfully assigned as follows:
      Engine:        ORACLE
      Physical Name: exadata

2    /* create a table to play with */
3    data myora.cars;
4       set sashelp.cars;
5    run;

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: There were 428 observations read from the data set SASHELP.CARS.
NOTE: The data set MYORA.CARS has 428 observations and 15 variables.
NOTE: DATA statement used (Total process time):
      real time           3.44 seconds
      cpu time            0.09 seconds

 

CREATE TABLE AS SELECT Statement Example

 

For Oracle, NODBIDIRECTEXEC is the default. This means we will need to set DBIDIRECTEXEC. We will do this and run a CREATE TABLE AS SELECT test. The key here is to ensure that the entire CTAS statement is run in Oracle.

 

 

6
7    /* set dbidirectexec and ensure that we can see what is happening */
8    options sastrace=',,,d' sastraceloc=saslog nostsuffix;
9    options dbidirectexec sql_ip_trace=note msglevel=i;
10
11   /* create an empty table like the cars one */
12   /* this entire statement should be run in oracle - data should not pass through sas */
13   proc sql;
14      create table myora.cars_CTAS  as select * from myora.cars;

ORACLE_1: Prepared: on connection 0
SELECT * FROM CARS_CTAS


ORACLE_2: Prepared: on connection 0
SELECT * FROM CARS


ORACLE_3: Executed: on connection 1
CREATE TABLE cars_CTAS as select TXT_1."MAKE", TXT_1."MODEL", TXT_1."TYPE", TXT_1."ORIGIN",
TXT_1."DRIVETRAIN", TXT_1."MSRP", TXT_1."INVOICE", TXT_1."ENGINESIZE", TXT_1."CYLINDERS",
TXT_1."HORSEPOWER", TXT_1."MPG_CITY", TXT_1."MPG_HIGHWAY", TXT_1."WEIGHT", TXT_1."WHEELBASE",
TXT_1."LENGTH" from CARS TXT_1

ORACLE:  *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*

ORACLE_4: Prepared: on connection 0
SELECT * FROM CARS_CTAS

SQL_IP_TRACE: The CREATE statement was passed to the DBMS.
15   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.76 seconds
      cpu time            0.06 seconds


SCHEMA used:  myuser

ORACLE_5: Prepared: on connection 1
SELECT OBJECT_NAME ,OBJECT_TYPE  FROM  USER_OBJECTS  OBJ WHERE (OBJ.OBJECT_TYPE  IN
('TABLE','VIEW'))


ORACLE_6: Executed: on connection 1
SELECT statement  ORACLE_5

 

INSERT AS SELECT Statement Example

 

Next, let's take a look at an INSERT AS SELECT (IAS) statement. The setup for this test is to create an empty copy of the CARS table and then run the IAS statement. This statement should be run entirely in Oracle. 

 

 

16   /* insert as select example */
17   /* need an empty table for the example - this should be handled entirely in oracle */
18   proc sql;
19      create table myora.cars_IAS  as select * from myora.cars where 0=1;

ORACLE_7: Prepared: on connection 0
SELECT * FROM CARS_IAS


ORACLE_8: Prepared: on connection 0
SELECT * FROM CARS


ORACLE_9: Executed: on connection 1
CREATE TABLE cars_IAS as select TXT_1."MAKE", TXT_1."MODEL", TXT_1."TYPE", TXT_1."ORIGIN",
TXT_1."DRIVETRAIN", TXT_1."MSRP", TXT_1."INVOICE", TXT_1."ENGINESIZE", TXT_1."CYLINDERS",
TXT_1."HORSEPOWER", TXT_1."MPG_CITY", TXT_1."MPG_HIGHWAY", TXT_1."WEIGHT", TXT_1."WHEELBASE",
TXT_1."LENGTH" from CARS TXT_1 where  0 = 1

ORACLE:  *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*

ORACLE_10: Prepared: on connection 0
SELECT * FROM CARS_IAS

SQL_IP_TRACE: The CREATE statement was passed to the DBMS.
20   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.06 seconds
      cpu time            0.00 seconds


SCHEMA used:  myuser

ORACLE_11: Prepared: on connection 1
SELECT OBJECT_NAME ,OBJECT_TYPE  FROM  USER_OBJECTS  OBJ WHERE (OBJ.OBJECT_TYPE  IN
('TABLE','VIEW'))


ORACLE_12: Executed: on connection 1
SELECT statement  ORACLE_11

21
22   /* insert as select should be handled entirely in oracle */
23   /* the hint makes this a direct path load - think bulk load */
24   proc sql;
25      insert into myora.cars_IAS (ORHINTS='/*+ APPEND PARALLEL*/')
26          select * from myora.cars;

ORACLE_13: Prepared: on connection 0
SELECT  /*+ APPEND PARALLEL*/ * FROM CARS_IAS


ORACLE_14: Prepared: on connection 0
SELECT * FROM CARS


ORACLE_15: Executed: on connection 1
insert  /*+ APPEND PARALLEL*/ into CARS_IAS ("MAKE", "MODEL", "TYPE", "ORIGIN", "DRIVETRAIN",
"MSRP", "INVOICE", "ENGINESIZE", "CYLINDERS", "HORSEPOWER", "MPG_CITY", "MPG_HIGHWAY",
"WEIGHT", "WHEELBASE", "LENGTH") select TXT_2."MAKE", TXT_2."MODEL", TXT_2."TYPE",
TXT_2."ORIGIN", TXT_2."DRIVETRAIN", TXT_2."MSRP", TXT_2."INVOICE", TXT_2."ENGINESIZE",
TXT_2."CYLINDERS", TXT_2."HORSEPOWER", TXT_2."MPG_CITY", TXT_2."MPG_HIGHWAY", TXT_2."WEIGHT",
TXT_2."WHEELBASE", TXT_2."LENGTH" from CARS TXT_2

ORACLE: 428 rows inserted/updated/deleted.
ORACLE:  *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
27   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           1.37 seconds
      cpu time            0.06 seconds

 

 

UPDATE Statement Example

 

This example shows updating a subset of the data. Notice that you don't see a SQL_IP_TRACE message here.

 

 

28   /* update example */
29   /* the update statement should be passed entirely to oracle */
30   proc sql;
31      update myora.cars_IAS
32        set make='bogus'
33        where make='Audi';

ORACLE_16: Prepared: on connection 0
SELECT * FROM CARS_IAS


ORACLE_17: Executed: on connection 1
update CARS_IAS set CARS_IAS."MAKE" = 'bogus' where CARS_IAS."MAKE" = 'Audi'

ORACLE: 19 rows inserted/updated/deleted.
ORACLE:  *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
34   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds

 

DELETE Statement Example

This example shows deleting a subset of the data stored in the table. 

 

 

35
36   /* delete example */
37   proc sql;
38      delete from myora.cars_IAS
39        where make='bogus';

ORACLE_18: Prepared: on connection 0
SELECT * FROM CARS_IAS


ORACLE_19: Executed: on connection 1
delete from cars_IAS where cars_IAS."MAKE" = 'bogus'

ORACLE: 19 rows inserted/updated/deleted.
ORACLE:  *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
SQL_IP_TRACE: The DELETE was passed to the DBMS.
40   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

 

Delete the entire contents of the the table. It is important that this happen all in Oracle. That being said, there are better ways to do this in Oracle (TRUNCATE statement).

 

 

41   /* delete all the data in the table - but it doesn't reset the highwater mark */
42   /* explicit truncate command would be a better choice */
43   proc sql;
44      delete from myora.cars_IAS;

ORACLE_20: Prepared: on connection 0
SELECT * FROM CARS_IAS


ORACLE_21: Executed: on connection 1
delete from cars_IAS

ORACLE: 409 rows inserted/updated/deleted.
ORACLE:  *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
SQL_IP_TRACE: The DELETE was passed to the DBMS.
45   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds

 

NODBIDIRECTEXEC Example

 

Just for fun, let's look at an example where the statement is not passed to Oracle. The SQL_IP_TRACE message tells us that the statement was not passed to Oracle. In fact, SAS read the data and then passed it back to Oracle. We know this because of the litany of INSERT statements. This is not good. Take some time to compare this to the IAS statement that was handled entirely by Oracle.

 

 

46   /* nodbidirectexec - what happens? */
47   
48   options nodbidirectexec;
49   proc sql;
50      insert into myora.cars_IAS (ORHINTS='/*+ APPEND PARALLEL*/')
51          select * from myora.cars;
WARNING: This SQL statement is not allowed to be passed directly to the DBMS for processing
         because the DBIDIRECTEXEC system option is not turned on by the user or is
         temporarily turned off by Proc SQL.

ORACLE_22: Prepared: on connection 2
SELECT  /*+ APPEND PARALLEL*/ * FROM CARS_IAS


ORACLE_23: Prepared: on connection 0
SELECT * FROM CARS

SQL_IP_TRACE: None of the SQL was directly passed to the DBMS.

ORACLE_24: Executed: on connection 0
SELECT statement  ORACLE_23


ORACLE_25: Prepared: on connection 2
INSERT  /*+ APPEND PARALLEL*/  INTO CARS_IAS
("MAKE","MODEL","TYPE","ORIGIN","DRIVETRAIN","MSRP","INVOICE","ENGINESIZE","CYLINDERS","HORSEPO
WER","MPG_CITY","MPG_HIGHWAY","WEIGHT","WHEELBASE","LENGTH") VALUES
(:"MAKE",:"MODEL",:"TYPE",:"ORIGIN",:"DRIVETRAIN",:"MSRP",:"INVOICE",:"ENGINESIZE",:"CYLINDERS"
,:"HORSEPOWER",:"MPG_CITY",:"MPG_HIGHWAY",:"WEIGHT",:"WHEELBASE",:"LENGTH")


ORACLE_26: Executed: on connection 2
INSERT statement  ORACLE_25


ORACLE_27: Executed: on connection 2
INSERT statement  ORACLE_25


ORACLE_28: Executed: on connection 2
INSERT statement  ORACLE_25


ORACLE_29: Executed: on connection 2
INSERT statement  ORACLE_25

... snip


ORACLE_67: Executed: on connection 2
INSERT statement  ORACLE_25


ORACLE_68: Executed: on connection 2
INSERT statement  ORACLE_25

ORACLE:  *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
NOTE: 428 rows were inserted into MYORA.CARS_IAS.

ORACLE:  *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
52   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           3.34 seconds
      cpu time            0.18 seconds


 

Here is the SAS code in case you want to try this...

 

 

libname myora oracle path=oraserv user=myuser password=mypassword;

/* create a table to play with */
data myora.cars;
   set sashelp.cars;
run;

/* set dbidirectexec and ensure that we can see what is happening */
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
options dbidirectexec sql_ip_trace=note msglevel=i;

/* create an empty table like the cars one */
/* this entire statement should be run in oracle - data should not pass through sas */
proc sql;
   create table myora.cars_CTAS  as select * from myora.cars;
quit;

/* insert as select example */
/* need an empty table for the example - this should be handled entirely in oracle */
proc sql;
   create table myora.cars_IAS  as select * from myora.cars where 0=1;
quit;

/* insert as select should be handled entirely in oracle */
/* the hint makes this a direct path load - think bulk load */
proc sql;
   insert into myora.cars_IAS (ORHINTS='/*+ APPEND PARALLEL*/')
       select * from myora.cars;
quit;


/* update example */
/* the update statement should be passed entirely to oracle */
proc sql;
   update myora.cars_IAS 
     set make='bogus'
     where make='Audi';
quit;

/* delete example */
proc sql;
   delete from myora.cars_IAS 
     where make='bogus';
quit;

/* delete all the data in the table - but it doesn't reset the highwater mark */
/* explicit truncate command would be a better choice */
proc sql;
   delete from myora.cars_IAS;
quit;

/* nodbidirectexec - what happens? */
/* This does not work properly;) */
options nodbidirectexec;
proc sql;
   insert into myora.cars_IAS (ORHINTS='/*+ APPEND PARALLEL*/')
       select * from myora.cars;
quit;

 

 

Comments
by Frequent Contributor
on ‎03-20-2017 06:06 PM

Interesting article.  I do a lot of back and forth between Oracle and SAS, it took me a lot of time to learn most of the stuff you wrote up since it's not in one convenient place.  I did notice you used the SAS option SQL_IP_TRACE, which I can't find very much documentation on from SAS's help or elsewhere.  Can you point me to someplace that explains this option and the values it can take?

by SAS Employee JBailey
on ‎03-22-2017 01:36 PM

Hi @Sven111

 

The option is undocumented. I will write-up a Communities article on it. Expect to see it sometime next week.

 

Best wishes,

Jeff

by PROC Star
on ‎05-16-2017 01:17 PM

This is cool stuff. I guess this option is available for other RDBMS too.

by SAS Employee JBailey
on ‎05-16-2017 01:33 PM

Hi @kiranv_

 

It sure is. Check out: DBIDIRECTEXEC SAS System Option

 

Here is the list of supported data sources: Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, Hadoop, HAWQ, Impala, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, Teradata, Vertica

 

DBIDIRECTEXEC is the default for Amazon Redshift

 

NODBIDIRECTEXEC is the default for all the others. That is why I say DBIDIRECTEXEC is like GOFAST=yes.

by Frequent Contributor
on ‎05-16-2017 01:44 PM

Hi @JBailey, did you ever write up that article on SQL_IP_TRACE?  If so could you send me a link?

by PROC Star
on ‎05-16-2017 01:51 PM

I am not sure whether the code will passed to DBMS,  if we have SAS specific function.

by SAS Employee JBailey
‎05-16-2017 02:05 PM - edited ‎05-16-2017 02:06 PM

Hi @kiranv_

 

It depends on if the SAS function will pass. It depends on which database you are using. This is covered in the doc. 

 

Pay attention to SQL_FUNCTIONS=. Setting it to all increases the number of functions that can be passed to the DBMS.

 

The best way to tell if the function is going to pass is to use the following OPTIONS line to see what happens.

 

OPTIONS SASTRACE=',,,d' SASTRACELOC=saslog NOSTSUFFIX;

 

 

by PROC Star
‎05-16-2017 02:12 PM - edited ‎05-16-2017 02:13 PM

Thanks for reply. I was thinking too  it might depend on functions. Just completed your paper on same topic in SUGI 2017. By the way, I am huge fan of your papers.  just want to say your papers have impacted my learning and indirectly they have impact on companies I have worked . Thank you sir.

by SAS Employee JBailey
on ‎05-16-2017 02:15 PM

Hi @kiranv_

 

You, my friend, have made my day! Thank you so much. 

Contributors
Your turn
Sign In!

Want to write an article? Sign in with your profile.


Looking for the Ask the Expert series? Find it in its new home: communities.sas.com/askexpert.