6 Replies Latest reply: Sep 10, 2013 4:00 AM by Jonathan Lewis RSS

    why oracle downgrade parallel execution

    user10858330

      Hello Everyone,

      Oracle downgraded my application quires to 1.

      {code}

      fast_start_parallel_rollback                  string LOW                                                                                           
      parallel_adaptive_multi_user                  booleanFALSE     (it was true i set it false)                                                                                    
      parallel_automatic_tuning                     booleanFALSE                                                                                         
      parallel_degree_limit                         string CPU                                                                                           
      parallel_degree_policy                        string MANUAL                                                                                        
      parallel_execution_message_size               integer16384                                                                                         
      parallel_force_local                          booleanFALSE                                                                                         
      parallel_instance_group                       string                                                                                                    
      parallel_io_cap_enabled                       booleanFALSE                                                                                         
      parallel_max_servers                          integer160                                                                                           
      parallel_min_percent                          integer0                                                                                             
      parallel_min_servers                          integer16                                                                                            
      parallel_min_time_threshold                   string AUTO                                                                                          
      parallel_server                               booleanFALSE                                                                                         
      parallel_server_instances                     integer1                                                                                             
      parallel_servers_target                       integer64                                                                                            
      parallel_threads_per_cpu                      integer2                                                                                             
      recovery_parallelism                          integer0

      cpu_count                                                                                                                                                                   4

      {\code}

       

      My all table created with parallel degree four. when i run simple select statement against any table it does not downgrade but as i run join table query it downgrade to 1 which take one hour to complete as i issue this Alter Session Force Parallel Query Parallel 2; it get complete in 8 seconds? Can anybody let me know what is the problem how can i avoid this here is y oracle version details Release 11.2.0.3.0 Production on Tue Sep 10 06:52:04 2013 on windows server 2008 64bit

        • 1. Re: why oracle downgrade parallel execution
          user10858330

          anyone can help?

          • 2. Re: why oracle downgrade parallel execution
            Martin Preiss

            Randolf Geist wrote (http://oracle-randolf.blogspot.de/2011/10/parallel-downgrade.html):

            There are many reasons why a parallel execution might not run with the expected degree of parallelism (DOP), beginning with running out of parallel slaves (PARALLEL_MAX_SERVERS or PROCESSES reached), PARALLEL_ADAPTIVE_MULTI_USER, downgrades at execution time via the Resource Manager, or the more recent features like PARALLEL_DEGREE_LIMIT or the Auto DOP introduced in Oracle 11.2.

             

            Maybe you find some additional answers in http://www.oracle.com/technetwork/articles/database-performance/geist-parallel-execution-2-1872405.html.

            • 3. Re: why oracle downgrade parallel execution
              Jonathan Lewis

              user10858330 wrote:

               

              My all table created with parallel degree four. when i run simple select statement against any table it does not downgrade but as i run join table query it downgrade to 1 which take one hour to complete as i issue this Alter Session Force Parallel Query Parallel 2; it get complete in 8 seconds? Can anybody let me know what is the problem how can i avoid this here is y oracle version details Release 11.2.0.3.0 Production on Tue Sep 10 06:52:04 2013 on windows server 2008 64bit

               

              Do you mean you are getting a parallel plan that is downgraded to serial at run time, or are you getting a serial plan ?

              How are you checking ?

               

              The first guess would be that the optimizer found a serial path with a lower cost.

               

              Regards

              Jonathan Lewis


              • 4. Re: why oracle downgrade parallel execution
                user10858330

                {code}

                plan FOR succeeded.

                PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                        

                ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                Plan hash value: 3201106479                                                                                                                                                                                                                                                                                

                                                                                                                                                                                                                                                                                                                           

                -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                              

                | Id  | Operation                                              | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |                                                                                                                              

                -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                              

                |   0 | SELECT STATEMENT                                       |                       |     1 |    86 | 22098   (1)| 00:05:10 |       |       |        |      |            |                                                                                                                              

                |   1 |  HASH GROUP BY                                         |                       |     1 |    86 |            |          |       |       |        |      |            |                                                                                                                              

                |   2 |   PX COORDINATOR                                       |                       |       |       |            |          |       |       |        |      |            |                                                                                                                              

                |   3 |    PX SEND QC (RANDOM)                                 | :TQ40003              |       |       |            |          |       |       |  Q4,03 | P->S | QC (RAND)  |                                                                                                                              

                |   4 |     BUFFER SORT                                        |                       |     1 |    86 |            |          |       |       |  Q4,03 | PCWP |            |                                                                                                                              

                |   5 |      CONCATENATION                                     |                       |       |       |            |          |       |       |  Q4,03 | PCWP |            |                                                                                                                              

                |   6 |       NESTED LOOPS                                     |                       |       |       |            |          |       |       |  Q4,03 | PCWP |            |                                                                                                                              

                |   7 |        NESTED LOOPS                                    |                       |     1 |    86 |     5   (0)| 00:00:01 |       |       |  Q4,03 | PCWP |            |                                                                                                                              

                |   8 |         NESTED LOOPS                                   |                       |     1 |    65 |     4   (0)| 00:00:01 |       |       |  Q4,03 | PCWP |            |                                                                                                                              

                |   9 |          NESTED LOOPS                                  |                       |     1 |    44 |     3   (0)| 00:00:01 |       |       |  Q4,03 | PCWP |            |                                                                                                                              

                |  10 |           PX PARTITION RANGE ITERATOR                  |                       |     1 |    22 |     1   (0)| 00:00:01 |     1 |   KEY |  Q4,03 | PCWC |            |                                                                                                                              

                |* 11 |            TABLE ACCESS BY LOCAL INDEX ROWID           | REBE01S1_DG0          |     1 |    22 |     1   (0)| 00:00:01 |     1 |   KEY |  Q4,03 | PCWP |            |                                                                                                                              

                |  12 |             BITMAP CONVERSION TO ROWIDS                |                       |       |       |            |          |       |       |  Q4,03 | PCWP |            |                                                                                                                              

                |* 13 |              BITMAP INDEX RANGE SCAN                   | MINDX_REBE01S1_DG037  |       |       |            |          |     1 |   KEY |  Q4,03 | PCWP |            |                                                                                                                              

                |  14 |           PARTITION RANGE OR                           |                       |     1 |    22 |     3   (0)| 00:00:01 |KEY(OR)|KEY(OR)|  Q4,03 | PCWP |            |                                                                                                                              

                |* 15 |            TABLE ACCESS BY LOCAL INDEX ROWID           | REBE01S1_JT           |     1 |    22 |     3   (0)| 00:00:01 |KEY(OR)|KEY(OR)|  Q4,03 | PCWP |            |                                                                                                                              

                |* 16 |             INDEX RANGE SCAN                           | MINDX_REBE01S1_JT_DG0 |     1 |       |     2   (0)| 00:00:01 |KEY(OR)|KEY(OR)|  Q4,03 | PCWP |            |                                                                                                                              

                |  17 |          PARTITION RANGE OR                            |                       |     1 |    21 |     0   (0)| 00:00:01 |KEY(OR)|KEY(OR)|  Q4,03 | PCWP |            |                                                                                                                              

                |* 18 |           TABLE ACCESS BY LOCAL INDEX ROWID            | REBE01S1_DG6          |     1 |    21 |     0   (0)| 00:00:01 |KEY(OR)|KEY(OR)|  Q4,03 | PCWP |            |                                                                                                                              

                |* 19 |            INDEX RANGE SCAN                            | PKINDX_REBE01S1_DG6   |     1 |       |     0   (0)| 00:00:01 |KEY(OR)|KEY(OR)|  Q4,03 | PCWP |            |                                                                                                                              

                |  20 |         PARTITION RANGE OR                             |                       |     1 |       |     1   (0)| 00:00:01 |KEY(OR)|KEY(OR)|  Q4,03 | PCWP |            |                                                                                                                              

                |* 21 |          INDEX RANGE SCAN                              | PKINDX_REBE01S1_DG4   |     1 |       |     1   (0)| 00:00:01 |KEY(OR)|KEY(OR)|  Q4,03 | PCWP |            |                                                                                                                              

                |* 22 |        TABLE ACCESS BY LOCAL INDEX ROWID               | REBE01S1_DG4          |     1 |    21 |     1   (0)| 00:00:01 |     1 |     1 |  Q4,03 | PCWP |            |                                                                                                                              

                |  23 |       BUFFER SORT                                      |                       |       |       |            |          |       |       |  Q4,03 | PCWC |            |                                                                                                                              

                |  24 |        PX RECEIVE                                      |                       |       |       |            |          |       |       |  Q4,03 | PCWP |            |                                                                                                                              

                |  25 |         PX SEND ROUND-ROBIN                            | :TQ40000              |       |       |            |          |       |       |        | S->P | RND-ROBIN  |                                                                                                                              

                |  26 |          BUFFER SORT                                   |                       |     1 |    86 |            |          |       |       |        |      |            |                                                                                                                              

                |  27 |           PX COORDINATOR                               |                       |       |       |            |          |       |       |        |      |            |                                                                                                                              

                |  28 |            PX SEND QC (RANDOM)                         | :TQ10000              |       |       |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |                                                                                                                              

                |  29 |             BUFFER SORT                                |                       |     1 |    86 |            |          |       |       |  Q1,00 | PCWP |            |                                                                                                                              

                |  30 |              NESTED LOOPS                              |                       |       |       |            |          |       |       |  Q1,00 | PCWP |            |                                                                                                                              

                |  31 |               NESTED LOOPS                             |                       |     1 |    86 |     3   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |                                                                                                                              

                |  32 |                NESTED LOOPS                            |                       |     1 |    65 |     2   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |                                                                                                                              

                |  33 |                 NESTED LOOPS                           |                       |     1 |    43 |     1   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |                                                                                                                              

                |  34 |                  PX PARTITION RANGE ITERATOR           |                       |     1 |    22 |     1   (0)| 00:00:01 |     1 |   KEY |  Q1,00 | PCWC |            |                                                                                                                              

                |  35 |                   TABLE ACCESS BY LOCAL INDEX ROWID    | REBE01S1_JT           |     1 |    22 |     1   (0)| 00:00:01 |     1 |   KEY |  Q1,00 | PCWP |            |                                                                                                                              

                |  36 |                    BITMAP CONVERSION TO ROWIDS         |                       |       |       |            |          |       |       |  Q1,00 | PCWP |            |                                                                                                                              

                |* 37 |                     BITMAP INDEX RANGE SCAN            | MINDX_REBE01S1_JT_PF_ |       |       |            |          |     1 |   KEY |  Q1,00 | PCWP |            |                                                                                                                              

                |  38 |                  PARTITION RANGE OR                    |                       |     1 |    21 |     0   (0)| 00:00:01 |KEY(OR)|KEY(OR)|  Q1,00 | PCWP |            |                                                                                                                              

                |* 39 |                   TABLE ACCESS BY LOCAL INDEX ROWID    | REBE01S1_DG6          |     1 |    21 |     0   (0)| 00:00:01 |KEY(OR)|KEY(OR)|  Q1,00 | PCWP |            |                                                                                                                              

                |* 40 |                    INDEX RANGE SCAN                    | PKINDX_REBE01S1_DG6   |     1 |       |     0   (0)| 00:00:01 |KEY(OR)|KEY(OR)|  Q1,00 | PCWP |            |                                                                                                                              

                |  41 |                 PARTITION RANGE SINGLE                 |                       |     1 |    22 |     1   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWP |            |                                                                                                                              

                |* 42 |                  TABLE ACCESS BY LOCAL INDEX ROWID     | REBE01S1_DG0          |     1 |    22 |     1   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWP |            |                                                                                                                              

                |* 43 |                   INDEX RANGE SCAN                     | PKINDX_REBE01S1_DG0   |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWP |            |                                                                                                                              

                |  44 |                PARTITION RANGE OR                      |                       |     1 |       |     1   (0)| 00:00:01 |KEY(OR)|KEY(OR)|  Q1,00 | PCWP |            |                                                                                                                              

                |* 45 |                 INDEX RANGE SCAN                       | PKINDX_REBE01S1_DG4   |     1 |       |     1   (0)| 00:00:01 |KEY(OR)|KEY(OR)|  Q1,00 | PCWP |            |                                                                                                                              

                |* 46 |               TABLE ACCESS BY LOCAL INDEX ROWID        | REBE01S1_DG4          |     1 |    21 |     1   (0)| 00:00:01 |     1 |     1 |  Q1,00 | PCWP |            |                                                                                                                              

                |  47 |       BUFFER SORT                                      |                       |       |       |            |          |       |       |  Q4,03 | PCWC |            |                                                                                                                              

                |  48 |        PX RECEIVE                                      |                       |       |       |            |          |       |       |  Q4,03 | PCWP |            |                                                                                                                              

                |  49 |         PX SEND ROUND-ROBIN                            | :TQ40001              |       |       |            |          |       |       |        | S->P | RND-ROBIN  |                                                                                                                              

                |  50 |          BUFFER SORT                                   |                       |     1 |    86 |            |          |       |       |        |      |            |                                                                                                                              

                |  51 |           PX COORDINATOR                               |                       |       |       |            |          |       |       |        |      |            |                                                                                                                              

                |  52 |            PX SEND QC (RANDOM)                         | :TQ20000              |       |       |            |          |       |       |  Q2,00 | P->S | QC (RAND)  |                                                                                                                              

                |  53 |             BUFFER SORT                                |                       |     1 |    86 |            |          |       |       |  Q2,00 | PCWP |            |                                                                                                                              

                |  54 |              NESTED LOOPS                              |                       |       |       |            |          |       |       |  Q2,00 | PCWP |            |                                                                                                                              

                |  55 |               NESTED LOOPS                             |                       |     1 |    86 |     6   (0)| 00:00:01 |       |       |  Q2,00 | PCWP |            |                                                                                                                              

                |  56 |                NESTED LOOPS                            |                       |     1 |    64 |     6   (0)| 00:00:01 |       |       |  Q2,00 | PCWP |            |                                                                                                                              

                |  57 |                 NESTED LOOPS                           |                       |     1 |    43 |     5   (0)| 00:00:01 |       |       |  Q2,00 | PCWP |            |                                                                                                                              

                |  58 |                  PX PARTITION RANGE ITERATOR           |                       |     1 |    21 |     1   (0)| 00:00:01 |     1 |   KEY |  Q2,00 | PCWC |            |                                                                                                                              

                |* 59 |                   TABLE ACCESS BY LOCAL INDEX ROWID    | REBE01S1_DG4          |     1 |    21 |     1   (0)| 00:00:01 |     1 |   KEY |  Q2,00 | PCWP |            |                                                                                                                              

                |  60 |                    BITMAP CONVERSION TO ROWIDS         |                       |       |       |            |          |       |       |  Q2,00 | PCWP |            |                                                                                                                              

                |* 61 |                     BITMAP INDEX RANGE SCAN            | MINDX_REBE01S1_DG431  |       |       |            |          |     1 |   KEY |  Q2,00 | PCWP |            |                                                                                                                              

                |  62 |                  PARTITION RANGE SINGLE                |                       |    46 |  1012 |     4   (0)| 00:00:01 |   KEY |   KEY |  Q2,00 | PCWP |            |                                                                                                                              

                |* 63 |                   TABLE ACCESS BY LOCAL INDEX ROWID    | REBE01S1_JT           |    46 |  1012 |     4   (0)| 00:00:01 |   KEY |   KEY |  Q2,00 | PCWP |            |                                                                                                                              

                |* 64 |                    INDEX RANGE SCAN                    | MINDX_REBE01S1_JT_DG4 |    15 |       |     1   (0)| 00:00:01 |   KEY |   KEY |  Q2,00 | PCWP |            |                                                                                                                              

                |  65 |                 PARTITION RANGE OR                     |                       |     1 |    21 |     0   (0)| 00:00:01 |KEY(OR)|KEY(OR)|  Q2,00 | PCWP |            |                                                                                                                              

                |* 66 |                  TABLE ACCESS BY LOCAL INDEX ROWID     | REBE01S1_DG6          |     1 |    21 |     0   (0)| 00:00:01 |KEY(OR)|KEY(OR)|  Q2,00 | PCWP |            |                                                                                                                              

                |* 67 |                   INDEX RANGE SCAN                     | PKINDX_REBE01S1_DG6   |     1 |       |     0   (0)| 00:00:01 |KEY(OR)|KEY(OR)|  Q2,00 | PCWP |            |                                                                                                                              

                |  68 |                PARTITION RANGE SINGLE                  |                       |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |  Q2,00 | PCWP |            |                                                                                                                              

                |* 69 |                 INDEX RANGE SCAN                       | PKINDX_REBE01S1_DG0   |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |  Q2,00 | PCWP |            |                                                                                                                              

                |* 70 |               TABLE ACCESS BY LOCAL INDEX ROWID        | REBE01S1_DG0          |     1 |    22 |     1   (0)| 00:00:01 |     1 |     1 |  Q2,00 | PCWP |            |                                                                                                                              

                |  71 |       BUFFER SORT                                      |                       |       |       |            |          |       |       |  Q4,03 | PCWC |            |                                                                                                                              

                |  72 |        PX RECEIVE                                      |                       |   772 | 66392 | 22082   (1)| 00:05:10 |       |       |  Q4,03 | PCWP |            |                                                                                                                              

                |  73 |         PX SEND ROUND-ROBIN                            | :TQ40002              |   772 | 66392 | 22082   (1)| 00:05:10 |       |       |        | S->P | RND-ROBIN  |                                                                                                                              

                |  74 |          BUFFER SORT                                   |                       |     1 |    86 |            |          |       |       |        |      |            |                                                                                                                              

                |  75 |           PX COORDINATOR                               |                       |       |       |            |          |       |       |        |      |            |                                                                                                                              

                |  76 |            PX SEND QC (RANDOM)                         | :TQ30003              |   772 | 66392 | 22082   (1)| 00:05:10 |       |       |  Q3,03 | P->S | QC (RAND)  |                                                                                                                              

                |  77 |             BUFFER SORT                                |                       |     1 |    86 |            |          |       |       |  Q3,03 | PCWP |            |                                                                                                                              

                |* 78 |              HASH JOIN                                 |                       |   772 | 66392 | 22082   (1)| 00:05:10 |       |       |  Q3,03 | PCWP |            |                                                                                                                              

                |  79 |               PX RECEIVE                               |                       |     7 |   147 |    11   (0)| 00:00:01 |       |       |  Q3,03 | PCWP |            |                                                                                                                              

                |  80 |                PX SEND BROADCAST                       | :TQ30002              |     7 |   147 |    11   (0)| 00:00:01 |       |       |  Q3,02 | P->P | BROADCAST  |                                                                                                                              

                |  81 |                 BUFFER SORT                            |                       |     1 |    86 |            |          |       |       |  Q3,02 | PCWP |            |                                                                                                                              

                |  82 |                  PX BLOCK ITERATOR                     |                       |     7 |   147 |    11   (0)| 00:00:01 |KEY(OR)|KEY(OR)|  Q3,02 | PCWC |            |                                                                                                                              

                |* 83 |                   TABLE ACCESS FULL                    | REBE01S1_DG6          |     7 |   147 |    11   (0)| 00:00:01 |KEY(OR)|KEY(OR)|  Q3,02 | PCWP |            |                                                                                                                              

                |* 84 |               HASH JOIN                                |                       |   772 | 50180 | 22071   (1)| 00:05:09 |       |       |  Q3,03 | PCWP |            |                                                                                                                              

                |  85 |                NESTED LOOPS                            |                       |       |       |            |          |       |       |  Q3,03 | PCWP |            |                                                                                                                              

                |  86 |                 NESTED LOOPS                           |                       | 23850 |  1001K|  2320   (1)| 00:00:33 |       |       |  Q3,03 | PCWP |            |                                                                                                                              

                |  87 |                  BUFFER SORT                           |                       |       |       |            |          |       |       |  Q3,03 | PCWC |            |                                                                                                                              

                |  88 |                   PX RECEIVE                           |                       |       |       |            |          |       |       |  Q3,03 | PCWP |            |                                                                                                                              

                |  89 |                    PX SEND ROUND-ROBIN                 | :TQ30000              |       |       |            |          |       |       |        | S->P | RND-ROBIN  |                                                                                                                              

                |  90 |                     BUFFER SORT                        |                       |     1 |    86 |            |          |       |       |        |      |            |                                                                                                                              

                |  91 |                      PARTITION RANGE SINGLE            |                       |   513 | 10773 |    39   (0)| 00:00:01 |   KEY |   KEY |        |      |            |                                                                                                                              

                |* 92 |                       TABLE ACCESS BY LOCAL INDEX ROWID| REBE01S1_DG4          |   513 | 10773 |    39   (0)| 00:00:01 |   KEY |   KEY |        |      |            |                                                                                                                              

                |  93 |                        BITMAP CONVERSION TO ROWIDS     |                       |       |       |            |          |       |       |        |      |            |                                                                                                                              

                |* 94 |                         BITMAP INDEX SINGLE VALUE      | MINDX_REBE01S1_DG4_0  |       |       |            |          |   KEY |   KEY |        |      |            |                                                                                                                              

                |  95 |                  PARTITION RANGE SINGLE                |                       |    15 |       |     1   (0)| 00:00:01 |   KEY |   KEY |  Q3,03 | PCWP |            |                                                                                                                              

                |* 96 |                   INDEX RANGE SCAN                     | MINDX_REBE01S1_JT_DG4 |    15 |       |     1   (0)| 00:00:01 |   KEY |   KEY |  Q3,03 | PCWP |            |                                                                                                                              

                |* 97 |                 TABLE ACCESS BY LOCAL INDEX ROWID      | REBE01S1_JT           |    46 |  1012 |     4   (0)| 00:00:01 |     1 |     1 |  Q3,03 | PCWP |            |                                                                                                                              

                |  98 |                BUFFER SORT                             |                       |       |       |            |          |       |       |  Q3,03 | PCWC |            |                                                                                                                              

                |  99 |                 PX RECEIVE                             |                       |   541K|    11M| 19748   (1)| 00:04:37 |       |       |  Q3,03 | PCWP |            |                                                                                                                              

                | 100 |                  PX SEND BROADCAST                     | :TQ30001              |   541K|    11M| 19748   (1)| 00:04:37 |       |       |        | S->P | BROADCAST  |                                                                                                                              

                | 101 |                   BUFFER SORT                          |                       |     1 |    86 |            |          |       |       |        |      |            |                                                                                                                              

                | 102 |                    TABLE ACCESS BY GLOBAL INDEX ROWID  | REBE01S1_DG0          |   541K|    11M| 19748   (1)| 00:04:37 | ROWID | ROWID |        |      |            |                                                                                                                              

                |*103 |                     INDEX RANGE SCAN                   | IDX$$_37E10001        |   396K|       |   903   (2)| 00:00:13 |       |       |        |      |            |                                                                                                                              

                -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                              

                                                                                                                                                                                                                                                                                                                           

                Predicate Information (identified by operation id):                                                                                                                                                                                                                                                        

                ---------------------------------------------------                                                                                                                                                                                                                                                        

                                                                                                                                                                                                                                                                                                                           

                  11 - filter("REBE01S1_DG0"."SESS_SESDATEUTC"=TO_DATE('08-1-2013','mm-dd-yyyy hh24:mi:ss'))                                                                                                                                                                                                               

                  13 - access("REBE01S1_DG0"."OPERATIONAL_DATE"<=TO_DATE('01-01-1499','mm-dd-yyyy hh24:mi:ss'))                                                                                                                                                                                                            

                       filter("REBE01S1_DG0"."OPERATIONAL_DATE"<=TO_DATE('01-01-1499','mm-dd-yyyy hh24:mi:ss'))                                                                                                                                                                                                            

                  15 - filter("REBE01S1_JT"."OPERATIONAL_DATE"=TO_DATE('08-1-2013','mm-dd-yyyy hh24:mi:ss') OR "REBE01S1_JT"."OPERATIONAL_DATE"<=TO_DATE('01-01-1499','mm-dd-yyyy                                                                                                                                          

                              hh24:mi:ss'))                                                                                                                                                                                                                                                                                

                  16 - access("REBE01S1_DG0"."DG0_ID"="REBE01S1_JT"."DG0_ID")                                                                                                                                                                                                                                              

                  18 - filter(("REBE01S1_DG6"."OPERATIONAL_DATE"=TO_DATE('08-1-2013','mm-dd-yyyy hh24:mi:ss') OR "REBE01S1_DG6"."OPERATIONAL_DATE"<=TO_DATE('01-01-1499','mm-dd-yyyy                                                                                                                                       

                              hh24:mi:ss')) AND ("REBE01S1_DG6"."SESS_SESSERVER"='sf-parb10b' OR "REBE01S1_DG6"."SESS_SESSERVER"='sf-parb10d' OR "REBE01S1_DG6"."SESS_SESSERVER"='sf-parb10e' OR                                                                                                                           

                              "REBE01S1_DG6"."SESS_SESSERVER"='sf-surf-b' OR "REBE01S1_DG6"."SESS_SESSERVER"='sf-web21c' OR "REBE01S1_DG6"."SESS_SESSERVER"='sf-web90a' OR                                                                                                                                                 

                              "REBE01S1_DG6"."SESS_SESSERVER"='sf-web90g'))                                                                                                                                                                                                                                                

                  19 - access("REBE01S1_DG6"."DG6_ID"="REBE01S1_JT"."DG6_ID")                                                                                                                                                                                                                                              

                  21 - access("REBE01S1_DG4"."DG4_ID"="REBE01S1_JT"."DG4_ID")                                                                                                                                                                                                                                              

                  22 - filter("REBE01S1_DG4"."LINE_TYPE"='Clicks Only' AND ("REBE01S1_DG4"."OPERATIONAL_DATE"=TO_DATE('08-1-2013','mm-dd-yyyy hh24:mi:ss') OR                                                                                                                                                              

                              "REBE01S1_DG4"."OPERATIONAL_DATE"<=TO_DATE('01-01-1499','mm-dd-yyyy hh24:mi:ss')))                                                                                                                                                                                                           

                  37 - access("REBE01S1_JT"."OPERATIONAL_DATE"<=TO_DATE('01-01-1499','mm-dd-yyyy hh24:mi:ss'))                                                                                                                                                                                                             

                       filter("REBE01S1_JT"."OPERATIONAL_DATE"<=TO_DATE('01-01-1499','mm-dd-yyyy hh24:mi:ss'))                                                                                                                                                                                                             

                  39 - filter(("REBE01S1_DG6"."OPERATIONAL_DATE"=TO_DATE('08-1-2013','mm-dd-yyyy hh24:mi:ss') OR "REBE01S1_DG6"."OPERATIONAL_DATE"<=TO_DATE('01-01-1499','mm-dd-yyyy                                                                                                                                       

                              hh24:mi:ss')) AND ("REBE01S1_DG6"."SESS_SESSERVER"='sf-parb10b' OR "REBE01S1_DG6"."SESS_SESSERVER"='sf-parb10d' OR "REBE01S1_DG6"."SESS_SESSERVER"='sf-parb10e' OR                                                                                                                           

                              "REBE01S1_DG6"."SESS_SESSERVER"='sf-surf-b' OR "REBE01S1_DG6"."SESS_SESSERVER"='sf-web21c' OR "REBE01S1_DG6"."SESS_SESSERVER"='sf-web90a' OR                                                                                                                                                 

                              "REBE01S1_DG6"."SESS_SESSERVER"='sf-web90g'))                                                                                                                                                                                                                                                

                  40 - access("REBE01S1_DG6"."DG6_ID"="REBE01S1_JT"."DG6_ID")                                                                                                                                                                                                                                              

                  42 - filter("REBE01S1_DG0"."SESS_SESDATEUTC"=TO_DATE('08-1-2013','mm-dd-yyyy hh24:mi:ss') AND "REBE01S1_DG0"."OPERATIONAL_DATE"=TO_DATE('08-1-2013','mm-dd-yyyy                                                                                                                                          

                              hh24:mi:ss') AND LNNVL("REBE01S1_DG0"."OPERATIONAL_DATE"<=TO_DATE('01-01-1499','mm-dd-yyyy hh24:mi:ss')))                                                                                                                                                                                    

                  43 - access("REBE01S1_DG0"."DG0_ID"="REBE01S1_JT"."DG0_ID")                                                                                                                                                                                                                                              

                  45 - access("REBE01S1_DG4"."DG4_ID"="REBE01S1_JT"."DG4_ID")                                                                                                                                                                                                                                              

                  46 - filter("REBE01S1_DG4"."LINE_TYPE"='Clicks Only' AND ("REBE01S1_DG4"."OPERATIONAL_DATE"=TO_DATE('08-1-2013','mm-dd-yyyy hh24:mi:ss') OR                                                                                                                                                              

                              "REBE01S1_DG4"."OPERATIONAL_DATE"<=TO_DATE('01-01-1499','mm-dd-yyyy hh24:mi:ss')))                                                                                                                                                                                                           

                  59 - filter("REBE01S1_DG4"."LINE_TYPE"='Clicks Only')                                                                                                                                                                                                                                                    

                  61 - access("REBE01S1_DG4"."OPERATIONAL_DATE"<=TO_DATE('01-01-1499','mm-dd-yyyy hh24:mi:ss'))                                                                                                                                                                                                            

                       filter("REBE01S1_DG4"."OPERATIONAL_DATE"<=TO_DATE('01-01-1499','mm-dd-yyyy hh24:mi:ss'))                                                                                                                                                                                                            

                  63 - filter("REBE01S1_JT"."OPERATIONAL_DATE"=TO_DATE('08-1-2013','mm-dd-yyyy hh24:mi:ss') AND                                                                                                                                                                                                            

                              LNNVL("REBE01S1_JT"."OPERATIONAL_DATE"<=TO_DATE('01-01-1499','mm-dd-yyyy hh24:mi:ss')))                                                                                                                                                                                                      

                  64 - access("REBE01S1_DG4"."DG4_ID"="REBE01S1_JT"."DG4_ID")                                                                                                                                                                                                                                              

                  66 - filter(("REBE01S1_DG6"."OPERATIONAL_DATE"=TO_DATE('08-1-2013','mm-dd-yyyy hh24:mi:ss') OR "REBE01S1_DG6"."OPERATIONAL_DATE"<=TO_DATE('01-01-1499','mm-dd-yyyy                                                                                                                                       

                              hh24:mi:ss')) AND ("REBE01S1_DG6"."SESS_SESSERVER"='sf-parb10b' OR "REBE01S1_DG6"."SESS_SESSERVER"='sf-parb10d' OR "REBE01S1_DG6"."SESS_SESSERVER"='sf-parb10e' OR                                                                                                                           

                              "REBE01S1_DG6"."SESS_SESSERVER"='sf-surf-b' OR "REBE01S1_DG6"."SESS_SESSERVER"='sf-web21c' OR "REBE01S1_DG6"."SESS_SESSERVER"='sf-web90a' OR                                                                                                                                                 

                              "REBE01S1_DG6"."SESS_SESSERVER"='sf-web90g'))                                                                                                                                                                                                                                                

                  67 - access("REBE01S1_DG6"."DG6_ID"="REBE01S1_JT"."DG6_ID")                                                                                                                                                                                                                                              

                  69 - access("REBE01S1_DG0"."DG0_ID"="REBE01S1_JT"."DG0_ID")                                                                                                                                                                                                                                              

                  70 - filter("REBE01S1_DG0"."SESS_SESDATEUTC"=TO_DATE('08-1-2013','mm-dd-yyyy hh24:mi:ss') AND "REBE01S1_DG0"."OPERATIONAL_DATE"=TO_DATE('08-1-2013','mm-dd-yyyy                                                                                                                                          

                              hh24:mi:ss') AND LNNVL("REBE01S1_DG0"."OPERATIONAL_DATE"<=TO_DATE('01-01-1499','mm-dd-yyyy hh24:mi:ss')))                                                                                                                                                                                    

                  78 - access("REBE01S1_DG6"."DG6_ID"="REBE01S1_JT"."DG6_ID")                                                                                                                                                                                                                                              

                  83 - filter(("REBE01S1_DG6"."OPERATIONAL_DATE"=TO_DATE('08-1-2013','mm-dd-yyyy hh24:mi:ss') OR "REBE01S1_DG6"."OPERATIONAL_DATE"<=TO_DATE('01-01-1499','mm-dd-yyyy                                                                                                                                       

                              hh24:mi:ss')) AND ("REBE01S1_DG6"."SESS_SESSERVER"='sf-parb10b' OR "REBE01S1_DG6"."SESS_SESSERVER"='sf-parb10d' OR "REBE01S1_DG6"."SESS_SESSERVER"='sf-parb10e' OR                                                                                                                           

                              "REBE01S1_DG6"."SESS_SESSERVER"='sf-surf-b' OR "REBE01S1_DG6"."SESS_SESSERVER"='sf-web21c' OR "REBE01S1_DG6"."SESS_SESSERVER"='sf-web90a' OR                                                                                                                                                 

                              "REBE01S1_DG6"."SESS_SESSERVER"='sf-web90g'))                                                                                                                                                                                                                                                

                  84 - access("REBE01S1_DG0"."DG0_ID"="REBE01S1_JT"."DG0_ID")                                                                                                                                                                                                                                              

                  92 - filter("REBE01S1_DG4"."OPERATIONAL_DATE"=TO_DATE('08-1-2013','mm-dd-yyyy hh24:mi:ss') AND                                                                                                                                                                                                           

                              LNNVL("REBE01S1_DG4"."OPERATIONAL_DATE"<=TO_DATE('01-01-1499','mm-dd-yyyy hh24:mi:ss')))                                                                                                                                                                                                     

                  94 - access("REBE01S1_DG4"."LINE_TYPE"='Clicks Only')                                                                                                                                                                                                                                                    

                  96 - access("REBE01S1_DG4"."DG4_ID"="REBE01S1_JT"."DG4_ID")                                                                                                                                                                                                                                              

                  97 - filter("REBE01S1_JT"."OPERATIONAL_DATE"=TO_DATE('08-1-2013','mm-dd-yyyy hh24:mi:ss') AND                                                                                                                                                                                                            

                              LNNVL("REBE01S1_JT"."OPERATIONAL_DATE"<=TO_DATE('01-01-1499','mm-dd-yyyy hh24:mi:ss')))                                                                                                                                                                                                      

                103 - access("REBE01S1_DG0"."SESS_SESDATEUTC"=TO_DATE('08-1-2013','mm-dd-yyyy hh24:mi:ss') AND "REBE01S1_DG0"."OPERATIONAL_DATE"=TO_DATE('08-1-2013','mm-dd-yyyy                                                                                                                                          

                              hh24:mi:ss'))                                                                                                                                                                                                                                                                                

                       filter(LNNVL("REBE01S1_DG0"."OPERATIONAL_DATE"<=TO_DATE('01-01-1499','mm-dd-yyyy hh24:mi:ss')))                                                                                                                                                                                                     

                                                                                                                                                                                                                                                                                                                           

                Note                                                                                                                                                                                                                                                                                                       

                -----                                                                                                                                                                                                                                                                                                      

                   - dynamic sampling used for this statement (level=5)                                                                                                                                                                                                                                                    

                 

                {code}

                • 5. Re: why oracle downgrade parallel execution
                  user10858330

                  how to use fix format. {code} something {code}

                  • 6. Re: why oracle downgrade parallel execution
                    Jonathan Lewis

                    user10858330 wrote:

                     

                    how to use fix format. {code} something {code}

                     

                    First: generate the output with TRIMSPOOL ON to get rid of the excess space at the ends of lines

                    Then paste the text into place

                    Then, just before sending, switch to "Use advanced editor" -- see top right corner of edit panel - this will include a drop-down list of fonts; highlight the plan, then select COURIER NEW

                     

                    Regards

                    Jonathan Lewis