1 2 3 Previous Next 38 Replies Latest reply on Jun 26, 2014 6:52 AM by Ivica Arsov

    Gurus: PARALLEL hint is NOT working in 11g. Any suggestions

    user527460

      SQL stmt:

       

      SELECT  /*+ PARALLEL(r) */

                     r.c1 , r.c2

      FROM    table1 r

       

      -->  Table1 has 44 million records.

       

      --> Explain plan is showing FULL TABLE SCAN

        • 1. Re: Gurus: PARALLEL hint is NOT working in 11g. Any suggestions
          SomeoneElse

          > --> Explain plan is showing FULL TABLE SCAN

           

          Well, sure.  You have no WHERE clause.

           

          It might help just a bit if you were to post the actual explain plan.

          • 2. Re: Gurus: PARALLEL hint is NOT working in 11g. Any suggestions
            user527460
            SELECT STATEMENT, GOAL = ALL_ROWSCost=300698Cardinality=44703140Bytes=447031400
            TABLE ACCESS FULLObject owner=axeObject name=Table1Cost=300698Cardinality=44703140Bytes=447031400
            • 3. Re: Gurus: PARALLEL hint is NOT working in 11g. Any suggestions
              sb92075

              it works OK for me

               

               

               

                1* select /*+ parallel(LOG_TRAIL) */ name from log_trail

              SQL> /

               

               

              Execution Plan

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

              Plan hash value: 2553508127

               

               

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

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

               

               

              | Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time

              |    TQ  |IN-OUT| PQ Distrib |

               

               

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

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

               

               

              |   0 | SELECT STATEMENT     |           |   108K|   528K|    57   (0)| 00:00:01

              |        |      |            |

               

               

              |   1 |  PX COORDINATOR      |           |       |       |            |

              |        |      |            |

               

               

              |   2 |   PX SEND QC (RANDOM)| :TQ10000  |   108K|   528K|    57   (0)| 00:00:01

              |  Q1,00 | P->S | QC (RAND)  |

               

               

              |   3 |    PX BLOCK ITERATOR |           |   108K|   528K|    57   (0)| 00:00:01

              |  Q1,00 | PCWC |            |

               

               

              |   4 |     TABLE ACCESS FULL| LOG_TRAIL |   108K|   528K|    57   (0)| 00:00:01

              |  Q1,00 | PCWP |            |

               

               

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

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

               

               

               

               

              SQL> set autotrace off

              SQL> /

               

               

              BANNER

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

              Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

              PL/SQL Release 11.2.0.2.0 - Production

              CORE    11.2.0.2.0      Production

              TNS for Linux: Version 11.2.0.2.0 - Production

              NLSRTL Version 11.2.0.2.0 - Production

               

               

              SQL>

              • 4. Re: Gurus: PARALLEL hint is NOT working in 11g. Any suggestions
                user527460

                Any recommendations?: SB92075

                • 5. Re: Gurus: PARALLEL hint is NOT working in 11g. Any suggestions
                  Mark D Powell

                  user527460, What edition of Oracle are you running?  What full version of Oracle are you using?  What do you have the database parameter settings related to PQO set to (that is, did you enable/disable the feature)?

                  - -

                  The database parameters are documented in the Oracle version# Reference manual.

                  - -

                  HTH -- Mark D Powell --

                  • 6. Re: Gurus: PARALLEL hint is NOT working in 11g. Any suggestions
                    sb92075

                    what happens when you include actual table_name, not alias, in the HINT?

                    • 7. Re: Gurus: PARALLEL hint is NOT working in 11g. Any suggestions
                      user527460

                      Hi Mark,

                      I am Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 .

                       

                      Let me know the complete name for parameter related to PQO so that I can verify. I didn't change any parameter settings so far.

                      • 8. Re: Gurus: PARALLEL hint is NOT working in 11g. Any suggestions
                        user527460

                        Hi SB96075:,

                        After your response I removed alias and used actual table_name in the query and the hint but found no difference.

                        • 9. Re: Gurus: PARALLEL hint is NOT working in 11g. Any suggestions
                          BartCern

                          What does "show parameter parallel" show?

                          Does the table have degree specified? (dba_tables.degree)

                          • 10. Re: Gurus: PARALLEL hint is NOT working in 11g. Any suggestions
                            user527460

                            Hi BartCern:    dba_tables.degree is 1

                             

                             

                             

                            SQL> show parameter parallel

                             

                             

                            NAME                                 TYPE        VALUE

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

                            fast_start_parallel_rollback         string      LOW

                            parallel_adaptive_multi_user         boolean     TRUE

                            parallel_automatic_tuning            boolean     FALSE

                            parallel_degree_limit                string      CPU

                            parallel_degree_policy               string      MANUAL

                            parallel_execution_message_size      integer     4096

                            parallel_force_local                 boolean     FALSE

                            parallel_instance_group              string      op015pdb01

                            parallel_io_cap_enabled              boolean     FALSE

                            parallel_max_servers                 integer     192

                            parallel_min_percent                 integer     0

                            parallel_min_servers                 integer     8

                            parallel_min_time_threshold          string      AUTO

                            parallel_server                      boolean     TRUE

                            parallel_server_instances            integer     2

                            parallel_servers_target              integer     192

                            parallel_threads_per_cpu             integer     2

                            recovery_parallelism                 integer     64

                            • 11. Re: Gurus: PARALLEL hint is NOT working in 11g. Any suggestions
                              Mark D Powell

                              user527460, the Oracle 11.2 Reference Manual shows 14 parameters that may be of interest:

                              http://docs.oracle.com/cd/E11882_01/server.112/e40402/toc.htm\

                              For when to use PQO and why you may not want to use PQO see the Database Data Warehousing Guide

                              http://docs.oracle.com/cd/E11882_01/server.112/e25554/px.htm#BCEGDJJF

                              - -

                              HTH -- Mark D Powell --

                              • 12. Re: Gurus: PARALLEL hint is NOT working in 11g. Any suggestions
                                SomeoneElse

                                What tool are you using to display the explain plan?

                                 

                                What does SQL Plus show?

                                 

                                SQL> create table table1 (c1, c2) as select owner, object_name from dba_objects;

                                 

                                Table created.

                                 

                                SQL> exec dbms_stats.gather_table_stats(ownname => USER, tabname => 'TABLE1', degree => dbms_stats.auto_degree);

                                 

                                PL/SQL procedure successfully completed.

                                 

                                SQL> explain plan for

                                  2  SELECT  /*+ PARALLEL(r) */

                                  3          r.c1 , r.c2

                                  4  FROM    table1 r

                                  5  ;

                                 

                                Explained.

                                 

                                SQL> select * from table(dbms_xplan.display());

                                 

                                PLAN_TABLE_OUTPUT

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

                                Plan hash value: 1574891232

                                 

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

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

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

                                |   0 | SELECT STATEMENT     |          | 72522 |  2124K|     7   (0)| 00:00:01 |        |      |            |

                                |   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |

                                |   2 |   PX SEND QC (RANDOM)| :TQ10000 | 72522 |  2124K|     7   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |

                                |   3 |    PX BLOCK ITERATOR |          | 72522 |  2124K|     7   (0)| 00:00:01 |  Q1,00 | PCWC |            |

                                |   4 |     TABLE ACCESS FULL| TABLE1   | 72522 |  2124K|     7   (0)| 00:00:01 |  Q1,00 | PCWP |            |

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

                                 

                                 

                                • 13. Re: Gurus: PARALLEL hint is NOT working in 11g. Any suggestions
                                  ddf_dba

                                  I, too, see no issue with this on 11.2.0.3:

                                   

                                  SQL> SELECT  /*+ PARALLEL(r) */
                                    2                r.c1 , r.c2
                                    3  FROM    table1 r;
                                  
                                          C1 C2
                                  ---------- -------------------------
                                      825434 NorperHoop825434
                                      825435 NorperHoop825435
                                      672832 NorperHoop672832
                                  ...
                                      252016 NorperHoop252016
                                  
                                  1000000 rows selected.
                                  
                                  
                                  Execution Plan
                                  ----------------------------------------------------------
                                  Plan hash value: 1574891232
                                  
                                  --------------------------------------------------------------------------------------------------------------
                                  | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
                                  --------------------------------------------------------------------------------------------------------------
                                  |   0 | SELECT STATEMENT     |          |  1007K|    25M|   223   (0)| 00:00:01 |        |      |            |
                                  |   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
                                  |   2 |   PX SEND QC (RANDOM)| :TQ10000 |  1007K|    25M|   223   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
                                  |   3 |    PX BLOCK ITERATOR |          |  1007K|    25M|   223   (0)| 00:00:01 |  Q1,00 | PCWC |            |
                                  |   4 |     TABLE ACCESS FULL| TABLE1   |  1007K|    25M|   223   (0)| 00:00:01 |  Q1,00 | PCWP |            |
                                  --------------------------------------------------------------------------------------------------------------
                                  
                                  Note
                                  -----
                                     - dynamic sampling used for this statement (level=2)
                                     - SQL plan baseline "SQL_PLAN_ccxxf6q4pjan381f6a895" used for this statement
                                  
                                  
                                  Statistics
                                  ----------------------------------------------------------
                                           78  recursive calls
                                          197  db block gets
                                        23673  consistent gets
                                         5917  physical reads
                                         8344  redo size
                                     35269260  bytes sent via SQL*Net to client
                                       733846  bytes received via SQL*Net from client
                                        66668  SQL*Net roundtrips to/from client
                                            0  sorts (memory)
                                            0  sorts (disk)
                                      1000000  rows processed
                                  
                                  SQL>

                                   

                                  David Fitzjarrell</>

                                  • 14. Re: Gurus: PARALLEL hint is NOT working in 11g. Any suggestions
                                    user527460

                                    Hi SomeoneElse,

                                     

                                    I am using PL/SQL developer ver7

                                    1 2 3 Previous Next