Scripts for Drilling Down Into Unknown Optimizer Changes

2018/11/08

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 (some lines highlighted with “<<<<<<<<<<<“)

  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

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.


NB! Check out my 2018/2019 online training classes: Advanced Oracle SQL Tuning training, Advanced Oracle Troubleshooting training and the new Practical Linux Performance & Application Troubleshooting training!