12c Adaptive Joins Plus Statistics Feedback For Joins Cardinality Estimate Bug

I've encountered a bug at several clients that upgraded to Oracle 12c - 12.1.0.2 - that requires the combination of several new adaptive features introduced with Oracle 12c.

It needs an execution plan that makes use of adaptive join methods, plus at runtime the activation of the new "statistics feedback for joins" feature that was also introduced with Oracle 12c. Note that in 11.2 there was already the "cardinality feedback" feature that only applies to single table cardinality misestimates, but not to join cardinality misestimates.

In case then the join method used at runtime is a Nested Loop join - not necessarily the join method preferred initially, so a runtime switch from Hash to Nested Loop join also reproduces the problem - the "statistics feedback for joins" feature generates a bad OPT_ESTIMATE hint for the join cardinality that always seems to be one, like the following: OPT_ESTIMATE(... JOIN (...) ROWS=1.000000), no matter what the actual join cardinality observed was.

This can lead to very inefficient execution plans that get generated based on the "statistics feedback" on subsequent executions of the same statement, caused by the misleading join cardinality used to generate the new plan.

The good news is that in 12.2.0.1 and in 12.1, when making use of the new backport available that enables the same optimizer default settings than in 12.2 - for more information see this MOS document: Recommendations for Adaptive Features in Oracle Database 12c Release 1 (2187449.1) and for example Christian Antognini's post - the "statistics feedback for joins" feature is disabled by default (in fact in principle only the "adaptive joins" feature is left enabled, all other adaptive features more or less disabled), so the problem doesn't occur there. So one more good reason why installing this backport in 12.1 is a good idea.

However, when enabling this feature specifically or simply enabling all "adaptive statistics" features (OPTIMIZER_ADAPTIVE_STATISTICS = TRUE in 12.2 / 12.1 with backport) the problem also reproduces in 12.2.

If you don't make use of the recommended optimizer settings backport in 12.1 yet then setting "_optimizer_use_feedback" to FALSE prevents the problem, however this will disable both, the "cardinality feedback for single table" 11.2 feature as well as the "statistics feedback for joins" 12c feature.

In 12.2, there are two parameters related to "cardinality feedback", "_OPTIMIZER_USE_FEEDBACK" that controls the "cardinality feedback for single table" 11.2 feature and "_OPTIMIZER_USE_FEEDBACK_FOR_JOIN" that controls the new "statistics feedback for joins" feature. Hence, in 12.2, when enabling the "adaptive statistics" feature, the problem can be avoided by setting specifically "_OPTIMIZER_USE_FEEDBACK_FOR_JOIN" to FALSE, which would still leave the "cardinality feedback for single table" 11.2 feature enabled.

For more information regarding the various optimizer related settings and differences between 12.1 and 12.2 / 12.1 backport, see this very informative post by Christian Antognini.

Here is a simplified test case that allows reproducing the problem:

-----------------------------------------------------------------------
-- Adaptive joins combined with statistics feedback for joins
-- lead to join cardinality estimate of 1
-- caused by incorrect statistics feedback OPT_ESTIMATE hint generated
--
-- The problem seems to be related to the following combination:
--
-- - Adaptive join method selected
-- - Statistics feedback for joins kicks in
-- - The runtime join method used is Nested Loop join
--
-- Reproduced: 12.1.0.2
--             12.2.0.1 (with adaptive statistics features re-enabled)
--
-----------------------------------------------------------------------

set echo on

alter system flush shared_pool;

drop table t1 purge;

drop table t2 purge;

create table t1
as
select
        rownum as id
      , cast('9999' as varchar2(4)) as hist_ind
      , rpad('x', 200) as filler
from
        dual
connect by
        level <= 30000
order by
        dbms_random.value
;

exec dbms_stats.gather_table_stats(null, 't1', method_opt => 'for all columns size 1')

create unique index t1_idx on t1 (id);

create table t2
as
with
generator1 as
(
  select /*+
              cardinality(1e3)
              materialize
          */
          rownum as id
        , rpad('x', 100) as filler
  from
          dual
  connect by
          level <= 1e3
),
generator2 as
(
  select /*+
              cardinality(1e3)
              materialize
          */
          rownum as id
        , rpad('x', 100) as filler
  from
          dual
  connect by
          level <= 1e3
),
generator as
(
select /*+ leading(b a) */
        (a.id - 1) * 1e3 + b.id as id
from
        generator1 a
      , generator2 b
)
select
        case when id <= 10000 then 1 when id <= 20000 then -1 else id end as id
      , cast('N' as varchar2(1)) as some_ind
      , rpad('x', 200) as filler
from
      generator
;

exec dbms_stats.gather_table_stats(null, 't2', method_opt => 'for all columns size 1')

create /*unique*/ index t2_idx on t2 (id);

set echo on define on

column sql_id new_value sql_id

alter session set statistics_level = all;

-- Enable statistics feedback for joins in 12.2.0.1
alter session set optimizer_adaptive_statistics = true;

-- Disabling statistics feedback for joins prevents the bug
-- alter session set "_OPTIMIZER_USE_FEEDBACK_FOR_JOIN" = false;

-- Or disabling adaptive joins prevents the bug
-- alter session set "_OPTIMIZER_ADAPTIVE_PLANS" = false;

-- alter session set "_OPTIMIZER_NLJ_HJ_ADAPTIVE_JOIN" = false;

