Oracle RDBMS and Tools Services - MI/OLTP Split

The Problem

The client had a Oracle database providing a mission critical Image and Workflow system. The same database housed a MI sub system which caused performance problems for the whole database. At set periods during the day when Operations Managers ran their reports a huge spike in CPU and disk usage would be experienced resulting in a period of poor overall performance. In extreme cases user sessions would be killed by DBA's to keep the workflow system going.

The Remit

The remit was to deliver a solution that split these 2 functions, moving the MI element off the main OLTP database into a second new one. The split had to be seamless to the front end MI application. The split had to ensure that the OLTP application was resilient to the MI database shutting down. Each database could then be tuned to it's particular needs.

The Solution Outline

• Tables in the MI schema that recorded the granular data were retained on the OLTP system, and Oracle Replication snapshot logs were added to these tables.
• All other tables in the MI schema, typically used for summarised data and reporting were moved to the new database.
• Logical Refresh Groups were defined, then refined for optimised performance and Oracle Replication was used to pull data from the source OLTP database.
• Aggregation, summary and reporting processes were moved to the new MI database.
• The client application configuration was changed to refer to the new MI database.
• OLTP database tuned for OLTP, and the MI database tuned for MI.

Benefits Realised

• Smaller MI footprint in the OLTP database
• Performance spikes caused by running of reports eliminated
• Reports now run more freqently in line with user expectations on the MI database

Learning Points

These are only discussed with prospective clients.