1 2 Previous Next 23 Replies Latest reply on May 27, 2017 12:52 PM by Jonathan Lewis

    What is causing optimizer to ignore Parallel Hints ?

    York35

      DB version: 11.2.0.4

      OS : Oracle Linux 6.5

      3-Node RAC DB

       

       

      In my Siebel DB,  Parallel hints are ignored by the optimizer,most of the time. This has become a headache for the DBA team as application managers occasionally send ad-hoc queries like below and they always need output ASAP.

       

      I know that special initialization parameters in Siebel is causing the optimizer to ignore Parallel Hints.

       

       

      If somebody can tell me which parameter is causing this, I can try unsetting or changing this parameter at session level.

      Query which I tried to run in parallel and its execution plan are mentioned below.

      Further down, I am listing the optimizer, Parallelism related parameters + hidden parameter set in this DB.

       

       

      Please note that the DB was upgraded to 11.2.0.4 two months ago. I am yet to modify COMPATIBLE parameter from 11.2.0.3 to 11.2.0.4.

      S_ASSET table is 700 GB in size

       

       

       

       

      --- Query where the PARALLEL hint is ignored ie. only 1 session was spawned for this query
      
      
      select /*+ parallel(5) */ prod_id, COUNT(*) FROM SIEBEL.s_asset 
      where prod_id in 
      (
      '1-B2W2SL',
      '1-BBUS2L',
      '1-BDMN1S',
      '1-BDMN3E',
      '1-BDMN6I',
      '1-S89N5P',
      '1-BDMN84',
      '1-BDMNAS',
      '1-BDMN50',
      '1-BSIOAA',
      '1-GPY0AZ'
      ) 
      and status_cd = 'Active'
      group by prod_id;
      
      
      --- Execution plan
      
      SQL> set pages 200 lines 200
      SQL> select * from table(dbms_xplan.display_cursor('91cp23dh3z8pb'));
      
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      SQL_ID  91cp23dh3z8pb, child number 0
      -------------------------------------
      select /*+ parallel(5) */ prod_id, COUNT(*) FROM SIEBEL.s_asset where
      prod_id in ( '1-B2W2SL', '1-BBUS2L', '1-BDMN1S', '1-BDMN3E',
      '1-BDMN6I', '1-S89N5P', '1-BDMN84', '1-BDMNAS', '1-BDMN50', '1-BSIOAA',
      '1-GPY0AZ' ) and status_cd = 'Active' group by prod_id
      
      
      Plan hash value: 1621483309
      
      
      --------------------------------------------------------------------------------------------
      | Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT              |            |       |       |  7099 (100)|          |
      |   1 |  SORT GROUP BY NOSORT         |            |    11 |   198 |  7099   (1)| 00:01:26 |
      |   2 |   INLIST ITERATOR             |            |       |       |            |          |
      |*  3 |    TABLE ACCESS BY INDEX ROWID| S_ASSET    |   159K|  2805K|  7099   (1)| 00:01:26 |
      |*  4 |     INDEX RANGE SCAN          | S_ASSET_U2 |   748K|       |    86   (0)| 00:00:02 |
      --------------------------------------------------------------------------------------------
      
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
      
         3 - filter("STATUS_CD"='Active')
         4 - access(("PROD_ID"='1-B2W2SL' OR "PROD_ID"='1-BBUS2L' OR "PROD_ID"='1-BDMN1S'
                    OR "PROD_ID"='1-BDMN3E' OR "PROD_ID"='1-BDMN50' OR "PROD_ID"='1-BDMN6I' OR
                    "PROD_ID"='1-BDMN84' OR "PROD_ID"='1-BDMNAS' OR "PROD_ID"='1-BSIOAA' OR
                    "PROD_ID"='1-GPY0AZ' OR "PROD_ID"='1-S89N5P'))
      
      
      Note
      -----
         - Degree of Parallelism is 1 because of hint
      
      
      
      
      32 rows selected.
      
      
      
      
      ----------- Relevant initilization parameters --------------------
      
      
      SQL> show parameter optim
      
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      _optimizer_max_permutations          integer     100
      object_cache_optimal_size            integer     102400
      optimizer_capture_sql_plan_baselines boolean     FALSE
      optimizer_dynamic_sampling           integer     1
      optimizer_features_enable            string      11.2.0.3
      optimizer_index_caching              integer     0
      optimizer_index_cost_adj             integer     1
      optimizer_mode                       string      ALL_ROWS
      optimizer_secure_view_merging        boolean     TRUE
      optimizer_use_invisible_indexes      boolean     FALSE
      optimizer_use_pending_statistics     boolean     FALSE
      optimizer_use_sql_plan_baselines     boolean     TRUE
      plsql_optimize_level                 integer     2
      SQL>
      SQL> show parameter compatible
      
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      compatible                           string      11.2.0.3.0
      
      
      
      
      --- Hidden parameters
      
      
      NAME                                VALUE                DESCRIPTION
      ----------------------------------- -------------------- ----------------------------------------------------------------------------------------------------
      _gc_defer_time                      0                    how long to defer pings for hot buffers in milliseconds
      _gc_read_mostly_locking             FALSE                if TRUE, enable read-mostly locking
      _gc_bypass_readers                  FALSE                if TRUE, modifications bypass readers
      _partition_view_enabled             FALSE                enable/disable partitioned views
      _b_tree_bitmap_plans                FALSE                enable the use of bitmap plans for tables w. only B-tree indexes
      _always_semi_join                   OFF                  always use this method for semi-join when possible
      _no_or_expansion                    FALSE                OR expansion during optimization disabled
      _optimizer_max_permutations         100                  optimizer maximum join permutations per query block
      _like_with_bind_as_equality         TRUE                 treat LIKE predicate with bind as an equality predicate
      _ash_size                           67108864             To set the size of the in-memory Active Session History buffers
      
      
      10 rows selected.
      
      
      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     16384
      parallel_force_local                 boolean     FALSE
      parallel_instance_group              string
      parallel_io_cap_enabled              boolean     FALSE
      parallel_max_servers                 integer     3200
      parallel_min_percent                 integer     0
      
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      parallel_min_servers                 integer     0
      parallel_min_time_threshold          string      AUTO
      parallel_server                      boolean     TRUE
      parallel_server_instances            integer     4
      parallel_servers_target              integer     1280
      parallel_threads_per_cpu             integer     2
      recovery_parallelism                 integer     0
      
        • 1. Re: What is causing optimizer to ignore Parallel Hints ?
          Mustafa KALAYCI

          are you sure you are using correct sql_id? if so what would happen when you run this before run your sql:

           

          ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;

          ALTER SESSION FORCE PARALLEL DML PARALLEL 4;

          ALTER SESSION FORCE PARALLEL DDL PARALLEL 4;

          1 person found this helpful
          • 2. Re: What is causing optimizer to ignore Parallel Hints ?
            Saubhik

            What happens if you use FULL and

            1. select /*+ parallel(tbl,5) */ prod_id, COUNT(*) FROM SIEBEL.s_asset  tbl
            1 person found this helpful
            • 3. Re: What is causing optimizer to ignore Parallel Hints ?
              Andrew Sayer

              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).

               

              But be warned, parallel just means throw hardware at the problem, this could seriously impact other users of the database. And if you have multiple users trying to do everything in parallel then good luck!

              1 person found this helpful
              • 4. Re: What is causing optimizer to ignore Parallel Hints ?
                gmaccri

                It might be the optimizer_index_cost_adj = 1 which makes index access look like it's only 1% of the actual cost.  This, if I remember correctly, used to be a Siebel recommended setting, as Siebel depended on indexes for performance.  Try changing it to the default to see what happens.

                1 person found this helpful
                • 5. Re: What is causing optimizer to ignore Parallel Hints ?
                  Andrew Sayer

                  Actually, the index cost is probably wildly wrong due to the special Siebel recomendations

                   

                  1. optimizer_index_cost_adj             integer     1 

                  I would bet a pint if that was set to 100 then you'd have better luck.

                   

                  Oh and I recon proper system and table stats would be much better than these silly hacks.

                  1 person found this helpful
                  • 6. Re: What is causing optimizer to ignore Parallel Hints ?
                    York35

                    I have tried the following 3 recommendations. But none have worked !!

                     

                     

                    1. Setting the following at session level

                    ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;

                    ALTER SESSION FORCE PARALLEL DML PARALLEL 4;

                    ALTER SESSION FORCE PARALLEL DDL PARALLEL 4;

                     

                    and then running

                    select /*+ parallel */ prod_id, COUNT(*) FROM SIEBEL.s_asset .........

                     

                    2. Saubhik's recommendation

                    select /*+ parallel(tbl,5) */ prod_id, COUNT(*) FROM SIEBEL.s_asset  tbl ......

                     

                     

                    3. Setting optimizer_index_cost_adj=100 (default for 11.2) at session level and executing the query

                    • 7. Re: What is causing optimizer to ignore Parallel Hints ?
                      Andrew Sayer

                      York35 wrote:

                       

                      I have tried the following 3 recommendations. But none have worked !!

                       

                       

                      1. Setting the following at session level

                      ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;

                      ALTER SESSION FORCE PARALLEL DML PARALLEL 4;

                      ALTER SESSION FORCE PARALLEL DDL PARALLEL 4;

                       

                      and then running

                      select /*+ parallel */ prod_id, COUNT(*) FROM SIEBEL.s_asset .........

                       

                      2. Saubhik's recommendation

                      select /*+ parallel(tbl,5) */ prod_id, COUNT(*) FROM SIEBEL.s_asset tbl ......

                       

                       

                      3. Setting optimizer_index_cost_adj=100 (default for 11.2) at session level and executing the query

                      So what was the new cost of that index scan with the 100 optimizer_index_cost_adj?

                      What is the cost of the full hinted plan?

                      • 8. Re: What is causing optimizer to ignore Parallel Hints ?
                        York35

                        Cost hasn't changed after setting optimizer_index_cost_adj=100

                         

                         

                        Plan hash value: 1621483309

                         

                         

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

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

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

                        |   0 | SELECT STATEMENT              |            |       |       |  7099 (100)|          |

                        |   1 |  SORT GROUP BY NOSORT         |            |    11 |   198 |  7099   (1)| 00:01:26 |

                        |   2 |   INLIST ITERATOR             |            |       |       |            |          |

                        |*  3 |    TABLE ACCESS BY INDEX ROWID| S_ASSET    |   159K|  2805K|  7099   (1)| 00:01:26 |

                        |*  4 |     INDEX RANGE SCAN          | S_ASSET_U2 |   748K|       |    86   (0)| 00:00:02 |

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

                         

                         

                        Will check the cost for FULL hinted plan soon.

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

                          A serial tablescan isn't necessarily a bad thing - Siebel is one of those applications that has lots of tables with more than 255 columns, and if the 2nd (or later) column piece isn't in the same block as the first row piece a parallel tablescan will have to do a "db file sequential read" for every row where it has to find the continued row.  (A recent client had to do 8 million single block reads to do a parallel tablescan of a table of only 400K blocks because of this anomaly.)  See: http://jonathanlewis.wordpress.com/2015/05/18/migrated-rows/

                           

                          Ignoring this problem - as others have said there's often a costing issue that behind Oracle's choice. If you're certain that you've hinted parallelism correctly, and have parallel query slaves, and parallel query enabled and you're still not getting parallel query then you may be facing one of the restrictions on parallel query:  the only one that springs to mind at the moment is that your table may include a LOB column, but if you check the manuals (particularly the data warehouse one) you'll find there are three or four reasons.

                           

                          It might help if you included the 'outline' option when generating the execution plans  - this might show some parameter setting or fix_control that you're overlooking.

                           

                          Regards

                          Jonathan Lewis

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

                            That looks like the parameter change hasn't had an effect - possibly because you re-used the same cursor and your session didn't re-optimize the query.

                            With a rowid estimate of 748K, and a typical Siebel index the "normal" cost of the table access would be closer to 748K (every row in a different block), the fact that the cost is closer to 7K suggests that the optimizer is still using a cursor where the cost is "normal" cost / 100 ***

                             

                            Try modifying the query slightly so it looks like a new text before running it again.

                             

                            Regards

                            Jonathan Lewis

                             

                             

                            *** UPDATE: this is roughly the effect of "optimizer_index_cost_adj = 1"

                            • 11. Re: What is causing optimizer to ignore Parallel Hints ?
                              Andy Klock

                              I've been here before. Not sure if this applies to you, but thought I'd mention it.

                               

                              Bug 19849585 : PARALLEL HINT IGNORED WHEN USING GROUP BY

                              • 12. Re: What is causing optimizer to ignore Parallel Hints ?
                                Andrew Sayer

                                Andy Klock wrote:

                                 

                                I've been here before. Not sure if this applies to you, but thought I'd mention it.

                                 

                                Bug 19849585 : PARALLEL HINT IGNORED WHEN USING GROUP BY

                                92 - Closed, Not a Bug

                                 

                                A quick look suggests would be that one plan allowed a join to be eliminated and the other didn't. The one that didn't thought it was better to do a nested loop from index range scans.

                                • 13. Re: What is causing optimizer to ignore Parallel Hints ?
                                  Andy Klock

                                  Thanks Andrew. Updating my notes accordingly. I'd run across that bug reference while troubleshooting a PX 12c issue a couple years ago. Sorry for the confusion.

                                  • 14. Re: What is causing optimizer to ignore Parallel Hints ?
                                    Andrew Sayer

                                    Jonathan Lewis wrote:

                                     

                                    That looks like the parameter change hasn't had an effect - possibly because you re-used the same cursor and your session didn't re-optimize the query.

                                    With a rowid estimate of 748K, and a typical Siebel index the "normal" cost of the table access would be closer to 748K (every row in a different block), the fact that the cost is closer to 7K suggests that the optimizer is still using a cursor where the cost is "normal" cost / 100 ***

                                     

                                    Try modifying the query slightly so it looks like a new text before running it again.

                                     

                                    Regards

                                    Jonathan Lewis

                                     

                                     

                                    *** UPDATE: this is roughly the effect of "optimizer_index_cost_adj = 1"

                                    If the parameter was modified at the session level (which would be the safest way to test such a thing IMO, well second to opt_param hint) then a new child cursor would have been created with the optimizer_mismatch reason in v$sql_shared_cursor.

                                     

                                    The lack of difference suggests it was changed at the instance level, and either that doesn't get used by existing sessions or the existing session had its own value explicitly set with alter session already (and a guess would suggest logon trigger).

                                     

                                    Of course I'm assuming we're using sqlplus without statement caching, which I think would have the effect of reusing the old child cursor because it's statement cached... but I'm guessing.

                                    1 2 Previous Next