-- Not related to NL join optimized plan shapes
--alter session set "_nlj_batching_enabled" = 0;

--alter session set "_table_lookup_prefetch_size" = 0;

-- Expected number of rows, initial join NL, still "statistics feedback" kicks in (is it a bug that statistics feedback kicks in here?)
-- No join method switch
-- Bad OPT_ESTIMATE hint for join (ROWS=1) => bug
SELECT /*+ leading(a1) */ count(A1.SOME_IND)   FROM
T1 A0  INNER JOIN T2 A1
ON 1 = 1
AND A1.ID = A0.ID
WHERE 1 = 1
AND A0.HIST_IND       = '9999'
AND A0.ID between 20000 and 22000
;

-- Re-execute to have statistics feedback kick in
/

select prev_sql_id as sql_id from v$session where sid = userenv('sid');

select * from table(dbms_xplan.display_cursor('&sql_id', null, format => 'TYPICAL ALLSTATS LAST ADAPTIVE'));

select hint_text from V$SQL_REOPTIMIZATION_HINTS where sql_id = '&sql_id' and hint_text like '%JOIN%';

-- More than expected number of rows, initial join NL, "statistics feedback" kicks in
-- Switch from NL to HASH (at runtime and at re-optimization time)
-- Generated OPT_ESTIMATE hints are OK
SELECT /*+ leading(a1) */ count(A1.SOME_IND)   FROM
T1 A0  INNER JOIN T2 A1
ON 1 = 1
AND A1.ID = A0.ID
WHERE 1 = 1
AND A0.HIST_IND       = '9999'
AND A0.ID between 1 and 2
;

-- Re-execute to have statistics feedback kick in
/

select prev_sql_id as sql_id from v$session where sid = userenv('sid');

select * from table(dbms_xplan.display_cursor('&sql_id', null, format => 'TYPICAL ALLSTATS LAST ADAPTIVE'));

select hint_text from V$SQL_REOPTIMIZATION_HINTS where sql_id = '&sql_id' and hint_text like '%JOIN%';

-- Less than expected number of rows, initial join HASH, "statistics feedback" kicks in
-- Switch from HASH to NL (at runtime and at re-optimization time)
-- Bad OPT_ESTIMATE hint for join generated (ROWS=1) => bug
SELECT /*+ leading(a1) */ count(A1.SOME_IND)   FROM
T1 A0  INNER JOIN T2 A1
ON 1 = 1
AND A1.ID = A0.ID
WHERE 1 = 1
AND A0.HIST_IND       = '9999'
AND A0.ID between 2 and 20500
;

-- Re-execute to have statistics feedback kick in
/

select prev_sql_id as sql_id from v$session where sid = userenv('sid');

select * from table(dbms_xplan.display_cursor('&sql_id', null, format => 'TYPICAL ALLSTATS LAST ADAPTIVE'));

select hint_text from V$SQL_REOPTIMIZATION_HINTS where sql_id = '&sql_id' and hint_text like '%JOIN%';

-- Expected number of rows, initial join HASH, "statistics feedback" does not kick in
-- No join method switch
SELECT /*+ leading(a1) */ count(A1.SOME_IND)   FROM
T1 A0  INNER JOIN T2 A1
ON 1 = 1
AND A1.ID = A0.ID
WHERE 1 = 1
AND A0.HIST_IND       = '9999'
AND A0.ID between 20000 and 30000
;

-- Re-execute to have statistics feedback kick in
/

select prev_sql_id as sql_id from v$session where sid = userenv('sid');

select * from table(dbms_xplan.display_cursor('&sql_id', null, format => 'TYPICAL ALLSTATS LAST ADAPTIVE'));

select hint_text from V$SQL_REOPTIMIZATION_HINTS where sql_id = '&sql_id' and hint_text like '%JOIN%';

The critical part of the output looks like this:

SQL> -- Expected number of rows, initial join NL, still "statistics feedback" kicks in (is it a bug that statistics feedback kicks in here?)
SQL> -- No join method switch
SQL> -- Bad OPT_ESTIMATE hint for join (ROWS=1) => bug
SQL> SELECT /*+ leading(a1) */ count(A1.SOME_IND)   FROM
  2  T1 A0  INNER JOIN T2 A1
  3  ON 1 = 1
  4  AND A1.ID = A0.ID
  5  WHERE 1 = 1
  6  AND A0.HIST_IND       = '9999'
  7  AND A0.ID between 20000 and 22000
  8  ;

COUNT(A1.SOME_IND)
------------------
              2000

SQL>
SQL> -- Re-execute to have statistics feedback kick in
SQL> /

COUNT(A1.SOME_IND)
------------------
              2000

SQL>
SQL> select prev_sql_id as sql_id from v$session where sid = userenv('sid');

SQL_ID
-------------
8mqn521y28t58

SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id', null, format => 'TYPICAL ALLSTATS LAST ADAPTIVE'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8mqn521y28t58, child number 0
-------------------------------------
SELECT /*+ leading(a1) */ count(A1.SOME_IND)   FROM T1 A0  INNER JOIN
T2 A1 ON 1 = 1 AND A1.ID = A0.ID WHERE 1 = 1 AND A0.HIST_IND       =
'9999' AND A0.ID between 20000 and 22000

Plan hash value: 3258782287

