scaling oracle spatial using real application cluster n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Scaling Oracle Spatial using Real Application Cluster! PowerPoint Presentation
Download Presentation
Scaling Oracle Spatial using Real Application Cluster!

Loading in 2 Seconds...

play fullscreen
1 / 37

Scaling Oracle Spatial using Real Application Cluster! - PowerPoint PPT Presentation


  • 78 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Scaling Oracle Spatial using Real Application Cluster!' - ernie


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide2

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.

topics
Topics

<Insert Picture Here>

  • Key principles to scalability
  • Infinity Service
  • Infinity Service and Spatial
  • Questions and Answers
approach to scalability
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
approach to scalability1
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
slide7

Approach to Scalability

  • Building Blocks
    • ASM
    • Disk Group to LUN
    • Consider Double Striping SAN / ASM
    • Rebalance
    • Add more disk on demand
slide8

Approach to Scalability

  • Building Blocks
    • Share Resource
    • Oracle Real Application Cluster
    • Oracle Instance
    • Oracle ASM Instance
    • Oracle Clusterware
slide9

Approach to Scalability

  • Building Blocks
    • Add node
    • Scale out of the Box
    • Consider Interconnect
    • Do I need infiniband or just normal Gigabit?
slide10

Approach to Scalability

  • Building Blocks
    • Increase throughput on Disk
    • Rebalance
    • Think IOPS and Bandwidth
    • Think Network
    • Think SAN
what is an infinity service
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.
what is an infinity service1
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
case study
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.
what to watch out for when deploying an infinity service
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.
what can be expected
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
rules of engagement
“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.
rules of engagement1
“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
slide20

“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$)
slide21

“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)
slide22

“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)

slide23

“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

/

slide24

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

slide25

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

/

slide26

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

architecture challenges
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?
slide29

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.
slide30

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

slide31

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.
slide32

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

slide33

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

slide34

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

slide35

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.
slide36

Q

&

A