5 Replies Latest reply: Mar 27, 2013 1:20 PM by 999541 RSS

    Refresh of a materialized view

    999541
      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
          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
            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
              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
                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
                  Thanks JohnWatson!
                  THE really great finding!!!