Most of case DBA's faced issued with IOs and some times poor performance for mviews and mview refreshes. If creating index on mview resolve the issue, then we can focus how fast we can refresh the mviews.
Materialized Views are a wonderful tool for reducing repetitive I/O and they are a true silver bullet under certain circumstances. The problem is keeping the materialized view refreshed, and refreshing materialized views has always been resource-intensive and problematic.
The problem with materialized view for pre-joined tables is keeping them current with the refresh mechanism. Because the materialized view is built from many tables, and changes to the base tables require an update to the materialized view (via a snapshot refresh or full refresh).
Boost up materialized view refreshes:
Oracle "fast refresh" mechanism is already optimized by Oracle. So, you can speed-up a materialized view refresh on a high DML system. There are several options:
- Partition the base tables - Do a partition for remote/ base table. Use efficient partition. See my notes on partition in this blog.
- Use parallel DML - Use parallel materialized view refreshing on tables. It will improve performance considerably. Rather than having one load which took 2 hours, I run 4 parallel loads, one for each partition. The length of time for the whole process is now determined by how long the biggest partition takes to load. In my case, this is 40 minutes, with two 30 minute loads and one 20 minute load. Overall I am saving 1 hour 20 minutes. I can now add further partitions and do the same thing. My only limitation is the parallel loads because I don't have unlimited processing power.
- Use super-fast solid-state disks - The easiest and most reliable way is to speed-up a materialized view refresh is to move the target tables and MV's to SSD. SSD runs several hundred times faster than platter disk, and it plops right in, just a few hours to install.
Other Tips:
Use materialized views only for enabling query rewrite (which means creating them on a prebuilt summary table, and unless you are loading to the summary by partition exchange then you have to drop the MV, refresh the summary table, and recreate the MV). In 10g it is much more easy to use the DBMS_Advanced_Rewrite package instead of MV's.
Write your own refresh code, based on the usual principles of writing good SQL. If you don't need a merge then don't use it. If you don't need to join to dimension tables to get higher attributes then don't do it.
Leverage different levels of aggregation to help produce higher levels. For a series of hierarchical summaries, multi-level aggregations can be extremely beneficial.
Consider storing the refresh and MV definition SQL in CLOB columns of a summary management table, so they can be tuned and edited without needing to open up package code to do so.
Consider using a complete refresh, either through MV's or manually, for higher aggregation levels, particularly when you can reference another summary table to do so.
Materialized Views are a wonderful tool for reducing repetitive I/O and they are a true silver bullet under certain circumstances. The problem is keeping the materialized view refreshed, and refreshing materialized views has always been resource-intensive and problematic.
The problem with materialized view for pre-joined tables is keeping them current with the refresh mechanism. Because the materialized view is built from many tables, and changes to the base tables require an update to the materialized view (via a snapshot refresh or full refresh).
Boost up materialized view refreshes:
Oracle "fast refresh" mechanism is already optimized by Oracle. So, you can speed-up a materialized view refresh on a high DML system. There are several options:
- Partition the base tables - Do a partition for remote/ base table. Use efficient partition. See my notes on partition in this blog.
- Use parallel DML - Use parallel materialized view refreshing on tables. It will improve performance considerably. Rather than having one load which took 2 hours, I run 4 parallel loads, one for each partition. The length of time for the whole process is now determined by how long the biggest partition takes to load. In my case, this is 40 minutes, with two 30 minute loads and one 20 minute load. Overall I am saving 1 hour 20 minutes. I can now add further partitions and do the same thing. My only limitation is the parallel loads because I don't have unlimited processing power.
- Use super-fast solid-state disks - The easiest and most reliable way is to speed-up a materialized view refresh is to move the target tables and MV's to SSD. SSD runs several hundred times faster than platter disk, and it plops right in, just a few hours to install.
Other Tips:
Use materialized views only for enabling query rewrite (which means creating them on a prebuilt summary table, and unless you are loading to the summary by partition exchange then you have to drop the MV, refresh the summary table, and recreate the MV). In 10g it is much more easy to use the DBMS_Advanced_Rewrite package instead of MV's.
Write your own refresh code, based on the usual principles of writing good SQL. If you don't need a merge then don't use it. If you don't need to join to dimension tables to get higher attributes then don't do it.
Leverage different levels of aggregation to help produce higher levels. For a series of hierarchical summaries, multi-level aggregations can be extremely beneficial.
Consider storing the refresh and MV definition SQL in CLOB columns of a summary management table, so they can be tuned and edited without needing to open up package code to do so.
Consider using a complete refresh, either through MV's or manually, for higher aggregation levels, particularly when you can reference another summary table to do so.
This comment has been removed by a blog administrator.
ReplyDelete