To improve the refresh performance for a materialized view, I set up it to be refreshed in parallel. The view can be refreshed successfully, however, I did not see the view is refreshed in parallel from session browser, can someone let me know if I miss any steps?
1) In DB A (running 8 CPUs), set up the base table to be parallelized, the base table is called table1
ALTER TABLE A.table1 PARALLEL ( DEGREE Default INSTANCES Default );
2) In Database A, set up the materialized view log
CREATE MATERIALIZED VIEW LOG
ON table1 WITH primary key
INCLUDING NEW VALUES;
3) In Database B (in the same server with Database A), there is an existing table called table1, prebuilt with millions of records in the table. Due to the size of table1, I have to use prebuilt option
Drop MATERIALIZED VIEW table1;
CREATE MATERIALIZED VIEW table1 ON PREBUILT TABLE
REFRESH FAST with primary key
select /*+ PARALLEL(table1, 8) */ *
4) in Database B, I executed this stored procedure -
EXECUTE DBMS_MVIEW.REFRESH(LIST=>'table1',PARALLELISM=>8, METHOD=>'F');
Thanks in advance!
My DB is in 11g R1. I did read an article I found http://www.doug.org/newsletter/march/MV_Refresh_Parallel.pdf. It mentioned that I need to set the initialization parameter PARALLEL_AUTOMATIC_TUNING to TRUE, but our DBA told me that it was deprecated now.
is this true? Should I turn on this parameter?
Why do not you actually run this command & try refreshing it manually.. as below
ALTER TABLE table1 PARALLEL (DEGREE 2);
try refreshing MV manually..
first ... EXECUTE DBMS_MVIEW.REFRESH(LIST=>'LOG',PARALLELISM=>2);
second .. EXECUTE DBMS_MVIEW.REFRESH(LIST=>'LOG');
In both cases keep an eye on session browser..
Great to know PARALLELISM is working! PARALLEL_MAX_SERVERS is set to 135 in database A.
I have another question. I saw it does full table scan on the M View log:
1 TABLE ACCESS FULL TABLE A.MLOG$_TABLE1 A Cost: 10,518 Bytes: 9,105,294 Cardinality: 275,918
I check the view log, it has 5,036, 474 rows while the base table only has 2518237 rows. It will take a long time to read from the view log with full table scan
How do I avoid full table scan on the M view log?
1. How do you know it will take a long time to full-scan the mview log? Full scans are not evil.
2. You can't avoid full-scanning it because it's the purpose of the log to record all changes to the base table so that mview can be refreshed. Refresh will need to "replay" all those changes anyway.