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