1 / 32

Andrew Holdsworth Director Real World and ISV Performance Oracle Corporation Howard Plemmons

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

jbentley
Download Presentation

Andrew Holdsworth Director Real World and ISV Performance Oracle Corporation Howard Plemmons

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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. Also See http://www.sas.com/service/techsup/access/searchPage.hsql Oracle/SAS Certification Status

  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 sort,reg,dmreg,summary… SAS Applications 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 (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;

  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 (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;

  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 Optimizer=CHOOSE (Cost=106 Card=500 Bytes=76500) 1 0 TABLE ACCESS (FULL) OF 'CUSTOMERS' (Cost=106 Card=500 Bytes=76500)

  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 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' (NON-UNIQUE)(Cost=2 Card=91)

  24. SAS Performance • Oracle Execution Plan option sastrace=’,,,d’; SQL> SET AUTOTRACE TRACEONLY SQL> <Oracle SQL statement here>

  25. 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;

  26. 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 ;

  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. Q & Q U E S T I O N S A N S W E R S A

More Related