--------------------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                                | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                         |        |      1 |        |       |  2036 (100)|          |      1 |00:00:00.02 |    4044 |     38 |
|     1 |  SORT AGGREGATE                          |        |      1 |      1 |    17 |            |          |      1 |00:00:00.02 |    4044 |     38 |
|- *  2 |   HASH JOIN                              |        |      1 |   2001 | 34017 |  2036   (1)| 00:00:01 |   2000 |00:00:00.02 |    4044 |     38 |
|     3 |    NESTED LOOPS                          |        |      1 |   2001 | 34017 |  2036   (1)| 00:00:01 |   2000 |00:00:00.02 |    4044 |     38 |
|     4 |     NESTED LOOPS                         |        |      1 |   2002 | 34017 |  2036   (1)| 00:00:01 |   2000 |00:00:00.02 |    2044 |     38 |
|-    5 |      STATISTICS COLLECTOR                |        |      1 |        |       |            |          |   2000 |00:00:00.01 |    2007 |      6 |
|     6 |       TABLE ACCESS BY INDEX ROWID BATCHED| T2     |      1 |   2002 | 14014 |  1974   (0)| 00:00:01 |   2000 |00:00:00.01 |    2007 |      6 |
|  *  7 |        INDEX RANGE SCAN                  | T2_IDX |      1 |   2002 |       |     7   (0)| 00:00:01 |   2000 |00:00:00.01 |       7 |      6 |
|  *  8 |      INDEX UNIQUE SCAN                   | T1_IDX |   2000 |      1 |       |     0   (0)|          |   2000 |00:00:00.01 |      37 |     32 |
|  *  9 |     TABLE ACCESS BY INDEX ROWID          | T1     |   2000 |      1 |    10 |     1   (0)| 00:00:01 |   2000 |00:00:00.01 |    2000 |      0 |
|- * 10 |    TABLE ACCESS FULL                     | T1     |      0 |      1 |    10 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |
--------------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("A1"."ID"="A0"."ID")
   7 - access("A1"."ID">=20000 AND "A1"."ID"<=22000)
   8 - access("A1"."ID"="A0"."ID")
       filter(("A0"."ID">=20000 AND "A0"."ID"<=22000))
   9 - filter("A0"."HIST_IND"='9999')
  10 - filter(("A0"."ID">=20000 AND "A0"."ID"<=22000 AND "A0"."HIST_IND"='9999'))

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

SQL_ID  8mqn521y28t58, child number 1
-------------------------------------
SELECT /*+ leading(a1) */ count(A1.SOME_IND)   FROM T1 A0  INNER JOIN
T2 A1 ON 1 = 1 AND A1.ID = A0.ID WHERE 1 = 1 AND A0.HIST_IND       =
'9999' AND A0.ID between 20000 and 22000

Plan hash value: 3258782287

-----------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                                | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                         |        |      1 |        |       |  2036 (100)|          |      1 |00:00:00.02 |    4044 |
|     1 |  SORT AGGREGATE                          |        |      1 |      1 |    17 |            |          |      1 |00:00:00.02 |    4044 |
|- *  2 |   HASH JOIN                              |        |      1 |      1 |    17 |  2036   (1)| 00:00:01 |   2000 |00:00:00.02 |    4044 |
|     3 |    NESTED LOOPS                          |        |      1 |      1 |    17 |  2036   (1)| 00:00:01 |   2000 |00:00:00.02 |    4044 |
|     4 |     NESTED LOOPS                         |        |      1 |   2002 |    17 |  2036   (1)| 00:00:01 |   2000 |00:00:00.01 |    2044 |
|-    5 |      STATISTICS COLLECTOR                |        |      1 |        |       |            |          |   2000 |00:00:00.01 |    2007 |
|     6 |       TABLE ACCESS BY INDEX ROWID BATCHED| T2     |      1 |   2002 | 14014 |  1974   (0)| 00:00:01 |   2000 |00:00:00.01 |    2007 |
|  *  7 |        INDEX RANGE SCAN                  | T2_IDX |      1 |   2002 |       |     7   (0)| 00:00:01 |   2000 |00:00:00.01 |       7 |
|  *  8 |      INDEX UNIQUE SCAN                   | T1_IDX |   2000 |      1 |       |     0   (0)|          |   2000 |00:00:00.01 |      37 |
|  *  9 |     TABLE ACCESS BY INDEX ROWID          | T1     |   2000 |      1 |    10 |     1   (0)| 00:00:01 |   2000 |00:00:00.01 |    2000 |
|- * 10 |    TABLE ACCESS FULL                     | T1     |      0 |      1 |    10 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
-----------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("A1"."ID"="A0"."ID")
   7 - access("A1"."ID">=20000 AND "A1"."ID"<=22000)
   8 - access("A1"."ID"="A0"."ID")
       filter(("A0"."ID">=20000 AND "A0"."ID"<=22000))
   9 - filter("A0"."HIST_IND"='9999')
  10 - filter(("A0"."ID">=20000 AND "A0"."ID"<=22000 AND "A0"."HIST_IND"='9999'))

Note
-----
   - statistics feedback used for this statement
   - this is an adaptive plan (rows marked '-' are inactive)


77 rows selected.

SQL>
SQL> select hint_text from V$SQL_REOPTIMIZATION_HINTS where sql_id = '&sql_id' and hint_text like '%JOIN%';

