-- Oracle Materialized Views real case Performance Improvements --

Recently I got pulled into a case where our customer was complaining that Materialized Views were taking too long to refresh, upon research I found the following:

• Materialized Views are located on the same server as the source tables.
• They use the Materialized views to do joins of tables and views for reporting purposes.
• They have an Active Data Guard Database on the DR Data Center.
• They created 3 refresh groups in an attempt to improve performance
• They are not using the scheduler but DBA_JOBS.
• They do complete refresh every 2 hours.
• Their database is a 3 node RAC cluster.

These were my recommendations:

• Do you really need Materialized views, or a view using the proper partitioning and parallel query will do the job, never underestimate the power of Parallel Query using partitioned tables and having the right indexes in place.
• Use your Active Data Guard (ADG) for reporting purposes.
• Favor Fast Refresh over Complete refresh.
• Use the Oracle Scheduler rather than DBA_JOBS.
• Use node affinity so you do not tax a single server consuming a bunch of resources during the refresh.

I know this is just common sense, but I though about share it with you as there may be some other people having similar issues.