gby_pushdown

This is a note that’s been awaiting completion for nearly 10 years. It’s about a feature (or, at least, a hint for the feature) that appeared in 10.2.0.5 to control some of the inter-process messaging that takes place in parallel execution.

It’s a very simple idea that can make a significant difference in CPU usage for large parallel queries – can you aggregate the raw data before distributing it between slave sets (minimising the volume of traffic) or should you simply distribute the data and aggregate late to avoid aggregating twice. The strategy of aggregating early is known as “group by pushdown”.

I’ll start with a script to create a simple data set and a trivial query with a parallel hint:

rem
rem     Script:         gby_pushdown.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2023
rem
rem     Last tested 
rem             19.11.0.0
rem             12.2.0.1
rem

set linesize 180
set pagesize 60
set trimspool on
set tab off

create table t1 
as 
select 
        * 
from 
        all_objects 
where 
        rownum <= 50000
/


alter session set statistics_level = all;

set serveroutput off

prompt  ==================================================
prompt  Default (for this data) pushdown and hash group by
prompt  ==================================================

set feedback only

select 
        /*+ 
                qb_name(main)
                parallel(t1 2) 
--              no_gby_pushdown(@main)
--              no_use_hash_gby_for_pushdown(@main)
        */ 
        owner, object_type, count(*) ct
from 
        t1 
group by 
        owner, object_type
order by
        owner, object_type
/

set feedback on
select * from table(dbms_xplan.display_cursor(format=>'outline allstats cost hint_report -predicate'));

In the absence of any hints (apart from the qb_name() and parallel() hints), the plan I get from the query is as follows:

Plan hash value: 2919148568

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |      1 |        |    77 (100)|     96 |00:00:00.07 |       5 |      0 |       |       |          |
|   1 |  PX COORDINATOR          |          |      1 |        |            |     96 |00:00:00.07 |       5 |      0 | 11264 | 11264 |     1/0/0|
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |      0 |    276 |    77   (8)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|   3 |    SORT GROUP BY         |          |      2 |    276 |    77   (8)|     96 |00:00:00.01 |       0 |      0 |  4096 |  4096 |     2/0/0|
|   4 |     PX RECEIVE           |          |      2 |    276 |    77   (8)|    121 |00:00:00.01 |       0 |      0 |       |       |          |
|   5 |      PX SEND RANGE       | :TQ10000 |      0 |    276 |    77   (8)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|   6 |       HASH GROUP BY      |          |      2 |    276 |    77   (8)|    121 |00:00:00.04 |    1043 |    991 |  1079K|  1079K|     2/0/0|
|   7 |        PX BLOCK ITERATOR |          |      2 |  50000 |    73   (3)|  50000 |00:00:00.02 |    1043 |    991 |       |       |          |
|*  8 |         TABLE ACCESS FULL| T1       |     26 |  50000 |    73   (3)|  50000 |00:00:00.01 |    1043 |    991 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"MAIN")
      FULL(@"MAIN" "T1"@"MAIN")
      GBY_PUSHDOWN(@"MAIN")
      USE_HASH_GBY_FOR_PUSHDOWN(@"MAIN")
      END_OUTLINE_DATA
  */

You’ll notice in the Outline Data that Oracle has recorded the use_hash_gby_for_pushdown() hint and the gby_pushdown() hint. I’ll be repeating the query disabling each of these hints in turn – which is why the negative versions of the hints appear as comments in my original query.

If you look at operation 6 of the plan you can see that the optimizer has chosen to use a hash group by to aggregate the selected rows, reducing 50,000 rows to 121 rows. We could query v$pq_tqstat, or run the SQL Monitor report to get more detail about how much work each PX process did, but it’s fairly safe to assume that it was shared reasonably evenly between the two processes.

After aggregating their data the first layer of PX processes distributes the results by range (operation 5, PX Send Range) to the second layer of PX processes, which re-aggregate the much reduced data set. At this point Oracle chooses to aggregate by sorting (Sort Group By) as this will deal with the order by clause at the same time. (Note: you could tell Oracle to split the aggregation and ordering by adding a use_hash_aggregation hint to the query.)

With my data set it’s fairly clear that it’s a good idea to do this “two stage” aggregation because the initial raw data is reduced by a very large factor the first layer of PX processes before they forward the results – and the reduction in inter-process messaging is likely to be a good idea.

There may be cases, of course, where some feature of the data pattern means that two-stage aggregation is a good idea, but aggregating by a sort is more efficient than an aggregation by hashing – a cardinality or clustering estimate might have persuaded the optimizer to make the wrong choice – so let’s see what happens to the plan if we enable the no_use_hash_gby_for_pushdown() hint:

lan hash value: 3954212205

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |      1 |        |    77 (100)|     96 |00:00:00.14 |       5 |       |       |          |
|   1 |  PX COORDINATOR          |          |      1 |        |            |     96 |00:00:00.14 |       5 | 11264 | 11264 |     1/0/0|
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |      0 |    276 |    77   (8)|      0 |00:00:00.01 |       0 |       |       |          |
|   3 |    SORT GROUP BY         |          |      2 |    276 |    77   (8)|     96 |00:00:00.03 |       0 |  4096 |  4096 |     2/0/0|
|   4 |     PX RECEIVE           |          |      2 |    276 |    77   (8)|    120 |00:00:00.03 |       0 |       |       |          |
|   5 |      PX SEND RANGE       | :TQ10000 |      0 |    276 |    77   (8)|      0 |00:00:00.01 |       0 |       |       |          |
|   6 |       SORT GROUP BY      |          |      2 |    276 |    77   (8)|    120 |00:00:00.13 |    1043 |  9216 |  9216 |     2/0/0|
|   7 |        PX BLOCK ITERATOR |          |      2 |  50000 |    73   (3)|  50000 |00:00:00.07 |    1043 |       |       |          |
|*  8 |         TABLE ACCESS FULL| T1       |     26 |  50000 |    73   (3)|  50000 |00:00:00.02 |    1043 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------

It doesn’t really make any difference in this very small test case, though the sorting does seem to have take a fraction of a second more CPU. The key change is that operation 6 has become a Sort Group By.

There is an interesting detail to look out for, though, in the Outline Data and Hint Report:

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"MAIN")
      FULL(@"MAIN" "T1"@"MAIN")
      GBY_PUSHDOWN(@"MAIN")
      END_OUTLINE_DATA
  */

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
   1 -  MAIN
           -  qb_name(main)

   8 -  MAIN / T1@MAIN
           -  parallel(t1 2)

I added the hint /*+ no_use_hash_gby_for_pushdown(@main) */ to the query, and the hint has clearly worked; but it’s not reported in the Hint Report, and it doesn’t appear in the Outline Data. This suggests that if you created an SQL Baseline for this query with this hint in place the plan would not reproduce because SQL Baseline would be missing the critical hint. (To be tested – left as an exercise to readers.)

The next step is to enable the no_gby_pushdown() hint. For the purposes of the note this is the important one. It’s also the one that you are more likely to use as it’s fairly obvious (if you know the data) when it’s a good idea to use it. (In some cases, of course, the optimizer might have avoided “group by pushdown” when it should have used it – in which case you’d be using the gby_pushdown() hint.) Here’s the plan when I block “group by pushdown”.

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |      1 |        |    77 (100)|     96 |00:00:00.08 |       5 |       |       |          |
|   1 |  PX COORDINATOR         |          |      1 |        |            |     96 |00:00:00.08 |       5 |  6144 |  6144 |     1/0/0|
|   2 |   PX SEND QC (ORDER)    | :TQ10001 |      0 |    276 |    77   (8)|      0 |00:00:00.01 |       0 |       |       |          |
|   3 |    SORT GROUP BY        |          |      2 |    276 |    77   (8)|     96 |00:00:00.11 |       0 |  4096 |  4096 |     2/0/0|
|   4 |     PX RECEIVE          |          |      2 |  50000 |    73   (3)|  50000 |00:00:00.07 |       0 |       |       |          |
|   5 |      PX SEND RANGE      | :TQ10000 |      0 |  50000 |    73   (3)|      0 |00:00:00.01 |       0 |       |       |          |
|   6 |       PX BLOCK ITERATOR |          |      4 |  50000 |    73   (3)|  50091 |00:00:00.02 |    1051 |       |       |          |
|*  7 |        TABLE ACCESS FULL| T1       |     28 |  50000 |    73   (3)|  50091 |00:00:00.01 |    1051 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"MAIN")
      FULL(@"MAIN" "T1"@"MAIN")
      END_OUTLINE_DATA
  */

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3
---------------------------------------------------------------------------
   1 -  MAIN
           -  no_gby_pushdown(@main)
           -  qb_name(main)

   7 -  MAIN / T1@MAIN
           -  parallel(t1 2)

Key details to highlight here are:

• There’s only one aggregation step, appearing at operation 3 after the PX SEND/RECEIVE
• 50,000 rows are passed from slave set 1 (operation 6) to slave set 2 (operation 4).
• The no_gby_pushdown(@main) does appear in the Hint Report
• The no_gby_pushdown(@main) doesn’t appear in the Outline Data (which is, again, a little worrying).

Again, with this small data set, the change in plan isn’t going to make much difference to the end user, but you may find cases where there is a best choice to keep the user sufficiently happy and save machine resources.

Summary

When using parallel query the optimizer may choose to aggregate the data in two steps so that the current rowsource is reduced by one set of PX processes before it is passed to the next set of PX processes that does a second aggregation step. This tends to be a good idea if the first set of processes can reduce the size of the data set by a large factor and save a lot of machine resources on the subsequence inter-process communication; but it is a bad idea if the data doesn’t reduce and a large volume of data ends up being aggregated in both sets of PX processes.

You can over-ride the optimizer’s choice with the [no_]gby_pushdown() hint.

Swap_Join_Inputs

This is just a short note (I hope) prompted by a conversation on the Oracle-L list server. A query from a 3rd party application was doing a very large full tablescan with hash join when it should have been doing a high precision index driven nested loop join, and the poor choice of plan was due an optimizer defect when handling column groups (fixed in 23c) when one or more of the columns involved is always, or nearly always, null.

As a follow-up the owner of the problem asked what hints should go into an SQL Patch to make the optimizer use the nested loop. There’s a simple guideline that usually works for this type of “wrong join” problem: report the “Outline Data” from the current execution plan; find the relevant join hint(s) (in this case a use_hash() hint and a full() hint), change those join hint(s) (e.g. use_nl(), index()), and write the entire edited outline data into an SQL Patch watching out for a recently fixed defect in the SQL patch code.

There are, however, various refinements that add complexity to this strategy, as you can appreciate from a note I wrote some years ago about how to hint a hash join properly. This note is an example of handling one such refinement.

The query was a little complex, and the optimizer had unnested a subquery that consisted of a union all of 4 branches, and one of those branches had contributed a very large cardinality estimate to the total for the view, so the optimizer had chosen a hash join between the unnested subquery and a large table. Stripped to a bare minimum that part of the plan looked like this:

HASH JOIN
      VIEW                   VW_NSO_1
      TABLE ACCESS FULL      PO_LINE_LOCATIONS_ALL        

A quick scan of the Outline Data found the join hint (converted to lower case, with quotes removed): use_hash(@sel$ac90cd92 vw_nso_1@sel$ac90cd92), and an over-hasty response might be to convert the use_hash to a use_nl and leave it at that – except for three possible warnings:

1. we wanted to see a nested loop into po_line_locations_all, so the suggested use_nl() hint would be looping into the wrong “table”
2. the plan shows the view vw_nso_1 as the build table, while the reported hint is about vw_nso_1 being the second table in the join order
3. there are further references to vw_nso_1 (and to po_line_locations_all) in the outline data

Here’s the complete set of original hints that might have been relevant to this particular part of the plan:

full(@sel$ac90cd92 po_line_locations_all@sel$2)
no_access(@sel$ac90cd92 vw_nso_1@sel$ac90cd92)
leading(@sel$ac90cd92 po_line_locations_all@sel$2 vw_nso_1@sel$ac90cd92)
use_hash(@sel$ac90cd92 vw_nso_1@sel$ac90cd92)
swap_join_inputs(@sel$ac90cd92 vw_nso_1@sel$ac90cd92)

index_rs_asc(@sel$b584fdd1 po_line_locations_all@sel$16 (......))
batch_table_access_by_rowid(@sel$b584fdd1 po_line_locations_all@sel$16)

index_rs_asc(@sel$5ed1c707 po_line_locations_all@sel$13 (......))
batch_table_access_by_rowid(@sel$5ed1c707 po_line_locations_all@sel$13)

index_rs_asc(@sel$2f35337b po_line_locations_all@sel$10 (......))
batch_table_access_by_rowid(@sel$2f35337b po_line_locations_all@sel$10)

index_rs_asc(@sel$1b7d9ae9 po_line_locations_all@sel$7 (......))

This is where knowing about the “Query Block / Object Alias” section the execution plans is important – I’ve split the list into several parts based on the query block (@sel$xxxxxxxxxx) they are aimed at, and it’s only the first 5 we need to worry about.

Conveniently this part of the plan is a self-contained query block (@sel$ac90cd92) and we can see why we have an apparent contradiction between vw_nso_1 being the second table in the join order while being the build table: it’s second because of the leading() hint which dictates the join order, but it becomes the build table, hence appearing to be the first table in the join order, because of the swap_join_inputs() hint.

What we want is a join order where vw_nso_1 really is the first table in the join order, followed by a nested loop join into po_line_locations_all, using an index (not the full tablescan that the current hints dictate). It would probably be a good idea to get rid of the redundant no_swap_join_inputs() hints at the same time because that hint applies only to hash joins. So I think we need to replace the 5 hints above with the following 4 hints:

no_access(@sel$ac90cd92 vw_nso_1@sel$ac90cd92)
leading(@sel$ac90cd92 vw_nso_1@sel$ac90cd92 po_line_locations_all@sel$2)
use_nl(@sel$ac90cd92 po_line_locations_all@sel$2)
index(@sel$ac90cd92 po_line_locations_all@sel$2(line_location_id))

The index hint references column line_location_id because that’s the access predicate used in original hash join and I’m assuming that there is an index that starts with that column. It’s always a bit hit and miss with hinting and it might have been sufficient (as a first attempt) to use the index hint without trying to reference a specific index, and there might be good reasons for adding more columns to the list, or simple naming the index rather than describing it.

It’s quite likely that if this change in the hints is sufficient the resulting Outline Data would look a little different anyway; in particular the index() hint that I’ve suggested might get expanded to index_rs_asc(), and there might be a batch_table_access_by_rowid() added. Basically you do test runs until you get the result you want and then use the resulting Outline Data for the patch (although, occasionally, you still find that the Outline Data doesn’t reproduce the plan that it came from).

Frivolous Footnote

There were 75 hints totalling 3,191 bytes in the original Outline Data. If the text gets too long and messy for you to cope with when you create the patch you can probably remove all the double quotes, all the table names from the fully qualified column names in indexes, all the outline() and outline_leaf() hints, all the opt_param() hints that reflect system level parameter changes and the begin/end_outline_data hints and the ignore_optim_embedded_hints hint. You could also change long index descriptions to index_names and, if you’re like me, change it all to lower case anyway because I hate reading in capitals – and if you do change it all to lower case you have to remove the quotes. When I did all this to the original outline data the result was 1,350 bytes for 30 hints.

Descending max()

I’ve written a few notes about problems with “descending” indexes in the past (the word is in quotes because it’s not the index that’s defined as descending, it’s a proper subset of the columns of the index). A silly example came up recently on twitter where the cost of an execution plan changed so dramatically that it had to be hinted when the query changed from “order by list of column names” to “order by list of numeric positions”.

Just to make things a little messier, the index was not just “descending” but the column that had been declared as descending was actually a to_char() of a date column. I won’t reproduce the example here as the tweet links to a pastebin drop of a self-contained example. What I have, instead, is an even simpler example of a “descending” index producing a most unsuitable plan – even when you try to hint around it.

Here’s the demonstration that I’ve run on 19.11 and 23.3:

rem
rem     Script:         ind_desc_max_bug.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2023
rem
rem     Last tested 
rem             21.3.0.0
rem             19.11.0.0
rem

create table t1 
as 
select  * 
from    all_objects
where   rownum <= 10000
/
  
alter table t1 modify object_name not null;

-- create index t1_i1a on t1(object_name);
create index t1_i1d on t1(object_name desc);

execute dbms_stats.gather_table_stats(user,'t1',cascade=>true)

alter session set statistics_level=all;
set serveroutput off

select  max(object_name)
from    t1
/

select * from table(dbms_xplan.display_cursor(format=>'cost allstats last hint_report projection'));

set serveroutput on
alter session set statistics_level = typical;

A couple of details to mention:

• The code includes lines to create two indexes, one ascending and one descending. When I run the code I create only one of them.
• I gather stats after creating the index – this is for the benefit of the descending index only, which needs stats collected on the underlying hidden column definition that Oracle creates to support it.
• There’s a call to define object_name as not null – this is for the benefit of 23c. In 19c the view has several columns which carry forward their underlying not null declarations. In 23c none of the view columns has a not null declaration.

If I create the ascending index 19c and 23c both produce the following plan:

Plan hash value: 1421318352

------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name   | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |        |      1 |        |     2 (100)|      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE            |        |      1 |      1 |            |      1 |00:00:00.01 |       2 |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T1_I1A |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=0) MAX("OBJECT_NAME")[128]
   2 - "OBJECT_NAME"[VARCHAR2,128]

If I create the descending index the plan changes (19c and 23c behave the same way, the following plan is from 23c):

-------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name   | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |      1 |        |     8 (100)|      1 |00:00:00.01 |      49 |
|   1 |  SORT AGGREGATE       |        |      1 |      1 |            |      1 |00:00:00.01 |      49 |
|   2 |   INDEX FAST FULL SCAN| T1_I1D |      1 |  10000 |     8   (0)|  10000 |00:00:00.01 |      49 |
-------------------------------------------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=0) MAX(SYS_OP_UNDESCEND("T1"."SYS_NC00029$"))[128]
   2 - "T1"."SYS_NC00029$"[RAW,193]

There was a slight difference between versions – there are a couple of extra columns in the 23c view so the hidden column referenced in the Projection Information was sys_nc00027$ in 19c compared to sys_nc00029$ in 23c). I don’t know why the length is reported as 193 – I would have expected it to be 129 (since it’s going to hold the one’s-complement of the object_name and a trailing 0xFF byte).

The critical point, of course, is that the query is no longer using the special min/max path, it’s doing an index fast full scan, scanning through 49 buffers instead of accessing just the 2 buffers the min/max needed.

I added a no_index_ffs(t1) hint to see what would happen if I tried to block the bad path: Oracle did a tablescan; so I also added an index(t1) hint to see if that would help and got the following plan:

--------------------------------------------------------------------------------------------------
| Id  | Operation        | Name   | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |      1 |        |    46 (100)|      1 |00:00:00.01 |      46 |
|   1 |  SORT AGGREGATE  |        |      1 |      1 |            |      1 |00:00:00.01 |      46 |
|   2 |   INDEX FULL SCAN| T1_I1D |      1 |  10000 |    46   (0)|  10000 |00:00:00.01 |      46 |
--------------------------------------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=0) MAX(SYS_OP_UNDESCEND("T1"."SYS_NC00029$"))[128]
   2 - "T1"."SYS_NC00029$"[RAW,193]

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
   2 -  SEL$1 / "T1"@"SEL$1"
           -  index(t1)

As you can see from the Hint Report, the optimizer accepted and used my hint. But it hasn’t used the min/max optimisation, it’s done an index full scan, walking through 46 buffers in index order, which could well be more resource-intensive than the fast full scan.

I tried various tricks to get back the min/max optimisation, and I did finally manage to achieve it – but it’s not a realistic solution so don’t copy it. Here’s the SQL and plan:

select
        /*+ index(t1) */
        utl_raw.cast_to_varchar2(
                sys_op_undescend(
                        min(sys_op_descend(object_name))
                )
        )
from    t1
/

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |    46 (100)|      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE             |        |      1 |      1 |            |      1 |00:00:00.01 |       2 |
|   2 |   FIRST ROW                 |        |      1 |  10000 |    46   (0)|      1 |00:00:00.01 |       2 |
|   3 |    INDEX FULL SCAN (MIN/MAX)| T1_I1D |      1 |  10000 |    46   (0)|      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=0) MIN("T1"."SYS_NC00029$")[193]
   2 - "T1"."SYS_NC00029$"[RAW,193]
   3 - "T1"."SYS_NC00029$"[RAW,193]

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
   3 -  SEL$1 / "T1"@"SEL$1"
           -  index(t1)

There is a “extra” first row operator in the plan but you can see that we’re back to a min/max optimisation accessing only 2 buffers to get the result we want. (And it was the same result).

1. For 23c the indexed column is the hidden column SYS_NC00029$, and I know that the optimizer will recognise the equivalence between the column and the expression sys_op_descend(object_name) so it’s been fooled into using the min/max optimisation on the index.
2. If I want the maximum object name I want the minimum sys_op_descend(object_name). Possibly the first row operation appears because the optimizer doesn’t know that the function call will always return a non-null result.
3. Once I’ve found the minimum I need to reverse the effect of the sys_op_descend() – which is where the sys_op_undescend() comes in, but that returns a raw value, so I’ve had to call a utl_raw function to convert the raw to a varchar2(). Watch out, though, because it’s going to be a maximum length varchar2().

If I can make it happen without even changing the shape of the query the optimizer ought to be able to make it happen – but there’s probably a little bit of generic index-usage code that’s currently getting in the way.

Summary

We’ve seen the pattern fairly often: indexes with a mix of ascending and descending columns can be very helpful in specific cases, but we shouldn’t be surprised when a mechanism that appears for “ascending only” indexes doesn’t work perfectly for an index with some descending columns.

Push Group by

Jump to summary.

A new optimizer feature that appears in 23c (probably not 21c) was the ability to push group by clauses into union all set operations. This will happen unhinted, but can be hinted with the highly memorable [no_]push_gby_into_union_all() hint that appeared in 23.1.0.0 according to v$sql_hint. and the feature can be disabled by setting the (equally memorable) hidden parameter _optimizer_push_gby_into_union_all to false.

From a couple of simple experiments it looks as if the hint should be used to identify query blocks where you want an aggregation (group by) that appears “outside” a union all (inline) view to happen “inside” the view. Here’s a trivial demonstration that I’ve run on 23.3

rem
rem     Script:         push_gby_ua.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2023
rem
rem     Last tested 
rem             23.3.0.0
rem

create table t1 
as 
select  *
from    all_Objects 
where   rownum <= 50000
;

set linesize 156
set pagesize 60
set trimspool on
set tab off
set serveroutput off

alter session set statistics_level = all;

select
        /*+
                -- qb_name(main)
                push_gby_into_union_all(@sel$2)
                no_push_gby_into_union_all(@sel$3)
        */
        owner, count(*)
from    (
        select /*  qb_name(u1) */ owner from t1 where owner = 'SYS'
        union all
        select /*  qb_name(u2) */ owner from t1 where owner = 'PUBLIC'
)       
group by owner
/

select * from table(dbms_xplan.display_cursor(format=>'allstats last cost outline alias hint_report qbregistry qbregistry_graph '));

All I’ve done here is create a table that copies 50,000 rows from the view all_objects, then executed a query that reports the number of objects for owners SYS and PUBLIC by selecting the two sets of objects separately and aggregating a union all of those sets.

For maximum visibility I’ve shown the positive and negative versions of the hint – the aggregation doesn’t have to apply to all the branches of the view and it’s not unknown for the optimizer to make the wrong choices if it hasn’t managed to produce a good cost estimate.

I had to know that the two subqueries inside the view would be named sel$2 and sel$3 by default, and I’ve used these names in the hints. In general it can be quite hard to work out the internal names of query blocks and you’ll note that I had used the qb_name() hint for the two subqueries (and the main query block) but then turned those hints into comments because the qbregistry_graph format I’d used in my call to dbms_xplan can’t cope with user-supplied query block names (yet). [Correction: I must have done something wrong when I was using the qb_name hints. I’ve re-run the tests and the graph comes out correctly]

Here’s the execution plan (with some of the bits removed) that I got from 23.3 for this test:

----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |      1 |        |   295 (100)|      2 |00:00:00.03 |    2232 |   1114 |
|   1 |  HASH GROUP BY          |      |      1 |     15 |   295   (4)|      2 |00:00:00.03 |    2232 |   1114 |
|   2 |   VIEW                  |      |      1 |   3334 |   293   (3)|   9288 |00:00:00.03 |    2232 |   1114 |
|   3 |    UNION-ALL            |      |      1 |   3334 |   293   (3)|   9288 |00:00:00.03 |    2232 |   1114 |
|   4 |     SORT GROUP BY NOSORT|      |      1 |      1 |   147   (3)|      1 |00:00:00.02 |    1116 |   1114 |
|*  5 |      TABLE ACCESS FULL  | T1   |      1 |   3333 |   147   (3)|  39724 |00:00:00.01 |    1116 |   1114 |
|*  6 |     TABLE ACCESS FULL   | T1   |      1 |   3333 |   147   (3)|   9287 |00:00:00.01 |    1116 |      0 |
----------------------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('23.1.0')
      DB_VERSION('23.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$FC1F66D1")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SET$22FBD6DA")
      PUSH_GBY_INTO_UNION_ALL(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SET$1")
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
      USE_HASH_AGGREGATION(@"SEL$1" GROUP_BY)
      FULL(@"SEL$3" "T1"@"SEL$3")
      FULL(@"SEL$FC1F66D1" "T1"@"SEL$2")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("OWNER"='SYS')
   6 - filter("OWNER"='PUBLIC')

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
   4 -  SEL$FC1F66D1
           -  push_gby_into_union_all(@sel$2)

   6 -  SEL$3
           -  no_push_gby_into_union_all(@sel$3)

It’s interesting to note that the Hint Report tells us that both my hints were valid (and used); but the Ouline Data echoes only one of them (the “positive” push_gby_into_union_all). Because I’ve used the same table twice it’s not instantly clear that the optimizer has pushed the subquery that I had specified but if you check the Predicate Information you can confirm that the SYS data has been aggregated inside the union all and the PUBLIC data has been passed up to the union all operator without aggregation. (In the absence of the hints both data sets would have been aggregated early.)

Here, in comparison, is the plan (slightly reduced, and with the qbregistry options removed) that I got from 19.11.0.0

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |   265 (100)|      2 |00:00:00.09 |    1986 |    991 |       |       |          |
|   1 |  HASH GROUP BY       |      |      1 |     13 |   265   (4)|      2 |00:00:00.09 |    1986 |    991 |  1422K|  1422K|  653K (0)|
|   2 |   VIEW               |      |      1 |   7692 |   263   (3)|  48446 |00:00:00.07 |    1986 |    991 |       |       |          |
|   3 |    UNION-ALL         |      |      1 |        |            |  48446 |00:00:00.06 |    1986 |    991 |       |       |          |
|*  4 |     TABLE ACCESS FULL| T1   |      1 |   3846 |   131   (3)|  42034 |00:00:00.02 |     993 |    991 |       |       |          |
|*  5 |     TABLE ACCESS FULL| T1   |      1 |   3846 |   131   (3)|   6412 |00:00:00.01 |     993 |      0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("OWNER"='SYS')
   5 - filter("OWNER"='PUBLIC')

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
   1 -  SEL$1
         E -  push_gby_into_union_all

As you can see, 19.11 treats the hint as an error and both subqueries against t1 pass their rows up to the union all without aggregation. The 19.11 plan also gives you some idea of why it can be worth pushing the group by: 23.3 doesn’t report any memory used for either of the aggregation operations that take place while the postponed (or, rather, unpushed) aggregation in 19.11 reports 1.4M of memory used. As a general principle we might expect several small aggregations have a lower peak of memory usage than one large aggregation. There’s also a CPU benefit when Oracle doesn’t have to push lots of rows up through a couple of operations.

In fact the absence of memory-related columns in the 23.3 plan is a little suspect and I may have to examine it further. It may simply be the case that the size of the “small allocation” that doesn’t get reported in earlier versions has been increased to (best guess) 1MB; it may be that dbms_xplan in 23c has got a little bug that omits that part of the report.

Summary

Oracle 23c has a new transformation that will probably help to reduce memory and CPU consumption when it comes into play. Queries that aggregate over union all views may change plans to push the aggregation into some or all of the separate subqueries inside the union.

The feature is cost-based but you can over-ride the optimizer’s choice of which subqueries should be aggregated early with the hint [no_]push_gby_into_union_all(@qbname). The feature can also be disabled completely by setting the hidden parameter _optimizer_push_gby_into_union_all to false.

Addendum

It occurred to me that the optimizer will transform an IN-list to a list of equalities with OR, and it’s also capable of using OR-expansion then there might be cases where an aggregate based on an IN-list could go through the two steps and then benefit from this new feature, for example:

select
        sts, count(*) ct
from    t1
where   sts in ('B','C')
group by
        sts
/

-------------------------------------------------------------------------------
| Id  | Operation	     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |	      |       |       |     3 (100)|	      |
|   1 |  SORT GROUP BY NOSORT|	      |     2 |     4 |     3	(0)| 00:00:01 |
|   2 |   INLIST ITERATOR    |	      |       |       | 	   |	      |
|*  3 |    INDEX RANGE SCAN  | T1_I1A |   100 |   200 |     3	(0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access(("STS"='B' OR "STS"='C'))

Alas, no. Although we can see the rewrite of the IN-list the optimizer doesn’t then use OR-expansion. And when I added the hint /*+ or_expand */ to try to push Oracle into the right direction the Hint Report told me:

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
   1 -	SEL$1
	 U -  or_expand / No valid predicate for OR expansion

Maybe in the next release.

no_merge() #JoelKallmanday

This is a second follow-up to the video Connor McDonald produced a few days ago about the risks of depending on “current tricks” to control the order of predicate operation, then showing how some requirements for the pattern “check condition B only for rows which have already satisfied condition A” could be handled through a case expression.

The first follow-up note highlighted the problem of a significant change in the handling of CTEs (a.k.a. “with” subqueries); the point I want to discuss in this note is about the step where Connor tried to use a no_merge() hint in a failed attempt to isolate some activity inside an inline view and made the comment that: “no_merge isn’t explicitly designed for inline views of this type”.

Here’s the SQL to create the data, and a version of the query that uses an inline view rather than a CTE:

rem
rem     Script:         ignore_no_merge.sql
rem     Author:         Connor McDonald / Jonathan Lewis
rem     Dated:          Oct 2023
rem
rem
rem     Last tested 
rem             23.2.0.0
rem             19.11.0.0
rem

drop table t purge;

create table t as
select * from dba_objects
/

insert into t ( object_id, owner, object_name)
values (-1,'BAD','BAD')
/

commit;

select
        /*+ no_merge(@inline) */
        count(object_name)
from    (
        select  /*+ qb_name(inline) */
                *
        from    t
        where object_id > 0
        )       my_view
where   sqrt(object_id) between 1 and 10
/

I’ve used a query block name for my inline view, then referenced that name in a no_merge() hint in the main query block to ensure that there’s no possible ambiguity about where the hint should apply. When I run the query in either 19.11 or 23.2 Oracle (often – but not always, thanks to statistical effects) raises the error “ORA-01428: Argument -1 is out of range.” which rather suggests that the no_merge() has been ignored and that the optimizer has managed to transform the query into a single query block combining the predicates rather than forcing the inline query block to eliminate the threat before the main query block evaluates the square root.

If you check the hidden parameters you will find a pair that go a long way back in Oracle’s history (at least 8i for the first of the pair and 10g for the second) relating to view merging :

_complex_view_merging    enable complex view merging 
_simple_view_merging     control simple view merging performed by the optimizer

For a very long time I’ve believed that the no_merge() hint was supposed to block complex view merging but not block simple view merging (and the view merging required for our query is an example of simple view merging). I’m no longer sure why I believed this, maybe it used to be true in the very early days maybe I just assumed that complex and simple view merging worked differently, either way I don’t have any scripts that I might have used to test the idea.

In fact it’s quite simple to show that Oracle hasn’t ignored the hint – even though it’s easy to assume that it has because the run-time response doesn’t seem to have changed. But take a look at the execution plan for the query which, pulled from memory with the alias and outline format options, looks like this:

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |   193 (100)|          |
|   1 |  SORT AGGREGATE     |      |     1 |    66 |            |          |
|   2 |   VIEW              |      |   184 | 12144 |   193   (5)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T    |   184 |  7360 |   193   (5)| 00:00:01 |
----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - INLINE / MY_VIEW@SEL$1
   3 - INLINE / T@INLINE

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"INLINE")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "MY_VIEW"@"SEL$1")
      FULL(@"INLINE" "T"@"INLINE")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter((SQRT("T"."OBJECT_ID")>=1 AND SQRT("T"."OBJECT_ID")<=10
              AND "OBJECT_ID">0))

As you can see, the inline view has suffered some type of transformation effect that results in both the object_id and sqrt() predicates being applied during the tablescan – but the query block named inline still appears in the plan, and we still have an object called my_view.

Compare this with the plan we get if we don’t use the no_merge() hint:

----------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   193 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    40 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   184 |  7360 |   193   (5)| 00:00:01 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$5843E819
   2 - SEL$5843E819 / T@INLINE

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5843E819")
      MERGE(@"INLINE" >"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"INLINE")
      FULL(@"SEL$5843E819" "T"@"INLINE")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter((SQRT("T"."OBJECT_ID")>=1 AND SQRT("T"."OBJECT_ID")<=10
              AND "OBJECT_ID">0))

In this case we can see the optimizer’s strategy in the merge(@inline>sel$1) hint in the Outline Data, a new query block name has appeared (derived from the (default) sel$1 combined with inline) and my inline view my_view has disappeared from the plan.

So how come the optimizer has managed to combine predicates from two different query blocks without merging those query blocks? The answer is in another hidden parameter: _optimizer_filter_pushdown, which defaults to true. The optimizer has pushed the sqrt() predicate down into the inline view. We can avoid the problem by setting the parameter to false, for example:

select
        /*+ 
                no_merge(@inline) 
                opt_param('_optimizer_filter_pushdown','false')
        */
        count(object_name)
...

select * from table(dbms_xplan.display_cursor(format=>'outline alias'))
/

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |   190 (100)|          |
|   1 |  SORT AGGREGATE     |      |     1 |    79 |            |          |
|*  2 |   VIEW              |      | 73570 |  5675K|   190   (4)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T    | 73570 |  2873K|   190   (4)| 00:00:01 |
----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - INLINE / MY_VIEW@SEL$1
   3 - INLINE / T@INLINE

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"INLINE")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "MY_VIEW"@"SEL$1")
      FULL(@"INLINE" "T"@"INLINE")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter((SQRT("OBJECT_ID")>=1 AND SQRT("OBJECT_ID")<=10))
   3 - filter("OBJECT_ID">0)

The overall shape of the plan doesn’t change but we now see the sqrt() predicate being applied at operation 2 (after the table data has been safely filtered at operation 3). We can also see that the Row estimate has changed dramatically – the 73,570 rows extracted from the table is reasonable (but the optimizer seems to have forgotten about making any allowance for the impact of the sqrt() predicates, it should have reported the same 184 that we see in the earlier plans – the normal 5% of 5% for “I haven’t got a clue what a range using a function will do”.)

For this example I’ve used the opt_param() hint to disable filter pushdown, you could, in principle, set the parameter at the session or system level. Whatever you do the effect does not get captured in the Outline Data – so if you can’t rewrite the SQL (and if you could you might opt for a safer strategy anyway) your best bet might be to create an SQL Patch to inject the hint.

Summary

When you use the no_merge() hint to block view merging you may get the impression that it hasn’t worked because the optimizer can still do filter predicate pushdown, so a predicate that you wanted to apply “outside” the view still gets applied “inside” the view. Don’t assume you know what the plan looks like just because the outcome of running the query hasn’t changed – check the actual execution plan and especially the Predicate Information. You may need to set _optimizer_filter_pushdown to false to achieve the “delayed” application of your predicate, possibly by means of an SQL Patch.

Case Study

A recent post on the Oracle SQL and PL/SQL forum posted the following query with a complaint that it produced the wrong results:

select  count(*) 
from    all_synonyms
left join 
        all_objects b 
on      (b.owner,object_name)=(select table_owner,table_name from dual)
;

This caused a little confusion to start with since the opening complaint was that the query sometimes produced the wrong results and sometimes produced Oracle error ORA-01799: a column may not be outer-joined to a subquery. The variation in behaviour appears, of course, because the restriction applied to older versions of Oracle but had been lifted by 12c.