HINT_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OPT_ESTIMATE (@"SEL$58A6D7F6" JOIN ("A0"@"SEL$1" "A1"@"SEL$1") ROWS=1.000000 )

SQL>
SQL> -- More than expected number of rows, initial join NL, "statistics feedback" kicks in
SQL> -- Switch from NL to HASH (at runtime and at re-optimization time)
SQL> -- Generated OPT_ESTIMATE hints are OK
SQL> SELECT /*+ leading(a1) */ count(A1.SOME_IND)   FROM
  2  T1 A0  INNER JOIN T2 A1
  3  ON 1 = 1
  4  AND A1.ID = A0.ID
  5  WHERE 1 = 1
  6  AND A0.HIST_IND       = '9999'
  7  AND A0.ID between 1 and 2
  8  ;

COUNT(A1.SOME_IND)
------------------
             10000

SQL>
SQL> -- Re-execute to have statistics feedback kick in
SQL> /

COUNT(A1.SOME_IND)
------------------
             10000

SQL>
SQL> select prev_sql_id as sql_id from v$session where sid = userenv('sid');

SQL_ID
-------------
92rttcj6ntzqs

SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id', null, format => 'TYPICAL ALLSTATS LAST ADAPTIVE'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  92rttcj6ntzqs, child number 0
-------------------------------------
SELECT /*+ leading(a1) */ count(A1.SOME_IND)   FROM T1 A0  INNER JOIN
T2 A1 ON 1 = 1 AND A1.ID = A0.ID WHERE 1 = 1 AND A0.HIST_IND       =
'9999' AND A0.ID between 1 and 2

Plan hash value: 777836357

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                                | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                         |        |      1 |        |       |     7 (100)|          |      1 |00:00:00.04 |    1262 |     70 |       |       |          |
|     1 |  SORT AGGREGATE                          |        |      1 |      1 |    17 |            |          |      1 |00:00:00.04 |    1262 |     70 |       |       |          |
|  *  2 |   HASH JOIN                              |        |      1 |      2 |    34 |     7   (0)| 00:00:01 |  10000 |00:00:00.04 |    1262 |     70 |  2061K|  2061K| 1355K (0)|
|-    3 |    NESTED LOOPS                          |        |      1 |      2 |    34 |     7   (0)| 00:00:01 |  10000 |00:00:00.03 |    1258 |     40 |       |       |          |
|-    4 |     NESTED LOOPS                         |        |      1 |      3 |    34 |     7   (0)| 00:00:01 |  10000 |00:00:00.02 |    1258 |     40 |       |       |          |
|-    5 |      STATISTICS COLLECTOR                |        |      1 |        |       |            |          |  10000 |00:00:00.02 |    1258 |     40 |       |       |          |
|     6 |       TABLE ACCESS BY INDEX ROWID BATCHED| T2     |      1 |      3 |    21 |     6   (0)| 00:00:01 |  10000 |00:00:00.02 |    1258 |     40 |       |       |          |
|  *  7 |        INDEX RANGE SCAN                  | T2_IDX |      1 |      3 |       |     3   (0)| 00:00:01 |  10000 |00:00:00.01 |      23 |     40 |       |       |          |
|- *  8 |      INDEX UNIQUE SCAN                   | T1_IDX |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|- *  9 |     TABLE ACCESS BY INDEX ROWID          | T1     |      0 |      1 |    10 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  * 10 |    TABLE ACCESS BY INDEX ROWID BATCHED   | T1     |      1 |      1 |    10 |     1   (0)| 00:00:01 |      2 |00:00:00.01 |       4 |     30 |       |       |          |
|  * 11 |     INDEX RANGE SCAN                     | T1_IDX |      1 |      1 |       |     0   (0)|          |      2 |00:00:00.01 |       2 |     30 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("A1"."ID"="A0"."ID")
   7 - access("A1"."ID">=1 AND "A1"."ID"<=2)
   8 - access("A1"."ID"="A0"."ID")
       filter(("A0"."ID"<=2 AND "A0"."ID">=1))
   9 - filter("A0"."HIST_IND"='9999')
  10 - filter("A0"."HIST_IND"='9999')
  11 - access("A0"."ID">=1 AND "A0"."ID"<=2)

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

SQL_ID  92rttcj6ntzqs, child number 1
-------------------------------------
SELECT /*+ leading(a1) */ count(A1.SOME_IND)   FROM T1 A0  INNER JOIN
T2 A1 ON 1 = 1 AND A1.ID = A0.ID WHERE 1 = 1 AND A0.HIST_IND       =
'9999' AND A0.ID between 1 and 2

Plan hash value: 3588347061

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |        |      1 |        |       |  7982 (100)|          |      1 |00:00:00.13 |   29516 |       |       |          |
|   1 |  SORT AGGREGATE                       |        |      1 |      1 |    17 |            |          |      1 |00:00:00.13 |   29516 |       |       |          |
|*  2 |   HASH JOIN                           |        |      1 |  10000 |   166K|  7982   (1)| 00:00:01 |  10000 |00:00:00.13 |   29516 |  2061K|  2061K| 1356K (0)|
|*  3 |    TABLE ACCESS FULL                  | T2     |      1 |  10000 | 70000 |  7978   (1)| 00:00:01 |  10000 |00:00:00.12 |   29512 |       |       |          |
|*  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1     |      1 |      2 |    20 |     4   (0)| 00:00:01 |      2 |00:00:00.01 |       4 |       |       |          |
|*  5 |     INDEX RANGE SCAN                  | T1_IDX |      1 |      2 |       |     2   (0)| 00:00:01 |      2 |00:00:00.01 |       2 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("A1"."ID"="A0"."ID")
   3 - filter(("A1"."ID"<=2 AND "A1"."ID">=1))
   4 - filter("A0"."HIST_IND"='9999')
   5 - access("A0"."ID">=1 AND "A0"."ID"<=2)

