Using oracle workspace manager to cut costs case studies
This presentation is the property of its rightful owner.
Sponsored Links
1 / 35

Using Oracle Workspace Manager to Cut Costs: Case Studies PowerPoint PPT Presentation


  • 110 Views
  • Uploaded on
  • Presentation posted in: General

Session id: 40081. Using Oracle Workspace Manager to Cut Costs: Case Studies. Bill Beauregard Principal Product Manager Oracle Corporation. Agenda. Workspace Manager Overview Case Studies City of Edmonton Operational Data Store for a Major HMO. Workspace Manager.

Download Presentation

Using Oracle Workspace Manager to Cut Costs: Case Studies

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


Using oracle workspace manager to cut costs case studies

Session id: 40081

Using Oracle Workspace Manager to Cut Costs: Case Studies

Bill BeauregardPrincipal Product Manager

Oracle Corporation


Agenda

Agenda

  • Workspace Manager Overview

  • Case Studies

    • City of Edmonton

    • Operational Data Store for a Major HMO


Workspace manager

Workspace Manager

  • Feature of the Oracle Database for application developers and DBAs

  • Manages current, proposed and historical values for data in the same database

  • Isolate a collection of changes to production data

  • Keep a history of changes to data

  • Perform “what if” analysis

    Saves Money, Time and Labor


How it benefits users

How it Benefits Users

  • Saves Money

    • City of Edmonton consolidated 49 physical asset databases into one database

    • HMO cut hardware requirements by 50% and software licensing for a half terabyte data store

  • Saves Time

    • Concurrent access to current, proposed and historical data with consistency and no latency

  • Saves Labor

    • Single point of update and management

    • Easy to manage with Enterprise Manager

    • No custom code or application specific version data


Workspace manager architecture

WORKSPACE-ENABLED APPLICATIONS

PL/SQL APIs

SQL

Data

Mgmt.

WS

Mgmt.

Privilege

Mgmt.

Lock

Mgmt.

Conflict

Mgmt.

WORKSPACE MANAGER

RDBMS

Workspace Manager Architecture

Metadata

Views


Workspace manager mechanics

LIVE Workspace

2

3

1

Workspace B

Workspace D

4

5

7

Workspace C

Workspace E

8

9

6

Savepoint

Workspace Manager Mechanics

  • Workspace logically isolates a collection of row versions

  • Workspaces hierarchies can be of any depth and width

  • Row versions created within a version-enabled table

  • No changes to application SQL or queries

  • View workspace versions in context of rest of database


Workspace manager mechanics1

LIVE Workspace

2

3

1

Workspace B

Workspace D

4

5

7

Workspace C

Workspace E

8

9

6

Savepoint

Workspace Manager Mechanics

  • Savepoint groups collection of changes in a workspace

  • Savepoint allows rollback by causing subsequent row changes to be automatically captured as a new version

  • Automatic conflict detection – resolve to parent, child, original

  • Optional history enables “goto date” navigation


Version enabling a table

Version-Enabling a Table

DBMS_WM.ENABLEVERSIONING('CATALOG');

UPDATE catalog

SET . . .

RENAME…

CATALOG_LT:

Renamed base

table with four

new columns

CATALOG:

view with instead-of triggers

CATALOG:

base table


Workspace manager operations

Workspace Manager Operations

Use PL/SQL APIs and Enterprise Manager

  • Table: EnableVersioning, DisableVersioning

  • Workspace: create, refresh, merge, rollback, remove, goto, compress, alter

  • Savepoints (persistent): create, alter, goto

  • History: goto date

  • Privileges: access, create, delete, rollback, merge

  • Access Modes: read, write, management, none

  • Locks (persistent): exclusive and shared

  • Differences: compares savepoints and workspaces

  • Detect / Resolve Conflicts: choose version to merge


Code sample

Code Sample

--Version enable the PERSONNEL table with history and timestamp all changes

DBMS_WM.EnableVersioning('PERSONNEL', Hist=>'VIEW_WO_OVERWRITE');

-- Create a workspace called PERSONNEL_UPDATES

dbms_wm.createWorkspace('PERSONNEL_UPDATES');

