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
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
OR
Incase of existing mviews:
alter materialized view log on
3.) When you create the mview, create it with a parallel clause:
create materialized view
OR
create materialized view
Or
Incase of existing mviews:
alter table
alter materialized view
Lastly while executing the refresh:
EXECUTE DBMS_MVIEW.REFRESH(LIST=>'
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
2.) Replace
3.) Replacedblink with the name of the database link.
Tuesday, November 30, 2010
How to refresh MVIEW in parallel
Posted by Apun Hiran at 7:33 AM
Labels: alter mview, alter table, mview, oracle, parallel, parallelism
Subscribe to:
Post Comments (Atom)
2 Comments:
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
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
Post a Comment