Scaling Oracle Spatial using Real Application Cluster!

1. Scaling Oracle Spatial using Real Application Cluster! John Abel Executive Consultant

2. The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions.The development, release, and timing of any features or functionality described for Oracle’s products remain at the sole discretion of Oracle.

3. Topics <Insert Picture Here> • Key principles to scalability • Infinity Service • Infinity Service and Spatial • Questions and Answers

4. Key Principles to Scalability

5. Approach to Scalability • High Level Approach • Use a methodology (OUM, TOGAF, EADF) • Keep it simple • Scalability • Create a capacity plan • Agree with Business Requirements (now and future) • Technical Proving Phase (Remove Risk) • Commodity Computing • “Scale out of the box” • Single point of contention • Calculate the TCO

6. Approach to Scalability • Building Blocks • SAN Storage • Use SAME (Stripe and Mirror Everything) • Think IOPS, rather than just Bandwidth • Big is not always best • Hardware RAID 5 or 1+0

7. Approach to Scalability • Building Blocks • ASM • Disk Group to LUN • Consider Double Striping SAN / ASM • Rebalance • Add more disk on demand

8. Approach to Scalability • Building Blocks • Share Resource • Oracle Real Application Cluster • Oracle Instance • Oracle ASM Instance • Oracle Clusterware

9. Approach to Scalability • Building Blocks • Add node • Scale out of the Box • Consider Interconnect • Do I need infiniband or just normal Gigabit?

10. Approach to Scalability • Building Blocks • Increase throughput on Disk • Rebalance • Think IOPS and Bandwidth • Think Network • Think SAN

11. Infinity service

12. What is an infinity service? “Infinity service is the ability to run a discrete process with out the need for a specific single resource” • What we actually mean is no single process, operation, etc. needs to rely on a physical piece of the topology. • It should be able exist on multiple components. • Benefits scalability, availability and resilience. • Approach can apply to application server and database server.

13. What is an infinity service? • Example • An external service is invoked and publishes a message • An one node of the cluster could read the request • An one node could run the request • An one node could restart a process that has failed

14. Case Study • Large batch processing 1-2TB per data of data selection. • What benefit did infinity service provide? • Near linear scalability of Real Application Cluster. • Allow rollback and failover of extraction tasks. • Provided the ability to do maintenance without outage. • Less conflict of resource. • Uses standard Oracle features AQ, DBMS Scheduler, RAC. • Customer today hits 80,000-120,000 data rows per second. • Only known limitation is CPU and Disk speed.

15. What to watch out for when deploying an infinity service? • It will not apply to everything. • Very good for batch, could be used for online users, but hard. • DBMS_SCHEDULER and Resource management in the database are infinity aware. • It is a concept not a product. • Consider up front, not during deployment. • Works well in Oracle database. • Not something new, just a different approach.

16. Infinity Service and Spatial

17. What can be expected • Using the following methods performance of loading and using OS Mastermap are as follows, this may be different for each implementation • 100,000+ spatial row accessed per second • SQL*Load loading 1.2+Billion spatial features in 4 hours • Spatial Index build for 1.2+Billion features 6 hours • Spatial Validation for 1.2+Billion features 2 hours

18. “Rules of Engagement” • Spatial is a complex area meaning two attributes are critical. • CPU – Processing Spatial Data and the calculations mean spatial is resource intensive on CPU. • IOPS – Generally spatial, even a full table scan is I/O resource intensive.

19. “Rules of Engagement” • Loading Spatial Data • Split Data files into small chunks (around 2GB) • Load Spatial Data without Spatial Index • Parallel SQL*Loader Spatial Data into Destination Table • Create destination table as a partition table • Once loaded into table reference back in future, moving is expensive in resources

20. “Rules of Engagement” • Spatial Index • Create Spatial Index in parallel. • Make sure use work_tablespace • Make sure MDRT$ are placed in own tablespace (one per partition) • Use LAYER_GTYPE • In a RAC environment Spatial index create on single node • Monitor v$Session_Wait, watch for latch on sequence (MRDS_XX$)

21. “Rules of Engagement” • Moving Spatial Data • Load data into staging table • Validate and Clean Data • Once clean then transport around the spatial environments using Oracle Transportable Tablespaces (TTSP) • Remember • Spatial Data and Index can be moved through TTSP. • Spatial Index needs the same Endian format (e.g. byte value)

22. “Rules of Engagement” • Query Performance • Validate the query plan • Hints may be required to drive execution plan /*+ ORDERED */ • Use sub query to drive relational data first and work on spatial data second select a.x, b.x from (select relational.x from relational where join….) a , spatial b where a.join = b.join)

23. “Rules of Engagement” • PL/SQL table for spatial results and join to feature level data. Example code below – Step 1 CREATE OR REPLACE TYPE db_dept_record_t AS OBJECT ( deptno NUMBER(2), dname VARCHAR2(14) ) / show err; PROMPT Creating features table type CREATE OR REPLACE TYPE db_dept_table_t IS TABLE OF db_dept_record_t /

