Tuning DW and OLTP SQL in a Single Oracle Database

Learn how to implement Oracle Real-Time Materialized Views to tune a single Oracle Database for both Data Warehouse and OLTP use. Configuration is not necessarily straight forward. We’ll demonstrate the problems, and how to fix them.

Background:
How can you tune an Oracle database for both OLTP and Data Warehouse use at the same time? A common solution is to use materialized views: the small transactions address the tables, the big queries hit the views. However, if the queries need to return real-time information, you will likely end up with either fast transactions and slow queries, or slow transactions and fast queries. Or perhaps everything will be slow.

In this tutorial, John will demonstrate how to use Oracle materialized views to optimize queries without impacting on transaction processing by implementing the recently added feature of Real-Time Materialized Views.

From Oracle Database release 12.2 and up, not only can queries be transparently re-written to address the views (that isn’t new at all) but if the view is stale changes in the materialized view logs can be transparently merged into the result set, thus giving real-time results without the overheads of FAST (or even ON COMMIT) refresh. However, configuring this is not necessarily straight forward. We’ll demonstrate the problems, and how to fix them.

Prerequisite Knowledge:
Oracle Database Administrators or anyone with:
– A solid understanding of Data Warehouse and Online Transaction Processing (OLTP) concepts
– Some experience with Materialized Views
– Some Oracle tuning experience such as interpreting Execution Plans. A free tutorial on Reading Oracle Execution Plans, great as an introduction OR a refresher, can be found here.

Presenter:
John Watson, Oracle Certified Master DBA, SkillBuilders Director of All Things Oracle Database