Jerry Held

1
(No Transcript)
2
Using Oracle Workspace Manager to Cut Costs
Case Studies
Session id 40081

• Bill BeauregardPrincipal Product Manager
• Oracle Corporation

3
Agenda

• Workspace Manager Overview
• Case Studies
• City of Edmonton
• Operational Data Store for a Major HMO

4
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

5
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

6
Workspace Manager Architecture
Metadata Views
7
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

8
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

9
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
10
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

11
Code Sample

• --Version enable the PERSONNEL table with history
  and timestamp all changes
• DBMS_WM.EnableVersioning('PERSONNEL',
  Histgt'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....

12
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','POTENTIA
  L_CHANGES')

13
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 gt true)
• -- Disable versioning on the PERSONNEL table
• dbms_wm.DisableVersioning('PERSONNEL')

14
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

15
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)
• SQLLoader bulk loading
• Replication
• VPDs
• Materialized Views (full refresh)

16
Case Study City of Edmonton

• Spatial Land Inventory Management System provides
  a single mgt. environment for citys 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

17
City of Edmonton (continued)

• Users
• 1000s 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

18
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

19
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

20
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

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

22
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

23
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

24
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

25
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

26
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

27
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

28
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

29
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

30
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

31
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

32
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_mg
  r

33
Reminder please complete the OracleWorld
online session surveyThank you.
34
A
35
(No Transcript)