Jerry Held

1
Andrew HoldsworthDirector Real World and ISV
Performance Oracle Corporation Howard
Plemmons Senior Software Manager SAS Institute
Inc.
2
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

3
Oracle/SAS Certification Status
SAS Release Platforms Comments
V8 SAS Windows, UNIX, MVS, VMS 32 and 64 Bit, All Supported Oracle releases
V9 SAS Windows, UNIX, MVS, VMS 32 and 64 Bit, Oracle 8.1.7 and up
 

• Also See http//www.sas.com/service/techsup/access
  /searchPage.hsql

4
Current Oracle Technology Adoption at SAS

• OCI programming
• Partitioning and Parallelism
• RAC Implementation
• LOBS

5
Technical Solutions Using Oracle and SAS
6
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

7
SAS
8
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

9
SAS Intelligence Value Chain
10
Intelligence Value Chain (IVC)

• Planning
• SAS/ACCESS products (ETL)
• Leverage Oracle Infrastructure
• Performance Optimizations SAS 9

11
SAS Oracle Release Matrix

• http//www.sas.com/service/techsup/access/listPage
  .hsql
• SAS V9
• 64 bit OS support
• PC/unix/MVS/midrange

12
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

13
What is SAS/ACCESS

• Terminology
• SAS I/O model
• SAS V9
• SAS MVA
• SAS Procedures
• SAS Options

14
SAS I/O model SAS 9

• SAS Applications

sort,reg,dmreg,summary
Engine Supervisor
XOT
Access Engine
XOT
Oracle DBMS Client
ORACLE DBMS
15
SAS/ACCESS Today

• SAS 9 Features
• Threaded read
• Temporary table support
• Detailed trace information
• Metadata support

16
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

17
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

18
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

19
SAS Performance

• Explicit SQL
• proc sql
• connect to ORACLE as ORACON (usersh
  passwordsh1 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

20
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

21
SAS Performance

• Analyze SQL
• proc sql
• connect to ORACLE as ORACON (usersh
  passwordsh1 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

22
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 OptimizerCHOOSE
  (Cost106 Card500 Bytes76500)
• 1 0 TABLE ACCESS (FULL) OF 'CUSTOMERS'
  (Cost106 Card500 Bytes76500)

23
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 OptimizerCHOOSE
  (Cost84 Card91 Bytes13923)
• 1 0 TABLE ACCESS (BY INDEX ROWID) OF
  'CUSTOMERS' (Cost84 Card91 Bytes13923)
• 2 1 INDEX (RANGE SCAN) OF
  'CUST_PHONE_NBR_IDX' (NON-UNIQUE)(Cost2 Card91)

24
SAS Performance

• Oracle Execution Plan
• option sastrace,,,d
• SQLgt SET AUTOTRACE TRACEONLY
• SQLgt ltOracle SQL statement heregt

25
SAS Performance

• Temporary Table Support - create
• /--- create Oracle temporary table explicit SQL
  ---/
• /--- (ORACLE SQL sent from SAS) ---/
• proc sql
• connect to oracle (userscott passwordtiger
  pathoraclev10)
• execute (create global temporary table oratemp
• (empid number, salary number)) by oracle
• run

26
SAS Performance

• Temporary Table Support - load
• libname ora oracle userscott passwordtiger
  pathoraclev10 connectionshared
• /--- load the Oracle temporary table with SAS
  data ---/
• proc append baseora.oratemp set work.trans
• run

27
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 

28
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

29
Summary

• SAS and Oracle development relationship
• SAS and Oracle support SAS and Oracle V9
• SAS Intelligent Architecture
• Customer Questions
• Performance Considerations

30
Oracle/SASForums

• Do Oracle/SASUsers wish to form a SIG ?
• What sort of forums do the Users want in future
  conferences ?

31
A
32
(No Transcript)