Note
-----
   - statistics feedback used for this statement


71 rows selected.

SQL>
SQL> select hint_text from V$SQL_REOPTIMIZATION_HINTS where sql_id = '&sql_id' and hint_text like '%JOIN%';

HINT_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OPT_ESTIMATE (@"SEL$58A6D7F6" JOIN ("A0"@"SEL$1" "A1"@"SEL$1") ROWS=10000.000000 )

SQL>
SQL> -- Less than expected number of rows, initial join HASH, "statistics feedback" kicks in
SQL> -- Switch from HASH to NL (at runtime and at re-optimization time)
SQL> -- Bad OPT_ESTIMATE hint for join generated (ROWS=1) => bug
SQL> SELECT /*+ leading(a1) */ count(A1.SOME_IND)   FROM
  2  T1 A0  INNER JOIN T2 A1
  3  ON 1 = 1
  4  AND A1.ID = A0.ID
  5  WHERE 1 = 1
  6  AND A0.HIST_IND       = '9999'
  7  AND A0.ID between 2 and 20500
  8  ;

COUNT(A1.SOME_IND)
------------------
               500

SQL>
SQL> -- Re-execute to have statistics feedback kick in
SQL> /

COUNT(A1.SOME_IND)
------------------
               500

SQL>
SQL> select prev_sql_id as sql_id from v$session where sid = userenv('sid');

SQL_ID
-------------
c55rjg5mdxpph

SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id', null, format => 'TYPICAL ALLSTATS LAST ADAPTIVE'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  c55rjg5mdxpph, child number 0
-------------------------------------
SELECT /*+ leading(a1) */ count(A1.SOME_IND)   FROM T1 A0  INNER JOIN
T2 A1 ON 1 = 1 AND A1.ID = A0.ID WHERE 1 = 1 AND A0.HIST_IND       =
'9999' AND A0.ID between 2 and 20500

Plan hash value: 1011946885

-------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                      | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT               |        |      1 |        |       |  8227 (100)|          |      1 |00:00:00.13 |   30028 |
|     1 |  SORT AGGREGATE                |        |      1 |      1 |    17 |            |          |      1 |00:00:00.13 |   30028 |
|- *  2 |   HASH JOIN                    |        |      1 |  20500 |   340K|  8227   (1)| 00:00:01 |    500 |00:00:00.13 |   30028 |
|     3 |    NESTED LOOPS                |        |      1 |  20500 |   340K|  8227   (1)| 00:00:01 |    500 |00:00:00.13 |   30028 |
|     4 |     NESTED LOOPS               |        |      1 |        |       |            |          |    500 |00:00:00.13 |   29528 |
|-    5 |      STATISTICS COLLECTOR      |        |      1 |        |       |            |          |    500 |00:00:00.13 |   29512 |
|  *  6 |       TABLE ACCESS FULL        | T2     |      1 |  20500 |   140K|  7978   (1)| 00:00:01 |    500 |00:00:00.13 |   29512 |
|  *  7 |      INDEX UNIQUE SCAN         | T1_IDX |    500 |        |       |            |          |    500 |00:00:00.01 |      16 |
|  *  8 |     TABLE ACCESS BY INDEX ROWID| T1     |    500 |      1 |    10 |   248   (0)| 00:00:01 |    500 |00:00:00.01 |     500 |
|- *  9 |    TABLE ACCESS FULL           | T1     |      0 |  20501 |   200K|   248   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("A1"."ID"="A0"."ID")
   6 - filter(("A1"."ID"<=20500 AND "A1"."ID">=2))
   7 - access("A1"."ID"="A0"."ID")
       filter(("A0"."ID"<=20500 AND "A0"."ID">=2))
   8 - filter("A0"."HIST_IND"='9999')
   9 - filter(("A0"."ID"<=20500 AND "A0"."ID">=2 AND "A0"."HIST_IND"='9999'))

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

SQL_ID  c55rjg5mdxpph, child number 1
-------------------------------------
SELECT /*+ leading(a1) */ count(A1.SOME_IND)   FROM T1 A0  INNER JOIN
T2 A1 ON 1 = 1 AND A1.ID = A0.ID WHERE 1 = 1 AND A0.HIST_IND       =
'9999' AND A0.ID between 2 and 20500

