Transcript Workspace Manager - Oracle Software Downloads

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
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
WORKSPACE-ENABLED APPLICATIONS
PL/SQL APIs
SQL
Data
WS
Privilege Lock Conflict Metadata
Mgmt. Mgmt. Mgmt.
Mgmt. Mgmt.
Views
WORKSPACE MANAGER
RDBMS
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
2
1
3
Workspace B
Workspace D
4
5
7
Workspace C
6
Workspace E
8
9
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
LIVE Workspace
2
1
3
Workspace B
Workspace D
4
5
7
Workspace C
6
Workspace E
8
9
Savepoint
Version-Enabling a Table
DBMS_WM.ENABLEVERSIONING('CATALOG');
UPDATE catalog
SET . . .
RENAME…
CATALOG:
base table
CATALOG_LT:
CATALOG:
Renamed base
table with four
new columns
view with
instead-of
triggers
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
DBMS_WM.EnableVersioning('PERSONNEL',
Hist=>'VIEW_WO_OVERWRITE');
-- Create a workspace called PERSONNEL_UPDATES
dbms_wm.createWorkspace('PERSONNEL_UPDAT
ES');
-- 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_UPDAT
ES', ‘POTENTIAL_CHANGES');
update PERSONNEL....
-- Undo the last set of changes
dbms_wm.RollbackToSP('PERSONNEL_UPDATES'
,'POTENTIAL_CHANGES');
Code Sample (Continued)
-- Merge changes into LIVE (production)
Workspace and remove the workspace
PERSONNEL_UPDATES
dbms_wm.gotoWorkspace('LIVE');
dbms_wm.MergeWorkspace('PERSONNEL_UPD
ATES', remove_workspace => true);
-- Disable versioning on the PERSONNEL table
dbms_wm.DisableVersioning('PERSONNEL');
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 versiontree/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
SLIM Data














Legal Survey parcels
Administrative areas such as:
Assessment parcels
 Community leagues
 Neighbourhoods
Title parcels
 Wards
Civic holdings
 Voting subdivisions
Parkland Assets
 Business Revitalization Zones
Zoning and Land Use
 Residential parking program
Underground utilities
 Neighbourhood structure plans
Street Lights and Trolley
 Area structure plans
Addresses
 Inspection areas
Single Line Street Network  Traffic districts / zones
Sidewalk structure/condition  Major commercial corridors
Road structure/condition
Buildings, entryways
Demographic data
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
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
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
 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
 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
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
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
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
 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
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
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 http://otn.oracle.com/products/workspace_mgr
Reminder –
please complete the
OracleWorld online session
survey
Thank you.
QUESTIONS
ANSWERS