Andrew Holdsworth
Director Real World and ISV
Performance
Oracle Corporation
Howard Plemmons
Senior Software Manager
SAS Institute Inc.
Oracle and SAS Development
Status





Introduction
Oracle/SAS Certification Status
Oracle Technology Adoption at SAS
Technical Solutions Using Oracle and SAS
Oracle Database 10g New Features
Applicable to SAS Users
 Oracle/SAS Forums
Oracle/SAS Certification Status
SAS
Release
Platforms
Comments
V8 SAS
Windows, UNIX, MVS,
VMS
V9 SAS
Windows, UNIX, MVS,
VMS
32 and 64 Bit, All
Supported Oracle
releases
32 and 64 Bit,
Oracle 8.1.7 and
up
 Also See
http://www.sas.com/service/techsup/access/searchPage.hsql
Current Oracle Technology
Adoption at SAS
•
•
•
•
OCI programming
Partitioning and Parallelism
RAC Implementation
LOBS
Technical Solutions
Using Oracle and SAS
Considerations
 Can I predict/project data/hardware needs to
meet short term goals
 What questions can I ask of the data
 What questions should I ask of the data
 What are the expectations for answers
 How can I make SAS and Oracle more
efficient
SAS
What is SAS
 SAS Intelligence Architecture
–
–
Component Parts (i.e. ETL, Data Warehousing,
Analytics, BI, Reporting, Portability)
Leverage your infrastructure
 Infrastructure
–
Components
 Oracle DBMS
 OS
 Platform
SAS – Intelligence Value Chain
Intelligence Value Chain (IVC)




Planning
SAS/ACCESS products (ETL)
Leverage Oracle Infrastructure
Performance Optimizations SAS 9
SAS & Oracle Release Matrix
 http://www.sas.com/service/techsup/access/listPage.h
sql
 SAS V9
–
–
64 bit OS support
PC/unix/MVS/midrange
SAS/ACCESS
 SAS V9 Supports Oracle (8.1.7.2, 9i and 10G)
on:
–
–
–
–
Windows – XP, NT, 2000, W64
Unix – 64 bit platforms (HP, SUN, AIX, ALX), 32
bit linux, HP Itanium
MVS
Midrange – Alpha VMS
GA 2003
What is SAS/ACCESS
 Terminology
–
–
–
–
–
SAS I/O model
SAS V9
SAS MVA
SAS Procedures
SAS Options
SAS I/O model – SAS 9
SAS Applications
Engine Supervisor
XOT
Access Engine
XOT
Oracle DBMS Client
ORACLE
DBMS
sort,reg,dmreg,
summary…
SAS/ACCESS Today
SAS 9 Features
 Threaded read
 Temporary table support
 Detailed trace information
 Metadata support
SAS Threaded Applications
 SAS V9 Threaded Applications
–
–
–
–
–
–
–
–
PROC SORT
PROC SUMMARY
PROC DMINE
PROC MEANS
PROC REG; PROC DMREG
PROC GLM
PROC ROBUSTREG
PROC LOESS; PROC DMDB
SAS/Oracle User Concerns
 Implicit vs. Explicit SQL
 Performance issues
 How SAS accommodates new Oracle
releases
 Many options to choose from
 Supporting older components
 Masking passwords
SAS Performance
 Implicit SQL
proc sql;
INSERT INTO oradata.quartly_city_iias
SELECT T.FISCAL_QUARTER_DESC, C.CUST_CITY,
COUNT(*) AS SALE_COUNT,
SUM(S.AMOUNT_SOLD) AS SALE_DOLLARS
FROM oradata.CUSTOMERS C, oradata.SALES S,
oradata.TIMES T
WHERE C.CUST_ID = S.CUST_ID
AND T.TIME_ID = S.TIME_ID
GROUP BY T.FISCAL_QUARTER_DESC, C.CUST_CITY;
quit;
SAS Performance
 Explicit SQL
