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;
