1 2 Previous Next 23 Replies Latest reply on May 27, 2017 12:52 PM by Jonathan Lewis Go to original post
      • 15. Re: What is causing optimizer to ignore Parallel Hints ?
        Jonathan Lewis

        Andrew,

         

        I don't think that's the answer on that one. A plan that does a table scan of emp with a nested loop to dept can run parallel - the tablescan can run parallel while the nested loop is still a "for each row" indexed access.

         

        It's a long time since I wrote it but there's a note on my blog (9.2.0.6 era, I think) that is a reminder of this, and even a comment showing exactly the paralle plan: http://jonathanlewis.wordpress.com/2006/12/28/parallel-execution/#comment-35331

         

        I think the failure to go parallel in that MoS note might be a limitation of the group by combined with the collect operator.

        I tried modelling it, and when I'd got the relevant "ignore the hint" behaviour in 11.2.0.4 I switched it to 12.2.0.1 which did the parallel tablescan on emp with nested loop.

         

        Regards

        Jonathan Lewis

        • 16. Re: What is causing optimizer to ignore Parallel Hints ?
          AndrewSayer

          Jonathan Lewis wrote:

           

          Andrew,

           

          I don't think that's the answer on that one. A plan that does a table scan of emp with a nested loop to dept can run parallel - the tablescan can run parallel while the nested loop is still a "for each row" indexed access.

           

          It's a long time since I wrote it but there's a note on my blog (9.2.0.6 era, I think) that is a reminder of this, and even a comment showing exactly the paralle plan: http://jonathanlewis.wordpress.com/2006/12/28/parallel-execution/#comment-35331

           

          I think the failure to go parallel in that MoS note might be a limitation of the group by combined with the collect operator.

          I tried modelling it, and when I'd got the relevant "ignore the hint" behaviour in 11.2.0.4 I switched it to 12.2.0.1 which did the parallel tablescan on emp with nested loop.

           

          Regards

          Jonathan Lewis

          You're right Jonathan, actually the parallel nested loop is something I've (ab)used before with a query like

          select /*+parallel*/t.* from (select distinct index_key from table_name ctrl where ctrl.index_key between :y and :Z) ctrl

          join table_name t on (ctrl.index_key = t.index_key)

           

          I had started with thinking it must be to do with the collect but then got too distracted by that join elimination. Interestingly, the emp table was costed the same for a parallel FTS as a serial FTS, and the nested loop access was costed zero per row which is sort of suspicious... but probably not relevant, just a symptom of small tables.

           

          Cheers for the correction

          • 17. Re: What is causing optimizer to ignore Parallel Hints ?
            Randolf Geist

            Andrew Sayer wrote:

             

            There are some operations that just don't parallelize, if the CBO believes that a serial index range scan then table access is faster than a parallelized full table scan then it's going to do that. In 12.1 and 12.2 there are less restrictions with index range scans (12.1 can do the table access in parallel, 12.2 can do the range scan in parallel). If you want to use parallel in 11.2 by force then stick in some further hints like FULL(tbl).

             

            Hi Andrew,

             

            could you elaborate a bit more on the change in 12.2 that you mention regarding the parallel index range scans? Do you have an example at hand? So far I was only aware of the parallel table access by rowid following a serialised index range scan (that looks like parallel due to new the PX SELECTOR operator) introduced in 12.1 as demonstrated by Jonathan here:

             

            https://jonathanlewis.wordpress.com/2016/06/10/uniquely-parallel/

             

            Randolf

            • 18. Re: What is causing optimizer to ignore Parallel Hints ?
              AndrewSayer

              Randolf Geist wrote:

               

              Andrew Sayer wrote:

               

              There are some operations that just don't parallelize, if the CBO believes that a serial index range scan then table access is faster than a parallelized full table scan then it's going to do that. In 12.1 and 12.2 there are less restrictions with index range scans (12.1 can do the table access in parallel, 12.2 can do the range scan in parallel). If you want to use parallel in 11.2 by force then stick in some further hints like FULL(tbl).

               

              Hi Andrew,

               

              could you elaborate a bit more on the change in 12.2 that you mention regarding the parallel index range scans? Do you have an example at hand? So far I was only aware of the parallel table access by rowid following a serialised index range scan (that looks like parallel due to new the PX SELECTOR operator) introduced in 12.1 as demonstrated by Jonathan here:

               

              https://jonathanlewis.wordpress.com/2016/06/10/uniquely-parallel/

               

              Randolf

              Hi Randolf, apologies, I think I slipped up here.

               

              I think I had misinterpreted Timur Akhmadeev's post (https://twitter.com/tmmdv/status/865936285483106304 ) as new in 12.2 and when I noticed my own 12.1 instance doing the parallel table access from an index I made the leap that there would be a difference and that was from the index range scan. Having a second look, the 12.2 plan is still a serial index range scan as noted by the SCWP in IN-OUT.

               

              Thanks for making me take a second look.

              • 19. Re: What is causing optimizer to ignore Parallel Hints ?
                Randolf Geist

                Andrew Sayer wrote:

                 

                Hi Randolf, apologies, I think I slipped up here.

                 

                I think I had misinterpreted Timur Akhmadeev's post (https://twitter.com/tmmdv/status/865936285483106304 ) as new in 12.2 and when I noticed my own 12.1 instance doing the parallel table access from an index I made the leap that there would be a difference and that was from the index range scan. Having a second look, the 12.2 plan is still a serial index range scan as noted by the SCWP in IN-OUT.

                 

                Thanks for making me take a second look.

                Hi Andrew,

                 

                thanks for the clarification!

                 

                Randolf

                • 20. Re: What is causing optimizer to ignore Parallel Hints ?
                  DeepC

                  check the value of profile sessions_per_user in the profile against the user.If sessions_per_user is set to 1 in the profile of the user then whatever you do, none of the SQL will be executed in parallel

                  • 21. Re: What is causing optimizer to ignore Parallel Hints ?
                    AndrewSayer

                    DeepC wrote:

                     

                    check the value of profile sessions_per_user in the profile against the user.If sessions_per_user is set to 1 in the profile of the user then whatever you do, none of the SQL will be executed in parallel

                    Would that just prevent a parallel plan from being executed in parallel? (like when there is not a free parallel slave and no statement queueing)

                    Here we have a plan that is serial

                    • 22. Re: What is causing optimizer to ignore Parallel Hints ?
                      Vsevolod Afanassiev

                      The optimizer doesn't ignore hints. What really happens is that the optimizer is unable to use a particular hint because it never arrives to the fork in the decision tree when this hint could be used.

                      Specifically for the PARALLEL hint:

                      - First the optimizer decides whether to use full table scan or index-based access. The presence of PARALLEL hint doesn't influence this decision.

                      - If it decides to use index-based access then it is unable to use PARALLEL hint

                      - If it decides to use full table scan then PARALLEL hint is used

                       

                      See example below (tested on 11.2.0.4):

                      - TEST1 is a table with 28 million rows

                      - ID is primary key column (unique and NOT NULL), datatype = NUMBER(10)

                      - DESCRIPTION is VARCHAR2(255) column, it isn't part of any index

                       

                      If I specify condition on ID column then the only way to get it to use PARALLEL hint is to add FULL hint. In this case FULL hint applies to the first fork in the decision tree (FTS vs index-based) and PARALLEL applies to the second fork (sequential vs parallel).

                      If I specify condition on DESCRIPTION column then PARALLEL hint works without FULL

                       

                      SQL> select * from TEST1 where ID = 2939575;

                       

                      Execution Plan

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

                      Plan hash value: 2381478903

                       

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

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

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

                      |  0 | SELECT STATEMENT            |          |    1 |  313 |    1  (0)| 00:00:01 |

                      |  1 |  TABLE ACCESS BY INDEX ROWID| TEST1    |    1 |  313 |    1  (0)| 00:00:01 |

                      |*  2 |  INDEX UNIQUE SCAN        | PK_TEST1 |    1 |      |    1  (0)| 00:00:01 |

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

                       

                      Predicate Information (identified by operation id):

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

                       

                        2 - access("ID"=2939575)

                       

                      SQL> select /*+ parallel(2) */ * from TEST1 where ID = 2939575;

                       

                      Execution Plan

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

                      Plan hash value: 2381478903

                       

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

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

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

                      |  0 | SELECT STATEMENT            |          |    1 |  313 |    1  (0)| 00:00:01 |

                      |  1 |  TABLE ACCESS BY INDEX ROWID| TEST1    |    1 |  313 |    1  (0)| 00:00:01 |

                      |*  2 |  INDEX UNIQUE SCAN        | PK_TEST1 |    1 |      |    1  (0)| 00:00:01 |

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

                       

                      Predicate Information (identified by operation id):

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

                       

                        2 - access("ID"=2939575)

                       

                      Note

                      -----

                        - Degree of Parallelism is 1 because of hint

                       

                      SQL> select /*+ parallel(t,2) */ * from TEST1 t where ID = 2939575;

                       

                      Execution Plan

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

                      Plan hash value: 2381478903

                       

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

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

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

                      |  0 | SELECT STATEMENT            |          |    1 |  313 |    1  (0)| 00:00:01 |

                      |  1 |  TABLE ACCESS BY INDEX ROWID| TEST1    |    1 |  313 |    1  (0)| 00:00:01 |

                      |*  2 |  INDEX UNIQUE SCAN        | PK_TEST1 |    1 |      |    1  (0)| 00:00:01 |

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

                       

                      Predicate Information (identified by operation id):

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

                       

                        2 - access("ID"=2939575)

                       

                      SQL> select /*+ full(t) parallel(t,2) */ * from TEST1 t where ID = 2939575;

                       

                      Execution Plan

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

                      Plan hash value: 1251158984

                       

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

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

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

                      |  0 | SELECT STATEMENT    |          |    1 |  313 |  123K  (1)| 00:28:56 |        |      |            |

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

                      |  2 |  PX SEND QC (RANDOM)| :TQ10000 |    1 |  313 |  123K  (1)| 00:28:56 |  Q1,00 | P->S | QC (RAND)  |

                      |  3 |    PX BLOCK ITERATOR |          |    1 |  313 |  123K  (1)| 00:28:56 |  Q1,00 | PCWC |            |

                      |*  4 |    TABLE ACCESS FULL| TEST1    |    1 |  313 |  123K  (1)| 00:28:56 |  Q1,00 | PCWP |            |

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

                       

                      Predicate Information (identified by operation id):

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

                       

                        4 - filter("ID"=2939575)

                       

                      SQL> select * from TEST1 where DESCRIPTION = 'ABC';

                       

                      Execution Plan

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

                      Plan hash value: 804664763

                       

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

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

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

                      |  0 | SELECT STATEMENT  |      |    3 |  939 |  223K  (1)| 00:52:10 |

                      |*  1 |  TABLE ACCESS FULL| TEST1 |    3 |  939 |  223K  (1)| 00:52:10 |

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

                       

                      Predicate Information (identified by operation id):

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

                       

                        1 - filter("DESCRIPTION"=U'ABC')

                       

                      SQL> select /*+ parallel(2) */ * from TEST1 where DESCRIPTION = 'ABC';

                       

                      Execution Plan

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

                      Plan hash value: 1251158984

                       

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

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

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

                      |  0 | SELECT STATEMENT    |          |    3 |  939 |  123K  (1)| 00:08:16 |        |      |            |

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

                      |  2 |  PX SEND QC (RANDOM)| :TQ10000 |    3 |  939 |  123K  (1)| 00:08:16 |  Q1,00 | P->S | QC (RAND)  |

                      |  3 |    PX BLOCK ITERATOR |          |    3 |  939 |  123K  (1)| 00:08:16 |  Q1,00 | PCWC |            |

                      |*  4 |    TABLE ACCESS FULL| TEST1    |    3 |  939 |  123K  (1)| 00:08:16 |  Q1,00 | PCWP |            |

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

                       

                      Predicate Information (identified by operation id):

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

                       

                        4 - filter("DESCRIPTION"=U'ABC')

                       

                      Note

                      -----

                        - Degree of Parallelism is 2 because of hint

                       

                      SQL> select /*+ parallel(t,2) */ * from TEST1 t where DESCRIPTION = 'ABC';

                       

                      Execution Plan

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

                      Plan hash value: 1251158984

                       

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

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

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

                      |  0 | SELECT STATEMENT    |          |    3 |  939 |  123K  (1)| 00:28:56 |        |      |            |

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

                      |  2 |  PX SEND QC (RANDOM)| :TQ10000 |    3 |  939 |  123K  (1)| 00:28:56 |  Q1,00 | P->S | QC (RAND)  |

                      |  3 |    PX BLOCK ITERATOR |          |    3 |  939 |  123K  (1)| 00:28:56 |  Q1,00 | PCWC |            |

                      |*  4 |    TABLE ACCESS FULL| TEST1    |    3 |  939 |  123K  (1)| 00:28:56 |  Q1,00 | PCWP |            |

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

                       

                      Predicate Information (identified by operation id):

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

                       

                        4 - filter("DESCRIPTION"=U'ABC')

                       

                       

                      SQL>

                      • 23. Re: What is causing optimizer to ignore Parallel Hints ?
                        Jonathan Lewis

                        Specifically for the PARALLEL hint:

                        - First the optimizer decides whether to use full table scan or index-based access. The presence of PARALLEL hint doesn't influence this decision.

                        - If it decides to use index-based access then it is unable to use PARALLEL hint

                        - If it decides to use full table scan then PARALLEL hint is used

                         

                        That's not really the right way to think about it - the potential for parallelism is used while comparing the tablescan with the indexed access path:

                         

                        create table t1 as select * from all_objects;

                        create index t1_indx on t1(object_id);

                         

                        exec dbms_stats.gather_table_stats(null,'t1',method_opt=>'for all columns size 1')

                         

                        alter session set tracefile_identifier = 'JPL';

                        alter session set events '10053 trace name context forever';

                        select /*+ parallel(2) */ * from t1 where object_id between 1010  and 1020;

                        alter session set events '10053 trace name context forever';

                         

                         

                        Then, from the trace file:

                         

                        SINGLE TABLE ACCESS PATH

                          Single Table Cardinality Estimation for T1[T1]

                         

                          Column (#4): OBJECT_ID(

                            AvgLen: 5 NDV: 63310 Nulls: 0 Density: 0.000016 Min: 16 Max: 256084

                          Table: T1  Alias: T1

                            Card: Original: 63310.000000  Rounded: 4  Computed: 4.47  Non Adjusted: 4.47

                          Access Path: TableScan

                            Cost:  254.42  Resp: 141.35  Degree: 0

                              Cost_io: 253.00  Cost_cpu: 16473941

                              Resp_io: 140.56  Resp_cpu: 9152190

                          Access Path: index (RangeScan)

                            Index: T1_INDX

                            resc_io: 3.00  resc_cpu: 24514

                            ix_sel: 0.000071  ix_sel_with_filters: 0.000071

                            Cost: 3.00  Resp: 3.00  Degree: 1

                          Best:: AccessPath: IndexRange

                          Index: T1_INDX

                                Cost: 3.00  Degree: 1  Resp: 3.00  Card: 4.47  Bytes: 0

                         

                        Note how the tablescan has two costs, the "cost" and the "resp" - which represent the serial and parallel response times respectively - and the optimizer will compare the resp figures to choose the access path.

                        So the degree (which may have to be derived by combining the hint with other factors) is part of the decision, not a calculation made after the event.

                         

                        Regards

                        Jonathan Lewis

                        1 2 Previous Next