Plan hash value: 1011946885

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |      1 |        |       |  7994 (100)|          |      1 |00:00:00.13 |   30028 |
|   1 |  SORT AGGREGATE               |        |      1 |      1 |    17 |            |          |      1 |00:00:00.13 |   30028 |
|   2 |   NESTED LOOPS                |        |      1 |      1 |    17 |  7994   (1)| 00:00:01 |    500 |00:00:00.13 |   30028 |
|   3 |    NESTED LOOPS               |        |      1 |    500 |    17 |  7994   (1)| 00:00:01 |    500 |00:00:00.13 |   29528 |
|*  4 |     TABLE ACCESS FULL         | T2     |      1 |    500 |  3500 |  7978   (1)| 00:00:01 |    500 |00:00:00.13 |   29512 |
|*  5 |     INDEX UNIQUE SCAN         | T1_IDX |    500 |      1 |       |     0   (0)|          |    500 |00:00:00.01 |      16 |
|*  6 |    TABLE ACCESS BY INDEX ROWID| T1     |    500 |      1 |    10 |     1   (0)| 00:00:01 |    500 |00:00:00.01 |     500 |
----------------------------------------------------------------------------------------------------------------------------------

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

   4 - filter(("A1"."ID"<=20500 AND "A1"."ID">=2))
   5 - access("A1"."ID"="A0"."ID")
       filter(("A0"."ID"<=20500 AND "A0"."ID">=2))
   6 - filter("A0"."HIST_IND"='9999')

Note
-----
   - statistics feedback used for this statement


69 rows selected.

SQL>
SQL> select hint_text from V$SQL_REOPTIMIZATION_HINTS where sql_id = '&sql_id' and hint_text like '%JOIN%';

HINT_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OPT_ESTIMATE (@"SEL$58A6D7F6" JOIN ("A0"@"SEL$1" "A1"@"SEL$1") ROWS=1.000000 )

SQL>
SQL> -- Expected number of rows, initial join HASH, "statistics feedback" does not kick in
SQL> -- No join method switch
SQL> SELECT /*+ leading(a1) */ count(A1.SOME_IND)   FROM
  2  T1 A0  INNER JOIN T2 A1
  3  ON 1 = 1
  4  AND A1.ID = A0.ID
  5  WHERE 1 = 1
  6  AND A0.HIST_IND       = '9999'
  7  AND A0.ID between 20000 and 30000
  8  ;

COUNT(A1.SOME_IND)
------------------
             10000

SQL>
SQL> -- Re-execute to have statistics feedback kick in
SQL> /

COUNT(A1.SOME_IND)
------------------
             10000

SQL>
SQL> select prev_sql_id as sql_id from v$session where sid = userenv('sid');

SQL_ID
-------------
4tj7bn17xcbad

SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id', null, format => 'TYPICAL ALLSTATS LAST ADAPTIVE'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4tj7bn17xcbad, child number 0
-------------------------------------
SELECT /*+ leading(a1) */ count(A1.SOME_IND)   FROM T1 A0  INNER JOIN
T2 A1 ON 1 = 1 AND A1.ID = A0.ID WHERE 1 = 1 AND A0.HIST_IND       =
'9999' AND A0.ID between 20000 and 30000

Plan hash value: 4274056747

----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                      | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT               |        |      1 |        |       |  8227 (100)|          |      1 |00:00:00.17 |   30434 |       |       |          |
|     1 |  SORT AGGREGATE                |        |      1 |      1 |    17 |            |          |      1 |00:00:00.17 |   30434 |       |       |          |
|  *  2 |   HASH JOIN                    |        |      1 |  10001 |   166K|  8227   (1)| 00:00:01 |  10000 |00:00:00.17 |   30434 |  1969K|  1969K| 1895K (0)|
|-    3 |    NESTED LOOPS                |        |      1 |  10001 |   166K|  8227   (1)| 00:00:01 |  10000 |00:00:00.14 |   29512 |       |       |          |
|-    4 |     NESTED LOOPS               |        |      1 |        |       |            |          |  10000 |00:00:00.14 |   29512 |       |       |          |
|-    5 |      STATISTICS COLLECTOR      |        |      1 |        |       |            |          |  10000 |00:00:00.13 |   29512 |       |       |          |
|  *  6 |       TABLE ACCESS FULL        | T2     |      1 |  10002 | 70014 |  7978   (1)| 00:00:01 |  10000 |00:00:00.13 |   29512 |       |       |          |
|- *  7 |      INDEX UNIQUE SCAN         | T1_IDX |      0 |        |       |            |          |      0 |00:00:00.01 |       0 |       |       |          |
|- *  8 |     TABLE ACCESS BY INDEX ROWID| T1     |      0 |      1 |    10 |   248   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|  *  9 |    TABLE ACCESS FULL           | T1     |      1 |  10001 |    97K|   248   (0)| 00:00:01 |  10001 |00:00:00.01 |     922 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("A1"."ID"="A0"."ID")
   6 - filter(("A1"."ID"<=30000 AND "A1"."ID">=20000))
   7 - access("A1"."ID"="A0"."ID")
       filter(("A0"."ID">=20000 AND "A0"."ID"<=30000))
   8 - filter("A0"."HIST_IND"='9999')
   9 - filter(("A0"."ID">=20000 AND "A0"."HIST_IND"='9999' AND "A0"."ID"<=30000))

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)


37 rows selected.

SQL>
SQL> select hint_text from V$SQL_REOPTIMIZATION_HINTS where sql_id = '&sql_id' and hint_text like '%JOIN%';

no rows selected

SQL>

Note how in each case where the Nested Loop join gets used at runtime and "statistics feedback for joins" kicks in, the bad OPT_ESTIMATE hint gets generated.

I've discussed this case also with Nigel Bayliss at Oracle (the Optimizer Product Manager) and a corresponding bug was opened, so hopefully the problem gets addressed in the future.

Volatile Data, Dynamic Sampling And Shared Cursors

