Forum Stats

  • 3,759,877 Users
  • 2,251,609 Discussions
  • 7,870,855 Comments

Discussions

What is causing optimizer to ignore Parallel Hints ?

York35
York35 Member Posts: 340 Bronze Badge
edited May 27, 2017 8:52AM in General Database Discussions

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 queryselect /*+ 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 planSQL> set pages 200 lines 200SQL> 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 whereprod_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_idPlan 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 hint32 rows selected.----------- Relevant initilization parameters --------------------SQL> show parameter optimNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------_optimizer_max_permutations          integer     100object_cache_optimal_size            integer     102400optimizer_capture_sql_plan_baselines boolean     FALSEoptimizer_dynamic_sampling           integer     1optimizer_features_enable            string      11.2.0.3optimizer_index_caching              integer     0optimizer_index_cost_adj             integer     1optimizer_mode                       string      ALL_ROWSoptimizer_secure_view_merging        boolean     TRUEoptimizer_use_invisible_indexes      boolean     FALSEoptimizer_use_pending_statistics     boolean     FALSEoptimizer_use_sql_plan_baselines     boolean     TRUEplsql_optimize_level                 integer     2SQL>SQL> show parameter compatibleNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------compatible                           string      11.2.0.3.0--- Hidden parametersNAME                                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 buffers10 rows selected.SQL> show parameter parallelNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------fast_start_parallel_rollback         string      LOWparallel_adaptive_multi_user         boolean     TRUEparallel_automatic_tuning            boolean     FALSEparallel_degree_limit                string      CPUparallel_degree_policy               string      MANUALparallel_execution_message_size      integer     16384parallel_force_local                 boolean     FALSEparallel_instance_group              stringparallel_io_cap_enabled              boolean     FALSEparallel_max_servers                 integer     3200parallel_min_percent                 integer     0NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------parallel_min_servers                 integer     0parallel_min_time_threshold          string      AUTOparallel_server                      boolean     TRUEparallel_server_instances            integer     4parallel_servers_target              integer     1280parallel_threads_per_cpu             integer     2recovery_parallelism                 integer     0
John ThortonYork35Jonathan LewisAndrewSayerAndy KlockMustafa_KALAYCI
«13

Answers

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,366 Bronze Crown
    edited May 25, 2017 9:40AM

    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;

    York35
  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    edited May 25, 2017 9:46AM

    What happens if you use FULL and

    1. select /*+ parallel(tbl,5) */ prod_id, COUNT(*) FROM SIEBEL.s_asset  tbl
    York35
  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited May 25, 2017 9:54AM

    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!

    John ThortonYork35York35Jonathan Lewis
  • tarfu_dba
    tarfu_dba Member Posts: 134
    edited May 25, 2017 9:58AM

    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.

    York35Jonathan Lewis
  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited May 25, 2017 9:59AM

    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.

    York35
  • York35
    York35 Member Posts: 340 Bronze Badge
    edited May 25, 2017 10:20AM

    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

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited May 25, 2017 10:32AM
    York35 wrote:I have tried the following 3 recommendations. But none have worked !!1. Setting the following at session levelALTER 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 recommendationselect /*+ 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?

  • York35
    York35 Member Posts: 340 Bronze Badge
    edited May 25, 2017 11:01AM

    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.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,756 Gold Crown
    edited May 25, 2017 11:05AM

    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

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,756 Gold Crown
    edited May 25, 2017 11:19AM

    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"

    AndrewSayer
This discussion has been closed.