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,…
-
-
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…
-
I’ve started working on my abstract to submit for RMOUG 2011. I want to present on some of the CBO challenges I’ve taken on in the last year with large data sets. There are a number of tricks that could be very useful to others in how to manipulate the CBO to work with tables over 100G in size. Many of these tricks were requirements in areas where there have been small design flaws that “confused” the CBO and are very challenging for any DBA. I would also like to go into the steps I take to ensure that I am completely…