Tuesday, November 30, 2010

How to refresh MVIEW in parallel

Refresh TYPES are COMPLETE/FAST/ON COMMIT/ON DEMAND
Couple of things you should do to make a mview to refresh in parallel:
1.) Alter the master table to make sure it allows parallelism:

alter table parallel (degree 4);
To check
select degree from dba_tables where table_name='';

2.) When you create the mview log (incase of FAST refresh mviews), create it with a parallel clause:

create materialized view log on parallel (degree 4);
OR
Incase of existing mviews:
alter materialized view log on parallel (degree 4);

3.) When you create the mview, create it with a parallel clause:

create materialized view REFRESH COMPLETE ON DEMAND as select /*+ PARALLEL (table_name,4) */ * from @dblink;
OR
create materialized view REFRESH COMPLETE ON DEMAND parallel 4 as select * from @dblink;
Or
Incase of existing mviews:
alter table parallel (degree 4);
alter materialized view parallel (degree 4);

Lastly while executing the refresh:

EXECUTE DBMS_MVIEW.REFRESH(LIST=>'',PARALLELISM=>4);

This should make your mview refresh in parallel. With the above example the mview should run with parallel degree 4.

PS: Make sure that parallel is enabled for your database, check parameters:

PARALLEL_MAX_SERVERS
PARALLEL_EXECUTION_MESSAGE_SIZE
PARALLEL_ADAPTIVE_MULTI_USER
PARALLEL_AUTOMATIC_TUNING

Make sure that the above parameter are set to allow any parallelism to work.
Sample values for these parameters:

SQL> show parameter PARALLEL_MAX_SERVERS
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 120
SQL> show parameter PARALLEL_EXECUTION_MESSAGE_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_execution_message_size integer 16384
SQL> show parameter PARALLEL_ADAPTIVE_MULTI_USER
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_adaptive_multi_user boolean TRUE
SQL> show parameter PARALLEL_AUTOMATIC_TUNING
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_automatic_tuning boolean FALSE


Note:
1.) Replace with the table name.
2.) Replace with materialized view name.
3.) Replacedblink with the name of the database link.

2 Comments:

Anonymous said...

Hey! Thanks for the information you've provided on your blog, I am looking for this. Keep it up.

Good work and thanks again.


John
Baby Gift Baskets

Anonymous said...


Hi,

We have create MVIEW with parallel degree 4. But database parameter 'parallel_automatic_tuning' is set to false.
My question is that, in this case parallelism will work or not..?

should we have to change 'parallel_automatic_tuning' parameter.?

Thanks