This discussion is archived
5 Replies Latest reply: Mar 27, 2013 11:20 AM by 999541 RSS

Refresh of a materialized view

999541 Newbie
Currently Being Moderated
greatly appreciate your help.

how to limit the execution the following query to one instance, to avoid going through the interconnect in a RAC DB,
exec dbms_mview.refresh('schema.mat_view',parallelism=>10 );

in another words, to allow all the parallel slaves to be run on one node.
  • 1. Re: Refresh of a materialized view
    sb92075 Guru
    Currently Being Moderated
    996538 wrote:
    greatly appreciate your help.

    how to limit the execution the following query to one instance, to avoid going through the interconnect in a RAC DB,
    exec dbms_mview.refresh('schema.mat_view',parallelism=>10 );

    in another words, to allow all the parallel slaves to be run on one node.
    post SQL & results that show PARALLEL slaves run on different nodes.
  • 2. Re: Refresh of a materialized view
    sybrand_b Guru
    Currently Being Moderated
    When asking any question, always please post the full 4 digit database version and the OS (and full version).

    My question is why you bought a Ferrari with multiple motors, and want to exhaust only one of them.
    Also, did you consult documentation, before asking this? There doesn't seem to be any parameter to arrange this.

    --------------
    Sybrand Bakker
    Senior Oracle DBA
  • 3. Re: Refresh of a materialized view
    999541 Newbie
    Currently Being Moderated
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    SunOS 5.10

    yes, i checked the doc, and no clause available.

    the reason for Ferrary running on one node, the experience shows, if the parallel slaves run on one node, the
    query (select) runs much faster, b/c the results dont have to be sent through the interconnect and dont have
    to be merged from multiple nodes, this is a proven fact, so we like to apply the same approach, when a
    mat view refreshed.

    Could Oracle do it, may be some dynamic parameters have to be set for this session....? Just a thought....
  • 4. Re: Refresh of a materialized view
    JohnWatson Guru
    Currently Being Moderated
    This should do it,
    alter session set parallel_force_local=true;
    --
    John Watson
    Oracle Certified Master DBA
    http://skillbuilders.com
  • 5. Re: Refresh of a materialized view
    999541 Newbie
    Currently Being Moderated
    Thanks JohnWatson!
    THE really great finding!!!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points