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

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;

Saubhik

What happens if you use FULL and

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

AndrewSayer

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!

tarfu_dba

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.

AndrewSayer

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

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

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

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

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

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"