For the next couple of weeks I'll be picking up various random notes I've made during the sessions that I've attended at OOW. This particular topic was also a problem discussed recently at one of my clients, so it's certainly worth to be published here.

In one of the optimizer related sessions it was mentioned that for highly volatile data - for example often found in Global Temporary Tables (GTT) - it's recommended to use Dynamic Sampling rather than attempting to gather statistics. In particular for GTTs gathering statistics is problematic because the statistics are used globally and shared across all sessions. But GTTs could have a completely different data volume and distribution per session so sharing the statistics doesn't make sense in such scenarios.

So using Dynamic Sampling sounds like a reasonable advice and it probably is in many such cases.

However, there is still a potential problem even when resorting to Dynamic Sampling. If the cursors based on Dynamic Sampling get shared between sessions then they won't be re-optimized even if a GTT in one session is completely different from the one of the session that created the shared cursor previously.

So you can still end up with shared cursors and execution plans that are inappropriate to share across the different sessions. Using Dynamic Sampling doesn't address this issue. It addresses the issue if the cursors do not get shared, for example if they use literals and these literals differ so that different cursors will be generated based on the text matching.

Here is a simple test case that demonstrates the problem:

drop view v_gtt_dyn;

drop table gtt_dyn;

-- Create a Global Temporary Table with an index on it
create global temporary table gtt_dyn (
id number not null
, vc1 varchar2(100)
, filler varchar2(255)
)
on commit preserve rows
;

create index gtt_dyn_idx on gtt_dyn (id);

-- Create a simple view - it will become obvious later
-- why this has been used
create or replace view v_gtt_dyn as select * from gtt_dyn;

-- Run in Session 1
set echo on timing on

-- Unique value in ID column
insert into gtt_dyn
select
rownum as id
, rpad('x', 100) as vc1
, rpad('y', 255) as filler
from
dual
connect by level <= 10000
;

commit;

alter session set statistics_level = all;

alter session set optimizer_dynamic_sampling = 2;

select * from v_gtt_dyn where id = 10 and rownum > 1;

set linesize 200 pagesize 0 trimspool on tab off

select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

-- Run in Session 2
set echo on timing on

-- Single value in ID column
insert into gtt_dyn
select
10 as id
, rpad('x', 100) as vc1
, rpad('y', 255) as filler
from
dual
connect by level <= 10000
;

commit;

alter session set statistics_level = all;

alter session set optimizer_dynamic_sampling = 2;

select * from v_gtt_dyn where id = 10 and rownum > 1;

set linesize 200 pagesize 0 trimspool on tab off

select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

Now this is probably an extreme case of data distribution differences but if you run it you'll see it makes the point obvious: In the second session the data distribution of the GTT is completely different, and although there are no statistics gathered on the GTT and hence Dynamic Sampling gets used to arrive at an execution plan, the plan gets shared in the second session (there is only a child number 0) - but the plan is completely inappropriate for the data distribution of the GTT in the that session, you just need to look at the E-Rows and A-Rows columns of the runtime profile:

SQL> select * from v_gtt_dyn where id = 10 and rownum > 1;

no rows selected

Elapsed: 00:00:00.07
SQL>
SQL> set linesize 200 pagesize 0 trimspool on tab off
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
SQL_ID bjax3mksw1uza, child number 0
-------------------------------------
select * from v_gtt_dyn where id = 10 and rownum > 1

Plan hash value: 471827990

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.08 | 1117 |
| 1 | COUNT | | 1 | | 0 |00:00:00.08 | 1117 |
|* 2 | FILTER | | 1 | | 0 |00:00:00.08 | 1117 |
| 3 | TABLE ACCESS BY INDEX ROWID| GTT_DYN | 1 | 1 | 10000 |00:00:00.06 | 1117 |
|* 4 | INDEX RANGE SCAN | GTT_DYN_IDX | 1 | 1 | 10000 |00:00:00.02 | 63 |
-------------------------------------------------------------------------------------------------------

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

2 - filter(ROWNUM>1)
4 - access("ID"=10)

Note
-----
- dynamic sampling used for this statement (level=2)

Imagine a more complex plan with joins and a larger data volume and this is a receipt for disaster.

