This discussion is archived
6 Replies Latest reply: Sep 10, 2013 2:00 AM by Jonathan Lewis RSS

why oracle downgrade parallel execution

user10858330 Newbie
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    anyone can help?

  • 2. Re: why oracle downgrade parallel execution
    Martin Preiss Expert
    Currently Being Moderated

    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 Oracle ACE Director
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    {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 Newbie
    Currently Being Moderated

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

  • 6. Re: why oracle downgrade parallel execution
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    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

Legend

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