10 Replies Latest reply: Mar 10, 2011 9:58 PM by Vladimir Zakharychev RSS

    refresh a materialized view in parallel

    627703
      Hi,

      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
      AS
      select /*+ PARALLEL(table1, 8) */ *
      from table1@A;

      4) in Database B, I executed this stored procedure -

      EXECUTE DBMS_MVIEW.REFRESH(LIST=>'table1',PARALLELISM=>8, METHOD=>'F');


      Thanks in advance!

      Liz
        • 1. Re: refresh a materialized view in parallel
          Lubiez Jean-Valentin
          Hello,


          Did you enable parallel execution in the Database ? Some parameters (PARALLEL_MAX_SERVERS, ...) should be set before.

          The following Note of My Oracle Support may help you in this task:
          How to Refresh a Materialized View in Parallel [ID 577870.1]
          Hope this help.
          Best regards,
          Jean-Valentin
          • 2. Re: refresh a materialized view in parallel
            627703
            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?

            Thanks
            Liz
            • 3. Re: refresh a materialized view in parallel
              12cdb
              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..
              • 4. Re: refresh a materialized view in parallel
                12cdb
                yes, it is true.. the PARALLEL_AUTOMATIC_TUNING parameter is deprecated in 11gR1. It is retained for backward compatibility only.
                • 5. Re: refresh a materialized view in parallel
                  627703
                  I checked the execution plan when I executed the refresh stored procedure again, Does the plan indicates the parallel execution is working or not?

                  SELECT STATEMENT REMOTE ALL_ROWSCost: 32,174 Bytes: 775,392 Cardinality: 1,182                                                   
                  13 PX COORDINATOR                                              
                  12 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10001 :Q1001                                        
                       11 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1001                                   
                       9 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1001Cost: 32,174 Bytes: 775,392 Cardinality: 1,182                               
                            6 BUFFER SORT PARALLEL_COMBINED_WITH_CHILD :Q1001                         
                                 5 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1001                    
                                      4 PX SEND ROUND-ROBIN PARALLEL_FROM_SERIAL SYS.:TQ10000 A               
                                           3 VIEW APP_USR. Cost: 12,986 Bytes: 6,070,196 Cardinality: 275,918           
                                                2 HASH UNIQUE Cost: 12,986 Bytes: 9,105,294 Cardinality: 275,918      
                                                     1 TABLE ACCESS FULL TABLE A.MLOG$_TABLE1 A Cost: 10,518 Bytes: 9,105,294 Cardinality: 275,918
                       8 PARTITION RANGE ITERATOR PARALLEL_COMBINED_WITH_PARENT :Q1001Cost: 0 Cardinality: 1                          
                       7 INDEX RANGE SCAN INDEX (UNIQUE) PARALLEL_COMBINED_WITH_PARENT A.PK_TABLE1 :Q1001Cost: 0 Cardinality: 1                     
                  10 TABLE ACCESS BY LOCAL INDEX ROWID TABLE PARALLEL_COMBINED_WITH_PARENT A.TABLE1 :Q1001Cost: 0 Bytes: 634 Cardinality: 1
                  • 6. Re: refresh a materialized view in parallel
                    Vladimir Zakharychev
                    The plan is for parallel execution. However, if there are not enough parallel slaves available, it will execute serially anyway. See PARALLEL_MAX_SERVERS initialization parameter.
                    • 7. Re: refresh a materialized view in parallel
                      sb92075
                      Does the plan indicates the parallel execution is working or not?
                      yes

                      notice the lines with PX
                      • 8. Re: refresh a materialized view in parallel
                        627703
                        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?

                        Thanks
                        Liz
                        • 9. Re: refresh a materialized view in parallel
                          sb92075
                          How do I avoid full table scan on the M view log?
                          Can YOU tune SQL that you have never seen against unknown tables containing unknown data?
                          I can't.
                          • 10. Re: refresh a materialized view in parallel
                            Vladimir Zakharychev
                            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.