A common initial resolution to reporting requirements without a reporting database is to utilize materialized views.  Unfortunately, the cost to refresh these views can be extremely expensive on a database, especially if it’s an OLTP that rarely has a lull in activity.

A lesser known feature of Oracle’s is the Change Data Capture, (a.k.a CDC
http://download.oracle.com/docs/cd/B10500_01/server.920/a96520/cdc.htm )
The CDC process utilizes a materizalized view log on a source table with a destination copy of the source table that can be kept insync with the original in any interval chosen.  The result is often a much lighter footprint than an mview refresh, no matter if you’ve employed complete or fast refreshes. 

I’ve had added success by pulling sections of the CDC underlying code to syncronize destination objects and replicating just the pieces I need to get the results desired.  CDC has a lot of features and can be over-whelming the first time a DBA or developer delves into it.  For anyone who would like to reproduce the simple source-to-reporting object syncronization with an mview log, here are the high level steps:

1.  Create a materialized view log on the source table using a primary key or rowid.
2.  Create a stage table that will hold two columns of information:
            1st Column:  The type of row, Insert, Update or Delete, (‘I’,'U’,'D’).
            2nd Column: The primary key or rowid.
3.  Create two stored procs, either in a package or separately, where the 1st one will populate the stage table and the second will take the info from the stage table and process the rows to the destination table.

In depth, the process and code will appear similar to the following example:

Create the materizlied view log on the source table, (SRC_TBL):
CREATE MATERIALIZED VIEW LOG ON SCOTT.SRC_TBL;

Create the staging table:
CREATE TABLE STG_PK_IDS
(OPT CHAR(1),
PK_ID NUMBER not null)
tablespace USERS;

Create the stored procedure to load the stage table, (STG_TBL):
CREATE OR REPLACE PROCEDURE UPD_PKID_FOR_RPT IS
/*********************************************************************
–Get Records for both insert and update
CURSOR cur IS
SELECT *
FROM (SELECT ‘I’ opt, pk_id
FROM scott.mlog$_src_tbl where DMLTYPE$$=’I')  –Query the mview log on the source
UNION ALL
(SELECT ‘U’ opt, pk_id
FROM scott.mlog$_src_tbl where DMLTYPE$$=’U')
UNION ALL
(SELECT ‘D’ opt, pk_id
FROM scott.mlog$_src_tbl where DMLTYPE$$=’D');


BEGIN


– Insert records for transactions into staging table, all of them
FOR rec IN cur LOOP

IF rec.opt in (‘I’,'U’,'D’) THEN
INSERT INTO scott.STG_TBL (opt, pk_id) VALUES (rec.opt, rec.pk_id);
END IF;
END LOOP;
COMMIT;


–Purge processed rows from the mview log, using the reporting table to tell us which rows were added to it.
DELETE FROM scott.mlog$_src_tbl
WHERE OPT in (‘I’,'U’)
AND PK_ID in (select PK_ID from scott.RPT_TBL);


COMMIT;


END UPD_PKID_FOR_RPT;

Create a second stored prodedure to populate the data to the reporting table, (RPT_TBL)
CREATE OR REPLACE PROCEDURE UPD_RPTTBL_SP AS

/*********************************************************************

CURSOR cur IS
SELECT *
FROM (SELECT stg.opt,
stg.pk_id,
src.user_id,
src.phn_nbr,
src.login_id,
src.usr_type_cd,
src.usr_dt,
src.email_add
FROM scott.SRC_TBL src, scott.STG_TBL stg
WHERE src.pk_id = stg.pk_id
AND stg.opt=’I’

UNION ALL
SELECT stg.opt,
stg.pk_id,
src.user_id,
src.phn_nbr,
src.login_id,
src.usr_type_cd,
src.usr_dt,
src.email_add
FROM scott.SRC_TBL src, scott.STG_TBL stg
WHERE src.pk_id = stg.pk_id
AND stg.opt=’U’

UNION ALL
SELECT stg.opt,
stg.pk_id,
src.user_id,
src.phn_nbr,
src.login_id,
src.usr_type_cd,
src.usr_dt,
src.email_add
FROM scott.SRC_TBL src, scott.STG_TBL stg
WHERE src.pk_id = stg.pk_id
AND stg.opt=’D’
);

v_bgn_cnt number :=0;
v_step_name varchar2(200);
v_rec_ins number := 0;
v_start_dt date := sysdate;
v_error_msg varchar2(500);


BEGIN
v_step_name := ‘Process inserts and updates into scott.RPT_TBL’;


SELECT count(*)
into v_bgn_cnt
FROM scott.STG_TBL;


IF v_bgn_cnt >0 THEN


– Process each change


FOR rec IN cur LOOP


IF rec.opt = ‘I’ THEN
INSERT INTO scott.RPT_TBL
VALUES
( rec.pk_id,
rec.user_id,
rec.phn_nbr,
rec.login_id,
rec.usr_type_cd,
rec.usr_dt,
rec.email_add);
END IF;

IF rec.opt = ‘U’
THEN
UPDATE scott.RPT_TBL
set USER_ID = rec.user_id,
PHN_NBR = rec.phn_nbr,
login_id = rec.login_id,
user_type_cd = rec.user_type_cd,
usr_dt = rec.usr_dt,
email_add = rec.email_add
WHERE pk_ID = rec.pk_ID;
END IF;

IF rec.opt = ‘D’
THEN
DELETE from scott.RPT_TBL
WHERE pk_ID = rec.pk_ID;
END IF;


COMMIT;
END LOOP;
END IF;

–Insert Error handling here, I’m going to skip this in the example…
–Add a lookup table to track the pk_id from previous runs, etc.
–Truncate the stage table for the next run once success had been reported

END UPD_RPTTBL_SP;

We now can use the objects and procedures to maintain a copy of the SRC_TBL.  The DBA or developer simply needs to choose a functional interval to refresh and execute the procedures view a scheduled job or through a shell script.
exec UPD_PKID_FOR_RPT;
exec UPD_RPTTBL_SP;

Ta-da!  A simple process to syncronize data from a transactional table to a reporting copy.  This process can be scaled to load and commit in many different ways, depending on the demand and size of the targets.  It’s simple and can support many levels of loading intervals without the impact that often occurs with a materialized view refresh.

 

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!