Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
What is causing optimizer to ignore Parallel Hints ?

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
Answers
-
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;
-
What happens if you use FULL and
- select /*+ parallel(tbl,5) */ prod_id, COUNT(*) FROM SIEBEL.s_asset tbl
-
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!
-
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.
-
Actually, the index cost is probably wildly wrong due to the special Siebel recomendations
- 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.
-
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
-
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?
-
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.
-
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
-
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"