proc sql;
connect to ORACLE as ORACON (user=sh password=sh1
path='demo.na.sas.com');
execute (insert /*+ APPEND */ into quartly_city_ii as
select T.FISCAL_QUARTER_DESC, C.CUST_CITY, COUNT(*) AS
SALE_COUNT,
SUM(S.AMOUNT_SOLD) AS SALE_DOLLARS
FROM CUSTOMERS C, SALES S, TIMES T
WHERE C.CUST_ID = S.CUST_ID AND T.TIME_ID = S.TIME_ID
GROUP BY T.FISCAL_QUARTER_DESC, C.CUST_CITY) by ORACON;
disconnect from ORACON;
quit;
SAS Performance
 Analyze SQL
proc sql;
create table work.friday_ny_sales
as
SELECT T.DAY_NAME ,
C.CUST_CITY ,
COUNT(*) AS SALE_COUNT,
SUM(S. AMOUNT_SOLD ) AS SALE_DOLLARS
FROM oradata.CUSTOMERS C, oradata.SALES S, oradata.TIMES T
WHERE (C.CUST_ID = S.CUST_ID ) AND (T.TIME_ID = S.TIME_ID )
AND (c.cust_state_province = 'NY')
AND (t.day_name = 'Friday
')
GROUP BY T.DAY_NAME , C.CUST_CITY;
quit;
SAS Performance
 Analyze SQL
proc sql;
connect to ORACLE as ORACON (user=sh password=sh1
path='demodb.na.sas.com');
execute ( ANALYZE TABLE CUSTOMERS COMPUTE STATISTICS
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED
COLUMNS) by ORACON;
execute ( ANALYZE TABLE SALES COMPUTE STATISTICS
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED
COLUMNS) by ORACON;
execute ( ANALYZE TABLE TIMES COMPUTE STATISTICS
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED
COLUMNS) by ORACON;
disconnect from ORACON;
SAS Performance
 Index and Investigate
proc sql;
/* Full table scan the "function on column problem" */
create table work.customers_substr
as
select * from oradata.customers
where substr(cust_main_phone_number,1,5) = '123-4';
quit;
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Optimizer=CHOOSE (Cost=106 Card=500
Bytes=76500)
1
0
TABLE ACCESS (FULL) OF 'CUSTOMERS' (Cost=106 Card=500
Bytes=76500)
SAS Performance
 Index and Investigate
proc sql;
/* uses an index and returns the same data */
create table work.customers_like
as
select * from oradata.customers
where cust_main_phone_number like '123-4%';
quit;
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Optimizer=CHOOSE (Cost=84 Card=91
Bytes=13923)
1
0
TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS' (Cost=84
Card=91 Bytes=13923)
2
1
INDEX (RANGE SCAN) OF 'CUST_PHONE_NBR_IDX' (NONUNIQUE)(Cost=2 Card=91)
SAS Performance
 Oracle Execution Plan
option sastrace=’,,,d’;
SQL> SET AUTOTRACE TRACEONLY
SQL> <Oracle SQL statement here>
SAS Performance
 Temporary Table Support - create
/*--- create Oracle temporary table explicit SQL ---*/
/*--- (ORACLE SQL sent from SAS) ---*/
proc sql;
connect to oracle (user=scott password=tiger
path=oraclev10);
execute (create global temporary table oratemp
(empid number, salary number)) by
oracle;
run;
SAS Performance
 Temporary Table Support - load
libname ora oracle user=scott password=tiger
path=oraclev10 connection=shared;
/*--- load the Oracle temporary table with SAS data
proc append base=ora.oratemp set work.trans;
run ;
---*/
SAS Performance
 Temporary Table Support - use
/*--- push the join to Oracle ---*/
proc sql;
select lastname, firstname, salary from
ora.employees t1, ora.oratemp t2
where t1.empno = t2.empno;
run ;
Oracle Database 10g New
Features Applicable to SAS
Users
•
•
•
•
•
•
•
•
•
Wide Table Selects
New CBO algorithms and costing model
LOBS performance improvements
Data pump
Server Manageability
OLAP Support for Analytic Applications
Globalization and Unicode improvements
SQL improvements
.NET and 64 bit Windows support
Summary





SAS and Oracle development relationship
SAS and Oracle support – SAS and Oracle V9
SAS Intelligent Architecture
Customer Questions
Performance Considerations
Oracle/SASForums
 Do Oracle/SASUsers wish to form a SIG ?
 What sort of forums do the Users want in
future conferences ?
QUESTIONS
ANSWERS