Performance issue with ODS, Materialized Views, and their resolution

Several years ago I worked on an ODS system where data was getting fetched from an OLTP database in the form of Materialized Views (M-Views). The M-Views were exact replicas of source tables that were replicated after 1-2 hours, and had an additional column specifying when they were last refreshed.

The purpose of ODS was to create an alternative to OLTP system so that Reporting applications could make use of it thus reducing load on an already resurce heavy database having several transactions every second.

There were few reports that were taking a long time thus impacting productivity of client as they could not proceed otherwise. Upon investigation the reasons turned out to be the following.

1. At times there were multiple applications accessing same set of data for reports.
2. Every 1-2 hours the M-Views would start refreshing thus putting additional load on the system and delaying the existing report fetching process even further.
3. There was issue in base tables as well. Like there was one with 500 million records and no table partition, and it was being updated everyday. So its M-View had to have performance lags as well.

To resolve this issue the following performance measures were suggested.

1. Scheduled concurrent applications at different times e.g. one set of applications from 2 to 4 AM, another from 4 to 6 AM, then 6 to 7 AM , and so on. So that when one runs others won’t.
2. Made use of parallel processing features of the Database by using parallel hint.
3. A huge amount of data was moved on to another server and some data was archived so as to reduce load.
4. The most time consuming queries on the Database, even though not related to the application I was looking to resolve, were looked at since their slow performance would have impacted overall database.
5. It was suggested that to resolve issues in Base tables let the entire OLTP system be redesigned, and the activity did get approval after more than a year. The OLTP system was designed for small amounts of data and no one had anticipated such data growth, so it had to be replaced altogether.

I leave it to you, dear reader, to visualize the end result.

What I learned from this was

1. Develop your Database taking into consideration how much data could be stored on to it so that it gets scaled accordingly.
2. Maintain inventory over who all is using your system and grant access accordingly so that precious resources do not get abused.
3. Know your DBMS well and use its features to the hilt for optimal usage.

Special round of appreciation for

1. My entire team that coordinated with me on the issue.
2. Mr. PC Reddy, for hearing this incident and encouraging me to write it down.
3. Mr. Sandeep Jain, my career guide, for reviewing it and providing feedback as necessitated.