1 / 35

Using Oracle Workspace Manager to Cut Costs: Case Studies

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.

chandler-wiley
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Session id: 40081 Using Oracle Workspace Manager to Cut Costs: Case Studies Bill BeauregardPrincipal Product Manager Oracle Corporation

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

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

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

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

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

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

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

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

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

  11. 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');

  12. 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');

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

  14. 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)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  32. Reminder – please complete the OracleWorld online session surveyThank you.

  33. Q & Q U E S T I O N S A N S W E R S A

More Related