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

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

  • 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

Session id: 40081

Using Oracle Workspace Manager to Cut Costs: Case Studies

Bill BeauregardPrincipal Product Manager

Oracle Corporation


  • Workspace Manager Overview

  • Case Studies

    • City of Edmonton

    • Operational Data Store for a Major HMO

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

  • 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



LIVE Workspace




Workspace B

Workspace D




Workspace C

Workspace E





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

LIVE Workspace




Workspace B

Workspace D




Workspace C

Workspace E





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


UPDATE catalog

SET . . .



Renamed base

table with four

new columns


view with instead-of triggers


base table

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

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


-- Create a workspace called PERSONNEL_UPDATES


-- Go to workspace PERSONNEL_UPDATES and update

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

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


Code Sample (Continued)

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


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

-- Disable versioning on the PERSONNEL table


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

  • 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

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)

  • 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

Legal Survey parcels

Assessment parcels

Title parcels

Civic holdings

Parkland Assets

Zoning and Land Use

Underground utilities

Street Lights and Trolley


Single Line Street Network

Sidewalk structure/condition

Road structure/condition

Buildings, entryways

Demographic data

Administrative areas such as:

Community leagues



Voting subdivisions

Business Revitalization Zones

Residential parking program

Neighbourhood structure plans

Area structure plans

Inspection areas

Traffic districts / zones

Major commercial corridors



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


Address related tables

  • Addresses

    • 395,243 current

    • 1,175,994 historic

  • Buildings / Floors / Entryways / Suites

    • 908,012 current

    • 1,066,799 historic


    • 182,943 current

    • 205,311 historic


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

    • 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


    • 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

    • 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


    • 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

    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


    • 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

    • 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

    • 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


    • 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


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

    • 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

    Reminder – please complete the OracleWorld online session surveyThank you.



    Q U E S T I O N S

    A N S W E R S


  • Login