When I delivered my Advanced Oracle SQL Tuning training last week, I jumped into a free-form demo to show that the PARALLEL hints don’t really force anything other than adjusting the cost of parallelizable operations for CBO evaluation as usual. If a serial plan (or branch) still has a lower cost than the respective parallel processing cost, then serial execution would be used there. But when demoing this, I got a surprise.
Demo Problems
How the demo was supposed to go was this:
SQL> CREATE /*+ GATHER_OPTIMIZER_STATISTICS */ TABLE t AS SELECT * FROM dba_objects;
Table created.
SQL> CREATE INDEX i ON t(object_id);
Index created.
SQL> SELECT COUNT(*) FROM t WHERE object_id = 12345;
COUNT(*)
----------
1
SQL> @x
----------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | SORT AGGREGATE | | 1 | 5 | |
|* 2 | INDEX RANGE SCAN| I | 1 | 5 | 1 (0)|
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=12345)
We get a quick & nice serial lookup with the help of the index. Now when we “force” statement-level parallelism with a hint, are we going to get a parallel plan?
SQL> SELECT /*+ PARALLEL(4) */ COUNT(*) FROM t WHERE object_id = 12345;
COUNT(*)
----------
1
SQL> @x
----------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | SORT AGGREGATE | | 1 | 5 | |
|* 2 | INDEX RANGE SCAN| I | 1 | 5 | 1 (0)|
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=12345)
We still get a serial plan as the optimizer has found that a serial index range scan would still be cheaper than a parallel full table scan or index fast full scan with the specified degree. An excerpt from the CBO trace for the query with PARALLEL hint is here:
Access Path: TableScan
Cost: 345.441899 Resp: 95.956083 Degree: 0
Cost_io: 344.000000 Cost_cpu: 19525740
Resp_io: 95.555556 Resp_cpu: 5423817
Access Path: index (index (FFS))
Index: I
resc_io: 42.000000 resc_cpu: 13948819
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 43.030065 Resp: 11.952796 Degree: 4
Cost_io: 42.000000 Cost_cpu: 13948819
Resp_io: 11.666667 Resp_cpu: 3874672
****** Costing Index I
Estimated selectivity: 1.3316e-05 , col: #4
Access Path: index (AllEqRange)
Index: I
resc_io: 1.000000 resc_cpu: 8171
ix_sel: 1.3316e-05 ix_sel_with_filters: 1.3316e-05
Cost: 1.000603 Resp: 1.000603 Degree: 1
Best:: AccessPath: IndexRange
Index: I
Cost: 1.000603 Degree: 1 Resp: 1.000603 Card: 1.000000 Bytes: 0.000000
See, the Resp cost - response-time-reduction optimized cost (parallel), not resource-efficiency-optimized cost (serial) is about 95.5 and 11.6 for the TableScan and the index FFS above. But the serial IndexRange cost is still just ~1. So this demo successfully shows that a parallel hint doesn’t force parallelism, it just adjusts costs (and the optimizer typically ends up using parallelism for complex enough plans as a result).
However, I ran the free-form demo slightly differently. While previously I projected just a COUNT(*) that could be satisfied by walking the index structure alone, last week I also selected a table column that wasn’t part of the index:
SQL> SELECT owner FROM t WHERE object_id = 12345;
OWNER
------------------------------
SYS
SQL> @x
----------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 10 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I | 1 | | 1 (0)|
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=12345)
The serial plan makes sense, now we have to do a TABLE ACCESS BY INDEX ROWID after fetching rowids from the INDEX RANGE SCAN. Now let’s try with the parallel hint:
SQL> SELECT /*+ PARALLEL(4) */ owner FROM t WHERE object_id = 12345;
OWNER
------------------------------
SYS
SQL> @x
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| | | |
| 1 | PX COORDINATOR | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 10 | 2 (0)| Q1,01 | P->S | QC (RAND) |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 10 | 2 (0)| Q1,01 | PCWP | |
| 4 | BUFFER SORT | | | | | Q1,01 | PCWC | |
| 5 | PX RECEIVE | | 1 | | 1 (0)| Q1,01 | PCWP | |
| 6 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 1 | | 1 (0)| Q1,00 | S->P | HASH (BLOCK|
| 7 | PX SELECTOR | | | | | Q1,00 | SCWC | |
|* 8 | INDEX RANGE SCAN | I | 1 | | 1 (0)| Q1,00 | SCWP | |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("OBJECT_ID"=12345)
- Degree of Parallelism is 4 because of hint
Uh, what? Now that we have to visit the table too, we suddenly get a parallel plan - just for fetching a single row! If you look in the IN-OUT column you see that the index range scan is actually done in serial mode (IN-OUT column value starts with S) and the following table access is parallelized.
The line #8 - INDEX RANGE SCAN indirectly produces matching rowids to its “grandparent” consumer #3 - TABLE ACCESS BY INDEX ROWID BATCHED through the slave PX communication mechanisms PX* and apparently there’s a BUFFER SORT in the mix too, probably for fetching enough rowids from the index range scan producers, before passing a batch of them to the table access operator. And the #6 - PX SEND HASH (BLOCK ADDRESS) seems to show that the rowids are distributed to the consumer slaves by the data block address of the rowid to ensure better batching efficiency & cache locality for table access slaves.
While this relatively little-known plan shape is interesting to read, during my demo surprise, my main question was “what has changed in the optimizer” as I recall doing such demos without a problem in past. Also, for fetching just one row via an index lookup, doing a couple of logical IOs in serial mode (in the existing session) should definitely be faster than allocating PX processes/sessions and distributing work to parallel slaves.
Switching to Old Optimizer Behavior
Since I didn’t recall such behavior from past and parallelizing such a plan didn’t make sense anyway, I decided to see if I’d get back the old behavior with using smaller (older) values for the optimizer_features_enable setting. You can either set the session level parameter or use a hint as I’ve done below:
SQL> SELECT /*+ PARALLEL(4) OPTIMIZER_FEATURES_ENABLE('11.2.0.4') */ owner
FROM t WHERE object_id = 12345;
OWNER
------------------------------
SYS
SQL> @x
--------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I | 1 | | 1 (0)|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=12345)
I was demoing this on Oracle 18c, so I lowered the OFE parameter first to 12.2.0.1, then 12.1.0.2, then 12.1.0.1 and the problematic PX plan was picked up in all these versions. However with OFE 11.2.0.4 and lower, I got the desired serial plan! So something must have had changed between 11.2.0.4 and 12.1.0.1.
Exploring Optimizer Features
So, which exact optimizer features or decisions have changed across Oracle versions? I have a script cofep.sql (Compare Optimizer Features Enabled Parameters) for getting a high level overview. It actually requires some setup - look into the optimizer_features_matrix.sql setup script and add a schema name if you don’t want the “matrix” table to be created under SYS schema. You don’t have to install this into production, you can use this in your dev/sandbox database as long as it has the same version of Oracle installed as production.
The idea is to change the optimizer_features_enable variable to different DB version numbers and see which (undocumented) optimizer parameters also change as a result. So if you didn’t have a problem say in Oracle 11.2.0.3 but do have a problem in 11.2.0.4, you can list the undocumented parameters (optimizer features) that have been enabled or changed between these versions and see if something looks familiar from there:
SQL> @pvalid optimizer_features_enable
Display valid values for multioption parameters matching "optimizer_features_enable"...
PAR# PARAMETER ORD VALUE DEFAULT
------ ------------------------------ ----- ----------- -------
3482 optimizer_features_enable 35 18.1.0 DEFAULT
optimizer_features_enable 1 8.0.0
optimizer_features_enable 2 8.0.3
optimizer_features_enable 3 8.0.4
...
optimizer_features_enable 30 11.2.0.3
optimizer_features_enable 31 11.2.0.4
optimizer_features_enable 32 12.1.0.1
optimizer_features_enable 33 12.1.0.2
optimizer_features_enable 34 12.2.0.1
optimizer_features_enable 36 18.1.0.1
Now (after you’ve ran the setup script described above), you can report the optimizer parameter difference between any DB version. For example, here are the optimizer features (that have an associated parameter) that changed between 11.2.0.1 and 11.2.0.2:
SQL> @cofep 11.2.0.1 11.2.0.2
Compare Optimizer_Features_Enable Parameter differences
for values 11.2.0.1 and 11.2.0.2
PARAMETER '11.2.0.1' '11.2.0.2' DESCRIPTION
--------------------------------------------- -------------- ------------- ----------------------------------------------------------------------
_px_partition_scan_enabled FALSE TRUE enables or disables parallel partition-based scan
optimizer_features_enable 11.2.0.1 11.2.0.2 optimizer plan compatibility parameter
_optimizer_undo_cost_change 11.2.0.1 11.2.0.2 optimizer undo cost change
_optimizer_false_filter_pred_pullup FALSE TRUE optimizer false predicate pull up transformation
_optimizer_full_outer_join_to_outer FALSE TRUE enable/disable full outer to left outer join conversion
_optimizer_extended_stats_usage_control 224 192 controls the optimizer usage of extended stats
_optimizer_enable_table_lookup_by_nl FALSE TRUE consider table lookup by nl transformation
7 rows selected.
In my case I had to compare 11.2.0.4 and 12.1.0.1 as this is where the change apparently happened. You’ll need to scroll right to see the full output:
SQL> @cofep 11.2.0.4 12.1.0.1
Compare Optimizer_Features_Enable Parameter differences
for values 11.2.0.4 and 12.1.0.1
PARAMETER '11.2.0.4' '12.1.0.1' DESCRIPTION
------------------------------------------ ------------- ------------- ----------------------------------------------------------------------
_optimizer_unnest_scalar_sq FALSE TRUE enables unnesting of of scalar subquery
_px_object_sampling_enabled FALSE TRUE use base object sampling when possible for range distribution
_px_filter_parallelized FALSE TRUE enables or disables correlated filter parallelization
_px_cpu_autodop_enabled FALSE TRUE enables or disables auto dop cpu computation
_px_single_server_enabled FALSE TRUE allow single-slave dfo in parallel query
_optimizer_partial_join_eval FALSE TRUE partial join evaluation parameter
_optimizer_ansi_rearchitecture FALSE TRUE re-architecture of ANSI left, right, and full outer joins
_px_replication_enabled FALSE TRUE enables or disables replication of small table scans
_px_wif_extend_distribution_keys FALSE TRUE extend TQ data redistribution keys for window functions
_px_partial_rollup_pushdown OFF ADAPTIVE perform partial rollup pushdown for parallel execution
_optimizer_use_gtt_session_stats FALSE TRUE use GTT session private statistics
_px_concurrent FALSE TRUE enables pq with concurrent execution of serial inputs
_optimizer_proc_rate_level OFF BASIC control the level of processing rates
optimizer_features_enable 11.2.0.4 12.1.0.1 optimizer plan compatibility parameter
_optimizer_undo_cost_change 11.2.0.4 12.1.0.1 optimizer undo cost change
_optimizer_ansi_join_lateral_enhance FALSE TRUE optimization of left/full ansi-joins and lateral views
_optimizer_multi_table_outerjoin FALSE TRUE allows multiple tables on the left of outerjoin
_optimizer_cube_join_enabled FALSE TRUE enable cube join
_px_filter_skew_handling FALSE TRUE enable correlated filter parallelization to handle skew
_px_parallelize_expression FALSE TRUE enables or disables expression evaluation parallelization
_optimizer_gather_stats_on_load FALSE TRUE enable/disable online statistics gathering
_optimizer_batch_table_access_by_rowid FALSE TRUE enable table access by ROWID IO batching
_adaptive_window_consolidator_enabled FALSE TRUE enable/disable adaptive window consolidator PX plan
_optimizer_cluster_by_rowid FALSE TRUE enable/disable the cluster by rowid feature
_optimizer_null_accepting_semijoin FALSE TRUE enables null-accepting semijoin
_optimizer_hybrid_fpwj_enabled FALSE TRUE enable hybrid full partition-wise join when TRUE
_px_groupby_pushdown CHOOSE FORCE perform group-by pushdown for parallel query
_px_join_skew_handling FALSE TRUE enables skew handling for parallel joins
_px_wif_dfo_declumping OFF CHOOSE NDV-aware DFO clumping of multiple window sorts
29 rows selected.
Now I have a more detailed view - the 29 parameters that have changed across optimizer versions. This is not nearly the complete amount of changes in the CBO of course, but typically the more prominent (or tunable) optimizer features/settings that “deserve” their own parameters. So as a next step I set the optimizer_features_enable to 12.1.0.1 (the first version where the problem manifested itself) and started setting the more suspicious-looking parameters (given the parallel plan problem we witnessed) from TRUE to FALSE. For example, I disabled features like _optimizer_batch_table_access_by_rowid and _optimizer_cluster_by_rowid in my test environment, but no luck. Note that it’s probably a bad idea to just try out changing a variety undocumented parameters back to their “good” values in production systems, better to find the culprit in a test environment and once the cause is determined, decide what to do with it in production.
Nevertheless, none of parameters I tried gave me the good plan, so I wasn’t able to isolate the optimizer behavior change with the help of (undocumented) parameters.
So I had to go one level deeper.
Exploring Optimizer Bugfixes
Earlier I said that the (undocumented) optimizer parameters that change together with optimizer_features_enable settings don’t represent all changes in the CBO. Not every new behavior and decision has its own parameter. I’m sure there are quite a few completely “dark” changes in the optimizer introduced with every new release of the code. However, since Oracle 10g, Oracle introduced a more fine-grained way for controlling (and reverting) smaller changes in the optimizer behavior. Changes that are typically (but not always) introduced for fixing some specific bug.
So with many optimizer bugfixes and behavior changes, it’s possible to enable/disable or adjust them using the _fix_control session level parameter. You can see all possible (registered) CBO fixes/features from V$SYSTEM_FIX_CONTROL and V$SESSION_FIX_CONTROL. Every fix is identified by a bug number so you could try to look it up in My Oracle Support, but there’s also a helpful description column too.
For example, if I want to see the status of all fixes where the description contains %hash%, I could run this script:
SQL> @fix hash
SESSION_ID BUGNO VALUE SQL_FEATURE DESCRIPTION OPTIMIZER_FEATURE_ENABLE EVENT IS_DEFAULT CON_ID
---------- ---------- ---------- ------------------------------------ ---------------------------------------------------------------- ------------------------- ---------- ---------- ----------
11 5416118 1 QKSFM_CBQT_5416118 use hash-based mapping in copy service 8.0.0 0 1 0
11 6399597 0 QKSFM_COMPILATION_6399597 disallow HASH GROUP BY for subquery (in filter) processing 0 1 0
11 6982954 1 QKSFM_PQ_6982954 bloom filter for hash join with broadcast left 11.1.0.7 0 1 0
11 7834811 1 QKSFM_ACCESS_PATH_7834811 allow in-list iterators for hash clusters 10.2.0.5 0 1 0
11 5892599 0 QKSFM_CBO_5892599 disable hash join for NEED1ROW subqueries 0 1 0
11 8619631 0 QKSFM_COMPILATION_8619631 Allow hash aggregation for insert select except for plsql calls 0 1 0
11 4926618 1 QKSFM_EXECUTION_4926618 do not use hash unique for subqueries in update-set expression 11.2.0.2 0 1 0
11 6472966 1 QKSFM_PARTITION_6472966 load and use statistics for hash sub-partitions 11.2.0.2 0 1 0
11 10162430 1 QKSFM_CBO_10162430 plan hash value ignores tables starting with SYS_TEMP 11.2.0.3 0 1 0
11 10428450 1 QKSFM_PQ_10428450 distinguish range and hash keys for clumping decisions 8.0.0 0 1 0
11 11830663 1 QKSFM_COMPILATION_11830663 disallow HASH GROUP BY for subquery (in SELECT) processing 11.2.0.3 0 1 0
11 13406619 1 QKSFM_SQL_CODE_GENERATOR_13406619 disallow HASH UNIQUE in correlated subquery 11.2.0.4 0 1 0
11 13826669 1 QKSFM_EXECUTION_13826669 adaptive memory management for hash group-by 8.0.0 0 1 0
11 16792882 0 QKSFM_COMPILATION_16792882 Cardinality threshold for hash aggregation(raised to power of 10 0 1 0
11 21553593 1 QKSFM_EXECUTION_21553593 determines how XT directory object hash table is created 8.0.0 0 1 0
11 22540411 0 QKSFM_ALL_22540411 Use hash group by aggregation method when gby oby keys are clump 0 1 0
11 22301868 0 QKSFM_EXECUTION_22301868 use parallel SORT UNIQUE over HASH UNIQUE in a multi-restarted Q 0 1 0
11 21130817 1 QKSFM_PQ_21130817 support kgghash3 in table queue distribution 8.0.0 0 1 0
11 21870589 1 QKSFM_COMPILATION_21870589 allow hash group by for LISTAGG with constant ORDER-BY expressio 18.1.0 0 1 0
19 rows selected.
The columns should be pretty self-explanatory, the value column shows whether a fix is enabled (0 = disabled, 1 = enabled). Some fixes use values bigger than 1 too where the decision is not simply about enabling/disabling something, but about thresholds or bitmaps.
In the following example I’m looking a fix up by bug#, not description (scroll right to see the whole output):
SQL> @fix 14295250
SESSION_ID BUGNO VALUE SQL_FEATURE DESCRIPTION OPTIMIZER_FEATURE_ENABLE EVENT IS_DEFAULT CON_ID
---------- ---------- ---------- ------------------------------- ---------------------------------------------------------------- ------------------------- ---------- ---------- ----------
11 14295250 45000 QKSFM_TRANSFORMATION_14295250 Disallow View merging if there are too many operands 11.2.0.4 0 1 0
So, it is possible to use the same technique of changing the optimizer_features_enable to different database versions and witnessing some fixes automatically to be turned on and off as a result!
Note that while there is an optimizer_features_enable column also in the above V$ view output, in some cases it’s NULL and I want to be sure about behavior by altering the session and checking the true status of these fixes from V$SESSION_FIX_CONTROL.
This is why I recently wrote another script cofef.sql (Compare Optimizer Features Enabled Fixes) that lists you the status of “registered” optimizer bugfixes and behavior changes. And since I knew that the change happened somewhere between 11.2.0.4 and 12.1.0.1, I listed all known optimizer bugfix changes between these versions:
SQL> @cofef 11.2.0.4 12.1.0.1
Compare Optimizer_Features_Enable Fix differences
for values 11.2.0.4 and 12.1.0.1 (v$session_fix_control)
BUGNO SQL_FEATURE DESCRIPTION OPTIMIZER_FEATURE_ENABLE EVENT IS_DEFAULT '11.2.0.4' '12.1.0.1'
---------- ---------------------------------------- ------------------------------------------------------------------- ------------------------- ---------- ---------- ------------- ------------
9593547 QKSFM_ACCESS_PATH_9593547 estimate selectivity for unique scan predicates 12.1.0.1 0 1 0 1
9833381 QKSFM_ACCESS_PATH_9833381 rank predicates before costing 12.1.0.1 0 1 0 1
9929609 QKSFM_CBO_9929609 use qksvc to handle descending indexes 12.1.0.1 0 1 0 1
9832338 QKSFM_COMPILATION_9832338 disallow outer join oper (+) in CONNECT BY and START WITH clause 12.1.0.1 0 1 0 1
...
13345888 QKSFM_PQ_13345888 parallel table lookup access by rowid 12.1.0.1 0 1 0 1
...
9554026 QKSFM_ACCESS_PATH_9554026 store index filter selectivity/cost 12.1.0.1 0 1 0 1
10175079 QKSFM_SQL_CODE_GENERATOR_10175079 increment kafcoref to simulate obsolete fropqop list 12.1.0.1 0 1 0 1
13704977 QKSFM_COMPILATION_13704977 fixes names/hints for multiple-inlined WITH subqueries 12.1.0.1 0 1 0 1
11843466 QKSFM_PQ_11843466 do not force serialize px for serial pl/sql 12.1.0.1 0 1 0 1
53 rows selected.
I have removed most of the output, but this script helped me to narrow down my search from over 1300 registered bugfixes (in 18c) to 53. When visually scanning through the list, I easily noticed a candidate with a pretty convincing description - bug 13345888 - parallel table lookup access by rowid. If you scroll right you’ll see that its value has changed from “0” in 11.2.0.4 to “1” in 12.1.0.1.
Now all I had to do is to turn this individual fix off and see if it gives me the better serial plan (having optimizer_features_enable set to 12c and even 18c):
SQL> show parameter optimizer_feature
PARAMETER_NAME TYPE VALUE
------------------------------------------------------------ ----------- ------
optimizer_features_enable string 18.1.0
SQL> ALTER SESSION SET "_fix_control"='13345888:off';
Session altered.
SQL> SELECT /*+ PARALLEL(4) */ owner FROM t WHERE object_id = 12345;
OWNER
------------------------------
SYS
SQL> @x
----------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 10 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I | 1 | | 1 (0)|
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=12345)
Now, with this specific 12c feature parallel table lookup access by rowid disabled, I get the fast plan again and I have worked around my problem. I could now set that fix control systemwide until I’ve patched the database or fixed the root cause by some other means or use a login trigger to set this fix only for some sessions.
Or create a SQL patch with the fix just for that one SQL ID:
SQL> @create_sql_patch 8sgtnhcwrpdcf "opt_param('_fix_control','13345888:off')"
PL/SQL procedure successfully completed.
Notes & Summary
-
Interestingly this problem didn’t show up when I used object level parallelism hints, a’la
PARALLEL(t 4)or just set the table/index degree to 4 and removed any hints. I chose to use the statement level parallelism and the CBO happened to hit this problem in this mode. The CBO hit the same problem also when usingALTER SESSION FORCE PARALLEL QUERY PARALLEL 4to adjust parallel costing. -
It looks like not everything listed under the
V$SESSION_FIX_CONTROLis about bug-fixes. This parallel table lookup fix above looks like a new feature for me. But perhaps it wasn’t worthy of having its own parameter (18c has over 5000 parameters already). -
Coskan and others have written about the
_fix_controlparameter years ago, so it’s nothing new, but I hope that my scripts and the example story help you to discover some optimizer behavior changes easier. -
Out of the 1300+ optimizer fixes in 18c, there are 295 that are apparently not affected by the
optimizer_features_enableparameter at all. I have a cofef_missing.sql script for showing such “independent” fixes. -
One interesting example of such an independent fix is the “bugfix” 6941515 - use high watermark brokering for insert into single segment. The fix has been there since 10g or so but its
valueis 0, so despite the fix being shipped, it’s disabled by default!
SQL> @fix 6941515
SESSION_ID BUGNO VALUE SQL_FEATURE DESCRIPTION OPTIMIZER_FEATURE_ENABLE EVENT IS_DEFAULT CON_ID
---------- ---------- ---------- ---------------------------------------- ---------------------------------------------------------------- ------------------------- ---------- ---------- ----------
11 6941515 0 QKSFM_DML_6941515 use high watermark brokering for insert into single segment 0 1 0
You can read more about how this HWM brokering issue (and the fix being disabled by default) caused buffer busy waits contention on the SEG$ data dictionary base table in one of the complex Oracle performance troubleshooting case studies I’ve experienced in past.