The other point of confusion is that there’s no easy way to tell from the output that the result is wrong – especially when the count was in the thousands. This point was addressed by the OP supplying an image of the first rows (and a subset of the columns) for the query when “count(*)” was changed to just “*” (and switched from the “all_” views to the “dba_” views: the output for the columns dba_objects.owner and dba_objects.object_name reported the same pair of values for every row – in the case of the OP this was “SYS” and “DUAL”.

Investigation

You could attack this problem from several directions – you could just edit the query to avoid the problem (the structure is a little curious); you could investigate the 10053 trace file to see what the optimizer is doing every step of the way, or you could try to simplify the model and see if the problem still appears. Since I like to keep things simple (to start with, at least) I created table my_synonyms as a copy of the data in dba_synonyms, and my_objects as a copy of the data in dba_objects and created an index on my_objects(owner, object_name). So my query turned into:

select
        *
from    
        my_synonyms   syn
left join 
        my_objects    obj
on 
        (obj.owner, obj.object_name) = (
                select /*+ qb_name(dual_bug) */ syn.table_owner, syn.table_name from dual
        )
/

You’ll notice that I’ve given both tables a “meaningful” alias and used the aliases for every column, and I’ve also added a query block name (qb_name) to the subquery against dual – because I’d assumed that the subquery probably played a key role in messing the optimizer up (I was wrong) and I wanted to be able to track it easily.

The query produced the wrong results. In my case every row reported obj.object owner and obj.name as “SYS” / “PRINT_TABLE” – selecting 11,615 rows. The basic execution plan – pulled from memory using dbms_xplan.display_cursor() – looked like this:

----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |       |       | 46548 (100)|          |
|   1 |  MERGE JOIN OUTER                      |                 | 11615 |  6261K| 46548   (1)| 00:00:02 |
|   2 |   TABLE ACCESS FULL                    | MY_SYNONYMS     | 11615 |   805K|    18   (6)| 00:00:01 |
|   3 |   BUFFER SORT                          |                 |     1 |   481 | 46530   (1)| 00:00:02 |
|   4 |    VIEW                                | VW_LAT_881C048D |     1 |   481 |     4   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| MY_OBJECTS      |     1 |   132 |     4   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN                  | OBJ_I1          |     1 |       |     3   (0)| 00:00:01 |
|   7 |       FAST DUAL                        |                 |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("OBJ"."OWNER"= AND "OBJ"."OBJECT_NAME"=)

The optimizer had transformed the query into a form using a lateral view – and there have been bugs with lateral views in the past, particularly relating to decorrelation – so maybe the problem was there and a few tests with various hints or fix_controls, or optimizer parameter settings to disable certain features might identify the source of the problem; but before doing that I thought I’d just get a small result set to check the results and execution plan in detail by adding a predicate “rownum <= 12”.

After re-executing with rowsource execution stats enabled, and report the plan with Query Block Names and Object Aliases, Outline Data, and Column Projection Information I observed two critical changes:

• The query produced results that looked as if they were likely to be correct – I no longer had a repeating, incorrect, object owner and name on every row.
• The plan had changed (see following).

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |      1 |        |     12 |00:00:00.01 |      24 |
|*  1 |  COUNT STOPKEY                         |                 |      1 |        |     12 |00:00:00.01 |      24 |
|   2 |   NESTED LOOPS OUTER                   |                 |      1 |     12 |     12 |00:00:00.01 |      24 |
|   3 |    TABLE ACCESS FULL                   | MY_SYNONYMS     |      1 |     12 |      7 |00:00:00.01 |       3 |
|   4 |    VIEW                                | VW_LAT_881C048D |      7 |      1 |     12 |00:00:00.01 |      21 |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| MY_OBJECTS      |      7 |      1 |     12 |00:00:00.01 |      21 |
|*  6 |      INDEX RANGE SCAN                  | OBJ_I1          |      7 |      1 |     12 |00:00:00.01 |      17 |
|   7 |       FAST DUAL                        |                 |      7 |      1 |      7 |00:00:00.01 |       0 |
--------------------------------------------------------------------------------------------------------------------

There’s a “count stopkey” operation to handle the “rownum <= 12” predicate, of course; but most significantly the “merge join outer” operation has changed to a “nested loop outer” operation.

Repeating the test but with “rownum <= 1200” I still got results that looked correct, and still got a “nested loop outer”. A third attempt, with “rownum <= “30000” switched back to wrong results and a “merge join outer” (with a “count stopkey”). So – initial conclusion – it’s the implementation of the merge join that has gone wrong.

Resolution

If the only problem is the choice of join mechanism we can patch the code (by hand or with an sql_patch) to bypass the problem. Given the simple pattern that produces the problem I should be able to find two critical hints in the plan’s Outline Data, one which is a leading() hint that dictates the order (my_synonyms, vw_lat_xxxxxxxx) – though the user will presumably have a different name generated for their lateral view – and a use_merge() or use_nl() hint that dictates the mechanism to use to join from my_synonyms to vw_lat_xxxxxxxx.

In both plans the Outline Data showed me the following (cosmetically adjusted) leading() hint:

leading(@sel$d9e17d64 syn@sel$1 vw_lat_6b6b5ecb@sel$6b6b5ecb)

This was then followed by a use_nl() hint (again cosmetically adjusted) for the correct results:

use_nl(@sel$d9e17d64 vw_lat_6b6b5ecb@sel$6b6b5ecb)

and by a use_merge_cartesian() hint for the erroneous results (so it’s possibly just the “cartesian” bit that’s going wrong.:

use_merge_cartesian(@sel$d9e17d64 vw_lat_6b6b5ecb@sel$6b6b5ecb)

So, as a final test, I edited the query to include the leading() and use_nl() hints, and ran it.

select  /*+
                leading(@sel$d9e17d64 syn@sel$1 vw_lat_6b6b5ecb@sel$6b6b5ecb)
                use_nl(@sel$d9e17d64 vw_lat_6b6b5ecb@sel$6b6b5ecb)
        */
        *
from    
        my_synonyms syn
left join 
        my_objects obj
on 
        (obj.owner, obj.object_name) = (
                select /*+ qb_name(dual_bug) */ syn.table_owner, syn.table_name from dual
        )
/

The problem of the repeating “SYS” / “PRINT_TABLE” disappeared, and the total number of rows selected increased from 11,615 to 12,369 (mostly due to package and package bodies having the same owner and name and so doubling up a number of rows from the dba_synonyms table).

Summary

It’s almost always a good idea to simplify a messy problem.

Always check the execution plan, including Predicate Information, Outline Data and Query Block / Object Alias details.

Although I didn’t use the results in this case, executing with rowsource execution statistics enabled can be very helpful.

This case focused on a detail that looked as if a “newish” feature might be guilty; but the simple model suggested it’s an implementation detail of an old feature (possibly reached because the optimizer has applied a new feature). We can work around it very easily, and we can build a very simple test case to raise with Oracle in an SR.

opt_estimate 4a

I wrote a batch of notes about the opt_estimate() hint a couple of years ago, including one where I explained the option for using the hint to specify the number of rows in a query block. I’ve just come across a particular special case for that strategy that others might find a use for. It’s something to do when using the “select from dual … connect by” trick for multiplying rows.

Here’s a little data to model the idea – I’ve used the all_tables view to generate some “well-known” data since I want to add a tiny bit of complexity to the query while still leaving it easy to understand the index. The results from this demonstration come from Oracle 21.3.0.0, and I’ve included the hint /*+ no_adaptive_plan */ to stop Oracle from getting too clever during optimisation.

rem
rem     Script:         opt_estimate_dual.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2022
rem
rem     Last tested 
rem             21.3.0.0
rem

create table tables_table as select * from all_objects where object_type = 'TABLE';
create table objects_table as select * from all_objects;

alter table objects_table add constraint ot_pk primary key(object_id);

begin
        dbms_stats.gather_table_stats(
                ownname    => user,
                tabname    => 'tables_table',
                method_opt => 'for columns size 60 owner'
        );
end;
/

set serveroutput off

with driver as (
        select  /*+ materialize */
                tt.owner, tt.object_id, v1.rn
        from    tables_table tt,
                (
                select
                        /*+  opt_estimate(query_block scale_rows=10) */
                        rownum rn
                from    dual
                connect by
                        level <= 10
                ) v1
        where
                tt.owner = 'OUTLN'
)
select  /*+ no_adaptive_plan */
        dr.rn, dr.owner, dr.object_id,
        ot.object_id, ot.owner, ot.object_type, ot.object_name
from
        driver dr,
        objects_table   ot
where
        ot.object_id = dr.object_id
/

select * from table(dbms_xplan.display_cursor(format => 'hint_report'));

In my system tables_table holds 727 rows and objects_table holds 58383 rows. Three rows in tables_table correspond to tables owned by user ‘OUTLN’ which means I expect the driver CTE (common table expression / “with” subquery) to generate 30 rows and, given the join on unique id, the query to return 30 rows.

I’ve used the /*+ materialize */ hint to force Oracle to create an in-memory temporary table for the driver CTE, the /*+ no_adaptive_plan */ hint to stop Oracle from getting too clever during optimisation, and the critical /*+ opt_estimate() */ hint to help the optimizer understand the effect of my “connect by” on dual. Here’s the execution plan I get if I don’t include that last hint:

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |       |       |    14 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION               |                            |       |       |            |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6632_31D19D4 |       |       |            |          |
|   3 |    MERGE JOIN CARTESIAN                  |                            |     3 |    78 |     9   (0)| 00:00:01 |
|   4 |     VIEW                                 |                            |     1 |    13 |     2   (0)| 00:00:01 |
|   5 |      COUNT                               |                            |       |       |            |          |
|   6 |       CONNECT BY WITHOUT FILTERING       |                            |       |       |            |          |
|   7 |        FAST DUAL                         |                            |     1 |       |     2   (0)| 00:00:01 |
|   8 |     BUFFER SORT                          |                            |     3 |    39 |     9   (0)| 00:00:01 |
|*  9 |      TABLE ACCESS FULL                   | TABLES_TABLE               |     3 |    39 |     7   (0)| 00:00:01 |
|  10 |   NESTED LOOPS                           |                            |     3 |   453 |     5   (0)| 00:00:01 |
|  11 |    NESTED LOOPS                          |                            |     3 |   453 |     5   (0)| 00:00:01 |
|  12 |     VIEW                                 |                            |     3 |   276 |     2   (0)| 00:00:01 |
|  13 |      TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6632_31D19D4 |     3 |    78 |     2   (0)| 00:00:01 |
|* 14 |     INDEX UNIQUE SCAN                    | OT_PK                      |     1 |       |     0   (0)|          |
|  15 |    TABLE ACCESS BY INDEX ROWID           | OBJECTS_TABLE              |     1 |    59 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   9 - filter("TT"."OWNER"='OUTLN')
  14 - access("OT"."OBJECT_ID"="DR"."OBJECT_ID")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
   0 -  STATEMENT
           -  no_adaptive_plan

   2 -  SEL$1
           -  materialize

I’ve highlighted operations 4 and 8 in the plan: operation 4 is the view of dual that has generated 10 rows – unfortunately the optimizer has only considered the stats of the dual table, and hasn’t factored in the effects of the “connect by with rownum”. Operation 8 shows us that the optimizer has (correctly, thanks to the histogram I requested) estimated 3 rows for the tablescan of tables_table. The result of these two estimates is that operation 3 reports an estimate of 3 ( = 3 * 1 ) rows to be used in probing objects_table.

This is the plan after enabling the /*+ opt_estimate() */ hint:

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |       |       |    45 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION               |                            |       |       |            |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6633_31D19D4 |       |       |            |          |
|   3 |    MERGE JOIN CARTESIAN                  |                            |    30 |   780 |    13   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL                    | TABLES_TABLE               |     3 |    39 |     7   (0)| 00:00:01 |
|   5 |     BUFFER SORT                          |                            |    10 |   130 |     6   (0)| 00:00:01 |
|   6 |      VIEW                                |                            |    10 |   130 |     2   (0)| 00:00:01 |
|   7 |       COUNT                              |                            |       |       |            |          |
|   8 |        CONNECT BY WITHOUT FILTERING      |                            |       |       |            |          |
|   9 |         FAST DUAL                        |                            |     1 |       |     2   (0)| 00:00:01 |
|  10 |   NESTED LOOPS                           |                            |    30 |  4530 |    32   (0)| 00:00:01 |
|  11 |    NESTED LOOPS                          |                            |    30 |  4530 |    32   (0)| 00:00:01 |
|  12 |     VIEW                                 |                            |    30 |  2760 |     2   (0)| 00:00:01 |
|  13 |      TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6633_31D19D4 |    30 |   780 |     2   (0)| 00:00:01 |
|* 14 |     INDEX UNIQUE SCAN                    | OT_PK                      |     1 |       |     0   (0)|          |
|  15 |    TABLE ACCESS BY INDEX ROWID           | OBJECTS_TABLE              |     1 |    59 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("TT"."OWNER"='OUTLN')
  14 - access("OT"."OBJECT_ID"="DR"."OBJECT_ID")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
   0 -  STATEMENT
           -  no_adaptive_plan

   2 -  SEL$1
           -  materialize

There are three things that stand out in this report.

• I’ve highlighted operations 4 and 6: operation 4 is the tablescan of tables_table that correctly estimates 3 rows; operation 6 is the view operation that now correctly estimates 10 rows.
• With the correct estimate for the view the estimate for the join to objects_table is now correct and the join order for the merge join cartesian at operation 3 has been reversed.
• The Hint Report tells us that the opt_estimate() hint is not (always) an optimizer hint! This is a real pain because when the opt_estimate() hints you’ve tried to use don’t appear to work it’s not easy to work out what you’ve done wrong.

To make a point, I can take the demo a little further by changing the /*+ opt_estimate() */ hint to scale_rows=120. Here’s the body of the resulting plan:

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |       |       |   369 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION               |                            |       |       |            |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D663A_31D19D4 |       |       |            |          |
|   3 |    MERGE JOIN CARTESIAN                  |                            |   360 |  9360 |    13   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL                    | TABLES_TABLE               |     3 |    39 |     7   (0)| 00:00:01 |
|   5 |     BUFFER SORT                          |                            |   120 |  1560 |     6   (0)| 00:00:01 |
|   6 |      VIEW                                |                            |   120 |  1560 |     2   (0)| 00:00:01 |
|   7 |       COUNT                              |                            |       |       |            |          |
|   8 |        CONNECT BY WITHOUT FILTERING      |                            |       |       |            |          |
|   9 |         FAST DUAL                        |                            |     1 |       |     2   (0)| 00:00:01 |
|  10 |   HASH JOIN                              |                            |   360 | 54360 |   356   (1)| 00:00:01 |
|  11 |    VIEW                                  |                            |   360 | 33120 |     2   (0)| 00:00:01 |
|  12 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D663A_31D19D4 |   360 |  9360 |     2   (0)| 00:00:01 |
|  13 |    TABLE ACCESS FULL                     | OBJECTS_TABLE              | 58383 |  3363K|   354   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

The earlier plans used a nested loop join into objects_table. In this plan we can see at operation 10 that the optimizer has selected a hash join because the larger row estimate for the CTE has increased the cost of the query beyond the inflection point between nested loop and hash joins.

Summary

If you need to use the “connect by” in an inline view then you may find that the optimizer gets a very bad estimate of the number of rows the view definition will generate and that an /*+ opt_estimate() */ hint in the view using the “scale_rows=nnn” option will produce better estimates of cardinality, hence a better plan.

Footnote

In this particular case where I’ve used the dual table by itself in an inline view I could have used the “rows=NNN” option to get the same effect.

In any case I could have added a /*+ qb_name() */ hint to the inline view, and included a qualifying “@{queryblock}” in the /*+ opt_estimate() */ hint.

Using hints is hard, especially when they’re not documented. There is a lot more to learn about this hint: for example, telling the optimizer about the size of a rowsource doesn’t help if it’s going to use its estimate of distinct values in the next steps of the plan – a correction you’ve managed to inject at one stage may disappear in the very next optimizer calculation.

This catalogue lists more articles on the opt_estimate() hint and its relatives.

Hinting

This is just a lightweight note on the risks of hinting (which might also apply occasionally to SQL Plan Baselines). I’ve just rediscovered a little script I wrote (or possibly last tested/edited) in 2007 with a solution to the problem of how to structure a query to use an “index fast full scan” (index_ffs) following by a “table access by rowid” – a path that is not available to the optimizer for select statements (even when hinted) though it became available (sometimes inappropriately) for deletes and updates in 12c.

It’s possible that this method was something I designed for a client using 9i, but the code still behaves as expected in 11.1.0.7. Here’s the setup and query:

rem
rem     Script:         wildcard.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2007
rem
rem     Last tested
rem             11.1.0.7
rem

create table t1
as
select
        cast(dbms_random.string('a',8) as varchar2(8))  str,
        rpad('x',100)                                   padding
from
        all_objects
where
        rownum <= 10000
;

alter table t1 modify str not null;
create index t1_i1 on t1(str);

begin
        dbms_stats.gather_table_stats(
                user, 't1', 
                cascade => true,
                method_opt => 'for all columns size 1'
        );
end;
/

explain plan for
select  
        /*+ 
                qb_name(main) 
                unnest(@subq1)
                leading(@sel$2fc9d0fe t1@subq1 t1@main)
                index_ffs(@sel$2fc9d0fe t1@subq1(t1.str))
                use_nl(@sel$2fc9d0fe t1@main)
                rowid(@sel$2fc9d0fe t1@main)
        */
        * 
from    t1 
where   rowid in (
                select  /*+ qb_name(subq1) */
                        rowid 
                from    t1 
                where   upper(str) like '%CHD%'
)
;

select * from table(dbms_xplan.display(format=>'outline alias'));

As you can see, I’ve got an IN subquery (query block subq1) to generate a list of rowids from the table for the rows that match my predicate and then my main query (query block main) selects the rows identified by that list.

I’ve added hints to the main query block to unnest the subquery (which will result in a new query block appearing) then do a nested loop from the t1 referenced in subq1 (t1@subq1) to the t1 referenced in main (t1@main), starting with an index fast full scan of t1@subq1 and accessing t1@main by rowid.

The unnest hint was actually redundant – unnesting happened automatically and uncosted. You’ll notice all the other hints are directed at a query block called sel$2fc9d0fe which is the resulting query block name when subq1 is unnested into main.

Here’s the resulting execution plan showing, amongst other details in the Outline Data, that this really was running on 11.1.0.7

Plan hash value: 1953350015

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   500 | 65500 |   509   (0)| 00:00:07 |
|   1 |  NESTED LOOPS               |       |   500 | 65500 |   509   (0)| 00:00:07 |
|*  2 |   INDEX FAST FULL SCAN      | T1_I1 |   500 | 10500 |     9   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY USER ROWID| T1    |     1 |   110 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$2FC9D0FE
   2 - SEL$2FC9D0FE / T1@SUBQ1
   3 - SEL$2FC9D0FE / T1@MAIN

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$2FC9D0FE" "T1"@"MAIN")
      LEADING(@"SEL$2FC9D0FE" "T1"@"SUBQ1" "T1"@"MAIN")
      ROWID(@"SEL$2FC9D0FE" "T1"@"MAIN")
      INDEX_FFS(@"SEL$2FC9D0FE" "T1"@"SUBQ1" ("T1"."STR"))
      OUTLINE(@"SUBQ1")
      OUTLINE(@"MAIN")
      UNNEST(@"SUBQ1")
      OUTLINE_LEAF(@"SEL$2FC9D0FE")
      ALL_ROWS
      DB_VERSION('11.1.0.7')
      OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(UPPER("STR") LIKE '%CHD%')

Running the test under 19.11.0.0 (and adding the hint_report option to the dbms_xplan format) this is the resulting plan:

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   500 | 55000 |    47   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   500 | 55000 |    47   (0)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$48592A03 / T1@MAIN

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$48592A03" "T1"@"MAIN")
      OUTLINE(@"SUBQ1")
      OUTLINE(@"MAIN")
      ELIMINATE_SQ(@"SUBQ1")
      OUTLINE_LEAF(@"SEL$48592A03")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(UPPER("T1"."STR") LIKE '%CHD%')

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 5 (U - Unused (1), N - Unresolved (4))
---------------------------------------------------------------------------
   0 -  SEL$2FC9D0FE
         N -  index_ffs(@sel$2fc9d0fe t1@subq1(t1.str))
         N -  leading(@sel$2fc9d0fe t1@subq1 t1@main)
         N -  rowid(@sel$2fc9d0fe t1@main)
         N -  use_nl(@sel$2fc9d0fe t1@main)

   0 -  SUBQ1
         U -  unnest(@subq1)

Clearly the plan has changed – but the hint report says that Oracle has NOT ignored my hints; instead it tells us that they cannot be resolved. If we check the Query Block Name / Object Alias list and the Outline Data we see why: there is no query block named @sel$2fc9d0fe and the reason it doesn’t exist is that the optimizer has applied a previously non-existent transformation ‘eliminate_sq’ (which appeared in 12c) to subq1.

So, on the upgrade from 11.1.0.7 to 19.11.0.0 an SQL Plan Baseline that forced the path we wanted would no longer work (though it might be reported as “applied”) because there is a new transformation that we had (necessarily) not been blocking.

The solution is easy: add the hint no_eliminate_sq(@subq1) to our query and try again.

We still get the full tablescan even though the hint report tells us that the optimizer used the new hint. Here’s the new Outline Data, and the Hint Report showing that the hint was used.

  Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$8C456B9A" "T1"@"SUBQ1")
      OUTLINE(@"SUBQ1")
      OUTLINE(@"MAIN")
      UNNEST(@"SUBQ1")
      OUTLINE(@"SEL$2FC9D0FE")
      ELIMINATE_JOIN(@"SEL$2FC9D0FE" "T1"@"MAIN")
      OUTLINE_LEAF(@"SEL$8C456B9A")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(UPPER("STR") LIKE '%CHD%')

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 7 (U - Unused (4))
---------------------------------------------------------------------------
   0 -  SUBQ1
           -  no_eliminate_sq(@subq1)
           -  qb_name(subq1)

   1 -  SEL$8C456B9A
         U -  leading(@sel$2fc9d0fe t1@subq1 t1@main)
           -  qb_name(main)

   1 -  SEL$8C456B9A / T1@MAIN
         U -  rowid(@sel$2fc9d0fe t1@main)
         U -  use_nl(@sel$2fc9d0fe t1@main)

   1 -  SEL$8C456B9A / T1@SUBQ1
         U -  index_ffs(@sel$2fc9d0fe t1@subq1(t1.str))

But now the Outline Data is showing us a new hint – eliminate_join(@sel$2fc9dofe t1@main). So we’re not losing the subquery, but we’ve lost the join thanks to a transformation that was actually available in 10.2 but presumably couldn’t be applied to our code pattern until at least 12.1. So let’s try again adding in another blocking hint no_eliminate_join(@sel$2fc9dofe t1@main).

We still get the full tablescan – and this time the Outline Data tells us that the problem hint is now eliminate_join(@sel$2fc9dofe t1@subq1) – which we might have anticipated, and now address by adding no_eliminate_join(@sel$2fc9dofe t1@subq1) to the query and having one more go. This finally gets us back to the path that we had previously seen in 11.1.0.7.

(In fact, adding the hint optimizer_features_enable(‘11.1.0.’) to the original set of hints would – in this case, at least – have been enough to get the original execution plan.)

Summary

This note is just another simple demonstration that hints do not guarantee plan stability across upgrades – and then showing that it can take a few experimental steps to discover what’s new in the optimizer that is making your previous set of hints ineffective.

Typically the problem will be the availability of new transformations (or enhancements to existing transformations) which manage to invalidate the old hints before the optimizer has had a chance to consider them. This is (to some extent) why a SQL Plan Baseline always captures the value of optimiser_features_enable() as part of the baseline.

Adaptive Joins

There’s a question on the Oracle Forums at the moment about a query that’s taking a long time to parse. Even after being reduced to one prebuilt (currently remote) table with two non-correlated outer joins to it the parse time is several hundred seconds. This seems fairly bizarre – I have seen some very long parse times from Oracle, but 10 minutes for 3 tables is well over the top; it did remind me, though of a note I started a few years ago of a 4 table join taking 4 seconds to parse, so I thought I’d present the query, the plan, and a little chat on debugging. Here’s the query:

select
        /*+ parallel(t4,3) */
        t1.small_vc,
        t2.small_vc,
        t3.small_vc,
        count(t4.small_vc)
from
        t4,     
        t1,     
        t2,     
        t3
where
        t1.id = t4.id1
and     t2.id = t4.id2
and     t3.id = t4.id3
and     t1.small_vc in (1,2,3)
and     t2.small_vc in (1,2,3,4)
and     t3.small_vc in (1,2,3,4,5)
group by
        t1.small_vc,
        t2.small_vc,
        t3.small_vc
;

I’m expecting a simple cascade of hash joins, with t1, t2 and t3 – the “small” tables – turning into “build” tables, then t4 – the “large” table – passing through each of them in turn until the penultimate rowsource is aggregated.

Here’s the execution plan — which looks pretty much as I expected it to – but there’s something wrong about it that isn’t visible in the output. Why is the query (plan) saying it took 0.07 seconds to complete (A-time), returning only 60 rows, when my SQL*Plus session didn’t return any data for 4 seconds

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Starts | E-Rows | Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |      1 |        |   300 (100)|          |        |      |            |     60 |00:00:00.07 |       5 |      0 |       |       |          |
|   1 |  PX COORDINATOR                |          |      1 |        |            |          |        |      |            |     60 |00:00:00.07 |       5 |      0 | 73728 | 73728 |          |
|   2 |   PX SEND QC (RANDOM)          | :TQ10004 |      0 |      1 |   300   (4)| 00:00:01 |  Q1,04 | P->S | QC (RAND)  |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|   3 |    HASH GROUP BY               |          |      3 |      1 |   300   (4)| 00:00:01 |  Q1,04 | PCWP |            |     60 |00:00:00.01 |       0 |      0 |  1394K|  1394K|     3/0/0|
|   4 |     PX RECEIVE                 |          |      3 |      1 |   300   (4)| 00:00:01 |  Q1,04 | PCWP |            |    180 |00:00:00.01 |       0 |      0 |       |       |          |
|   5 |      PX SEND HASH              | :TQ10003 |      0 |      1 |   300   (4)| 00:00:01 |  Q1,03 | P->P | HASH       |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|   6 |       HASH GROUP BY            |          |      3 |      1 |   300   (4)| 00:00:01 |  Q1,03 | PCWP |            |    180 |00:00:00.14 |    6114 |   6018 |  1394K|  1394K|     3/0/0|
|*  7 |        HASH JOIN               |          |      3 |   8460 |   299   (4)| 00:00:01 |  Q1,03 | PCWP |            |  12600 |00:00:00.14 |    6114 |   6018 |  2171K|  2171K|     3/0/0|
|   8 |         JOIN FILTER CREATE     | :BF0000  |      3 |     22 |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |     75 |00:00:00.02 |       0 |      0 |       |       |          |
|   9 |          PX RECEIVE            |          |      3 |     22 |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |     75 |00:00:00.02 |       0 |      0 |       |       |          |
|  10 |           PX SEND BROADCAST    | :TQ10000 |      0 |     22 |     2   (0)| 00:00:01 |  Q1,00 | S->P | BROADCAST  |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  11 |            PX SELECTOR         |          |      3 |        |            |          |  Q1,00 | SCWC |            |     25 |00:00:00.01 |       3 |      0 |       |       |          |
|* 12 |             TABLE ACCESS FULL  | T3       |      1 |     22 |     2   (0)| 00:00:01 |  Q1,00 | SCWP |            |     25 |00:00:00.01 |       3 |      0 |       |       |          |
|* 13 |         HASH JOIN              |          |      3 |  27322 |   297   (4)| 00:00:01 |  Q1,03 | PCWP |            |  12600 |00:00:00.10 |    6114 |   6018 |  2171K|  2171K|     3/0/0|
|  14 |          JOIN FILTER CREATE    | :BF0001  |      3 |     21 |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |     72 |00:00:00.01 |       0 |      0 |       |       |          |
|  15 |           PX RECEIVE           |          |      3 |     21 |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |     72 |00:00:00.01 |       0 |      0 |       |       |          |
|  16 |            PX SEND BROADCAST   | :TQ10001 |      0 |     21 |     2   (0)| 00:00:01 |  Q1,01 | S->P | BROADCAST  |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  17 |             PX SELECTOR        |          |      3 |        |            |          |  Q1,01 | SCWC |            |     24 |00:00:00.01 |       3 |      0 |       |       |          |
|* 18 |              TABLE ACCESS FULL | T2       |      1 |     21 |     2   (0)| 00:00:01 |  Q1,01 | SCWP |            |     24 |00:00:00.01 |       3 |      0 |       |       |          |
|* 19 |          HASH JOIN             |          |      3 |  92953 |   294   (3)| 00:00:01 |  Q1,03 | PCWP |            |  12600 |00:00:00.10 |    6114 |   6018 |  2171K|  2171K|     3/0/0|
|  20 |           JOIN FILTER CREATE   | :BF0002  |      3 |     19 |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |     63 |00:00:00.01 |       0 |      0 |       |       |          |
|  21 |            PX RECEIVE          |          |      3 |     19 |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |     63 |00:00:00.01 |       0 |      0 |       |       |          |
|  22 |             PX SEND BROADCAST  | :TQ10002 |      0 |     19 |     2   (0)| 00:00:01 |  Q1,02 | S->P | BROADCAST  |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  23 |              PX SELECTOR       |          |      3 |        |            |          |  Q1,02 | SCWC |            |     21 |00:00:00.01 |       3 |      0 |       |       |          |
|* 24 |               TABLE ACCESS FULL| T1       |      1 |     19 |     2   (0)| 00:00:01 |  Q1,02 | SCWP |            |     21 |00:00:00.01 |       3 |      0 |       |       |          |
|  25 |           JOIN FILTER USE      | :BF0000  |      3 |    343K|   289   (2)| 00:00:01 |  Q1,03 | PCWP |            |  19517 |00:00:00.09 |    6114 |   6018 |       |       |          |
|  26 |            JOIN FILTER USE     | :BF0001  |      3 |    343K|   289   (2)| 00:00:01 |  Q1,03 | PCWP |            |  19517 |00:00:00.09 |    6114 |   6018 |       |       |          |
|  27 |             JOIN FILTER USE    | :BF0002  |      3 |    343K|   289   (2)| 00:00:01 |  Q1,03 | PCWP |            |  19517 |00:00:00.09 |    6114 |   6018 |       |       |          |
|  28 |              PX BLOCK ITERATOR |          |      3 |    343K|   289   (2)| 00:00:01 |  Q1,03 | PCWC |            |  19517 |00:00:00.09 |    6114 |   6018 |       |       |          |
|* 29 |               TABLE ACCESS FULL| T4       |     48 |    343K|   289   (2)| 00:00:01 |  Q1,03 | PCWP |            |  19517 |00:00:00.06 |    6114 |   6018 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
  12 - SEL$1 / T3@SEL$1
  18 - SEL$1 / T2@SEL$1
  24 - SEL$1 / T1@SEL$1
  29 - SEL$1 / T4@SEL$1

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('_fix_control' '16923858:5')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      FULL(@"SEL$1" "T4"@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      FULL(@"SEL$1" "T3"@"SEL$1")
      LEADING(@"SEL$1" "T1"@"SEL$1" "T4"@"SEL$1" "T2"@"SEL$1" "T3"@"SEL$1")
      USE_HASH(@"SEL$1" "T4"@"SEL$1")
      USE_HASH(@"SEL$1" "T2"@"SEL$1")
      USE_HASH(@"SEL$1" "T3"@"SEL$1")
      PQ_DISTRIBUTE(@"SEL$1" "T4"@"SEL$1" BROADCAST NONE)
      PX_JOIN_FILTER(@"SEL$1" "T4"@"SEL$1")
      PQ_DISTRIBUTE(@"SEL$1" "T2"@"SEL$1" NONE BROADCAST)
      PX_JOIN_FILTER(@"SEL$1" "T2"@"SEL$1")
      PQ_DISTRIBUTE(@"SEL$1" "T3"@"SEL$1" NONE BROADCAST)
      PX_JOIN_FILTER(@"SEL$1" "T3"@"SEL$1")
      SWAP_JOIN_INPUTS(@"SEL$1" "T2"@"SEL$1")
      SWAP_JOIN_INPUTS(@"SEL$1" "T3"@"SEL$1")
      GBY_PUSHDOWN(@"SEL$1")
      USE_HASH_AGGREGATION(@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - access("T3"."ID"="T4"."ID3")
  12 - filter((TO_NUMBER("T3"."SMALL_VC")=1 OR TO_NUMBER("T3"."SMALL_VC")=2 OR TO_NUMBER("T3"."SMALL_VC")=3 OR TO_NUMBER("T3"."SMALL_VC")=4 OR TO_NUMBER("T3"."SMALL_VC")=5))
  13 - access("T2"."ID"="T4"."ID2")
  18 - filter((TO_NUMBER("T2"."SMALL_VC")=1 OR TO_NUMBER("T2"."SMALL_VC")=2 OR TO_NUMBER("T2"."SMALL_VC")=3 OR TO_NUMBER("T2"."SMALL_VC")=4))
  19 - access("T1"."ID"="T4"."ID1")
  24 - filter((TO_NUMBER("T1"."SMALL_VC")=1 OR TO_NUMBER("T1"."SMALL_VC")=2 OR TO_NUMBER("T1"."SMALL_VC")=3))
  29 - access(:Z>=:Z AND :Z<=:Z)
       filter(SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0002,"T4"."ID1"),SYS_OP_BLOOM_FILTER(:BF0001,"T4"."ID2"),SYS_OP_BLOOM_FILTER(:BF0000,"T4"."ID3")))

One possible trouble-shooting step is simply to re-run the query, taking a snapshot of the session activity stats (v$mystat) and the session events (v$session_event) to see what they tell you (if anything) – here’s the critical extract from the session stats:

Name                                      Value
----                                      -----
CPU used when call started                  423
CPU used by this session                    429
parse time cpu                              411
parse time elapsed                          419

Most of the time is parse time, spent on the CPU. (If this had been a much larger scale problem and had occurred in the recent past I might have looked at ASH (v$active_session_hsitory) for any samples for the correct SQL_ID, and seen the problem revealed in the in_parse, in_hard_parse columns.

So let’s enable event 10053 and run the query again – but since it’s “only” 4 seconds, let’s tweak the timer option to report any step that took longer than 0.1 seconds. The default timer setting is a whole second (10^⁶ microseconds), so we set the fix-control to 5 to get 0.1 seconds (10^⁵ microseconds).

alter session set "_fix_control"='16923858:5';
alter session set events '10053 trace name context forever';

-- run the query, find the trace file

 grep TIMER or19_ora_23370.trc 

Here’s the output from the call to grep: it looks like group by placement (GBP) is causing a problem.

TIMER:  GBP: costing SEL$1 cpu: 0.303 sec elapsed: 0.309 sec
TIMER: GBP: iteration (#1) SEL$1 cpu: 0.303 sec elapsed: 0.309 sec
TIMER:  GBP: costing SEL$1565E019 cpu: 0.293 sec elapsed: 0.298 sec
TIMER: GBP: iteration (#2) SEL$1565E019 cpu: 0.294 sec elapsed: 0.299 sec
TIMER:  GBP: costing SEL$23EAFE84 cpu: 0.528 sec elapsed: 0.533 sec
TIMER: GBP: iteration (#3) SEL$23EAFE84 cpu: 0.528 sec elapsed: 0.533 sec
TIMER:  GBP: costing SEL$B5D97CA0 cpu: 0.533 sec elapsed: 0.540 sec
TIMER: GBP: iteration (#4) SEL$B5D97CA0 cpu: 0.534 sec elapsed: 0.540 sec
TIMER:  GBP: costing SEL$6C9B46B6 cpu: 0.531 sec elapsed: 0.531 sec
TIMER: GBP: iteration (#5) SEL$6C9B46B6 cpu: 0.531 sec elapsed: 0.532 sec
TIMER:  GBP: costing SEL$ED1298E3 cpu: 0.522 sec elapsed: 0.523 sec
TIMER: GBP: iteration (#8) SEL$ED1298E3 cpu: 0.523 sec elapsed: 0.524 sec
TIMER:  GBP: costing SEL$5968095A cpu: 0.523 sec elapsed: 0.523 sec
TIMER: GBP: iteration (#9) SEL$5968095A cpu: 0.524 sec elapsed: 0.523 sec
TIMER:  GBP: costing SEL$4CA81688 cpu: 0.525 sec elapsed: 0.527 sec
TIMER: GBP: iteration (#12) SEL$4CA81688 cpu: 0.526 sec elapsed: 0.528 sec
TIMER: Group-By Placement SEL$1 cpu: 3.766 sec elapsed: 3.793 sec
TIMER: Cost-Based Transformations (Overall) SEL$1 cpu: 3.769 sec elapsed: 3.795 sec
TIMER: Access Path Analysis (Final) SEL$1 cpu: 0.288 sec elapsed: 0.289 sec
TIMER: SQL Optimization (Overall) SEL$1 cpu: 4.072 sec elapsed: 4.108 sec

If you check further up the page, though, you’ll see in the Outline Data that Oracle has not used group by placement so one quick hack we could try is to add the hint /*+ no_place_group_by(@sel$1) */ to the query just to see what happens – and here’s the effect on the parse time:

Name                                      Value
----                                      -----
parse time cpu                               33
parse time elapsed                           34

Problem solved – provided we can get the hint into the code (by hand, or SQL Patch, etc.)

Note you’ve probably spotted the gby_pushdown() hint in the Outline Data. That’s not “group by placement” it’s “group by pushdown” which relates to aggregation in parallel execution and explains why you see hash group by appearing at both operations 3 and 6, viz: before sending and after receiving. The optimizer thinks that aggregating before sending is worth doing to reduce the size of the rowsource that has to be sent to the next layer of PX processing.

The question still remains: where did the time go? The trace file was fairly long (375,000 lines for the original, compared to 32,000 for the hinted) but a rapid scan seemed in order – and something very quickly caught my attention. It was pretty easy to spot because something big and nasty had happened 8 times.

The answer was in “Adaptive Plans”, which (mostly) get flagged with the label “AP:” in the 10053 trace file, for example:

AP: Computing costs for inflection point at min value 0.00
AP: Using binary search for inflection point search
AP: Costing Join for ADM inflection point at card 0.00
AP: Costing Join for ADM inflection point at card 0.00

You can see here that the optimizer is searching for an “inflection point”, that means it’s “thinking about” an adaptive join, and searching for the number of rows where a switch between a nested loop join and a hash join makes sense.

Notice particularly the comment about “using binary search”. After calculating the cost of the “best” join using the current estimates of cardinality for the incoming rowsource the optimizer starts calculating the possible costs (nested loop or hash, it ignores merge) for a set of alternative cardinalities until it finds the cardinality where the nested loop join and hash join have the same cost. That’s the number of rows that Oracle will use at run time to decide whether it should switch from its initial selection of join method to the alternative.

If the initial join selection was a hash join (i.e. large volume startegy) Oracle will use an alternative cardinality of 1 to start its list of estimates – on the assumption that that would produce a nested loop plan and then keep doubling the estimate until the resulting plan switched to a hash join, then close in on the break point by halving and doubling the remaining gap between the NL estimate and the HJ estimate.

If the initial join selection was a nested loop join Oracle will use a “worst case scenario” for the incoming estimate (acting, broadly speaking, as if every filter at that point had had a selectivity of 1 – i.e. 100% of the available data), and start by halving the estimate. This is fine when the starting estimate is (for example) for the first table in the join order and the esimate is just a few million rows.

Here’s the first estimate in my case (you’ll need a wide screen, or very small print):

6772:AP: Computing costs for inflection point at max value 179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.00 

After you’ve halved that number about 1,000 times you’re down to values in the region of a few thousand. And when you have to cost a couple of joins every time you halve, and when you’ve gone through the whole process 8 times that’s a lot of CPU.

In fact, with the no_place_group_by hint there was still one point where the optimizer did this adaptive join work – which probably accounts for most of the final 0.33 CPU seconds – but it didn’t show up in any separately reported timer events.

Of course the final little test of this parse time hypothesis is to add the hint /*+ no_adaptive_plan */ – so I did that, and the parse time was still about 0.3 seconds! Investigation of the 10053 trace showed that even with the hint in place the optimizer still went through that one huge binary chop – but when it had worked out the inflection point it printed the message:

AP: Adaptive plans bypassed for query block SEL$1 due to disabled by hint (no_adaptive_plan)
AP: Adaptive joins bypassed for query block SEL$1 due to adaptive plans disabled

According to the hint report the hint was valid, however, so that behaviour looks a little buggy. It then occurred to me that maybe I could have hinted /*+ no_adaptive_plan(@sel$1) */ – and that worked properly with the trace reporting:

AP: Checking validity for query block SEL$1, sqlid=7fjtvwktcmsgq
AP: Adaptive plans bypassed for query block SEL$1 due to disabled by hint (no_adaptive_plan)
AP: Adaptive joins bypassed for query block SEL$1 due to adaptive plans disabled

Had I not realised that this was possible I have fallen back on the hint /*+ opt_param() */ to set the parameter optimizer_adaptive_plans to false for the query in which case the following message (and no other “AP:” message) appeared near the start of the trace:

AP: Adaptive joins bypassed for query block SEL$1 due to disabled by adaptive join parameter

If you want to experiment on your own system here’s the script to create the data – the script name reflects the fact that I found this example by accident while working on something completely different:

rem
rem     Script:         12c_vector_transform_c.sql
rem     Author:         Jonathan Lewis
rem     Dated:          June 2015
rem     Purpose:        
rem
rem     Last tested 
rem             19.11.0.0
rem             12.2.0.1
rem             12.1.0.2

drop table t1;
drop table t2;
drop table t3;
drop table t4;
purge recyclebin;

create table t1 
as
select
        rownum          id,
        to_char(mod(rownum,10)) small_vc,
        rpad('x',100)   padding
from
        all_objects
where
        rownum <= 70
;

alter table t1 
        add constraint t1_pk primary key(id)
;

create table t2
as
select
        rownum          id,
        to_char(mod(rownum,12)) small_vc,
        rpad('x',100)   padding
from
        all_objects
where
        rownum <= 70
;

alter table t2
        add constraint t2_pk primary key(id)
;

create table t3
as
select
        rownum          id,
        to_char(mod(rownum,14)) small_vc,
        rpad('x',100)   padding
from
        all_objects
where
        rownum <= 70
;

alter table t3
        add constraint t3_pk primary key(id)
;

create table t4
nologging
as
select
        t1.id                   id1,
        t2.id                   id2,
        t3.id                   id3,
        rpad(rownum,10)         small_vc,
        rpad('x',100)           padding
from
        t1, t2, t3
;

spool 12c_vector_transform_c

set timing on
set linesize 255
set trimspool on
set pagesize 90
set arraysize 100
set serveroutput off

execute snap_my_stats.start_snap

alter session set statistics_level = all;

alter session set "_fix_control"='16923858:5';
alter session set events '10053 trace name context forever';

select
        /*+ 
--              opt_param('optimizer_adaptive_plans' 'false')
--              no_adaptive_plan
--              no_adaptive_plan(@sel$1)
--              no_place_group_by(@sel$1)
                parallel(t4,3)
        */
        t1.small_vc,
        t2.small_vc,
        t3.small_vc,
        count(t4.small_vc)
from
        t4,     
        t1,     
        t2,     
        t3
where
        t1.id = t4.id1
and     t2.id = t4.id2
and     t3.id = t4.id3
and     t1.small_vc in (1,2,3)
and     t2.small_vc in (1,2,3,4)
and     t3.small_vc in (1,2,3,4,5)
group by
        t1.small_vc,
        t2.small_vc,
        t3.small_vc
;

select * from table(dbms_xplan.display_cursor(null,null,'cost outline allstats all note -bytes'));

alter session set events '10053 trace name context off';

set serveroutput on
execute snap_my_stats.end_snap

spool off

v$sql_hint

Here’s a quick and dirty script I thought I’d published a very long time ago. It has to be run as SYS and queries v$sql_hint to produce a list of all the things which (at least in theory) could be used as hints somewhere in the code.

I’ve ordered the list by the columns version then version_outline. I believe the version is supposed to be the version in which the hint first appeared (even if it wasn’t documented) and the version_outline is the version at which the hint could appear as part of a query’s “Outline Data” (which can then be used to generate a Stored Outline or SQL Plan Baseline – or SQL Patch). You’ll notice that a lot of the hints don’t have a version_outline.

One of the conveniences of this report is the appearance of the inverse column. For many hints there is an “opposite” hint. Hints often come in pairs (especially in recent versions of Oracle) following the pattern do_xxx / NO_do_xxx though there are many hints that don’t have an inverse and a few that don’t follow the pattern (e.g. use_concat / no_expand).

Perhaps one of the most useful parts of the report comes from the target_level which is a bitmap that can be decoded to show the query level(s) to which the hint can apply. There are 4 levels (corresponding to bits 0 to 3):

• statement,
• query block,
• object
• join

That’s a useful piece of information to know when you’re trying to make best use of hints; for example: the cardinality hint is one that is not well-known and most of the people who knew anything about it seemed to assume that it could apply only to a table when in fact it can also be used for a query block (which can be extremely useful with subquery factoring) or a multi-table join (although that option is quite hard to use effectively except in the simplest cases).

rem
rem     Script:         sql_hints.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Oct 2007
rem

set linesize 255
set pagesize 100
set trimspool on
set tab off

column  name            format a32
column  inverse         format a32
column  sql_feature     format a30
column  version         format a8
column  outline         format a8
column  class           format a35
column  target          format 999
column  prop            format 999

column  bit0            format a6
column  bit1            format a6
column  bit2            format a6
column  bit3            format a6

column  bit4            format a3
column  bit5            format a3
column  bit6            format a3
column  bit7            format a3
column  bit8            format a3

break on version skip 1
compute count of version on version

spool sql_hints

select
        version,
        version_outline         outline,
        name,
        inverse,
        sql_feature,
        class,
        decode(bitand(target_level,1),1,'State')        bit0,
        decode(bitand(target_level,2),2,'QBlock')       bit1,
        decode(bitand(target_level,4),4,'Object')       bit2,
        decode(bitand(target_level,8),8,'Join')         bit3,
        target_level                                    target,
        decode(bitand(property,16),16,'16')     bit4,
        decode(bitand(property,32),32,'32')     bit5,
        decode(bitand(property,64),64,'64')     bit6,
        decode(bitand(property,128),128,'128')  bit7,
        decode(bitand(property,256),256,'256')  bit8,
        property                                prop
from
        v$sql_hint
order by
        to_number(substr(version,1,3)),
        version,
        to_number(substr(version_outline,1,3)) nulls last,
        version_outline nulls last,
        name
;

spool off

Footnote

There’s also a property column in the view, which seems to be another bitmap but (at present) using only bits 4 to 8 of “something”. That points to the possibility that the level and property might be derived from the same couple of bytes but a quick check on x$qksht (the x$ structure underneath gv$sql_hint) shows two different columns (level_qkshtsyrow, props_qkshtsyrow) – with a 4 byte difference in their offsets. (This may be a fake, of course, since the address (addr) of every single “row” in the structure is the same, which means that when you’re looking at the x$ it’s been dynamically constructed from somewhere else.)

I haven’t yet worked out a consistent pattern for the bits in the property column, even though there are a number “coincidences” that look almost convincing, but I keep reporting the property – broken down by bits as well as the actual value – in this report in case one day I spot some clear correlation between the bits and the function of the hints.

If you’re interested in the results and don’t have access to v$sql_hint, here’s the output from 21.3.0.0. Of the 388 hints listed only about 120 are (officially) documented anywhere – there are a lot of hints you’re not supposed to use:

VERSION  OUTLINE  NAME                             INVERSE                          SQL_FEATURE                    CLASS                               BIT0   BIT1   BIT2   BIT3   TARGET BIT BIT BIT BIT BIT PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ --- --- --- --- --- ----
8.0.0    8.1.7    CLUSTER                                                           QKSFM_CBO                      ACCESS                                            Object             4 16              256  272
         8.1.7    INDEX                            NO_INDEX                         QKSFM_INDEX                    ACCESS                                            Object             4 16  32          256  304
         8.1.7    ROWID                                                             QKSFM_CBO                      ACCESS                                            Object             4 16              256  272
         10.1.0   NO_MERGE                         MERGE                            QKSFM_CVM                      MERGE                                      QBlock Object             6 16                    16
                  EXPR_CORR_CHECK                                                   QKSFM_CBO                      EXPR_CORR_CHECK                     State                            1                        0
                  MERGE_CONST_ON                                                    QKSFM_CBO                      MERGE_CONST_ON                      State                            1                        0
                  NL_AJ                                                             QKSFM_JOIN_METHOD              ANTIJOIN                                   QBlock                    2 16                    16
                  NL_SJ                                                             QKSFM_JOIN_METHOD              SEMIJOIN                                   QBlock                    2 16                    16
                  NO_MONITORING                                                     QKSFM_ALL                      NO_MONITORING                       State                            1                        0
                  NO_ORDER_ROLLUPS                                                  QKSFM_TRANSFORMATION           NO_ORDER_ROLLUPS                           QBlock                    2                        0
                  NO_STATS_GSETS                                                    QKSFM_ALL                      NO_STATS_GSETS                             QBlock                    2                        0
                  ORDERED_PREDICATES                                                QKSFM_CBO                      ORDERED_PREDICATES                         QBlock                    2 16                    16
                  QUEUE_CURR                                                        QKSFM_CBO                      ACCESS                                            Object             4                 256  256
                  QUEUE_ROWP                                                        QKSFM_CBO                      ACCESS                                            Object             4                 256  256
--------
      14

8.1.0    8.1.5    BITMAP                                                            QKSFM_CBO                      BITMAP                                     QBlock                    2                 256  256
         8.1.5    NO_PUSH_PRED                     PUSH_PRED                        QKSFM_FILTER_PUSH_PRED         PUSH_PRED                                  QBlock Object             6 16                    16
         8.1.5    PUSH_PRED                        NO_PUSH_PRED                     QKSFM_FILTER_PUSH_PRED         PUSH_PRED                                  QBlock Object             6 16                    16
         8.1.5    RULE                                                              QKSFM_RBO                      MODE                                State                            1 16                    16
         8.1.7    AND_EQUAL                                                         QKSFM_AND_EQUAL                ACCESS                                            Object             4 16  32          256  304
         8.1.7    DRIVING_SITE                                                      QKSFM_ALL                      DRIVING_SITE                                      Object             4                 256  256
         8.1.7    FACT                             NO_FACT                          QKSFM_STAR_TRANS               FACT                                              Object             4 16              256  272
         8.1.7    FULL                                                              QKSFM_FULL                     ACCESS                                            Object             4 16              256  272
         8.1.7    HASH                                                              QKSFM_ALL                      ACCESS                                            Object             4 16              256  272
         8.1.7    HASH_AJ                                                           QKSFM_JOIN_METHOD              ANTIJOIN                                   QBlock                    2 16                    16
         8.1.7    HASH_SJ                                                           QKSFM_JOIN_METHOD              SEMIJOIN                                   QBlock                    2 16                    16
         8.1.7    INDEX_COMBINE                                                     QKSFM_INDEX_COMBINE            ACCESS                                            Object             4 16  32      128 256  432
         8.1.7    INDEX_DESC                       NO_INDEX                         QKSFM_INDEX_DESC               ACCESS                                            Object             4 16  32          256  304
         8.1.7    INDEX_FFS                                                         QKSFM_INDEX_FFS                ACCESS                                            Object             4 16  32          256  304
         8.1.7    MERGE_AJ                                                          QKSFM_JOIN_METHOD              ANTIJOIN                                   QBlock                    2 16                    16
         8.1.7    MERGE_SJ                                                          QKSFM_JOIN_METHOD              SEMIJOIN                                   QBlock                    2 16                    16
         8.1.7    NO_EXPAND                        USE_CONCAT                       QKSFM_USE_CONCAT               OR_EXPAND                                  QBlock                    2 16                    16
         8.1.7    NO_FACT                          FACT                             QKSFM_STAR_TRANS               FACT                                              Object             4 16              256  272
         8.1.7    ORDERED                                                           QKSFM_CBO                      ORDERED                                    QBlock                    2 16                    16
         8.1.7    SEMIJOIN_DRIVER                                                   QKSFM_CBO                      SEMIJOIN_DRIVER                            QBlock                    2 16                    16
         8.1.7    STAR_TRANSFORMATION              NO_STAR_TRANSFORMATION           QKSFM_STAR_TRANS               STAR_TRANSFORMATION                        QBlock Object             6 16                    16
         8.1.7    SWAP_JOIN_INPUTS                 NO_SWAP_JOIN_INPUTS              QKSFM_CBO                      SWAP_JOIN_INPUTS                                  Object             4 16              256  272
         8.1.7    USE_CONCAT                       NO_EXPAND                        QKSFM_USE_CONCAT               OR_EXPAND                                  QBlock                    2 16                    16
         8.1.7    USE_HASH                         NO_USE_HASH                      QKSFM_USE_HASH                 JOIN                                              Object             4 16      64  128 256  464
         8.1.7    USE_MERGE                        NO_USE_MERGE                     QKSFM_USE_MERGE                JOIN                                              Object             4 16      64      256  336
         8.1.7    USE_NL                           NO_USE_NL                        QKSFM_USE_NL                   JOIN                                              Object             4 16      64      256  336
         10.1.0   MERGE                            NO_MERGE                         QKSFM_CVM                      MERGE                                      QBlock Object             6 16                    16
         10.2.0.1 ALL_ROWS                                                          QKSFM_ALL_ROWS                 MODE                                State                            1 16                    16
         10.2.0.1 FIRST_ROWS                                                        QKSFM_FIRST_ROWS               MODE                                State                            1 16                    16
         10.2.0.5 PUSH_SUBQ                        NO_PUSH_SUBQ                     QKSFM_TRANSFORMATION           PUSH_SUBQ                                  QBlock                    2 16                    16
                  APPEND                           NOAPPEND                         QKSFM_CBO                      APPEND                              State                            1                        0
                  CACHE                            NOCACHE                          QKSFM_EXECUTION                CACHE                                             Object             4                 256  256
                  CHOOSE                                                            QKSFM_CHOOSE                   MODE                                State                            1 16                    16
                  DEREF_NO_REWRITE                                                  QKSFM_ALL                      DEREF_NO_REWRITE                    State                            1                        0
                  INDEX_ASC                        NO_INDEX                         QKSFM_INDEX_ASC                ACCESS                                            Object             4 16  32          256  304
                  NESTED_TABLE_GET_REFS                                             QKSFM_ALL                      NESTED_TABLE_GET_REFS               State                            1                        0
                  NOAPPEND                         APPEND                           QKSFM_CBO                      APPEND                              State                            1                        0
                  NOCACHE                          CACHE                            QKSFM_EXECUTION                CACHE                                             Object             4                 256  256
                  NOPARALLEL                       SHARED                           QKSFM_PARALLEL                 SHARED                              State         Object             5                 256  256
                  NO_PARALLEL_INDEX                PARALLEL_INDEX                   QKSFM_PQ                       PARALLEL_INDEX                                    Object             4     32          256  288
                  PARALLEL_INDEX                   NO_PARALLEL_INDEX                QKSFM_PQ                       PARALLEL_INDEX                                    Object             4     32          256  288
                  PIV_GB                                                            QKSFM_ALL                      PIV_GB                                     QBlock                    2                        0
                  PIV_SSF                                                           QKSFM_ALL                      PIV_SSF                                    QBlock                    2                        0
                  REMOTE_MAPPED                                                     QKSFM_ALL                      REMOTE_MAPPED                              QBlock                    2 16              256  272
                  SHARED                           NO_PARALLEL                      QKSFM_PARALLEL                 SHARED                              State         Object             5                 256  256
                  STAR                                                              QKSFM_STAR_TRANS               STAR                                       QBlock                    2 16                    16
                  TIV_GB                                                            QKSFM_ALL                      PIV_GB                                     QBlock                    2                        0
                  TIV_SSF                                                           QKSFM_ALL                      PIV_SSF                                    QBlock                    2                        0
                  USE_ANTI                                                          QKSFM_CBO                      USE_ANTI                                          Object             4 16              256  272
                  USE_SEMI                                                          QKSFM_CBO                      USE_SEMI                                          Object             4 16              256  272
--------
      50

8.1.5    8.1.7    NO_ACCESS                                                         QKSFM_ALL                      NO_ACCESS                                         Object             4                 256  256
         8.1.7    NO_INDEX                         INDEX                            QKSFM_INDEX                    NO_INDEX                                          Object             4 16  32          256  304
         8.1.7    NO_REWRITE                       REWRITE                          QKSFM_TRANSFORMATION           REWRITE                                    QBlock                    2 16                    16
         8.1.7    PQ_DISTRIBUTE                                                     QKSFM_PQ_DISTRIBUTE            PQ_DISTRIBUTE                                     Object             4 16              256  272
         8.1.7    REWRITE                          NO_REWRITE                       QKSFM_TRANSFORMATION           REWRITE                                    QBlock                    2 16                    16
         10.1.0.3 INDEX_JOIN                                                        QKSFM_INDEX_JOIN               ACCESS                                            Object             4 16  32          256  304
         10.2.0.1 DOMAIN_INDEX_NO_SORT             DOMAIN_INDEX_SORT                QKSFM_CBO                      DOMAIN_INDEX_SORT                          QBlock                    2                        0
         10.2.0.1 DOMAIN_INDEX_SORT                DOMAIN_INDEX_NO_SORT             QKSFM_CBO                      DOMAIN_INDEX_SORT                          QBlock                    2                        0
                  BUFFER                           NO_BUFFER                        QKSFM_CBO                      BUFFER                                     QBlock                    2                        0
                  BYPASS_UJVC                                                       QKSFM_CBO                      BYPASS_UJVC                                QBlock                    2                        0
                  CACHE_CB                         NOCACHE                          QKSFM_CBO                      CACHE_CB                                          Object             4                 256  256
                  CUBE_GB                                                           QKSFM_CBO                      CUBE_GB                                    QBlock                    2                        0
                  NESTED_TABLE_SET_SETID                                            QKSFM_ALL                      NESTED_TABLE_SET_SETID              State                            1                        0
                  NO_BUFFER                        BUFFER                           QKSFM_CBO                      BUFFER                                     QBlock                    2                        0
                  RESTORE_AS_INTERVALS                                              QKSFM_CBO                      RESTORE_AS_INTERVALS                       QBlock                    2                        0
                  SAVE_AS_INTERVALS                                                 QKSFM_CBO                      SAVE_AS_INTERVALS                          QBlock                    2                        0
                  SCN_ASCENDING                                                     QKSFM_ALL                      SCN_ASCENDING                       State                            1                        0
--------
      17

8.1.6    10.1.0   NO_UNNEST                        UNNEST                           QKSFM_UNNEST                   UNNEST                                     QBlock                    2 16                    16
         10.1.0   UNNEST                           NO_UNNEST                        QKSFM_UNNEST                   UNNEST                                     QBlock                    2 16                    16
         10.1.0.3 LEADING                                                           QKSFM_JOIN_ORDER               LEADING                                                  Join        8 16              256  272
                  SYS_PARALLEL_TXN                                                  QKSFM_CBO                      SYS_PARALLEL_TXN                           QBlock                    2                        0
--------
       4


VERSION  OUTLINE  NAME                             INVERSE                          SQL_FEATURE                    CLASS                               BIT0   BIT1   BIT2   BIT3   TARGET BIT BIT BIT BIT BIT PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ --- --- --- --- --- ----
9.0.0    10.2.0.1 INDEX_SS                         NO_INDEX_SS                      QKSFM_INDEX_SS                 ACCESS                                            Object             4 16  32          256  304
         10.2.0.1 INDEX_SS_DESC                    NO_INDEX_SS                      QKSFM_INDEX_SS_DESC            ACCESS                                            Object             4 16  32          256  304
         10.2.0.1 PQ_MAP                           PQ_NOMAP                         QKSFM_PQ_MAP                   PQ_MAP                                            Object             4 16              256  272
         10.2.0.1 PQ_NOMAP                         PQ_MAP                           QKSFM_PQ_MAP                   PQ_MAP                                            Object             4 16              256  272
         18.1.0   INLINE                           MATERIALIZE                      QKSFM_TRANSFORMATION           INLINE                                     QBlock                    2 16                    16
         18.1.0   MATERIALIZE                      INLINE                           QKSFM_TRANSFORMATION           INLINE                                     QBlock                    2 16                    16
                  ANTIJOIN                                                          QKSFM_TRANSFORMATION           ANTIJOIN                                   QBlock                    2 16                    16
                  BYPASS_RECURSIVE_CHECK                                            QKSFM_ALL                      BYPASS_RECURSIVE_CHECK                     QBlock                    2                        0
                  CARDINALITY                                                       QKSFM_STATS                    CARDINALITY                                QBlock Object Join       14 16              256  272
                  CPU_COSTING                      NO_CPU_COSTING                   QKSFM_CPU_COSTING              CPU_COSTING                                QBlock                    2 16                    16
                  CURSOR_SHARING_EXACT                                              QKSFM_CBO                      CURSOR_SHARING_EXACT                       QBlock                    2                        0
                  DML_UPDATE                                                        QKSFM_CBO                      DML_UPDATE                          State                            1                        0
                  GBY_CONC_ROLLUP                                                   QKSFM_TRANSFORMATION           GBY_CONC_ROLLUP                            QBlock                    2                        0
                  HWM_BROKERED                                                      QKSFM_CBO                      HWM_BROKERED                               QBlock                    2                        0
                  INDEX_RRS                                                         QKSFM_CBO                      ACCESS                                            Object             4 16  32          256  304
                  INDEX_SS_ASC                     NO_INDEX_SS                      QKSFM_INDEX_SS_ASC             ACCESS                                            Object             4 16  32          256  304
                  LOCAL_INDEXES                                                     QKSFM_CBO                      LOCAL_INDEXES                              QBlock                    2                        0
                  MV_MERGE                                                          QKSFM_TRANSFORMATION           MV_MERGE                                   QBlock                    2                        0
                  NO_CPU_COSTING                   CPU_COSTING                      QKSFM_CPU_COSTING              CPU_COSTING                                QBlock                    2 16                    16
                  NO_PRUNE_GSETS                                                    QKSFM_TRANSFORMATION           NO_PRUNE_GSETS                             QBlock                    2                        0
                  NO_SEMIJOIN                      SEMIJOIN                         QKSFM_TRANSFORMATION           SEMIJOIN                                   QBlock                    2 16                    16
                  OVERFLOW_NOMOVE                                                   QKSFM_CBO                      OVERFLOW_NOMOVE                            QBlock                    2                        0
                  SEMIJOIN                         NO_SEMIJOIN                      QKSFM_TRANSFORMATION           SEMIJOIN                                   QBlock                    2 16                    16
                  SKIP_EXT_OPTIMIZER                                                QKSFM_CBO                      SKIP_EXT_OPTIMIZER                         QBlock                    2 16                    16
                  SQLLDR                                                            QKSFM_CBO                      SQLLDR                              State                            1                        0
                  USE_TTT_FOR_GSETS                                                 QKSFM_TRANSFORMATION           USE_TTT_FOR_GSETS                          QBlock                    2                        0
--------
      26

9.2.0    10.1.0   EXPAND_GSET_TO_UNION             NO_EXPAND_GSET_TO_UNION          QKSFM_TRANSFORMATION           EXPAND_GSET_TO_UNION                       QBlock                    2                        0
         10.1.0   NO_EXPAND_GSET_TO_UNION          EXPAND_GSET_TO_UNION             QKSFM_TRANSFORMATION           EXPAND_GSET_TO_UNION                       QBlock                    2                        0
         10.2.0.5 NO_PUSH_SUBQ                     PUSH_SUBQ                        QKSFM_TRANSFORMATION           PUSH_SUBQ                                  QBlock                    2 16                    16
         11.1.0.6 FORCE_XML_QUERY_REWRITE          NO_XML_QUERY_REWRITE             QKSFM_XML_REWRITE              FORCE_XML_QUERY_REWRITE             State                            1                        0
         11.1.0.6 NO_XML_QUERY_REWRITE             FORCE_XML_QUERY_REWRITE          QKSFM_XML_REWRITE              FORCE_XML_QUERY_REWRITE             State                            1                        0
                  DYNAMIC_SAMPLING                                                  QKSFM_DYNAMIC_SAMPLING         DYNAMIC_SAMPLING                           QBlock Object             6 16              256  272
                  DYNAMIC_SAMPLING_EST_CDN                                          QKSFM_DYNAMIC_SAMPLING_EST_CDN DYNAMIC_SAMPLING_EST_CDN                          Object             4 16              256  272
                  IGNORE_WHERE_CLAUSE                                               QKSFM_ALL                      IGNORE_WHERE_CLAUSE                 State                            1                        0
                  NO_QKN_BUFF                                                       QKSFM_CBO                      NO_QKN_BUFF                                QBlock                    2                        0
                  NO_REF_CASCADE                   REF_CASCADE_CURSOR               QKSFM_CBO                      REF_CASCADE_CURSOR                  State                            1                        0
                  REF_CASCADE_CURSOR               NO_REF_CASCADE                   QKSFM_CBO                      REF_CASCADE_CURSOR                  State                            1                        0
                  SYS_DL_CURSOR                                                     QKSFM_CBO                      SYS_DL_CURSOR                       State                            1                        0
                  SYS_RID_ORDER                                                     QKSFM_ALL                      SYS_RID_ORDER                              QBlock                    2                        0
--------
      13

10.1.0.3 10.1.0.3 NO_BASETABLE_MULTIMV_REWRITE     REWRITE                          QKSFM_ALL                      REWRITE                                    QBlock                    2 16                    16
         10.1.0.3 NO_INDEX_FFS                     INDEX_FFS                        QKSFM_INDEX_FFS                NO_INDEX_FFS                                      Object             4 16  32          256  304
         10.1.0.3 NO_INDEX_SS                      INDEX_SS                         QKSFM_INDEX_SS                 NO_INDEX_SS                                       Object             4 16  32          256  304
         10.1.0.3 NO_MULTIMV_REWRITE               REWRITE                          QKSFM_ALL                      REWRITE                                    QBlock                    2 16                    16
         10.1.0.3 NO_SET_TO_JOIN                   SET_TO_JOIN                      QKSFM_SET_TO_JOIN              SET_TO_JOIN                                QBlock                    2 16                    16
         10.1.0.3 NO_STAR_TRANSFORMATION           STAR_TRANSFORMATION              QKSFM_STAR_TRANS               STAR_TRANSFORMATION                        QBlock Object             6 16                    16
         10.1.0.3 NO_SWAP_JOIN_INPUTS              SWAP_JOIN_INPUTS                 QKSFM_CBO                      SWAP_JOIN_INPUTS                                  Object             4 16              256  272
         10.1.0.3 NO_USE_HASH                      USE_HASH                         QKSFM_USE_HASH                 NO_USE_HASH                                       Object             4 16      64      256  336
         10.1.0.3 NO_USE_MERGE                     USE_MERGE                        QKSFM_USE_MERGE                NO_USE_MERGE                                      Object             4 16      64      256  336
         10.1.0.3 NO_USE_NL                        USE_NL                           QKSFM_USE_NL                   NO_USE_NL                                         Object             4 16      64      256  336
         10.1.0.3 SET_TO_JOIN                      NO_SET_TO_JOIN                   QKSFM_SET_TO_JOIN              SET_TO_JOIN                                QBlock                    2 16                    16
         10.2.0.1 IGNORE_OPTIM_EMBEDDED_HINTS                                       QKSFM_ALL                      IGNORE_OPTIM_EMBEDDED_HINTS         State                            1                        0
         10.2.0.1 OPTIMIZER_FEATURES_ENABLE                                         QKSFM_ALL                      OPTIMIZER_FEATURES_ENABLE           State                            1 16              256  272
                  COLUMN_STATS                                                      QKSFM_STATS                    TABLE_STATS                         State                            1 16              256  272
                  FBTSCAN                                                           QKSFM_CBO                      FBTSCAN                             State                            1                        0
                  GATHER_PLAN_STATISTICS                                            QKSFM_GATHER_PLAN_STATISTICS   GATHER_PLAN_STATISTICS              State                            1                        0
                  INCLUDE_VERSION                                                   QKSFM_ALL                      INCLUDE_VERSION                     State                            1                        0
                  INDEX_STATS                                                       QKSFM_STATS                    TABLE_STATS                         State                            1 16              256  272
                  MODEL_DONTVERIFY_UNIQUENESS                                       QKSFM_TRANSFORMATION           MODEL_DONTVERIFY_UNIQUENESS                QBlock                    2                        0
                  MODEL_MIN_ANALYSIS                                                QKSFM_TRANSFORMATION           MODEL_MIN_ANALYSIS                         QBlock                    2                        0
                  MODEL_NO_ANALYSIS                                                 QKSFM_ALL                      MODEL_MIN_ANALYSIS                         QBlock                    2                        0
                  MODEL_PUSH_REF                   NO_MODEL_PUSH_REF                QKSFM_TRANSFORMATION           MODEL_PUSH_REF                             QBlock                    2                        0
                  NESTED_TABLE_FAST_INSERT                                          QKSFM_ALL                      NESTED_TABLE_FAST_INSERT            State                            1                        0
                  NO_MODEL_PUSH_REF                MODEL_PUSH_REF                   QKSFM_ALL                      MODEL_PUSH_REF                             QBlock                    2                        0
                  NO_PARALLEL                      SHARED                           QKSFM_CBO                      SHARED                              State         Object             5                 256  256
                  NO_PARTIAL_COMMIT                                                 QKSFM_CBO                      NO_PARTIAL_COMMIT                   State                            1                        0
                  NO_QUERY_TRANSFORMATION                                           QKSFM_TRANSFORMATION           NO_QUERY_TRANSFORMATION             State                            1 16                    16
                  OPAQUE_TRANSFORM                                                  QKSFM_TRANSFORMATION           OPAQUE_TRANSFORM                    State                            1                        0
                  OPAQUE_XCANONICAL                                                 QKSFM_TRANSFORMATION           OPAQUE_XCANONICAL                   State                            1                        0
                  OPT_ESTIMATE                                                      QKSFM_OPT_ESTIMATE             OPT_ESTIMATE                               QBlock Object Join       14 16              256  272
                  QB_NAME                                                           QKSFM_ALL                      QB_NAME                                    QBlock                    2                 256  256
                  RESTRICT_ALL_REF_CONS                                             QKSFM_ALL                      RESTRICT_ALL_REF_CONS               State                            1                        0
                  REWRITE_OR_ERROR                                                  QKSFM_TRANSFORMATION           REWRITE                                    QBlock                    2                        0
                  SKIP_UNQ_UNUSABLE_IDX                                             QKSFM_CBO                      SKIP_UNQ_UNUSABLE_IDX               State                            1                        0
                  STREAMS                                                           QKSFM_CBO                      STREAMS                             State                            1                        0
                  TABLE_STATS                                                       QKSFM_STATS                    TABLE_STATS                         State                            1 16              256  272
                  TRACING                                                           QKSFM_EXECUTION                TRACING                             State                            1                        0
                  USE_NL_WITH_INDEX                NO_USE_NL                        QKSFM_USE_NL_WITH_INDEX        USE_NL_WITH_INDEX                                 Object             4 16  32          256  304
                  USE_WEAK_NAME_RESL                                                QKSFM_ALL                      USE_WEAK_NAME_RESL                  State                            1                        0
                  VECTOR_READ                                                       QKSFM_CBO                      VECTOR_READ                         State                            1                        0
                  VECTOR_READ_TRACE                                                 QKSFM_CBO                      VECTOR_READ_TRACE                   State                            1                        0
                  X_DYN_PRUNE                                                       QKSFM_CBO                      X_DYN_PRUNE                                QBlock                    2                        0
--------
      42

10.2.0.1 10.2.0.1 BITMAP_TREE                                                       QKSFM_BITMAP_TREE              ACCESS                                            Object             4 16  32          256  304
         10.2.0.1 ELIMINATE_JOIN                   NO_ELIMINATE_JOIN                QKSFM_TABLE_ELIM               ELIMINATE_JOIN                                    Object             4 16                    16
         10.2.0.1 ELIMINATE_OBY                    NO_ELIMINATE_OBY                 QKSFM_OBYE                     ELIMINATE_OBY                              QBlock                    2 16                    16
         10.2.0.1 NO_ELIMINATE_JOIN                ELIMINATE_JOIN                   QKSFM_TABLE_ELIM               ELIMINATE_JOIN                                    Object             4 16                    16
         10.2.0.1 NO_ELIMINATE_OBY                 ELIMINATE_OBY                    QKSFM_OBYE                     ELIMINATE_OBY                              QBlock                    2 16                    16
         10.2.0.1 NO_PULL_PRED                     PULL_PRED                        QKSFM_PULL_PRED                PULL_PRED                                         Object             4 16                    16
         10.2.0.1 OLD_PUSH_PRED                                                     QKSFM_OLD_PUSH_PRED            OLD_PUSH_PRED                              QBlock Object             6 16                    16

VERSION  OUTLINE  NAME                             INVERSE                          SQL_FEATURE                    CLASS                               BIT0   BIT1   BIT2   BIT3   TARGET BIT BIT BIT BIT BIT PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ --- --- --- --- --- ----
10.2.0.1 10.2.0.1 OPT_PARAM                                                         QKSFM_ALL                      OPT_PARAM                           State                            1 16              256  272
         10.2.0.1 OUTLINE                                                           QKSFM_ALL                      OUTLINE                                    QBlock                    2                        0
         10.2.0.1 OUTLINE_LEAF                                                      QKSFM_ALL                      OUTLINE_LEAF                               QBlock                    2                        0
         10.2.0.1 PULL_PRED                        NO_PULL_PRED                     QKSFM_PULL_PRED                PULL_PRED                                         Object             4 16                    16
         10.2.0.1 RBO_OUTLINE                                                       QKSFM_RBO                      RBO_OUTLINE                         State                            1                        0
         10.2.0.5 NO_USE_HASH_AGGREGATION          USE_HASH_AGGREGATION             QKSFM_ALL                      USE_HASH_AGGREGATION                       QBlock                    2                        0
         10.2.0.5 USE_HASH_AGGREGATION             NO_USE_HASH_AGGREGATION          QKSFM_ALL                      USE_HASH_AGGREGATION                       QBlock                    2                        0
         11.1.0.6 NO_PX_JOIN_FILTER                PX_JOIN_FILTER                   QKSFM_PX_JOIN_FILTER           PX_JOIN_FILTER                                    Object             4 16      64      256  336
         11.1.0.6 NO_XML_DML_REWRITE                                                QKSFM_XML_REWRITE              NO_XML_DML_REWRITE                  State                            1                        0
         11.1.0.6 PX_JOIN_FILTER                   NO_PX_JOIN_FILTER                QKSFM_PX_JOIN_FILTER           PX_JOIN_FILTER                                    Object             4 16      64      256  336
                  DBMS_STATS                                                        QKSFM_DBMS_STATS               DBMS_STATS                          State                            1                        0
                  INLINE_XMLTYPE_NT                                                 QKSFM_ALL                      INLINE_XMLTYPE_NT                   State                            1                        0
                  MODEL_COMPILE_SUBQUERY                                            QKSFM_TRANSFORMATION           MODEL_COMPILE_SUBQUERY                     QBlock                    2                        0
                  MODEL_DYNAMIC_SUBQUERY                                            QKSFM_TRANSFORMATION           MODEL_DYNAMIC_SUBQUERY                     QBlock                    2                        0
                  NO_CARTESIAN                                                      QKSFM_ALL                      NO_CARTESIAN                                      Object             4 16      64      256  336
                  NO_SQL_TUNE                                                       QKSFM_ALL                      NO_SQL_TUNE                         State                            1                        0
                  PRECOMPUTE_SUBQUERY                                               QKSFM_TRANSFORMATION           PRECOMPUTE_SUBQUERY                        QBlock                    2                        0
                  PRESERVE_OID                                                      QKSFM_ALL                      PRESERVE_OID                        State                            1                        0
--------
      25

10.2.0.2 10.2.0.2 CONNECT_BY_COST_BASED            NO_CONNECT_BY_COST_BASED         QKSFM_TRANSFORMATION           CONNECT_BY_COST_BASED                      QBlock                    2 16                    16
         10.2.0.2 CONNECT_BY_FILTERING             NO_CONNECT_BY_FILTERING          QKSFM_ALL                      CONNECT_BY_FILTERING                       QBlock                    2 16                    16
         10.2.0.2 NO_CONNECT_BY_COST_BASED         CONNECT_BY_COST_BASED            QKSFM_TRANSFORMATION           CONNECT_BY_COST_BASED                      QBlock                    2 16                    16
         10.2.0.2 NO_CONNECT_BY_FILTERING          CONNECT_BY_FILTERING             QKSFM_ALL                      CONNECT_BY_FILTERING                       QBlock                    2 16                    16
--------
       4

10.2.0.3 10.2.0.3 NATIVE_FULL_OUTER_JOIN           NO_NATIVE_FULL_OUTER_JOIN        QKSFM_ALL                      NATIVE_FULL_OUTER_JOIN                     QBlock                    2 16                    16
         10.2.0.3 NO_NATIVE_FULL_OUTER_JOIN        NATIVE_FULL_OUTER_JOIN           QKSFM_ALL                      NATIVE_FULL_OUTER_JOIN                     QBlock                    2 16                    16
         10.2.0.3 NUM_INDEX_KEYS                                                    QKSFM_CBO                      ACCESS                                            Object             4 16  32          256  304
--------
       3

10.2.0.4 10.2.0.4 CONNECT_BY_COMBINE_SW            NO_CONNECT_BY_COMBINE_SW         QKSFM_ALL                      CONNECT_BY_COMBINE_SW                      QBlock                    2 16                    16
         10.2.0.4 NO_CONNECT_BY_COMBINE_SW         CONNECT_BY_COMBINE_SW            QKSFM_ALL                      CONNECT_BY_COMBINE_SW                      QBlock                    2 16                    16
--------
       2

10.2.0.5 10.2.0.5 CONNECT_BY_CB_WHR_ONLY           NO_CONNECT_BY_CB_WHR_ONLY        QKSFM_TRANSFORMATION           CONNECT_BY_CB_WHR_ONLY                     QBlock                    2 16                    16
         10.2.0.5 GBY_PUSHDOWN                     NO_GBY_PUSHDOWN                  QKSFM_ALL                      GBY_PUSHDOWN                               QBlock                    2 16                    16
         10.2.0.5 NO_CONNECT_BY_CB_WHR_ONLY        CONNECT_BY_CB_WHR_ONLY           QKSFM_TRANSFORMATION           CONNECT_BY_CB_WHR_ONLY                     QBlock                    2 16                    16
         10.2.0.5 NO_GBY_PUSHDOWN                  GBY_PUSHDOWN                     QKSFM_ALL                      GBY_PUSHDOWN                               QBlock                    2 16                    16
--------
       4

11.1.0.6 11.1.0.6 COST_XML_QUERY_REWRITE           NO_COST_XML_QUERY_REWRITE        QKSFM_COST_XML_QUERY_REWRITE   COST_XML_QUERY_REWRITE              State                            1                        0
         11.1.0.6 DB_VERSION                                                        QKSFM_ALL                      DB_VERSION                          State                            1 16              256  272
         11.1.0.6 DOMAIN_INDEX_FILTER              NO_DOMAIN_INDEX_FILTER           QKSFM_CBO                      DOMAIN_INDEX_FILTER                               Object             4 16  32          256  304
         11.1.0.6 INDEX_RS_ASC                                                      QKSFM_INDEX_RS_ASC             ACCESS                                            Object             4 16  32          256  304
         11.1.0.6 INDEX_RS_DESC                                                     QKSFM_INDEX_RS_DESC            ACCESS                                            Object             4 16  32          256  304
         11.1.0.6 NLJ_BATCHING                     NO_NLJ_BATCHING                  QKSFM_EXECUTION                ACCESS                                            Object             4 16              256  272
         11.1.0.6 NLJ_PREFETCH                     NO_NLJ_PREFETCH                  QKSFM_EXECUTION                NLJ_PREFETCH                                      Object             4 16              256  272
         11.1.0.6 NO_COST_XML_QUERY_REWRITE        COST_XML_QUERY_REWRITE           QKSFM_COST_XML_QUERY_REWRITE   NO_COST_XML_QUERY_REWRITE           State                            1                        0
         11.1.0.6 NO_DOMAIN_INDEX_FILTER           DOMAIN_INDEX_FILTER              QKSFM_CBO                      NO_DOMAIN_INDEX_FILTER                            Object             4 16  32          256  304
         11.1.0.6 NO_NLJ_BATCHING                  NLJ_BATCHING                     QKSFM_EXECUTION                ACCESS                                            Object             4 16              256  272
         11.1.0.6 NO_NLJ_PREFETCH                  NLJ_PREFETCH                     QKSFM_EXECUTION                NLJ_PREFETCH                                      Object             4 16              256  272
         11.1.0.6 NO_OUTER_JOIN_TO_INNER           OUTER_JOIN_TO_INNER              QKSFM_OUTER_JOIN_TO_INNER      OUTER_JOIN_TO_INNER                        QBlock Object             6 16                    16
         11.1.0.6 NO_PLACE_GROUP_BY                PLACE_GROUP_BY                   QKSFM_PLACE_GROUP_BY           PLACE_GROUP_BY                             QBlock                    2 16                    16
         11.1.0.6 NO_SUBQUERY_PRUNING              SUBQUERY_PRUNING                 QKSFM_CBO                      SUBQUERY_PRUNING                                  Object             4 16              256  272
         11.1.0.6 NO_USE_INVISIBLE_INDEXES         USE_INVISIBLE_INDEXES            QKSFM_INDEX                    USE_INVISIBLE_INDEXES               State                            1                        0
         11.1.0.6 NO_XMLINDEX_REWRITE              XMLINDEX_REWRITE                 QKSFM_XMLINDEX_REWRITE         XMLINDEX_REWRITE                    State                            1                        0
         11.1.0.6 NO_XMLINDEX_REWRITE_IN_SELECT    XMLINDEX_REWRITE_IN_SELECT       QKSFM_XMLINDEX_REWRITE         XMLINDEX_REWRITE                    State                            1                        0
         11.1.0.6 OUTER_JOIN_TO_INNER              NO_OUTER_JOIN_TO_INNER           QKSFM_OUTER_JOIN_TO_INNER      OUTER_JOIN_TO_INNER                        QBlock Object             6 16                    16
         11.1.0.6 PLACE_GROUP_BY                   NO_PLACE_GROUP_BY                QKSFM_PLACE_GROUP_BY           PLACE_GROUP_BY                             QBlock                    2 16                    16
         11.1.0.6 SUBQUERY_PRUNING                 NO_SUBQUERY_PRUNING              QKSFM_CBO                      SUBQUERY_PRUNING                                  Object             4 16              256  272
         11.1.0.6 USE_INVISIBLE_INDEXES            NO_USE_INVISIBLE_INDEXES         QKSFM_INDEX                    USE_INVISIBLE_INDEXES               State                            1                        0
         11.1.0.6 USE_MERGE_CARTESIAN                                               QKSFM_USE_MERGE_CARTESIAN      JOIN                                              Object             4 16      64      256  336
         11.1.0.6 XMLINDEX_REWRITE                 NO_XMLINDEX_REWRITE              QKSFM_XMLINDEX_REWRITE         XMLINDEX_REWRITE                    State                            1                        0
         11.1.0.6 XMLINDEX_REWRITE_IN_SELECT       NO_XMLINDEX_REWRITE_IN_SELECT    QKSFM_XMLINDEX_REWRITE         XMLINDEX_REWRITE                    State                            1                        0
         11.1.0.6 XML_DML_RWT_STMT                                                  QKSFM_XML_REWRITE              XML_DML_RWT_STMT                    State                            1                        0
                  CHECK_ACL_REWRITE                NO_CHECK_ACL_REWRITE             QKSFM_CHECK_ACL_REWRITE        CHECK_ACL_REWRITE                   State                            1                        0
                  MONITOR                          NO_MONITOR                       QKSFM_ALL                      MONITOR                             State                            1                        0
                  NO_CHECK_ACL_REWRITE             CHECK_ACL_REWRITE                QKSFM_CHECK_ACL_REWRITE        NO_CHECK_ACL_REWRITE                State                            1                        0
                  NO_LOAD                                                           QKSFM_EXECUTION                NO_LOAD                             State                            1                        0
                  NO_MONITOR                       MONITOR                          QKSFM_ALL                      MONITOR                             State                            1                        0
                  NO_RESULT_CACHE                  RESULT_CACHE                     QKSFM_EXECUTION                RESULT_CACHE                               QBlock                    2                        0
                  RESULT_CACHE                     NO_RESULT_CACHE                  QKSFM_EXECUTION                RESULT_CACHE                               QBlock                    2                        0
--------
      32

11.1.0.7          BIND_AWARE                       NO_BIND_AWARE                    QKSFM_CURSOR_SHARING           BIND_AWARE                          State                            1                        0
                  CHANGE_DUPKEY_ERROR_INDEX                                         QKSFM_DML                      CHANGE_DUPKEY_ERROR_INDEX                         Object             4     32          256  288
                  IGNORE_ROW_ON_DUPKEY_INDEX                                        QKSFM_DML                      IGNORE_ROW_ON_DUPKEY_INDEX                        Object             4     32          256  288
                  NO_BIND_AWARE                    BIND_AWARE                       QKSFM_CURSOR_SHARING           BIND_AWARE                          State                            1                        0
                  RETRY_ON_ROW_CHANGE                                               QKSFM_DML                      RETRY_ON_ROW_CHANGE                 State                            1                        0
--------
       5

11.2.0.1 11.2.0.1 COALESCE_SQ                      NO_COALESCE_SQ                   QKSFM_COALESCE_SQ              COALESCE_SQ                                QBlock                    2 16                    16
         11.2.0.1 CONNECT_BY_ELIM_DUPS             NO_CONNECT_BY_ELIM_DUPS          QKSFM_ALL                      CONNECT_BY_ELIM_DUPS                       QBlock                    2 16                    16
         11.2.0.1 EXPAND_TABLE                     NO_EXPAND_TABLE                  QKSFM_TABLE_EXPANSION          EXPAND_TABLE                                      Object             4 16                    16
         11.2.0.1 FACTORIZE_JOIN                   NO_FACTORIZE_JOIN                QKSFM_JOINFAC                  FACTORIZE_JOIN                             QBlock                    2 16                    16
         11.2.0.1 NO_COALESCE_SQ                   COALESCE_SQ                      QKSFM_COALESCE_SQ              COALESCE_SQ                                QBlock                    2 16                    16
         11.2.0.1 NO_CONNECT_BY_ELIM_DUPS          CONNECT_BY_ELIM_DUPS             QKSFM_ALL                      CONNECT_BY_ELIM_DUPS                       QBlock                    2 16                    16
         11.2.0.1 NO_EXPAND_TABLE                  EXPAND_TABLE                     QKSFM_TABLE_EXPANSION          EXPAND_TABLE                                      Object             4 16                    16
         11.2.0.1 NO_FACTORIZE_JOIN                FACTORIZE_JOIN                   QKSFM_JOINFAC                  FACTORIZE_JOIN                             QBlock                    2 16                    16

VERSION  OUTLINE  NAME                             INVERSE                          SQL_FEATURE                    CLASS                               BIT0   BIT1   BIT2   BIT3   TARGET BIT BIT BIT BIT BIT PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ --- --- --- --- --- ----
11.2.0.1 11.2.0.1 NO_PLACE_DISTINCT                PLACE_DISTINCT                   QKSFM_DIST_PLCMT               PLACE_DISTINCT                             QBlock                    2 16                    16
         11.2.0.1 NO_TRANSFORM_DISTINCT_AGG        TRANSFORM_DISTINCT_AGG           QKSFM_TRANSFORMATION           TRANSFORM_DISTINCT_AGG                     QBlock                    2                        0
         11.2.0.1 PLACE_DISTINCT                   NO_PLACE_DISTINCT                QKSFM_DIST_PLCMT               PLACE_DISTINCT                             QBlock                    2 16                    16
         11.2.0.1 TRANSFORM_DISTINCT_AGG           NO_TRANSFORM_DISTINCT_AGG        QKSFM_TRANSFORMATION           TRANSFORM_DISTINCT_AGG                     QBlock                    2                        0
         21.1.0.1 NO_DST_UPGRADE_INSERT_CONV       DST_UPGRADE_INSERT_CONV          QKSFM_ALL                      DST_UPGRADE_INSERT_CONV             State                            1                        0
                  APPEND_VALUES                    NOAPPEND                         QKSFM_CBO                      APPEND_VALUES                       State                            1                        0
                  DST_UPGRADE_INSERT_CONV          NO_DST_UPGRADE_INSERT_CONV       QKSFM_ALL                      DST_UPGRADE_INSERT_CONV             State                            1                        0
                  NO_STATEMENT_QUEUING             STATEMENT_QUEUING                QKSFM_PARALLEL                 STATEMENT_QUEUING                   State                            1                        0
                  NO_SUBSTRB_PAD                                                    QKSFM_EXECUTION                NO_SUBSTRB_PAD                      State                            1                        0
                  STATEMENT_QUEUING                NO_STATEMENT_QUEUING             QKSFM_PARALLEL                 STATEMENT_QUEUING                   State                            1                        0
                  XMLINDEX_SEL_IDX_TBL                                              QKSFM_ALL                      XMLINDEX_SEL_IDX_TBL                State                            1                        0
--------
      19

11.2.0.2 11.2.0.2 NO_TABLE_LOOKUP_BY_NL            TABLE_LOOKUP_BY_NL               QKSFM_TABLE_LOOKUP_BY_NL       TABLE_LOOKUP_BY_NL                                Object             4 16                    16
         11.2.0.2 NO_USE_HASH_GBY_FOR_PUSHDOWN     USE_HASH_GBY_FOR_PUSHDOWN        QKSFM_ALL                      USE_HASH_GBY_FOR_PUSHDOWN                  QBlock                    2                        0
         11.2.0.2 TABLE_LOOKUP_BY_NL               NO_TABLE_LOOKUP_BY_NL            QKSFM_TABLE_LOOKUP_BY_NL       TABLE_LOOKUP_BY_NL                                Object             4 16                    16
         11.2.0.2 USE_HASH_GBY_FOR_PUSHDOWN        NO_USE_HASH_GBY_FOR_PUSHDOWN     QKSFM_ALL                      USE_HASH_GBY_FOR_PUSHDOWN                  QBlock                    2                        0
                  NO_XDB_FASTPATH_INSERT           XDB_FASTPATH_INSERT              QKSFM_ALL                      XDB_FASTPATH_INSERT                 State                            1                        0
                  XDB_FASTPATH_INSERT              NO_XDB_FASTPATH_INSERT           QKSFM_ALL                      XDB_FASTPATH_INSERT                 State                            1                        0
--------
       6

11.2.0.3 11.2.0.3 FULL_OUTER_JOIN_TO_OUTER         NO_FULL_OUTER_JOIN_TO_OUTER      QKSFM_CBO                      FULL_OUTER_JOIN_TO_OUTER                          Object             4 16              256  272
         11.2.0.3 NO_FULL_OUTER_JOIN_TO_OUTER      FULL_OUTER_JOIN_TO_OUTER         QKSFM_CBO                      FULL_OUTER_JOIN_TO_OUTER                          Object             4 16              256  272
         11.2.0.3 NO_OUTER_JOIN_TO_ANTI            OUTER_JOIN_TO_ANTI               QKSFM_CBO                      OUTER_JOIN_TO_ANTI                                Object             4 16              256  272
         11.2.0.3 NO_SEMI_TO_INNER                 SEMI_TO_INNER                    QKSFM_CBO                      NO_SEMI_TO_INNER                                  Object             4 16              256  272
         11.2.0.3 OUTER_JOIN_TO_ANTI               NO_OUTER_JOIN_TO_ANTI            QKSFM_CBO                      OUTER_JOIN_TO_ANTI                                Object             4 16              256  272
         11.2.0.3 SEMI_TO_INNER                    NO_SEMI_TO_INNER                 QKSFM_CBO                      SEMI_TO_INNER                                     Object             4 16              256  272
--------
       6

11.2.0.4          DISABLE_PARALLEL_DML             ENABLE_PARALLEL_DML              QKSFM_DML                      ENABLE_PARALLEL_DML                 State                            1                        0
                  ENABLE_PARALLEL_DML              DISABLE_PARALLEL_DML             QKSFM_DML                      ENABLE_PARALLEL_DML                 State                            1                        0
--------
       2

12.1.0.1 12.1.0.1 BATCH_TABLE_ACCESS_BY_ROWID      NO_BATCH_TABLE_ACCESS_BY_ROWID   QKSFM_EXECUTION                BATCH_TABLE_ACCESS_BY_ROWID                       Object             4 16              256  272
         12.1.0.1 BITMAP_AND                                                        QKSFM_BITMAP_TREE              BITMAP_AND                                        Object             4 16  32                48
         12.1.0.1 CLUSTERING                       NO_CLUSTERING                    QKSFM_CLUSTERING               CLUSTERING                          State                            1                        0
         12.1.0.1 CLUSTER_BY_ROWID                 NO_CLUSTER_BY_ROWID              QKSFM_CBO                      CLUSTER_BY_ROWID                                  Object             4 16              256  272
         12.1.0.1 CUBE_AJ                                                           QKSFM_JOIN_METHOD              ANTIJOIN                                   QBlock                    2 16                    16
         12.1.0.1 CUBE_SJ                                                           QKSFM_JOIN_METHOD              SEMIJOIN                                   QBlock                    2 16                    16
         12.1.0.1 DATA_SECURITY_REWRITE_LIMIT      NO_DATA_SECURITY_REWRITE         QKSFM_DATA_SECURITY_REWRITE    DATA_SECURITY_REWRITE_LIMIT         State                            1                        0
         12.1.0.1 DECORRELATE                      NO_DECORRELATE                   QKSFM_DECORRELATE              DECORRELATE                                QBlock                    2 16                    16
         12.1.0.1 NO_BATCH_TABLE_ACCESS_BY_ROWID   BATCH_TABLE_ACCESS_BY_ROWID      QKSFM_EXECUTION                BATCH_TABLE_ACCESS_BY_ROWID                       Object             4 16              256  272
         12.1.0.1 NO_CLUSTERING                    CLUSTERING                       QKSFM_CLUSTERING               CLUSTERING                          State                            1                        0
         12.1.0.1 NO_CLUSTER_BY_ROWID              CLUSTER_BY_ROWID                 QKSFM_CBO                      CLUSTER_BY_ROWID                                  Object             4 16              256  272
         12.1.0.1 NO_DATA_SECURITY_REWRITE         DATA_SECURITY_REWRITE_LIMIT      QKSFM_DATA_SECURITY_REWRITE    DATA_SECURITY_REWRITE_LIMIT         State                            1                        0
         12.1.0.1 NO_DECORRELATE                   DECORRELATE                      QKSFM_DECORRELATE              DECORRELATE                                QBlock                    2 16                    16
         12.1.0.1 NO_PARTIAL_JOIN                  PARTIAL_JOIN                     QKSFM_PARTIAL_JOIN             PARTIAL_JOIN                                      Object             4 16              256  272
         12.1.0.1 NO_PARTIAL_ROLLUP_PUSHDOWN       PARTIAL_ROLLUP_PUSHDOWN          QKSFM_PQ                       PARTIAL_ROLLUP_PUSHDOWN                    QBlock                    2 16                    16
         12.1.0.1 NO_PQ_CONCURRENT_UNION           PQ_CONCURRENT_UNION              QKSFM_PQ                       PQ_CONCURRENT_UNION                 State  QBlock                    3                        0
         12.1.0.1 NO_PQ_REPLICATE                  PQ_REPLICATE                     QKSFM_PQ_REPLICATE             PQ_REPLICATE                                      Object             4 16              256  272
         12.1.0.1 NO_PQ_SKEW                       PQ_SKEW                          QKSFM_PQ                       PQ_SKEW                                           Object             4 16              256  272
         12.1.0.1 NO_PX_FAULT_TOLERANCE            PX_FAULT_TOLERANCE               QKSFM_PQ                       PX_FAULT_TOLERANCE                  State                            1                        0
         12.1.0.1 NO_USE_CUBE                      USE_CUBE                         QKSFM_USE_CUBE                 JOIN                                              Object             4 16      64      256  336
         12.1.0.1 NO_ZONEMAP                       ZONEMAP                          QKSFM_ZONEMAP                  ZONEMAP                                           Object             4                 256  256
         12.1.0.1 PARTIAL_JOIN                     NO_PARTIAL_JOIN                  QKSFM_PARTIAL_JOIN             PARTIAL_JOIN                                      Object             4 16              256  272
         12.1.0.1 PARTIAL_ROLLUP_PUSHDOWN          NO_PARTIAL_ROLLUP_PUSHDOWN       QKSFM_PQ                       PARTIAL_ROLLUP_PUSHDOWN                    QBlock                    2 16                    16
         12.1.0.1 PQ_CONCURRENT_UNION              NO_PQ_CONCURRENT_UNION           QKSFM_PQ                       PQ_CONCURRENT_UNION                 State  QBlock                    3                        0
         12.1.0.1 PQ_DISTRIBUTE_WINDOW                                              QKSFM_PQ                       PQ_DISTRIBUTE_WINDOW                       QBlock                    2 16                    16
         12.1.0.1 PQ_FILTER                                                         QKSFM_PQ                       PQ_FILTER                                  QBlock                    2                        0
         12.1.0.1 PQ_REPLICATE                     NO_PQ_REPLICATE                  QKSFM_PQ_REPLICATE             PQ_REPLICATE                                      Object             4 16              256  272
         12.1.0.1 PQ_SKEW                          NO_PQ_SKEW                       QKSFM_PQ                       PQ_SKEW                                           Object             4 16              256  272
         12.1.0.1 PX_FAULT_TOLERANCE               NO_PX_FAULT_TOLERANCE            QKSFM_PQ                       PX_FAULT_TOLERANCE                  State                            1                        0
         12.1.0.1 USE_CUBE                         NO_USE_CUBE                      QKSFM_USE_CUBE                 JOIN                                              Object             4 16      64      256  336
         12.1.0.1 ZONEMAP                          NO_ZONEMAP                       QKSFM_ZONEMAP                  ZONEMAP                                           Object             4                 256  256
                  AUTO_REOPTIMIZE                  NO_AUTO_REOPTIMIZE               QKSFM_AUTO_REOPT               AUTO_REOPTIMIZE                     State                            1                        0
                  GATHER_OPTIMIZER_STATISTICS      NO_GATHER_OPTIMIZER_STATISTICS   QKSFM_DBMS_STATS               GATHER_OPTIMIZER_STATISTICS         State                            1                        0
                  NO_AUTO_REOPTIMIZE               AUTO_REOPTIMIZE                  QKSFM_AUTO_REOPT               AUTO_REOPTIMIZE                     State                            1                        0
                  NO_GATHER_OPTIMIZER_STATISTICS   GATHER_OPTIMIZER_STATISTICS      QKSFM_DBMS_STATS               GATHER_OPTIMIZER_STATISTICS         State                            1                        0
                  USE_HIDDEN_PARTITIONS                                             QKSFM_PARTITION                USE_HIDDEN_PARTITIONS                      QBlock                    2                        0
                  WITH_PLSQL                                                        QKSFM_ALL                      WITH_PLSQL                          State                            1                        0
--------
      37

12.1.0.2 12.1.0.2 ADAPTIVE_PLAN                    NO_ADAPTIVE_PLAN                 QKSFM_ADAPTIVE_PLAN            ADAPTIVE_PLAN                       State                            1 16                    16
         12.1.0.2 ANSI_REARCH                      NO_ANSI_REARCH                   QKSFM_ANSI_REARCH              ANSI_REARCH                                QBlock                    2 16                    16
         12.1.0.2 ELIM_GROUPBY                     NO_ELIM_GROUPBY                  QKSFM_TRANSFORMATION           ELIM_GROUPBY                               QBlock                    2 16                    16
         12.1.0.2 INMEMORY                         NO_INMEMORY                      QKSFM_EXECUTION                INMEMORY                                   QBlock Object             6         64            64
         12.1.0.2 INMEMORY_PRUNING                 NO_INMEMORY_PRUNING              QKSFM_EXECUTION                INMEMORY_PRUNING                           QBlock Object             6         64            64
         12.1.0.2 NO_ADAPTIVE_PLAN                 ADAPTIVE_PLAN                    QKSFM_ADAPTIVE_PLAN            ADAPTIVE_PLAN                       State                            1 16                    16
         12.1.0.2 NO_ANSI_REARCH                   ANSI_REARCH                      QKSFM_ANSI_REARCH              ANSI_REARCH                                QBlock                    2 16                    16
         12.1.0.2 NO_ELIM_GROUPBY                  ELIM_GROUPBY                     QKSFM_TRANSFORMATION           ELIM_GROUPBY                               QBlock                    2 16                    16
         12.1.0.2 NO_INMEMORY                      INMEMORY                         QKSFM_EXECUTION                INMEMORY                                   QBlock Object             6         64            64
         12.1.0.2 NO_INMEMORY_PRUNING              INMEMORY_PRUNING                 QKSFM_EXECUTION                INMEMORY_PRUNING                           QBlock Object             6         64            64
         12.1.0.2 NO_USE_VECTOR_AGGREGATION        USE_VECTOR_AGGREGATION           QKSFM_VECTOR_AGG               USE_VECTOR_AGGREGATION                     QBlock                    2 16                    16
         12.1.0.2 NO_VECTOR_TRANSFORM              VECTOR_TRANSFORM                 QKSFM_VECTOR_AGG               VECTOR_TRANSFORM                           QBlock                    2 16                    16
         12.1.0.2 NO_VECTOR_TRANSFORM_DIMS         VECTOR_TRANSFORM_DIMS            QKSFM_VECTOR_AGG               VECTOR_TRANSFORM_DIMS                             Object             4 16      64            80
         12.1.0.2 NO_VECTOR_TRANSFORM_FACT         VECTOR_TRANSFORM_FACT            QKSFM_VECTOR_AGG               VECTOR_TRANSFORM_FACT                             Object             4 16      64            80
         12.1.0.2 USE_VECTOR_AGGREGATION           NO_USE_VECTOR_AGGREGATION        QKSFM_VECTOR_AGG               USE_VECTOR_AGGREGATION                     QBlock                    2 16                    16
         12.1.0.2 VECTOR_TRANSFORM                 NO_VECTOR_TRANSFORM              QKSFM_VECTOR_AGG               VECTOR_TRANSFORM                           QBlock                    2 16                    16
         12.1.0.2 VECTOR_TRANSFORM_DIMS            NO_VECTOR_TRANSFORM_DIMS         QKSFM_VECTOR_AGG               VECTOR_TRANSFORM_DIMS                             Object             4 16      64            80
         12.1.0.2 VECTOR_TRANSFORM_FACT            NO_VECTOR_TRANSFORM_FACT         QKSFM_VECTOR_AGG               VECTOR_TRANSFORM_FACT                             Object             4 16      64            80
                  RESERVOIR_SAMPLING                                                QKSFM_EXECUTION                RESERVOIR_SAMPLING                  State                            1                        0
--------

VERSION  OUTLINE  NAME                             INVERSE                          SQL_FEATURE                    CLASS                               BIT0   BIT1   BIT2   BIT3   TARGET BIT BIT BIT BIT BIT PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ --- --- --- --- --- ----
      19

12.2.0.1 12.2.0.1 BUSHY_JOIN                       NO_BUSHY_JOIN                    QKSFM_BUSHY_JOIN               BUSHY_JOIN                                 QBlock                    2 16                    16
         12.2.0.1 CONTAINERS                                                        QKSFM_ALL                      CONTAINERS                          State                            1                        0
         12.2.0.1 DIST_AGG_PROLLUP_PUSHDOWN        NO_DIST_AGG_PROLLUP_PUSHDOWN     QKSFM_PQ                       DIST_AGG_PROLLUP_PUSHDOWN                  QBlock                    2 16                    16
         12.2.0.1 ELIMINATE_SQ                     NO_ELIMINATE_SQ                  QKSFM_ELIMINATE_SQ             ELIMINATE_SQ                               QBlock                    2 16                    16
         12.2.0.1 NO_BUSHY_JOIN                    BUSHY_JOIN                       QKSFM_BUSHY_JOIN               BUSHY_JOIN                                 QBlock                    2 16                    16
         12.2.0.1 NO_DIST_AGG_PROLLUP_PUSHDOWN     DIST_AGG_PROLLUP_PUSHDOWN        QKSFM_PQ                       DIST_AGG_PROLLUP_PUSHDOWN                  QBlock                    2 16                    16
         12.2.0.1 NO_ELIMINATE_SQ                  ELIMINATE_SQ                     QKSFM_ELIMINATE_SQ             ELIMINATE_SQ                               QBlock                    2 16                    16
         12.2.0.1 NO_OR_EXPAND                     OR_EXPAND                        QKSFM_CBQT_OR_EXPANSION        OR_EXPAND                                  QBlock                    2 16                    16
         12.2.0.1 NO_USE_DAGG_UNION_ALL_GSETS      USE_DAGG_UNION_ALL_GSETS         QKSFM_GROUPING_SET_XFORM       DAGG_OPTIM_GSETS                           QBlock                    2                        0
         12.2.0.1 NO_USE_HASH_GBY_FOR_DAGGPSHD     USE_HASH_GBY_FOR_DAGGPSHD        QKSFM_ALL                      USE_HASH_GBY_FOR_DAGGPSHD                  QBlock                    2                        0
         12.2.0.1 NO_USE_PARTITION_WISE_DISTINCT   USE_PARTITION_WISE_DISTINCT      QKSFM_PARTITION                USE_PARTITION_WISE_DISTINCT                QBlock                    2                        0
         12.2.0.1 NO_USE_PARTITION_WISE_GBY        USE_PARTITION_WISE_GBY           QKSFM_PARTITION                USE_PARTITION_WISE_GBY                     QBlock                    2                        0
         12.2.0.1 ORDER_SUBQ                                                        QKSFM_TRANSFORMATION           ORDER_SUBQ                                 QBlock                    2 16                    16
         12.2.0.1 OR_EXPAND                        NO_OR_EXPAND                     QKSFM_CBQT_OR_EXPANSION        OR_EXPAND                                  QBlock                    2 16                    16
         12.2.0.1 USE_DAGG_UNION_ALL_GSETS         NO_USE_DAGG_UNION_ALL_GSETS      QKSFM_GROUPING_SET_XFORM       DAGG_OPTIM_GSETS                           QBlock                    2                        0
         12.2.0.1 USE_HASH_GBY_FOR_DAGGPSHD        NO_USE_HASH_GBY_FOR_DAGGPSHD     QKSFM_ALL                      USE_HASH_GBY_FOR_DAGGPSHD                  QBlock                    2                        0
         12.2.0.1 USE_PARTITION_WISE_DISTINCT      NO_USE_PARTITION_WISE_DISTINCT   QKSFM_PARTITION                USE_PARTITION_WISE_DISTINCT                QBlock                    2                        0
         12.2.0.1 USE_PARTITION_WISE_GBY           NO_USE_PARTITION_WISE_GBY        QKSFM_PARTITION                USE_PARTITION_WISE_GBY                     QBlock                    2                        0
                  DATA_VALIDATE                                                     QKSFM_EXECUTION                DATA_VALIDATE                       State                            1                        0
                  FRESH_MV                                                          QKSFM_MVIEWS                   FRESH_MV                            State                            1                        0
                  SQL_SCOPE                                                         QKSFM_COMPILATION              SQL_SCOPE                           State                            1                        0
                  XMLTSET_DML_ENABLE                                                QKSFM_ALL                      XMLTSET_DML_ENABLE                  State                            1                        0
--------
      22

18.1.0   18.1.0   ANSWER_QUERY_USING_STATS         NO_ANSWER_QUERY_USING_STATS      QKSFM_ANSWER_QUERY_USING_STATS ANSWER_QUERY_USING_STATS                   QBlock                    2 16                    16
         18.1.0   NO_ANSWER_QUERY_USING_STATS      ANSWER_QUERY_USING_STATS         QKSFM_ANSWER_QUERY_USING_STATS ANSWER_QUERY_USING_STATS                   QBlock                    2 16                    16
         18.1.0   NO_PUSH_HAVING_TO_GBY            PUSH_HAVING_TO_GBY               QKSFM_EXECUTION                PUSH_HAVING_TO_GBY                         QBlock                    2                        0
         18.1.0   NO_REORDER_WIF                   REORDER_WIF                      QKSFM_PARTITION                REORDER_WIF                                QBlock                    2                        0
         18.1.0   NO_USE_PARTITION_WISE_WIF        USE_PARTITION_WISE_WIF           QKSFM_PARTITION                USE_PARTITION_WISE_WIF                     QBlock                    2                        0
         18.1.0   PUSH_HAVING_TO_GBY               NO_PUSH_HAVING_TO_GBY            QKSFM_EXECUTION                PUSH_HAVING_TO_GBY                         QBlock                    2                        0
         18.1.0   REORDER_WIF                      NO_REORDER_WIF                   QKSFM_PARTITION                REORDER_WIF                                QBlock                    2                        0
         18.1.0   USE_PARTITION_WISE_WIF           NO_USE_PARTITION_WISE_WIF        QKSFM_PARTITION                USE_PARTITION_WISE_WIF                     QBlock                    2                        0
                  AV_CACHE                                                          QKSFM_EXECUTION                AV_CACHE                                   QBlock                    2                        0
                  CURRENT_INSTANCE                                                  QKSFM_ALL                      CURRENT_INSTANCE                    State                            1                        0
                  MEMOPTIMIZE_WRITE                                                 QKSFM_EXECUTION                MEMOPTIMIZE_WRITE                   State                            1                        0
                  PDB_LOCAL_ONLY                                                    QKSFM_DML                      PDB_LOCAL_ONLY                      State                            1                        0
                  SKIP_PROXY                                                        QKSFM_ALL                      SKIP_PROXY                          State                            1                        0
                  SUPPRESS_LOAD                                                     QKSFM_DDL                      SUPPRESS_LOAD                       State                            1                        0
                  SYSTEM_STATS                                                      QKSFM_ALL                      SYSTEM_STATS                        State                            1 16              256  272
--------
      15

19.1.0   19.1.0   NO_PQ_EXPAND_TABLE               PQ_EXPAND_TABLE                  QKSFM_TABLE_EXPANSION          PQ_EXPAND_TABLE                                   Object             4 16                    16
         19.1.0   NO_USE_SCALABLE_GBY_INVDIST      USE_SCALABLE_GBY_INVDIST         QKSFM_PQ                       USE_SCALABLE_GBY_INVDIST                   QBlock                    2                        0
         19.1.0   PQ_EXPAND_TABLE                  NO_PQ_EXPAND_TABLE               QKSFM_TABLE_EXPANSION          PQ_EXPAND_TABLE                                   Object             4 16                    16
         19.1.0   USE_SCALABLE_GBY_INVDIST         NO_USE_SCALABLE_GBY_INVDIST      QKSFM_PQ                       USE_SCALABLE_GBY_INVDIST                   QBlock                    2                        0
                  JSON_LENGTH                                                       QKSFM_EXECUTION                JSON_LENGTH                         State                            1                        0
                  QUARANTINE                                                        QKSFM_EXECUTION                QUARANTINE                          State                            1                        0
--------
       6

20.1.0   20.1.0   FORCE_JSON_TABLE_TRANSFORM       NO_JSON_TABLE_TRANSFORM          QKSFM_JSON_REWRITE             FORCE_JSON_TABLE_TRANSFORM          State                            1                        0
         20.1.0   NO_JSON_TABLE_TRANSFORM          FORCE_JSON_TABLE_TRANSFORM       QKSFM_JSON_REWRITE             FORCE_JSON_TABLE_TRANSFORM          State                            1                        0
         20.1.0   NO_SET_GBY_PUSHDOWN              SET_GBY_PUSHDOWN                 QKSFM_ALL                      SET_GBY_PUSHDOWN                           QBlock                    2 16                    16
         20.1.0   SET_GBY_PUSHDOWN                 NO_SET_GBY_PUSHDOWN              QKSFM_ALL                      SET_GBY_PUSHDOWN                           QBlock                    2 16                    16
                  ANALYTIC_VIEW_SQL                                                 QKSFM_COMPILATION              ANALYTIC_VIEW_SQL                          QBlock                    2                        0
                  DENORM_AV                                                         QKSFM_COMPILATION              DENORM_AV                                  QBlock                    2                        0
--------
       6

21.1.0   21.1.0   DAGG_OPTIM_GSETS                 NO_DAGG_OPTIM_GSETS              QKSFM_GROUPING_SET_XFORM       DAGG_OPTIM_GSETS                           QBlock                    2                        0
         21.1.0   HASHSET_BUILD                                                     QKSFM_EXECUTION                HASHSET_BUILD                              QBlock                    2 16                    16
         21.1.0   NO_DAGG_OPTIM_GSETS              DAGG_OPTIM_GSETS                 QKSFM_GROUPING_SET_XFORM       DAGG_OPTIM_GSETS                           QBlock                    2                        0
         21.1.0   NO_OBY_GBYPD_SEPARATE            OBY_GBYPD_SEPARATE               QKSFM_PQ                       OBY_GBYPD_SEPARATE                         QBlock                    2 16                    16
         21.1.0   NO_PQ_NONLEAF_SKEW               PQ_NONLEAF_SKEW                  QKSFM_PQ                       PQ_NONLEAF_SKEW                                   Object             4 16              256  272
         21.1.0   OBY_GBYPD_SEPARATE               NO_OBY_GBYPD_SEPARATE            QKSFM_PQ                       OBY_GBYPD_SEPARATE                         QBlock                    2 16                    16
         21.1.0   ORDER_KEY_VECTOR_USE                                              QKSFM_VECTOR_AGG               ORDER_KEY_VECTOR_USE                       QBlock                    2 16              256  272
         21.1.0   OSON_GET_CONTENT                                                  QKSFM_JSON                     OSON_GET_CONTENT                    State                            1                        0
         21.1.0   PQ_NONLEAF_SKEW                  NO_PQ_NONLEAF_SKEW               QKSFM_PQ                       PQ_NONLEAF_SKEW                                   Object             4 16              256  272
--------
       9


388 rows selected

system_stats() hint

Starting from 18.3 Oracle introduced the system_stats() hint, apparently allowing you to set the system statistics for the duration of a query. However the hint didn’t seem to have any effect in that version of Oracle – even though the fix_control that seemed to be the most relevant (QKSFM_DBMS_STATS_24952618) was set to 1, so maybe the hint was acting strictly according to the fix control description, which was: “turn on Exadata stats: MBRC,IOTFRSPEED,IOSEEKTIME” (or maybe the fix control had nothing to do with the hint)

According to my notes I had a test that showed it working on live SQL, which (in my notes) I said was running 19.2 at the time; however, I can’t get it to work on 19.11.0.0 or 21.3.0.0 on a Linux VM (or on the current Live SQL version) despite a load of fiddling with potentially relevant hidden parameters, fix controls, and numeric event numbers. So maybe it is only for Exadata.

It’s not documented, of course, but I’m fairly confident I’m using the correct syntax – which was quite easy to find (sometimes you get lucky) because a search through the binary for the hint text produced a perfect result:

[oracle@linux183 bin]$ strings -a oracle | grep -T -n  -i system_stats\(
1762556:BEGIN :1 := dbms_stats_internal.store_system_stats(:2, :3, :4); END;
1787190:system_stats(mbrc=%f ioseektim=%f iotfrspeed=%f)

So it would seem (from line 1787190) that we can override three of the system statistics: mbrc, ioseektim, and iotfrspeed. Thanks to the hint_report option that 19c introduced to dispay_xxxxxx() calls in dbms_xplan it’s easy to see that this syntax is correct but unused. From a call to dbms_xplan.display_cursor() in 19.11.0.0:

select  /*+ system_stats(mbrc=128 ioseektim=1 iotfrspeed=262144) */ count(*) from t1

Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |  2732 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 50000 |  2732   (1)| 00:00:01 |
-------------------------------------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
   0 -  STATEMENT
         U -  system_stats(mbrc=128 ioseektim=1 iotfrspeed=262144)

Other tests reported shorter versions of the hint (e.g. /*+ system_stats(mbrc=128) */ ) as errors:

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
   1 -  SEL$1
         E -  system_stats

In passing, it’s interesting to note that the text was reported as a “query block” hint (sel$1) when it had a syntax error despite being a “statement-level” hint when it was recognised. Presumably the generic parsing rule is: “it’s a query block hint unless proved otherwise”.

The call to dbms_stat_internal.store_system_stats() that also dropped out of the scan of the executable looks as if it’s the function that sets the “noworkload” statistics – the three parameters are, in order: ioseektim, iotfrspeed, cpuspeednw – but possibly it’s the internal call used when you use the ‘EXADATA’ option for gathering system stats.

Bottom line:

Maybe there’s a way to switch this hint on to override the default system stats; maybe it just needs to be run on Exadata; and maybe – if it can be switched on – it could be attached as an SQL_Patch.  Experimentation left to readers who have access to an Exadata system, any results are welcome.

Hash Aggregation – 1

Here’s an observation I made some time in 2007, but didn’t mention online until a (possibly) relevant question appeared on the Oracle database forum in 2017; and the topic reappeared in a nearly unrelated question a little while ago. The 2017 question was this:

I am running following SQL, which it taking almost 4-5 seconds and returning only 18 records.

SELECT
   SUM(TOTAL_AMOUNT) C1,
   a.LEVEL2_ENAME AS c2,
   c.CURR_YEARMO AS c3
FROM TERRITORY_PRESET_MSNP a,
   CV_RESTATED_MSNP b
   LEFT OUTER JOIN
   MONTH_D c
   ON b.YEARMO = c.CURR_YEARMO,
   PRODUCT_OFFERING d
WHERE   b.PO_ID = d.ROW_ID
    AND b.DATASOURCE_ID = 10
    AND b.YEARMO = 201704
    AND b.OWNER_TERR_ID = a.TERR_ID
    AND c.CURR_YEARMO = 201704
    AND a.YEARMO = 201706
GROUP BY c.CURR_YEARMO, a.LEVEL2_ENAME
ORDER BY C3, C2;

If I remove the ORDER BY clause it is returning results in 1 second.

Before saying anything else, I’ll just make a couple of points about the SQL:

• It’s not a good idea to mix traditional Oracle syntax with “ANSI” syntax – it’s likely to make things harder for the next person to read the code and there’s just a slight possibility that the rewrite that Oracle applies to hide the ANSI syntax may block some of the possible execution paths.
• The C3, C2 in the order by clause are the column aliases for the curr_yearno, level2_ename columns used in the group by clause.  Although Oracle allows you to use aliases in the order by (but not in the group by) doing so can only make the SQL a little harder to interpret (especially in a case like this when you have both clauses).
• There’s a left outer join to month_d (aliased as c), but the where clause then includes the predicate c.CURR_YEARMO = 201704 which will eliminate any rows where curr_yearmo is null, thus converting (we hope – but the mix and match syntax might introduce a side-effect) the outer join to an inner join – so maybe that’s a design error in the SQL.

Addressing the question, though, the first thought (rapidly eliminated) is that perhaps this is the standard complaint of the GUI interface: “it’s fast until I add an order by clause”.

The commonest reason for this complaint is that the typical GUI interface shows you the first few rows and waits for you to page down, so your impression of the response time is “the time to see the first few rows” rather than “the time to get the complete result set” when it might take much more time to return the entire result set. When you add an order by clause it’s possible that Oracle will have to sort the entire result set before you see any of it. It’s often the difference between “first few rows” and “entire result set” that triggers the complaint.

In this case the “rapid elimination” of this thought is due to the OP saying the result set was only 18 rows. which is likely to produce the gut feeling that it shouldn’t take very long for Oracle to sort 18 rows if it had to find all of them before showing displaying them. On the other hand the thought might need a little follow-up, after all:

• it’s possible that the GUI is only displaying 15 rows at a time and it’s takes a lot of time to find the extra 3 rows. Just think of a tablescan with a filter subquery when the rows you want are the first few in the table. Without an order by the rows can be displayed as they are found, with an order by Oracle will have to get to the end of the tablescan before the rows can be sorted and displayed.
• the optimizer can produce terrible estimates and the order by clause might prompt it to say “if I start with a different table, driving through a specific index, and changing the join order then I won’t have to do any sorting for the order by clause” The resulting path may be a very bad idea if the arithmetic produces the wrong results.

The OP hasn’t shown us the execution plan – and that’s what we really need to see; but there is an alternative  guess that we could make about what the optimizer is doing that would affect the performance this much.

The query is an aggregate query – we have a group by. Since 10g Oracle has been able to use “hash aggregation” – which shows up as the HASH GROUP BY operation in an execution plan. Here’s a little demo script, with a couple of sample queries:

rem
rem     Script:         sort_hash.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2012
rem 

create table t1
as
with generator as (
        select
                rownum id 
        from dual 
        connect by 
                level <= 1e4  -- > comment to avoid wordpress format issue
)
select
        trunc(dbms_random.value(0,262144))      n_256K,
        trunc(dbms_random.value(0,131072))      n_128K,
        trunc(dbms_random.value(0,8192))        n_8k
from
        generator       v1,
        generator       v2
where
        rownum <= 8 * 1048576  -- > comment to avoid wordpress format issue
;

set arraysize 1000
set timing on
set autotrace traceonly 

prompt  ===========
prompt  No Order by
prompt  ===========

select
        n_8K, count(*) ct
from
        t1
group by
        n_8k
;


prompt  =============
prompt  With Order by
prompt  =============

select
        n_8K, count(*) ct
from
        t1
group by
        n_8k
order by
        1
;

My table has 8M rows, and my queries target the column with 8K distinct values. I’ve enabled autotrace from SQL*Plus, set a large arraysize (to reduce time lost to SQL*Net round-trips), and set timing on so we can get an elapsed time for total execution. I’ve set autotrace to “traceonly” so that the SQL*Plus client will fetch the data but won’t doesn’t waste resources formatting it, but I’m not actually interested in the handful of execution statistics that will be reported.

Here are the two sets of results from a test run on 19.11.0.0. Note, particularly, the figure for Elapsed:

===========
No Order by
===========

8192 rows selected.

Elapsed: 00:00:00.58

Execution Plan
----------------------------------------------------------
Plan hash value: 136660032

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  8192 | 32768 |  6938  (61)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |  8192 | 32768 |  6938  (61)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |  8388K|    32M|  3460  (21)| 00:00:01 |
---------------------------------------------------------------------------

=============
With Order by
=============

8192 rows selected.

Elapsed: 00:00:03.03

Execution Plan
----------------------------------------------------------
Plan hash value: 3946799371

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  8192 | 32768 |  6938  (61)| 00:00:01 |
|   1 |  SORT GROUP BY     |      |  8192 | 32768 |  6938  (61)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |  8388K|    32M|  3460  (21)| 00:00:01 |
---------------------------------------------------------------------------

The time has jumped from slightly under 0.6 seconds to just over 3 seconds as the critical operation changes from a HASH GROUP BY to a SORT GROUP BY (even though the estimated cost, hence predicted run-time, of execution has not changed).

Your first thought at this point is probably along the lines of “surely it doesn’t take 2.4 seconds to sort 8,192 small rows, why doesn’t Oracle do a hash group by followed by a sort order by?” The answer seems to be “it just doesn’t”. So here’s one way to make it happen (with execution plan and elapsed time from 19.11.0.0 again):

select
        dist_8k, ct
from
        (
        select  /*+ no_merge */
                n_8K dist_8k, count(*) ct
        from
                t1
        group by
                n_8k
        )
order by 
        dist_8k
;

8192 rows selected.

Elapsed: 00:00:00.59

Execution Plan
----------------------------------------------------------
Plan hash value: 1705136228

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  8192 |   208K|  6938  (61)| 00:00:01 |
|   1 |  SORT ORDER BY       |      |  8192 |   208K|  6938  (61)| 00:00:01 |
|   2 |   VIEW               |      |  8192 |   208K|  6938  (61)| 00:00:01 |
|   3 |    HASH GROUP BY     |      |  8192 | 32768 |  6938  (61)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |  8388K|    32M|  3460  (21)| 00:00:01 |
-----------------------------------------------------------------------------

Again the estimated cost of execution doesn’t (to the nearest whole number) change from the basic cost of the hash aggregation – but we have brought the time back down to just under 0.6 seconds.

It’s worth reminding you at this point that if you can re-engineer any SQL that’s performing badly and can see that the driving core of the query can be reduced to something much simpler and more efficient, then wrapping that core into an inline view with the /*+ no_merge */ hint (and possibly putting it up into a “with subquery” clause) might be the safest first step and most effective way of improving performance.

There is an option for avoiding the query rewrite here – hint the path you want to see:

select  /*+ use_hash_aggregation */
        n_8K, count(*) ct
from
        t1
group by
        n_8k
order by 
        1
;

8192 rows selected.

Elapsed: 00:00:00.59

Execution Plan
----------------------------------------------------------
Plan hash value: 2808104874

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  8192 | 32768 |  6938  (61)| 00:00:01 |
|   1 |  SORT ORDER BY      |      |  8192 | 32768 |  6938  (61)| 00:00:01 |
|   2 |   HASH GROUP BY     |      |  8192 | 32768 |  6938  (61)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |  8388K|    32M|  3460  (21)| 00:00:01 |
----------------------------------------------------------------------------

The nice thing about this, of course, is that you don’t actually have to edit the text; the hint could be attached to the query through an SQL Patch (or by abusing the SQL Profile or SQL Plan Baseline mechanisms).

The difficult part of hinting is finding the correct query block name for a more complex query. I simply added the hint /*+ use_hash_aggregation */ but the hint can be aimed at a query block so, in the absence of explicit query block names I could have used the hint /*+ use_hash_aggregation(@sel$1) */ using the default naming.

In a more complex case you can find the appropriate query block name by using the ‘alias’ format option when generating the execution plan. Consider the following query (where t2 and t3 are created from view all_objects), with its initial execution plan:

explain plan for
select 
        t2.owner, count(*)
from 
        t2 
where 
        t2.object_id in (
                select  t3.object_id 
                from    t3 
                where   t3.object_type = 'TABLE'
        )
group by 
        t2.owner
order by 
        t2.owner
/


select * from table(dbms_xplan.display(format=>'alias'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2646727453

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |    23 |   575 |   319   (8)| 00:00:01 |
|   1 |  SORT GROUP BY        |      |    23 |   575 |   319   (8)| 00:00:01 |
|*  2 |   HASH JOIN RIGHT SEMI|      |  2298 | 57450 |   317   (7)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL  | T3   |  2298 | 34470 |   158   (7)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | T2   | 57448 |   561K|   156   (6)| 00:00:01 |
------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$5DA710D3
   3 - SEL$5DA710D3 / T3@SEL$2
   4 - SEL$5DA710D3 / T2@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
   3 - filter("T3"."OBJECT_TYPE"='TABLE')

The Query Block Name / Object Alias information tells us that the query block holding the sort group by at operation 1 is named SEL$5DA710D3 so we can use that as the target query block in the hint: /*+ use_hash_aggregation(@SEL$5DA710D3) */ and the plan changes to:

-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |    23 |   575 |   319   (8)| 00:00:01 |
|   1 |  SORT ORDER BY         |      |    23 |   575 |   319   (8)| 00:00:01 |
|   2 |   HASH GROUP BY        |      |    23 |   575 |   319   (8)| 00:00:01 |
|*  3 |    HASH JOIN RIGHT SEMI|      |  2298 | 57450 |   317   (7)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL  | T3   |  2298 | 34470 |   158   (7)| 00:00:01 |
|   5 |     TABLE ACCESS FULL  | T2   | 57448 |   561K|   156   (6)| 00:00:01 |
-------------------------------------------------------------------------------

It’s possible that you’ll only notice a significant difference in performance (and, perhaps, PGA memory allocated) when you aggregate a large number of rows into a small result set. And it does seem that this is one case where the only way to get the plan you want without a significant code rewrite is through a hint

tl;dr

If you have some code which does a “group by X, Y, Z order by X, Y, Z” (the order of the columns/expressions used doesn’t have to be the same for the two clauses) then Oracle will use a sort group by operation to handle the aggregation and ordering in a single step, even though we can find cases where hash aggregation followed by sort ordering is more efficient.

If you come across such a case then injecting the hint /*+ use_hash_aggregation(@query_block_name) */ may be the only way to change the execution plan if you’re not allowed to edit the SQL.

Footnote

In the second of the two links to the Oracle Developer Forum you’ll see that one poster pointed out that if the order by clause uses a suitable expression to substitute for one of the columns in the group by clause then you don’t need to hint the code, e.g.

group by
        numeric_column
order by
        numeric_column + 0

Very cute, but not a good idea.

Footnote 2

There is another part to the testing I started in 2007, and I’ll come back to that later in Hash Aggregation – 2.

use_nl redux

A question has just appeared on a note I wrote in 2012 about the incorrect use of the use_nl() hint in some sys-recursive SQL, linking forward to an explanation I wrote in 2017 of the use_nl() hint – particularly the interpretation of the form use_nl(a,b), which does not mean “use a nested loop from table A to table B)”.

The question is essentially – “does Oracle pick the join order before it looks at the hints”?

I’m going to look at one of the queries in the question (based on the 2017 table creation code) and explain how Oracle gets to the plan it finally picks. I’ll be using an instance of 21.3 in the examples below. Here’s the query, followed by the plan:

select
        /*+ use_nl(b) */
        a.v1, b.v1, c.v1, d.v1
from
        a, b, c, d
where
        d.n100 = 0
and     a.n100 = d.id
and     b.n100 = a.n2
and     c.id   = a.id
/


| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 20000 |  1347K|   105   (5)| 00:00:01 |
|*  1 |  HASH JOIN           |      | 20000 |  1347K|   105   (5)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | C    | 10000 |   146K|    26   (4)| 00:00:01 |
|*  3 |   HASH JOIN          |      | 20000 |  1054K|    78   (4)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL | D    |   100 |  1800 |    26   (4)| 00:00:01 |
|*  5 |    HASH JOIN         |      | 20000 |   703K|    52   (4)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| B    | 10000 |   136K|    26   (4)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| A    | 10000 |   214K|    26   (4)| 00:00:01 |
-----------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      SWAP_JOIN_INPUTS(@"SEL$1" "C"@"SEL$1")
      SWAP_JOIN_INPUTS(@"SEL$1" "D"@"SEL$1")
      USE_HASH(@"SEL$1" "C"@"SEL$1")
      USE_HASH(@"SEL$1" "D"@"SEL$1")
      USE_HASH(@"SEL$1" "A"@"SEL$1")
      LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1" "D"@"SEL$1" "C"@"SEL$1")
      FULL(@"SEL$1" "C"@"SEL$1")
      FULL(@"SEL$1" "D"@"SEL$1")
      FULL(@"SEL$1" "A"@"SEL$1")
      FULL(@"SEL$1" "B"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('21.1.0')
      OPTIMIZER_FEATURES_ENABLE('21.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C"."ID"="A"."ID")
   3 - access("A"."N100"="D"."ID")
   4 - filter("D"."N100"=0)
   5 - access("B"."N100"="A"."N2")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
   6 -  SEL$1 / "B"@"SEL$1"
         U -  use_nl(b)

Note
-----
   - this is an adaptive plan

Points to note:

• The Hint Report says the plan final did not use the use_nl(b) hint.
• Whatever you may think the join order is by looking at the body of the plan, the leading() hint in the Outline Data tells us that the join order was (B A D C), and that explains why the use_nl(b) hint could not be used: B was never “the next table in the join order”.
• The “visible” order of activity displayed in the plan is C D B A, but that’s because we swap_join_inputs(D) to put D above the (B,A) join, then swap_join_inputs(C) to put C above D.

So did Oracle completely pre-empt any plans that allowed B to be “the next table”, thus avoiding the hint, or did it consider some plans where B wasn’t the first table in the join order and, if so, would it have used a nested loop into B if that plan had had a low enough cost?

The only way to answer these questions is to look at the CBO (10053) trace file. For very simple queries it’s often enough to pick out a few lines as a starting point – in my case using egrep:

egrep -e "^Join order" -e"Best so far" or21_ora_15956.trc

Join order[1]:  D[D]#0  A[A]#1  B[B]#2  C[C]#3
Best so far:  Table#: 0  cost: 25.752439  card: 100.000000  bytes: 1800.000000
Join order[2]:  D[D]#0  A[A]#1  C[C]#3  B[B]#2
Best so far:  Table#: 0  cost: 25.752439  card: 100.000000  bytes: 1800.000000
Join order[3]:  D[D]#0  B[B]#2  A[A]#1  C[C]#3
Best so far:  Table#: 0  cost: 25.752439  card: 100.000000  bytes: 1800.000000
Join order[4]:  D[D]#0  B[B]#2  C[C]#3  A[A]#1
Join order aborted2: cost > best plan cost
Join order[5]:  D[D]#0  C[C]#3  A[A]#1  B[B]#2
Join order aborted2: cost > best plan cost
Join order[6]:  D[D]#0  C[C]#3  B[B]#2  A[A]#1
Join order aborted2: cost > best plan cost

Join order[7]:  A[A]#1  D[D]#0  B[B]#2  C[C]#3
Join order aborted2: cost > best plan cost
Join order[8]:  A[A]#1  D[D]#0  C[C]#3  B[B]#2
Join order aborted2: cost > best plan cost
Join order[9]:  A[A]#1  B[B]#2  D[D]#0  C[C]#3
Join order aborted2: cost > best plan cost
Join order[10]:  A[A]#1  C[C]#3  D[D]#0  B[B]#2
Join order aborted2: cost > best plan cost
Join order[11]:  A[A]#1  C[C]#3  B[B]#2  D[D]#0
Join order aborted2: cost > best plan cost

Join order[12]:  B[B]#2  D[D]#0  A[A]#1  C[C]#3
Join order aborted2: cost > best plan cost
Join order[13]:  B[B]#2  A[A]#1  D[D]#0  C[C]#3
Best so far:  Table#: 2  cost: 25.692039  card: 10000.000000  bytes: 140000.000000
Join order[14]:  B[B]#2  A[A]#1  C[C]#3  D[D]#0
Join order aborted2: cost > best plan cost
Join order[15]:  B[B]#2  C[C]#3  D[D]#0  A[A]#1
Join order aborted2: cost > best plan cost

Join order[16]:  C[C]#3  D[D]#0  A[A]#1  B[B]#2
Join order aborted2: cost > best plan cost
Join order[17]:  C[C]#3  A[A]#1  D[D]#0  B[B]#2
Join order aborted2: cost > best plan cost
Join order[18]:  C[C]#3  A[A]#1  B[B]#2  D[D]#0
Join order aborted2: cost > best plan cost
Join order[19]:  C[C]#3  B[B]#2  D[D]#0  A[A]#1
Join order aborted2: cost > best plan cost

Oracle has considerd 19 possible join orders (out of a maximum of 24 (i.e. 4!). In theory we might see 6 plans starting with each of the 4 tables. In fact we see that the optimizer’s first choice started with table D, producing 6 join orders, then switched to starting with table A, producing only 5 join orders.

The “missing” order is (A, B, C, D) which should have appeared between join orders 9 and 10. If we check the detail in the trace file for join order 9 (A, B, D, C) we’ll see that the optimizer aborted after calculating the join from A to B because the cost had already exceeded the “Best so far” by then and didn’t even start to calculate the cost of getting to the 3rd table (D) in that join order. Clearly, then, there was no point in the optimizer considering any other join order that started with (A, B) – hence the absence of (A, B, C, D), which would otherwise have been the next in the list

I’ve highlighted all the join orders where the optimizer didn’t abort, but produced a new “Best so far” line. My original egrep command was too simple to tell the whole story, though; the “Best so far” line includes only the cost of getting data from the first table in the join order and I need to expand the output in each case to 4 lines (one for each table). This is what the 4 non-aborted summaries look like:

egrep -A+3 -e"Best so far" or21_ora_15956.trc

Best so far:  Table#: 0  cost: 25.752439  card: 100.000000  bytes: 1800.000000
              Table#: 1  cost: 51.767478  card: 10000.000000  bytes: 400000.000000
              Table#: 2  cost: 30137.036118  card: 20000.000000  bytes: 1080000.000000
              Table#: 3  cost: 30163.548157  card: 20000.000000  bytes: 1380000.000000
--
Best so far:  Table#: 0  cost: 25.752439  card: 100.000000  bytes: 1800.000000
              Table#: 1  cost: 51.767478  card: 10000.000000  bytes: 400000.000000
              Table#: 3  cost: 78.079517  card: 10000.000000  bytes: 550000.000000
              Table#: 2  cost: 30163.348157  card: 20000.000000  bytes: 1380000.000000
--
Best so far:  Table#: 0  cost: 25.752439  card: 100.000000  bytes: 1800.000000
              Table#: 2  cost: 2483.956340  card: 1000000.000000  bytes: 32000000.000000
              Table#: 1  cost: 2530.068379  card: 20000.000000  bytes: 1080000.000000
              Table#: 3  cost: 2556.580418  card: 20000.000000  bytes: 1380000.000000
--
Best so far:  Table#: 2  cost: 25.692039  card: 10000.000000  bytes: 140000.000000
              Table#: 1  cost: 52.204078  card: 20000.000000  bytes: 720000.000000
              Table#: 0  cost: 78.479517  card: 20000.000000  bytes: 1080000.000000
              Table#: 3  cost: 104.991556  card: 20000.000000  bytes: 1380000.000000

As you can see, when we start with (B A) the estimated cost drops dramatically.

Now that we’ve see that Oracle looks at many (though not a completely exhaustive set of) plans on the way to the one it picks the thing we need to do to address the original question is check that for every single calculation where B is “the next table”, Oracle obeys our hint.

Each time the optimizer join “the next table” its default strategy is to consider the cost of a Nested Loop join, a Sort Merge join (possibly in two different ways), and a Hash join in that order. However, if the optimizer is obeying the hint it will consider only the nested loop join. Here’s a suitable call to egrep with the first four join orders::

egrep -e "^Join order" -e "^Now joining" -e"^NL Join" -e"^SM Join" -e"^HA Join" or21_ora_15956.trc

Join order[1]:  D[D]#0  A[A]#1  B[B]#2  C[C]#3
Now joining: A[A]#1
NL Join
SM Join
SM Join (with index on outer)
HA Join
Now joining: B[B]#2
NL Join
Now joining: C[C]#3
NL Join
SM Join
HA Join

Join order[2]:  D[D]#0  A[A]#1  C[C]#3  B[B]#2
Now joining: C[C]#3
NL Join
SM Join
HA Join
Now joining: B[B]#2
NL Join

Join order[3]:  D[D]#0  B[B]#2  A[A]#1  C[C]#3
Now joining: B[B]#2
NL Join
Now joining: A[A]#1
NL Join
SM Join
HA Join
Now joining: C[C]#3
NL Join
SM Join
HA Join

Join order[4]:  D[D]#0  B[B]#2  C[C]#3  A[A]#1
Now joining: C[C]#3
NL Join
Join order aborted2: cost > best plan cost

As you can see, the only join considered when the optimizer is “Now joining” B is a Nested Loop join; for all other tables all three join methods (and sometimes two variants of the Sort Merge join) are evaluated.

You may also notice another of the clever strategies the optimizer uses to minimise its workload. On the Join order[2] the optimizer has gone straight to “Now joining C” because it has remembered the result of joining A to D from the previous join order.

This is only a very simple example and analysis, but I hope it has given you some idea of how the optimizer works, how clever it tries to be about minimising the work, and how it can obey a hint while still producing an execution plan that appears to have ignored the hint.

Ordered hint

It’s been such a long time since Oracle deprecated the /*+ ordered */ hint that I can’t remember when it happened. The hint you should be using is the /*+ leading(…) */ hint which initially – maybe some time in 9i – would only allow you to specify the first table that the optimizer should use when examining join orders, but which soon changed to allow you to specify a complete join order.

I’ve written a few notes about the need to get rid of any /*+ ordered */ hints in production SQL because it can produce a join order you’re not expecting. I’ve just found an extreme case of this running a quick test on 19.11.0.0 then 21.3.0.0

I’m not going to bother with the data setup for the query but it’s a simple parent/child query that exhibits a surprising pattern. Here’s the query:

select
        /*+
                no_adaptive_plan
                ordered
                use_nl(ch)
        */
        par.n1,
        par.small_vc,
        sum(ch.n1)
from
        parent par,
        child ch
where
        par.n1 <= 20
and     ch.id_par = par.id
group by
        par.n1,
        par.small_vc
;

And here’s the plan, pulled from memory with a call to dbms_xplan.display_cursor() with the /*+ ordered */ in place. I’ve included the outline data, hint report and (since this is from 21c) the query block registry:

-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |       |       |    32 (100)|          |
|   1 |  HASH GROUP BY         |          |    20 |   780 |    32   (7)| 00:00:01 |
|*  2 |   HASH JOIN            |          |    20 |   780 |    31   (4)| 00:00:01 |
|   3 |    JOIN FILTER CREATE  | :BF0000  |    20 |   440 |     8   (0)| 00:00:01 |
|   4 |     VIEW               | VW_GBF_6 |    20 |   440 |     8   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL | PARENT   |    20 |   380 |     8   (0)| 00:00:01 |
|   6 |    VIEW                | VW_GBC_5 |  1000 | 17000 |    23   (5)| 00:00:01 |
|   7 |     HASH GROUP BY      |          |  1000 |  8000 |    23   (5)| 00:00:01 |
|   8 |      JOIN FILTER USE   | :BF0000  |  4000 | 32000 |    22   (0)| 00:00:01 |
|*  9 |       TABLE ACCESS FULL| CHILD    |  4000 | 32000 |    22   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('21.1.0')
      DB_VERSION('21.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$D2EA58F1")
      ELIM_GROUPBY(@"SEL$FFB6458A")
      OUTLINE_LEAF(@"SEL$FE9D3122")
      OUTLINE_LEAF(@"SEL$E2E47E3A")
      PLACE_GROUP_BY(@"SEL$1" ( "PAR"@"SEL$1" ) ( "CH"@"SEL$1" ) 5)
      OUTLINE(@"SEL$FFB6458A")
      ELIM_GROUPBY(@"SEL$1D9E464A")
      OUTLINE(@"SEL$E26B953F")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$1D9E464A")
      OUTLINE(@"SEL$E132E821")
      NO_ACCESS(@"SEL$E2E47E3A" "VW_GBF_6"@"SEL$E132E821")
      NO_ACCESS(@"SEL$E2E47E3A" "VW_GBC_5"@"SEL$E26B953F")
      LEADING(@"SEL$E2E47E3A" "VW_GBF_6"@"SEL$E132E821"
              "VW_GBC_5"@"SEL$E26B953F")
      USE_HASH(@"SEL$E2E47E3A" "VW_GBC_5"@"SEL$E26B953F")
      PX_JOIN_FILTER(@"SEL$E2E47E3A" "VW_GBC_5"@"SEL$E26B953F")
      USE_HASH_AGGREGATION(@"SEL$E2E47E3A" GROUP_BY)
      FULL(@"SEL$D2EA58F1" "PAR"@"SEL$1")
      FULL(@"SEL$FE9D3122" "CH"@"SEL$1")
      USE_HASH_AGGREGATION(@"SEL$FE9D3122" GROUP_BY)
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ITEM_1"="ITEM_1")
   5 - filter("PAR"."N1"<=20)
   9 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"CH"."ID_PAR"))

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1))
---------------------------------------------------------------------------

   0 -  STATEMENT
           -  no_adaptive_plan

   1 -  SEL$E2E47E3A
           -  ordered

   9 -  SEL$FE9D3122 / "CH"@"SEL$1"
         U -  use_nl(ch)

Query Block Registry:
---------------------

  SEL$1 (PARSER)
    SEL$E26B953F (QUERY BLOCK TABLES CHANGED SEL$1)
      SEL$E132E821 (QUERY BLOCK TABLES CHANGED SEL$E26B953F)
        SEL$1D9E464A (SPLIT/MERGE QUERY BLOCKS SEL$E132E821)
          SEL$FFB6458A (ELIMINATION OF GROUP BY SEL$1D9E464A)
            SEL$D2EA58F1 (ELIMINATION OF GROUP BY SEL$FFB6458A) [FINAL]
      SEL$FE9D3122 (SPLIT/MERGE QUERY BLOCKS SEL$E26B953F) [FINAL]
    SEL$E2E47E3A (PLACE GROUP BY SEL$1) [FINAL]

The optimizer seems to have got rather carried away with how clever it cn be; so here’s the result of switching from /*+ ordered */ to using /*+ leading(par ch) */ – I won’t bother with all the extras since it’s a very simple plan:

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |       |       |   109 (100)|          |
|   1 |  HASH GROUP BY                |        |    80 |  2160 |   109   (1)| 00:00:01 |
|   2 |   NESTED LOOPS                |        |    80 |  2160 |   108   (0)| 00:00:01 |
|   3 |    NESTED LOOPS               |        |    80 |  2160 |   108   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL         | PARENT |    20 |   380 |     8   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | CHI_PK |     4 |       |     1   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| CHILD  |     4 |    32 |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("PAR"."N1"<=20)
   5 - access("CH"."ID_PAR"="PAR"."ID")

tl;dr

You should not be using the /*+ ordered */ hint in any recent version of Oracle.

Distributed Query

Here’s an example that appeared on the Oracle Developer Community forum about a year ago that prompted me to do a little investigative work. The question involved a distributed query that was “misbehaving” – the interesting points were the appearance of the /*+ rule */ and /*+ driving_site() */ hints in the original query when combined with a suggestion to address the problem using the /*+ materialize */ hint with factored subqueries (common table expressions – CTEs), or when combined with my suggestion to use the /*+ no_merge */ hint.

If you don’t want to read the whole article there’s a tl;dr summary just before the end.

The original question was posed with a handful of poorly constructed code fragments that were supposed to describe the problem, viz:

select /*+ DRIVING_SITE (s1) */ * from  Table1 s1 WHERE condition in (select att1 from local_table) ; -- query n°1

select /*+ RULE DRIVING_SITE (s2) */  * from  Table2 s2 where  condition in (select att1 from local_table); -- query n°2

select * from
select /*+ DRIVING_SITE (s1) */ * from  Table1 s1 WHERE condition in (select att1 from local_table) ,
select /*+ RULE DRIVING_SITE (s2) */  * from  Table2 s2 where  condition in (select att1 from local_table)
where att_table_1 = att_table_2  -- sic

The crux of the problem was that the two separate statements individually produced an acceptable execution plan but the attempt to use the queries in inline views with a join resulted in a plan that (from the description) sounded like the result of Oracle merging the two inline views and running the two IN subqueries as FILTER (existence) subqueries.

We weren’t shown any execution plans and only had the title of the question (“Distributed sql query through multiple databases”) to give us the clue that there might be three different databases involved.

Obviously there are several questions worth asking when presented with this problem. The first being “can we have a more realistic piece of code”, also “which vesion of Oracle”, and “where are the execution plans”. I can’t help feeling that there’s more to the problem than just the three tables that seem to be suggested by the fragments supplied.

More significant, though, was the surprise that rule and driving_site should work together. There’s a long-standing (but incorrect) assertion that “any other hint invalidates the RULE hint”. I think I’ve published an example somewhere showing that /*+ unnest */ would affect an execution plan where the optimizer still obeyed the /*+ rule */ hint, and there’s an old post on this blog which points out that transformation and optimisation are (or were, at the time) independent of each other, implying that you could combine the rule hint with “transformational” hints and still end up with a rule-based execution plan.

Despite old memories suggesting the contrary my first thought was that the rule and driving_site hints couldn’t be working together – and that made it worth running a little test. Then one of the other specialists on the forums suggested using subquery factoring with the materialize hint – and I thought that probably wouldn’t help because when you insert into a global temporary table the driving site has to become the site that holds the global temporary tables (in fact this isn’t just a feature of GTTs). So there was another thing prompting me to run a test. (And then I suggested using the /*+ no_merge */ hint – but thought I’d check if that idea was going to work before I suggested it.)

So here’s a code sample to create some data, and the first two simple queries with calls for their predicted execution plans:

rem
rem     Script:         distributed_multi.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jul 2020
rem     Purpose:
rem
rem     Last tested
rem             19.3.0.0
rem             12.2.0.1
rem             11.2.0.4
rem

rem     create public database link test@loopback using 'test';
rem     create public database link test2@loopback using 'test2';

rem     create public database link orcl@loopback using 'orcl';
rem     create public database link orcl2@loopback using 'orcl2';

rem     create public database link orclpdb@loopback using 'orclpdb';
rem     create public database link orclpdb2@loopback using 'orclpdb2';

define m_target=test@loopback
define m_target2=test2@loopback

define m_target=orcl@loopback
define m_target2=orcl2@loopback

define m_target=orclpdb@loopback
define m_target2=orclpdb2@loopback

create table t0
as
select  *
from    all_objects
where   mod(object_id,4) = 1
;

create table t1
as
select  *
from    all_objects
where   mod(object_id,11) = 0
;

create table t2
as
select  *
from    all_Objects
where   mod(object_id,13) = 0
;

explain plan for
select  /*+ driving_site(t1) */
        t1.object_name, t1.object_id
from    t1@&m_target    t1
where
        t1.object_id in (
                select  t0.object_id
                from    t0
        )
;

select * from table(dbms_xplan.display);

explain plan for
select
        /*+ rule driving_site(t2) */
        t2.object_name, t2.object_id
from    t2@&m_target2   t2
where
        t2.object_id in (
                select  t0.object_id
                from    t0
        )
;

select * from table(dbms_xplan.display);

Reading from the top down – t0 is in the local database, t1 is in remote database 1, t2 is in remote database 2. I’ve indicated the creation and selection of a pair of public database links at the top of the script – in this case both of them are loopback links to the local database, but I’ve used substitition variables in the SQL to allow me to adjust which databases are the remote ones. Since there are no indexes on any of the tables the optimizer is very limited in its choice of execution plans, which are as follows in 19.3 (the oraclepdb/orclpdb2 links).

First, the query against t1@orclpdb1 – which will run cost-based:

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |  5168 |   287K|    57   (8)| 00:00:01 |        |      |
|*  1 |  HASH JOIN SEMI        |      |  5168 |   287K|    57   (8)| 00:00:01 |        |      |
|   2 |   TABLE ACCESS FULL    | T1   |  5168 |   222K|    16   (7)| 00:00:01 | ORCLP~ |      |
|   3 |   REMOTE               | T0   | 14058 |   178K|    40   (5)| 00:00:01 |      ! | R->S |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A1"."OBJECT_ID"="A2"."OBJECT_ID")

Remote SQL Information (identified by operation id):
----------------------------------------------------
   3 - SELECT "OBJECT_ID" FROM "T0" "A2" (accessing '!' )

Note
-----
   - fully remote statement

You’ll note that operation 3 is simply REMOTE, and t0 is the object accessed – which means this query is behaving as if the (local) t0 table is the remote one as far as the execution plan is concerned. The IN-OUT column tells us that this operation is “Remote to Serial” (R->S)” and the instance called to is named “!” which is how the local database is identified in the plan from a remote database.

We can also see that the execution plan gives us the “Remote SQL Information” for operation 2 – and that’s the text of the query that gets sent by the driving site to the instance that holds the object of interest. In this case the query is simply selecting the object_id values from all the rows in t0.

Now the plan for the query against t2@orclpdb2 which includes a /*+ rule */ hint:

-----------------------------------------------------------
| Id  | Operation              | Name     | Inst   |IN-OUT|
-----------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|          |        |      |
|   1 |  MERGE JOIN            |          |        |      |
|   2 |   SORT JOIN            |          |        |      |
|   3 |    TABLE ACCESS FULL   | T2       | ORCLP~ |      |
|*  4 |   SORT JOIN            |          |        |      |
|   5 |    VIEW                | VW_NSO_1 | ORCLP~ |      |
|   6 |     SORT UNIQUE        |          |        |      |
|   7 |      REMOTE            | T0       |      ! | R->S |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("A1"."OBJECT_ID"="OBJECT_ID")
       filter("A1"."OBJECT_ID"="OBJECT_ID")

Remote SQL Information (identified by operation id):
----------------------------------------------------
   7 - SELECT /*+ RULE */ "OBJECT_ID" FROM "T0" "A2" (accessing '!' )

Note
-----
   - fully remote statement
   - rule based optimizer used (consider using cbo)

The most striking feature of this plan is that it is an RBO (rule based optimizer) plan not a cost-based plan – and the Note section confirms that observation. We can also see that the Remote SQL Information is echoing the /*+ RULE */ hint back in it’s query against t0. Since the query is operating rule-based the hash join mechanism is not available (it’s a costed path – it needs to know the size of the data that will be used in the build table), and that’s why the plan is using a sort/merge join.

Following the “incremental build” strategy for writing SQL all we have to do as the next step of producing the final code is put the two queries into separate views and join them:

explain plan for
select  v1.*, v2.*
from    (
        select  /*+ driving_site(t1) */
                t1.object_name, t1.object_id
        from    t1@&m_target    t1
        where
                t1.object_id in (
                        select  t0.object_id
                        from    t0
                )
        )       v1,
        (
        select
                /*+ rule driving_site(t2) */
                t2.object_name, t2.object_id
        from    t2@&m_target2 t2
        where
                t2.object_id in (
                        select  t0.object_id
                        from    t0
                )
        )       v2
where
        v1.object_id = v2.object_id
;

select * from table(dbms_xplan.display);

And here’s the execution plan – which, I have to admit, gave me a bit of a surprise on two counts when I first saw it:

-----------------------------------------------------------
| Id  | Operation              | Name     | Inst   |IN-OUT|
-----------------------------------------------------------
|   0 | SELECT STATEMENT       |          |        |      |
|   1 |  MERGE JOIN            |          |        |      |
|   2 |   MERGE JOIN           |          |        |      |
|   3 |    MERGE JOIN          |          |        |      |
|   4 |     SORT JOIN          |          |        |      |
|   5 |      REMOTE            | T2       | ORCLP~ | R->S |
|*  6 |     SORT JOIN          |          |        |      |
|   7 |      REMOTE            | T1       | ORCLP~ | R->S |
|*  8 |    SORT JOIN           |          |        |      |
|   9 |     VIEW               | VW_NSO_1 |        |      |
|  10 |      SORT UNIQUE       |          |        |      |
|  11 |       TABLE ACCESS FULL| T0       |        |      |
|* 12 |   SORT JOIN            |          |        |      |
|  13 |    VIEW                | VW_NSO_2 |        |      |
|  14 |     SORT UNIQUE        |          |        |      |
|  15 |      TABLE ACCESS FULL | T0       |        |      |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
       filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   8 - access("T2"."OBJECT_ID"="OBJECT_ID")
       filter("T2"."OBJECT_ID"="OBJECT_ID")
  12 - access("T1"."OBJECT_ID"="OBJECT_ID")
       filter("T1"."OBJECT_ID"="OBJECT_ID")

Remote SQL Information (identified by operation id):
----------------------------------------------------
   5 - SELECT /*+ RULE */ "OBJECT_NAME","OBJECT_ID" FROM "T2" "T2"
       (accessing 'ORCLPDB2.LOCALDOMAIN@LOOPBACK' )

   7 - SELECT /*+ RULE */ "OBJECT_NAME","OBJECT_ID" FROM "T1" "T1"
       (accessing 'ORCLPDB.LOCALDOMAIN@LOOPBACK' )

Note
-----
   - rule based optimizer used (consider using cbo)

The two surprises were that (a) the entire plan was rule-based, and (b) the driving_site() selection has disappeared from the plan.

Of course as soon as I actually started thinking about what I’d written (instead of trusting the knee-jerk “just stick the two bits together”) the flaw in the strategy became obvious.

• Either the whole query runs RBO or it runs CBO – you can’t split the planning.
• In the words of The Highlander “There can be only one” (driving site that is) – only one of the database involved will decide how to decompose and distribute the query.

It’s an interesting detail that the /*+ rule */ hint seems to have pushed the whole query into the arms of the RBO despite being buried somewhere in the depths of the query rather than being in the top level query block – but we’ve seen that before in some old data dictionary views.

The complete disregard for the driving_site() hints is less interesting – there is, after all, a comment in the manuals somewhere to the effect that when two hints contradict each other they are both ignored. (But I did wonder why the Hint Report that should appear with 19.3 plans didn’t tell me that the hints had been observed but not used.)

The other problem (from the perspective of the OP) is that the two inline views have been merged so the join order no longer reflects the two isolated components we used to have. So let’s fiddle around a little bit to see how close we can get to what the OP wants. The first step would be to add the /*+ no_merge */ hint to both inline view, and eliminate one of the /*+ driving_site() */ hints to see what happens, and since we’re modern we’ll also get rid of the /*+ rule */ hint:

explain plan for
select  v1.*, v2.*
from    (
        select  /*+ qb_name(subq1) no_merge driving_site(t1) */
                t1.object_name, t1.object_id
        from    t1@&m_target    t1
        where
                t1.object_id in (
                        select  t0.object_id
                        from    t0
                )
        )       v1,
        (
        select
                /*+ qb_name(subq2) no_merge */
                t2.object_name, t2.object_id
        from    t2@&m_target2 t2
        where
                t2.object_id in (
                        select  t0.object_id
                        from    t0
                )
        )       v2
where
        v1.object_id = v2.object_id
;

select * from table(dbms_xplan.display);

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |  4342 |   669K|    72   (9)| 00:00:01 |        |      |
|*  1 |  HASH JOIN             |      |  4342 |   669K|    72   (9)| 00:00:01 |        |      |
|   2 |   VIEW                 |      |  4342 |   334K|    14   (8)| 00:00:01 |        |      |
|   3 |    REMOTE              |      |       |       |            |          |      ! | R->S |
|   4 |   VIEW                 |      |  5168 |   398K|    57   (8)| 00:00:01 |        |      |
|*  5 |    HASH JOIN SEMI      |      |  5168 |   287K|    57   (8)| 00:00:01 |        |      |
|   6 |     TABLE ACCESS FULL  | T1   |  5168 |   222K|    16   (7)| 00:00:01 | ORCLP~ |      |
|   7 |     REMOTE             | T0   | 14058 |   178K|    40   (5)| 00:00:01 |      ! | R->S |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A2"."OBJECT_ID"="A1"."OBJECT_ID")
   5 - access("A3"."OBJECT_ID"="A6"."OBJECT_ID")

Remote SQL Information (identified by operation id):
----------------------------------------------------
   3 - EXPLAIN PLAN INTO "PLAN_TABLE" FOR SELECT /*+ QB_NAME ("SUBQ2") NO_MERGE */
       "A1"."OBJECT_NAME","A1"."OBJECT_ID" FROM  (SELECT DISTINCT "A3"."OBJECT_ID"
       "OBJECT_ID" FROM "T0" "A3") "A2","T2"@ORCLPDB2.LOCALDOMAIN@LOOPBACK "A1" WHERE
       "A1"."OBJECT_ID"="A2"."OBJECT_ID" (accessing '!' )

   7 - SELECT "OBJECT_ID" FROM "T0" "A6" (accessing '!' )

Note
-----
   - fully remote statement

In this plan we can see that the /*+ driving_site() */ hint has been applied – the plan is presented from the point of view of orclpdb (the database holding t1). The order of the two inline views has apparently been reversed as we move from the statement to its plan – but that’s just a minor side effect of the hash join (picking the smaller result set as the build table).

Operations 5 – 7 tell us that t1 is treated as the local table and used for the build table in a hash semi-join, and then t0 is accessed by a call back to our database and its result set is used as the probe table.

From operation 3 (in the body of the plan, and in the Remote SQL Information) we see that orclpdb has handed off the entire t2 query block to a remote operation – which is ‘accessing “!”‘. But there’s a problem (in my opinion) in the SQL that it’s handing off – the text is NOT the text of our inline view; it’s already been through a heuristic transformation that has unnested the IN subquery of our original text into a “join distinct view” – if we had used a hint to force this transformation it would have been the /*+ unnest(UNNEST_INNERJ_DISTINCT_VIEW) */ variant.

SELECT /*+ NO_MERGE */
        "A1"."OBJECT_NAME","A1"."OBJECT_ID"
FROM
       (SELECT DISTINCT "A3"."OBJECT_ID" "OBJECT_ID" FROM "T0" "A3") "A2",
       "T2"@ORCLPDB2.LOCALDOMAIN@LOOPBACK "A1"
WHERE
        "A1"."OBJECT_ID"="A2"."OBJECT_ID"

I tried to change this by adding alternative versions of the /* unnest() */ hint to the original query, following the query block names indicated by the outline information (not shown), but it looks as if the code path constructs the Remote SQL operates without considering the main query hints – perhaps the decomposition code is simply following the code path of the old heuristic “I’ll do it if it’s legal” unnest. The drawback to this is that if the original form of the text had been sent to the other site the optimizer that had to handle it could have used cost-based query transformation and may have come up with a better plan.

You may be wondering why I left the /*+ driving_site() */ hint in one of the inline views rather than inserting it in the main query block. The answer is simple – it didn’t seem to work (even in 19.3) when I put /*+ driving_site(t1@subq1) */ in the main query block.

tl;dr

The optimizer has to operate rule-based or cost-based, it can’t do a bit of both in the same query – so if you’ve got a /*+ RULE */ hint that takes effect anywhere in the query the entire query will be optimised under the rule-based optimizer.

There can be only one driving site for a query, and if you manage to get multiple driving_site() hints in a query that contradict each other the optimizer will ignore all of them.

When the optimizer decomposes a distributed query and produces non-trivial components to send to remote sites you may find that some of the queries constructed for the remote sites have been subject to transformations that you cannot influence by hinting.

Footnote

I mentioned factored subqueries and the /*+ materialize */ option in the opening notes. In plans where the attempt to specify the driving site failed (i.e. when the query ran locally) the factored subqueries did materialize. In any plans where the driving site was a remote site the factored subqueries were always inline. This may well be related to the documented (though not always implemented) restriction that temporary tables cannot take part in distributed transactions.