24. “Rules of Engagement” • PL/SQL table for spatial results and join to feature level data. Example code below – Step 2 CREATE OR REPLACE PACKAGE BODY EMP_DEPT_VT IS FUNCTION dept_all_temp RETURN db_dept_table_t AS BEGIN RETURN dept_table; END dept_all_temp; PROCEDURE populate_dept_all_temp AS v_sql_block VARCHAR2(4000) := 'select db_dept_record_t(deptno, dname) from dept'; BEGIN EXECUTE IMMEDIATE v_sql_block BULK COLLECT INTO dept_table; END populate_dept_all_temp; END;

25. “Rules of Engagement” • PL/SQL table for spatial results and join to feature level data. Example code below – Step 3 CREATE OR REPLACE PACKAGE EMP_DEPT_VT IS TYPE dept_record_t IS RECORD ( deptno NUMBER(2) ,dname VARCHAR2(14) ); TYPE dept_table_t IS TABLE OF dept_record_t; dept_table db_dept_table_t; FUNCTION dept_all_temp RETURN db_dept_table_t; PROCEDURE populate_dept_all_temp; PROCEDURE delete_dept_all_temp; END EMP_DEPT_VT; /

26. “Rules of Engagement” • PL/SQL table for spatial results and join to feature level data. Example code below – Step 4 exec emp_dept_vt.populate_dept_all_temp; SELECT COUNT(*) FROM TABLE(CAST(emp_dept_vt.dept_all_temp AS db_dept_table_t)) a , EMP b WHERE b.DEPTNO = a.DEPTNO;

27. Example Implementation

28. Architecture Challenges • How do we scale outside Physical Servers? • How do we integrate high volume traffic from multiply sources in different formats? • How do we keep file, relational and spatial data in sync within one topology? • Is it possible to implement Application Service Provider model within topology? • Monitoring topology/solution from one user interface? • What is best way to manage Raster, 2D and 3D data within an Oracle environment? • How do we mix functional requirements and management tasks within a single topology?

29. RAC ASM Disk Disk Disk Disk Approach to resolving Architecture ChallengesScale the Topology Scaling outside the physical Server using RAC (Real Application Cluster) and ASM (Automatic Storage Management) • Guidelines • Implement components in a service affinity model (e.g. Use DBMS_SCHEDULER rather than DBMS_JOB) allows for no one component to be dependant on single node of the cluster. • RAC and ASM can allow for more restore to be added within re-implementation. ASM controls disk usage for the Oracle Database. • Think IOPS rather than bandwidth with spatial.

30. Approach to resolving Architecture ChallengesHigh Volume Transaction, Multiple sources Using BPEL + Web Services and direct connections will allow for different scalability topologies. Web Service • Guidelines • Application Servers can be directed to a specific RAC node or load balanced using Oracle across a number of nodes. • Use Load Balancing to implement a service affinity topology. • Implement Web Services as Async messaging and not Sync. • Use Open Standards for communication (e.g. GML) • Implement Priority messaging within AQ. Application Tier RAC ASM

31. Approach to resolving Architecture ChallengesHigh Volume Transaction, Multiple sources Store and Foreword • Guidelines • Use Async Service were possible for batch processing, this allows for outage or limitation for large spatial extracts. Again supports Service affinity model. Use a web service interface or direct file output to be referenced in web service response.

32. Batch User Batch User Approach to resolving Architecture ChallengesResource Manager Maximising system usage when required • Guidelines • Oracle Resource Manager allows for different resource profiles to be applied to the database when required. • Example during the main operational window allow more resources for user processing. During batch processing allow more resources to be used. RAC ASM

33. Oracle Grid Oracle Grid Approach to resolving Architecture ChallengesSingle Management Interface Oracle Grid Control and Management Packs Web Service • Guidelines • Monitor all Oracle components • Monitor non-Oracle components from Grid Control. • Allows for patching, performance monitoring and configuration control Application Tier RAC ASM

34. Partition Data Disk Disk Approach to resolving Architecture ChallengesOracle Content DB Store in One Location • Guidelines • Store all data in one location (external files, Raster, 2D and 3D including Relational. • Why? Well time based recovery, if stored in separate locations hard to impossible to recover. • Oracle Drive part of Oracle Content DB allows for drive support (e.g. f:\) Oracle Drive Spatial Data Rational Data

35. Perform Performance test earlier than normal to limit impact Performance Tests High Level Implementation Approach Think Strategic and Implement Tactical Plan Design and Build Design and Build Prod UAT Design and Build Design and Build Design and Build Design and Build Design and Build System Test POC • UAT again will be increased due to complex nature of spatial data. • Use automated tools to decrease UAT. • Plan Implementation • Perform a short concentrated POC on technical focus areas • Think of strategic design principles at this stage. • Parallel Build threads • Separate into focus areas. Use POC lessons learnt as guidelines • A number of repeating loops will occur • System test and integration test can be performed as normal. • Difference with this approach more validation than normal due to complex nature of spatial.

36. Q & A