If this problem cannot be addressed from application side by helping the database to generate different cursors for the different data distributions (for example by simply adding different predicates that don't change the result like 1 = 1, 2 = 2 etc.) then you might be able to handle the issue by using Virtual Private Database (VPD, aka. Row Level Security / RLS, Fine Grained Access Control / FGAC). I've already demonstrated the general approach in the past here, but in this case a slightly more sophisticated approach could make sense.

By adding the following code and RLS policy I can drive Oracle to perform a re-optimization only in those cases where it is appropriate. This limits the damage that the general approach does to the Shared Pool by generating potentially numerous child cursors unconditionally.

create or replace package pkg_rls_force_hard_parse is
function force_hard_parse (in_schema varchar2, in_object varchar2) return varchar2;
end pkg_rls_force_hard_parse;
/

create or replace package body pkg_rls_force_hard_parse is
-- Cache the count in session state
g_cnt number;

function force_hard_parse (in_schema varchar2, in_object varchar2) return varchar2
is
s_predicate varchar2(100);
begin
-- Only execute query once in session
-- Change if re-evaluation is desired
if g_cnt is null then
select
count(*)
into
g_cnt
from
gtt_dyn
where
id = 10
and rownum <= 10;
end if;

-- We end up with exactly two child cursors
-- with the desired different plans
-- These child cursors will be shared accordingly
if g_cnt > 1 then
s_predicate := '1 = 1';
else
s_predicate := '2 = 2';
end if;

return s_predicate;
end force_hard_parse;
end pkg_rls_force_hard_parse;
/

-- CONTEXT_SENSITIVE avoids re-evaluation of policy function at execution time
-- Note however that it doesn't avoid re-evaluation at parse time
exec DBMS_RLS.ADD_POLICY (USER, 'v_gtt_dyn', 'hard_parse_policy', USER, 'pkg_rls_force_hard_parse.force_hard_parse', 'select', policy_type => DBMS_RLS.CONTEXT_SENSITIVE);

Now if you repeat above exercise - ideally with SQL trace enabled to see the additional acitivity caused by the RLS policy - you'll notice that the different sessions will end up with different child cursors and execution plans based on the check made.

Now the reason why the view is in place might become obvious: A RLS policy on the base table would have lead to an infinite recursive execution of the RLS policy function due to the query performed within the function. There are other obvious options how to deal with that, for example storing the RLS policy function in a separate schema with the EXEMPT ACCESS POLICY privilege should also work.

This is the result in the second session now:

SQL> select * from v_gtt_dyn where id = 10 and rownum > 1;

no rows selected

Elapsed: 00:00:00.12
SQL>
SQL> set linesize 200 pagesize 0 trimspool on tab off
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
SQL_ID bjax3mksw1uza, child number 1
-------------------------------------
select * from v_gtt_dyn where id = 10 and rownum > 1

Plan hash value: 424976618

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.04 | 1003 |
| 1 | COUNT | | 1 | | 0 |00:00:00.04 | 1003 |
|* 2 | FILTER | | 1 | | 0 |00:00:00.04 | 1003 |
|* 3 | TABLE ACCESS FULL| GTT_DYN | 1 | 9288 | 10000 |00:00:00.03 | 1003 |
-----------------------------------------------------------------------------------------

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

2 - filter(ROWNUM>1)
3 - filter("ID"=10)

Note
-----
- dynamic sampling used for this statement (level=2)

Notice how a second child cursor has been generated and that the cardinality estimate is now much closer to the reality.

Adaptive Cursor Sharing / Cardinality Feedback

I was curious to see if recent features like Adaptive Cursor Sharing or Cardinality Feedback would be able to solve the issue when using the 11g releases.

Cardinality Feedback (introduced in 11.2) unfortunately doesn't get used in the scenario described here, because Dynamic Sampling disables Cardinality Feedback in the current implementation.

Note that the usage of bind variables also disables Cardinality Feedback for those parts of the plan affected by the bind variables - as described in the Optimizer blog post that can be found here.

So may be Adaptive Cursor Sharing (ACS, introduced in 11.1) can come to rescue in case bind variables get used.

Of course the usage of bind variables increases the probability of cursor sharing in above scenario. As already outlined in a previous note ACS is a "reactive" and "non-persistent" feature, so it will only be able to correct things that have already been going wrong at least once. Furthermore if the ACS information gets aged out of the Shared Pool again mistakes will have to be repeated to get recognized by ACS.

However it is interesting to note that I wasn't able to get ACS working in a slightly modified scenario like this (without the RLS policy in place of course):

-- Session 1
set echo on timing on

set linesize 200 pagesize 0 trimspool on tab off

insert into gtt_dyn
select
rownum as id
, rpad('x', 100) as vc1
, rpad('y', 255) as filler
from
dual
connect by level <= 10000
;

commit;

alter session set statistics_level = all;

variable n1 number

exec :n1 := 10

select * from v_gtt_dyn where id <= :n1 and rownum > 1;

select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

-- Session 2
set echo on timing on

set linesize 200 pagesize 0 trimspool on tab off

insert into gtt_dyn
select
10 as id
, rpad('x', 100) as vc1
, rpad('y', 255) as filler
from
dual
connect by level <= 10000
;

commit;

alter session set statistics_level = all;

variable n1 number

exec :n1 := 10

select * from v_gtt_dyn where id <= :n1 and rownum > 1;

select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

-- Second execution to allow ACS potentially kicking in
select * from v_gtt_dyn where id <= :n1 and rownum > 1;

select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

There are some interesting points to notice when running this example:

1. A cursor that uses non-equal operators like above less or equal together with bind variables usually gets marked as "bind-sensitive" and will be monitored by ACS. But in the above case the cursor was not marked as bind sensitive and hence ACS didn't even bother to monitor

2. Consequently the two sessions share the single child cursor and the problem is not addressed by ACS even in subsequent executions

3. It looks like that again the usage of Dynamic Sampling disables ACS

Looking at the way ACS manages the Cursor Sharing criteria (check V$SQL_CS_SELECTIVITY for example) I see the problem that ACS probably wouldn't support the fact that the same value for the bind variable resulted in a completely different selectivity range.

May be this is an explanation why ACS is not activated for cursors that use Dynamic Sampling - ACS may only be able to cope with different bind value ranges that lead to different selectivities.

So even when using bind variables and 11g with ACS it looks like that only the RLS policy approach allows to address this issue from a database-only side. Ideally the application should be "data-aware" in such cases and help the database accordingly to arrive at reasonable execution plans by actively unsharing the cursors.