-- Go to workspace PERSONNEL_UPDATES and update

dbms_wm.gotoWorkspace('PERSONNEL_UPDATES');update PERSONNEL....


Code sample continued

Code Sample (Continued)

-- Create a savepoint called POTENTIAL_CHANGES in the PERSONNEL_UPDATES workspace & make more changes

dbms_wm.CreateSavepoint('PERSONNEL_UPDATES', ‘POTENTIAL_CHANGES'); update PERSONNEL....

-- Undo the last set of changes

dbms_wm.RollbackToSP('PERSONNEL_UPDATES','POTENTIAL_CHANGES');


Code sample continued1

Code Sample (Continued)

-- Merge changes into LIVE (production) Workspace and remove the workspace PERSONNEL_UPDATES

dbms_wm.gotoWorkspace('LIVE');

dbms_wm.MergeWorkspace('PERSONNEL_UPDATES', remove_workspace => true);

-- Disable versioning on the PERSONNEL table

dbms_wm.DisableVersioning('PERSONNEL');


Workspace manager features

Workspace Manager Features

  • Workspace hierarchies of arbitrary depth & width

  • No changes to application SQL or queries

  • Optimistic and pessimistic locking modes

  • Continually Refreshed (CR) and non-CR workspaces

  • Multi-Parent Workspaces

  • Persistent workspace locks

  • Differencing and Conflict detection/resolution

  • Partial and Full Merge/Refresh of workspace/table

  • Garbage collection operations to keep the version-tree/version-data sizes optimal

  • Event framework


Database integration

Database Integration

  • Manage via Enterprise Manager & metadata views

  • Supports Oracle Spatial

  • Supports all datatypes (including nested tables)

  • DDL operations on version-enabled tables

  • Constraints (Referential Integrity, Unique, Check)

  • Triggers

  • Import / Export (full and table)

  • SQL*Loader bulk loading

  • Replication

  • VPDs

  • Materialized Views (full refresh)


Case study city of edmonton

Case Study: City of Edmonton

Spatial Land Inventory Management System provides a single mgt. environment for city’s land based assets

  • Application platform:

    • Oracle Workspace Manager and Oracle Locator

    • Intergraph GeoMedia Pro, GeoMedia Transaction Manager.

  • Data feeds:

    • Land registry and surveys

    • Utilities and phone co.

    • Tax assessments

    • Dept. of Public Works


City of edmonton continued

City of Edmonton (continued)

  • Users:

    • 1000’s of end users - city officials, departments, mortgage lenders, citizens

    • 150 professionals - Engineers, planners, cartographers

    • 50 data entry personnel

  • Client access:

    • Internet, mobile and thick client tools

  • Database: 30gb and growing


Slim data

Legal Survey parcels

Assessment parcels

Title parcels

Civic holdings

Parkland Assets

Zoning and Land Use

Underground utilities

Street Lights and Trolley

Addresses

Single Line Street Network

Sidewalk structure/condition

Road structure/condition

Buildings, entryways

Demographic data

Administrative areas such as:

Community leagues

Neighbourhoods

Wards

Voting subdivisions

Business Revitalization Zones

Residential parking program

Neighbourhood structure plans

Area structure plans

Inspection areas

Traffic districts / zones

Major commercial corridors

SLIM Data


Statistics

Statistics

Land Parcel related tables

  • Title / Assessment / Civic Properties

    • 197,297 current records

    • 891,274 historic records

  • Title related information

    • 928,182 current owners

    • 1,251,509 historic owner records

  • Legal Descriptions (Lot / Block / Plan)

    • 817,027 current

    • 1,692,009 historic


Statistics1

Statistics

Address related tables

  • Addresses

    • 395,243 current

    • 1,175,994 historic

  • Buildings / Floors / Entryways / Suites

    • 908,012 current

    • 1,066,799 historic

      Assessment

    • 182,943 current

    • 205,311 historic


Statistics2

Statistics

Street Lights - just starting to maintain

  • 49,460 Poles

  • 89,641 Luminaires

  • 46,948 Hardware items

  • Future data

    • Additional Parkland Assets

    • Bus Stops

    • Scanned Roadways As-Built images

    • Traffic Signals

    • Street Markings

    • Parking Meters

    • and more…..


  • Pre slim environment

    Pre-SLIM Environment

    • Data duplication was common

    • Data was maintained in multiple data formats

    • Quality of data was inconsistent

    • Currency of data was often a problem

    • Some required data did not exist

    • Limited historic data


    Requirements

    Requirements

    • Single, centralized data store

    • Store data in three states:

      • Proposed

      • Current

      • Historical

    • Maintain audit trail for data maintainers

    • Maintain historical and proposed states for business users


    Workspace manager in production

    Workspace Manager in Production

    • Data maintainers

      • Create workspaces to isolate changes

      • Merge workspaces when changes are completed and approved

    • 112 version enabled tables

    • Referential constraints and triggers used heavily

    • Average 75 workspaces in use

    • Average rows merged at a time

      • Registries data load - 13 tables - 8800 rows

      • Addressing - 5 tables - 80 rows

      • Parcel Maintainers - 2 tables - 140 rows


    Results

    Results

    • Integrated, centralized, high quality data

      • Replaced 49 disparate land apps., 166 databases

      • Single point of update and management

      • Citywide sharing of consistent data with controlled access

    • Concurrency and historical perspective

      • Concurrency: end users access current data while data entry and updates are isolated in workspaces

      • History: all changes retained,“goto date” capability


    Case study operational data store

    Case Study: Operational Data Store

    A major HMO is building an ODS to:

    • Support key operational business processes

    • Aggregate transaction processing data from multiple legacy applications

    • Provide subject-oriented, integrated, near realtime, detailed data for a number of financial applications and reports


    Requirements1

    Requirements

    • Daily/ weekly / monthly snapshots of 500 GB Oracle9i Database

      (Hardware has 1TB storage limit)

    • Load 60 MB (120,000 transactions) per hour

    • No changes to application SQL or queries


    Two alternatives

    Two Alternatives

    • Multiple staging instances with refresh

      • Requires new hardware & software licenses

      • More labor

      • No availability during refresh

      • Stale data – refresh done infrequently

      • Cumbersome if additional snapshots required

    • Single instance hosts current & historical data

      • Same hardware and software licenses

      • Data added in near real time

      • High availability

      • Better operational decision making

      • Scalable – easy to add a new workspace


    Solution workspace manager

    Solution – Workspace Manager

    • Data loaded in LIVE (current state of the data)

    • 3 workspaces created to provide historical views

      • Daily = COB previous day

      • Weekly = end of the previous week

      • Monthly = end of the previous month

    • Workspace Refresh updates the workspace with the latest data

    • Workspace Compress removes old versions from LIVE


    Results1

    Results

    • 50% less hardware and corresponding software licenses required

    • One copy of the data to manage

    • Data updates are near realtime and available

    • No changes to application SQL and queries

    • Refresh is very fast because it is a metadata operation


    Summary

    Summary

    Workspace Manager….

    • Saves Money

      • Reduces hardware and software requirements

    • Saves Time

      • Concurrent access to current, proposed and historical data with consistency and no latency

    • Saves Labor

      • Single point of update and management

      • Easy to manage with Enterprise Manager

      • No custom code or application specific version data


    Next steps

    Next Steps….

    • Recommended sessions

      • # 40125 - Oracle10i: A Spatial VLDB Case Study

    • Recommended demos and/or hands-on labs

      • Performing Location-Based Analysis with Oracle Locator or Oracle Spatial, and Oracle Workspace Manager (Database Track)

    • See Your Business in Our Software

      • Visit the DEMOgrounds for a customized architectural review, see a customized demo with Solutions Factory, or receive a personalized proposal. Visit the DEMOgrounds for more information.

    • Visit http://otn.oracle.com/products/workspace_mgr


    Reminder please complete the oracleworld online session survey thank you

    Reminder – please complete the OracleWorld online session surveyThank you.


    Using oracle workspace manager to cut costs case studies

    Q

    &

    Q U E S T I O N S

    A N S W E R S

    A


  • Login