Transactional and Reporting

In my years as an Oracle and SQL Server DBA and the many technical environments I’ve worked in, there’s almost always one database in each place that is a hybrid of OLTP and OLAP.  A highly transactional database that also has reporting requirements, but no separate reporting database. 

This type of database is pretty much a guaranteed challenge for any DBA.   On one hand, the DBA has demands of the application demanding micro-second response time for completion of transactions.  On the other, he or she has users wanting information about the data resulting from all those transactions. 

A DBA’s best course for successfully taming these types of hybrid environments is working closely with developers and applications specialists. 

Steps I recommend taking to tune a hybrid environment:
1.  Identify top 10 processes that are impacting the database at a time-  five that are impacting users and five identified by the database group, introducing new statements as previous ones are tuned and removed from the list. 
2.  Group statements that are impacted by each other together and address as mini-projects.  Hybrid databases can become an over-whelming task- breaking the challenges down into smaller projects help.
3.  Lock down defined performance goals, (report “a” must respond to users desktops in 20 seconds,  transaction “2” must complete in .02 seconds, etc.)

4.  Challenging the queries for reporting- 
 – What queries simply need an index.  Ensure the index is cost-effective, (i.e. the cost of supporting the inserts and updates on the index are worth the savings to the query…)
 – Which queries are aggregating/summarizing transactional data?  These reports may require a reporting table or a materialized view.
 –  If a materialized view is seen as a resolution for the reporting query, inspect the impact of a materialized view log on the transactional table.
 –  If materialized view refreshes are the issue and not the materialized view log, research to see if a CDC process can be utilized to create a reporting table, (CDC= Oracle’s Change Data Capture)
 –  Inspect the queries-  ensure the SQL is optimized as efficiently as possible. 
 –  Ask questions and request justifications if a query doesn’t make sense.  Often a developer or application specialist will be just as surprised to find out an application or code is impacting, (or sometimes that reporting queries are still being utilized!)

5.  For transactional processing challenges-
 –  Inspect not only concurrency and waits of transactions, but logic/path of processes.  Many times performance waits can be a complex combination of issues and not one query/statement.
 –  Inspect the number of executions vs. the elapsed time per execution.
 –  Are the indexes being used effectively?  Are there range or skip scans that could be unique scans?
 –  Full table scans that could be effectively removed with a cost-effective index, (I know, I demand that cost-effective part… :))
 –  Ensure the application is querying the database efficiently-  look for oddities, such as single row results that have an order by or returning data to set a pre-cursor in an application that could be done without a database call.

Test thoroughly, but also expect a set back from time to time-  hybrid environments are a unique challenge for any DBA.

Finally, DON’T give up, be persistent and continue to identify the bottlenecks that are holding the hybrid database back.   These databases are worth the time and effort!