Oracle mechanics

The elements of Bind-Aware Cursor Sharing and Cardinality Feedback in Oracle 11g

Sometimes there may be noticed unusual Oracle 11g behavior — by some not obvious reasons the magic Adaptive/Extended Cursor Sharing is not used. And this can happen exactly where execution plan changes are required for better SQL with new set of binds performance. From Oracle Optimizer team we know that Bind Aware Cursor Sharing (BASC) is complex technology which consists of couple of consequential applying Adaptive Cursor Sharing (ACS) and Extended Cursor Sharing (ECS) technologies:

«How does ACS work?

  • Monitor queries where ECS is applicable (binds were peeked, predicate shape)
  • Identify queries with variable data volume
  • Enable ECS for these queries»

In MOS Adaptive Cursor Sharing Overview [ID 740052.1] can be found a list of preventing ECS usage conditions:

If any of the following checks fail ECS will be disabled :

  • Extended cursor sharing is disabled
  • The query has no binds
  • Parallel query is used
  • Certain parameters like («bind peeking»=false) are set
  • Hints are in use
  • Outlines are being used
  • It is a recursive query
  • The number of binds in a given sql statement are greater than 14

What we have to do if all conditions were complied and ECS still doesn’t work? Initial problem was successfully resolved by adding /*+ BIND_AWARE*/ hint into query — according to Bug 9532657:

For cursors where one knows it wants to be bind aware then add a /*+ BIND_AWARE */ hint. This makes the cursor aware of bind values rather than automatically trying to work out …

- fortunately, there was possibility to change the query text in our case.

But questions remain: how to force or forbid BACS technology usage on system / session / single query level?

On RUOUG conference I presented simple SQL*Plus demonstration of Bind Aware Cursor Sharing (as well as closely linked Cardinality Feedback) elements on Oracle 11.2.0.2. This note explains some interesting test results

These parameters seems to be related to ACS/ECS management:

SQL> @param_ _cursor_sharing
NAME                                   VALUE IS_DEF IS_MOD IS_ADJ DSC
-------------------------------------- ----- ------ ------ ------ ----------------------------------------------------------
_optimizer_adaptive_cursor_sharing     TRUE   TRUE  FALSE  FALSE  optimizer adaptive cursor sharing
_optimizer_extended_cursor_sharing     UDO    TRUE  FALSE  FALSE  optimizer extended cursor sharing
_optimizer_extended_cursor_sharing_rel SIMPLE TRUE  FALSE  FALSE  optimizer extended cursor sharing for relational operators

Test schema DDL

SQL> create table bacs(n1 number, n2 number, c1 char(2000));

Table created.

SQL> create index bacs_n1_idx on bacs(n1) nologging tablespace users;

Index created.

SQL> create index bacs_n2_idx on bacs(n2) nologging tablespace users;

Index created.

Test data (skewed)

SQL> insert /*+ append */ into bacs
2  select level, 1, 1
3  from dual
4  connect by level <= 102400
5  union all
6  select 1, level, 1
7  from dual
8  connect by level <= 102400;

204800 rows created.

BACS without histograms: the usual behavior

Oracle by default gathers the table statistics without column histograms (despite the obvious skewed data presence):

SQL> exec DBMS_STATS.gather_table_stats(ownname => NULL,tabname =>  'BACS');

PL/SQL procedure successfully completed.

SQL> select column_name, histogram from user_tab_col_statistics where table_name = 'BACS';
COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
N1                             NONE
N2                             NONE
C1                             NONE

Test query with rare bind variables values:

SQL> var v1 number
SQL> var v2 number
SQL> exec :v1 := 1000; :v2 := 1;

PL/SQL procedure successfully completed.
SQL> select/*+ gather_plan_statistics bacs*/ count(distinct n2) from bacs where n1 = :v1 and n2 >= :v2;

COUNT(DISTINCTN2)
-----------------
                1

1 row selected.

Elapsed: 00:00:00.04

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('','','basic +iostats +cost +note -outline -predicate last'));

PLAN_TABLE_OUTPUT
------------------------

Plan hash value: 3778004472

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |      1 |        |     3 (100)|      1 |00:00:00.01 |       3 |      1 |
|   1 |  SORT AGGREGATE                |             |      1 |      1 |            |      1 |00:00:00.01 |       3 |      1 |
|   2 |   VIEW                         | VW_DAG_0    |      1 |      2 |     3  (34)|      1 |00:00:00.01 |       3 |      1 |
|   3 |    HASH GROUP BY               |             |      1 |      2 |     3  (34)|      1 |00:00:00.01 |       3 |      1 |
|   4 |     TABLE ACCESS BY INDEX ROWID| BACS        |      1 |      2 |     2   (0)|      1 |00:00:00.01 |       3 |      1 |
|   5 |      INDEX RANGE SCAN          | BACS_N1_IDX |      1 |      2 |     1   (0)|      1 |00:00:00.01 |       2 |      0 |
------------------------------------------------------------------------------------------------------------------------------

Ok, everything is quite predictable, let’s try frequent bind values:

SQL> exec :v1 := 1; :v2 := 1;

PL/SQL procedure successfully completed.

SQL> select/*+ gather_plan_statistics bacs*/ count(distinct n2) from bacs where n1 = :v1 and n2 >= :v2;

COUNT(DISTINCTN2)
-----------------
102400

1 row selected.

Elapsed: 00:00:06.19

Child cursor looks bind sensitive (i.e. monitored — V$SQL.IS_BIND_SENSITIVE=Y), and not bind aware (V$SQL.IS_BIND_AWARE=N) so far:

SQL> @v$sql_bacs 9u8vgmxdk9bdz

old   9: from v$sql where sql_id = '&1'
new   9: from v$sql where sql_id = '9u8vgmxdk9bdz'

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS

------------- ------------ --------------- ----------------- ----------------- ----------------- ----------

9u8vgmxdk9bdz            0      3778004472 Y                 N                 Y                          2

After second query execution with the same frequent binds:

PLAN_TABLE_OUTPUT
------------------------

Plan hash value: 3778004472

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |      1 |        |     3 (100)|      1 |00:00:00.15 |   34316 |      1 |
|   1 |  SORT AGGREGATE                |             |      1 |      1 |            |      1 |00:00:00.15 |   34316 |      1 |
|   2 |   VIEW                         | VW_DAG_0    |      1 |      2 |     3  (34)|    102K|00:00:00.16 |   34316 |      1 |
|   3 |    HASH GROUP BY               |             |      1 |      2 |     3  (34)|    102K|00:00:00.14 |   34316 |      1 |
|   4 |     TABLE ACCESS BY INDEX ROWID| BACS        |      1 |      2 |     2   (0)|    102K|00:00:00.11 |   34316 |      1 |
|   5 |      INDEX RANGE SCAN          | BACS_N1_IDX |      1 |      2 |     1   (0)|    102K|00:00:00.02 |     181 |      0 |
------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - cardinality feedback used for this statement

«Note» section contains interesting phrase cardinality feedback used for this statement but this did not change plan itself.

As far as I understand ACS uses Cardinality Feedback (CF) as one of information sources for decision about marking the cursor as bind-aware (V$SQL.IS_BIND_AWARE) and triggering the new execution plan generation by ECS.

ECS does not use Cardinality Feedback, at least in form of OPT_ESTIMATE hints in 10053 trace

New child bind aware cursor (with the same plan hash value) appeared in V$SQL (CHILD_NUMBER 1), and old child cursor became not shareable (IS_SHAREABLE=N)

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
9u8vgmxdk9bdz            0      3778004472 Y                 N                 N                          2
9u8vgmxdk9bdz            1      3778004472 Y                 Y                 Y                          1

For the next repeated query executions with different rare bind values Oracle chooses the same child cursor #1 and don’t try to create new:

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
9u8vgmxdk9bdz            0      3778004472 Y                 N                 N                          2
9u8vgmxdk9bdz            1      3778004472 Y                 Y                 Y                          3

Without enough information about db objects (i.e. without column histograms) ECS has not opportunity to build new execution plan. But Oracle BACS tries to do this — luckily, only once

To be honest, in this case ECS can easily get row source stats from sql plan monitor statistics (because query with frequent bind variables consumed more than 5 seconds for its first execution in this testcase):

SQL> @V$SQL_PLAN_MONITOR_LAST 9u8vgmxdk9bdz

SQL_PLAN_HASH_VALUE PLAN_OPERATION    PLAN_OPTIONS    PLAN_OBJECT_NAME  PLAN_CARDINALITY OUTPUT_ROWS
------------------- ----------------- --------------- ----------------- ---------------- -----------
3778004472          SELECT STATEMENT                                                               1
3778004472          SORT              AGGREGATE                                        1           1
3778004472          VIEW                              VW_DAG_0                         2      102400
3778004472          HASH              GROUP BY                                         2      102400
3778004472          TABLE ACCESS      BY INDEX ROWID  BACS                             2      102401
3778004472          INDEX             RANGE SCAN      BACS_N1_IDX                      2      102401

ECS doesn’t use this information for new plan generation (for example in form of OPT_ESTIMATE hints like CF), and ACS decision about cursor bind-awareness is based on some optimizer statistics (V$SQL_SHARED_CURSOR.OPTIMIZER_STATS):

SQL> @v$sql_shared_cursor 9u8vgmxdk9bdz

SQL_ID         CHILD USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
------------- ------ ------------------ ---------------- ---------------- ---------------------------------------------
9u8vgmxdk9bdz      0 N                  Y                N                Bind mismatch(25)  |  extended_cursor_sharing
9u8vgmxdk9bdz      1 N                  N                Y

BACS with histograms: the usual behavior

SQL> exec DBMS_STATS.gather_table_stats(ownname => NULL,tabname =>  'BACS',method_opt => 'for all columns size skewonly');

PL/SQL procedure successfully completed.

SQL> select column_name, histogram from user_tab_col_statistics where table_name = 'BACS';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
N1                             HEIGHT BALANCED
N2                             HEIGHT BALANCED
C1                             FREQUENCY

Query with rare bind values produces the same plan with index access path:

PLAN_TABLE_OUTPUT
------------------------
EXPLAINED SQL STATEMENT:
------------------------
select/*+ gather_plan_statistics bacs+h*/ count(distinct n2) from bacs
where n1 = :v1 and n2 >= :v2

Plan hash value: 3778004472

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |      1 |        |     3 (100)|      1 |00:00:00.01 |       3 |      1 |
|   1 |  SORT AGGREGATE                |             |      1 |      1 |            |      1 |00:00:00.01 |       3 |      1 |
|   2 |   VIEW                         | VW_DAG_0    |      1 |      1 |     3  (34)|      1 |00:00:00.01 |       3 |      1 |
|   3 |    HASH GROUP BY               |             |      1 |      1 |     3  (34)|      1 |00:00:00.01 |       3 |      1 |
|   4 |     TABLE ACCESS BY INDEX ROWID| BACS        |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       3 |      1 |
|   5 |      INDEX RANGE SCAN          | BACS_N1_IDX |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |      0 |
------------------------------------------------------------------------------------------------------------------------------

For the second query execution with frequent bind values ECS generates a new plan (using HASH JOIN instead of INDEX RANGE SCAN):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------

Plan hash value: 3674053097

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |      1 |        |  1265 (100)|      1 |00:00:00.17 |     565 |
|   1 |  SORT AGGREGATE       |                  |      1 |      1 |            |      1 |00:00:00.17 |     565 |
|   2 |   VIEW                | VW_DAG_0         |      1 |  75881 |  1265   (2)|    102K|00:00:00.18 |     565 |
|   3 |    HASH GROUP BY      |                  |      1 |  75881 |  1265   (2)|    102K|00:00:00.17 |     565 |
|   4 |     VIEW              | index$_join$_001 |      1 |  99981 |   954   (1)|    102K|00:00:03.60 |     565 |
|   5 |      HASH JOIN        |                  |      1 |        |            |    102K|00:00:03.58 |     565 |
|   6 |       INDEX RANGE SCAN| BACS_N1_IDX      |      1 |  99981 |   189   (2)|    102K|00:00:00.02 |     181 |
|   7 |       INDEX RANGE SCAN| BACS_N2_IDX      |      1 |  99981 |  1502   (2)|    204K|00:00:00.04 |     384 |
-----------------------------------------------------------------------------------------------------------------

Note
-----
   - cardinality feedback used for this statement

Again cardinality feedback is noticed (by ACS) and new bind-aware child cursor was generated respectively:

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
dq20zj849552k            0      3778004472 Y                 N                 Y                          2
dq20zj849552k            1      3674053097 Y                 Y                 Y                          1

After several execution with different rare bind values we can see some bind aware childs with different plans and usage statuses (V$SQL.IS_SHARABLE):

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
dq20zj849552k            0      3778004472 Y                 N                 N                          2
dq20zj849552k            1      3674053097 Y                 Y                 Y                          1
dq20zj849552k            2      3778004472 Y                 Y                 N                          2
dq20zj849552k            3      3778004472 Y                 Y                 Y                          1

This is adequate behavior described by Oracle Optimizer development team

BACS with histograms: changing data

SQL> delete from bacs where n1 = 1;

102401 rows deleted.

SQL> commit;

Commit complete.

Now there is not data with frequent values (n1 = 1) in table. After several test query executions with frequent bind values (n1 = 1):

SQL > exec :v1 := 1; :v2 := 1;
PL/SQL procedure successfully completed.
SQL > select/*+ gather_plan_statistics bacs+h*/ count(distinct n2) from bacs where n1 = :v1 and n2 >= :v2;

COUNT(DISTINCTN2)
-----------------
0

1 row selected.

SQL > /
...

SQL > /
...
PLAN_TABLE_OUTPUT
---------------------------

Plan hash value: 3674053097

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |      1 |        |  1265 (100)|      1 |00:00:00.01 |     181 |
|   1 |  SORT AGGREGATE       |                  |      1 |      1 |            |      1 |00:00:00.01 |     181 |
|   2 |   VIEW                | VW_DAG_0         |      1 |  75881 |  1265   (2)|      0 |00:00:00.01 |     181 |
|   3 |    HASH GROUP BY      |                  |      1 |  75881 |  1265   (2)|      0 |00:00:00.01 |     181 |
|   4 |     VIEW              | index$_join$_001 |      1 |  99981 |   954   (1)|      0 |00:00:00.01 |     181 |
|   5 |      HASH JOIN        |                  |      1 |        |            |      0 |00:00:00.01 |     181 |
|   6 |       INDEX RANGE SCAN| BACS_N1_IDX      |      1 |  99981 |   189   (2)|      0 |00:00:00.01 |     181 |
|   7 |       INDEX RANGE SCAN| BACS_N2_IDX      |      0 |  99981 |  1502   (2)|      0 |00:00:00.01 |       0 |
-----------------------------------------------------------------------------------------------------------------

Note
-----
- cardinality feedback used for this statement

The same execution plan with HASH JOIN. In this case E-Rows estimations differs from the actual row source statistics very much. V$SQL shows that Oracle uses the same child cursor (child_number=1):

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
dq20zj849552k            0      3778004472 Y                 N                 N                          2
dq20zj849552k            1      3674053097 Y                 Y                 Y                          4
dq20zj849552k            2      3778004472 Y                 Y                 N                          2
dq20zj849552k            3      3778004472 Y                 Y                 Y                          1

Table statistics re-gathering has not any influence:

SQL> exec DBMS_STATS.gather_table_stats(ownname => NULL,tabname =>  'BACS',method_opt => 'for all columns size skewonly');

PL/SQL procedure successfully completed.

SQL> select column_name, histogram from user_tab_col_statistics where table_name = 'BACS';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
N1                             NONE
N2                             FREQUENCY
C1                             FREQUENCY
...
SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
dq20zj849552k            0      3778004472 Y                 N                 N                          2
dq20zj849552k            1      3674053097 Y                 Y                 Y                          7
dq20zj849552k            2      3778004472 Y                 Y                 N                          2
dq20zj849552k            3      3778004472 Y                 Y                 Y                          1

Attempts to force ECS  to generate new plan by using BIND_AWARE hint don’t give any results too.

As far as I understand, there is only one way to Oracle to produce new execution plan based on renewed statistics — to recreate cursor by:

  • changing the query text
  • purging the cursor from shared pool:
SQL> declare
2      v_address_hash varchar2(60);
3  begin
4      select address||', '||hash_value
5         into v_address_hash
6      from v$sqlarea
7      where sql_id = 'dq20zj849552k';
8  sys.dbms_shared_pool.purge(v_address_hash, 'c');
9  end;
10  /

PL/SQL procedure successfully completed.
  • or (as Ilya Deev suggested on RUOUG seminar) to gather table stats with no_invalidate => false flag, which leads to purging and replacing cursor in shared pool as well

One-off BACS applying (despite visible underlying data changes) is the obvious significant technology disadvantage

BACS with histograms: forcing ECS usage

With the same test schema and data. The same query with /*+ BIND_AWARE */ hint and rare binds values:

SQL> exec :v1 := 1000; :v2 := 1;

PL/SQL procedure successfully completed.

SQL> select/*+ BIND_AWARE*/ count(distinct n2) from bacs where n1 = :v1 and n2 >= :v2;

COUNT(DISTINCTN2)
-----------------
1

1 row selected.

PLAN_TABLE_OUTPUT
---------------------------

Plan hash value: 3778004472

----------------------------------------------------------------------------
| Id  | Operation                      | Name        | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |        |     3 (100)|
|   1 |  SORT AGGREGATE                |             |      1 |            |
|   2 |   VIEW                         | VW_DAG_0    |      1 |     3  (34)|
|   3 |    HASH GROUP BY               |             |      1 |     3  (34)|
|   4 |     TABLE ACCESS BY INDEX ROWID| BACS        |      1 |     2   (0)|
|   5 |      INDEX RANGE SCAN          | BACS_N1_IDX |      1 |     1   (0)|
----------------------------------------------------------------------------

and after first query execution with frequent binds:

SQL> exec :v1 := 1; :v2 := 1;

PL/SQL procedure successfully completed.

SQL> select/*+ BIND_AWARE*/ count(distinct n2) f

COUNT(DISTINCTN2)
-----------------
102400

1 row selected.

PLAN_TABLE_OUTPUT
------------------------
EXPLAINED SQL STATEMENT:
------------------------
select/*+ BIND_AWARE*/ count(distinct n2) from bacs where n1 = :v1 and
n2 >= :v2

Plan hash value: 3674053097

------------------------------------------------------------------------
| Id  | Operation             | Name             | E-Rows | Cost (%CPU)|
------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |        |  1274 (100)|
|   1 |  SORT AGGREGATE       |                  |      1 |            |
|   2 |   VIEW                | VW_DAG_0         |  76709 |  1274   (2)|
|   3 |    HASH GROUP BY      |                  |  76709 |  1274   (2)|
|   4 |     VIEW              | index$_join$_001 |    101K|   959   (1)|
|   5 |      HASH JOIN        |                  |        |            |
|   6 |       INDEX RANGE SCAN| BACS_N1_IDX      |    101K|   192   (2)|
|   7 |       INDEX RANGE SCAN| BACS_N2_IDX      |    101K|  1502   (2)|
------------------------------------------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level

IMPORTANT:  execution plan was changed for the first query execution (with frequent bind values) and without remark «cardinality feedback used for this statement» in Note section: no additional cardinality feedback statistics was used by ACS to decide is cursor bind-aware or not.

ACS mechanism was disabled by hint BIND_AWARE for this cursor, and cardinality feedback usage was not required to decide is cursor bind-aware or not, as far as I understand

ECS uses the only standard table statistics with column histogram data to build new plan, and this was showed in E-Rows plan values which differ from E-Rows values produced by true Cardinality Feedback applying (without binds, see below)

V$SQL shows that both child cursors were marked as bind-aware without repeated execution (ACS seems to be disabled despite the working query monitoring — IS_BIND_SENSITIVE=Y), and for frequent bind values the new plan was generated:

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
gvhr0bvnn3yph            0      3778004472 Y                 Y                 Y                          1
gvhr0bvnn3yph            1      3674053097 Y                 Y                 Y                          1

With different rare bind variables Oracle can use the same child cursor (CHILD_NUMBER=0) or generate the new one:

SQL> exec :v1 := 10; :v2 := 1;

PL/SQL procedure successfully completed.

SQL> select/*+ BIND_AWARE*/ count(distinct n2) from bacs where n1 = :v1 and n2 >= :v2;
...
SQL> exec :v1 := 10; :v2 := 10;

PL/SQL procedure successfully completed.

SQL> select/*+ BIND_AWARE*/ count(distinct n2) from bacs where n1 = :v1 and n2 >= :v2;

COUNT(DISTINCTN2)
-----------------
0

1 row selected.

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
gvhr0bvnn3yph            0      3778004472 Y                 Y                 N                          2
gvhr0bvnn3yph            1      3674053097 Y                 Y                 Y                          1
gvhr0bvnn3yph            2      3778004472 Y                 Y                 Y                          1

Hints BIND_AWARE / NO_BIND_AWARE can be used to disable ACS and to force ECS on the statement level only

Exactly the same results can be achieved with parameter setting _OPTIMIZER_ADAPTIVE_CURSOR_SHARING=FALSE on system and session levels, or in form /*+ opt_param(‘_optimizer_adaptive_cursor_sharing’ ‘false’)*/hint on statement level

BACS: how to disable?

Parameter _OPTIMIZER_EXTENDED_CURSOR_SHARING seems to be suitable for BACS disabling but it makes allowed values look strange:

SQL> @pvalid_ _optimizer_extended_cursor_sharing

PARAMETER                                          VALUE
-------------------------------------------------- -----
_optimizer_extended_cursor_sharing                 NONE
                                                   UDO

By default _OPTIMIZER_EXTENDED_CURSOR_SHARING = UDO (User Defined Operation), which complies parameter definition: Controls ECS for user-defined operators

Altering _OPTIMIZER_EXTENDED_CURSOR_SHARING value did not affect on ECS behavour not only for my initial test query (which obviously don’t use any user defined function) but also on queries with UDO (as I understand its concept):

SQL> create or replace function udo(n1 number, v1 number)
2   return number
3  as
4  begin
5    if n1 = v1 then return 1;
6       else return 0;
7    end if;
8  end;
9  /

Function created.

SQL> select/*+ opt_param('_optimizer_extended_cursor_sharing' 'none')*/ count(distinct n2) from bacs where udo(n1, :v1) = 1;
...
SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
57g18qxn49jpu            0       543507907 N                 N                 Y                       2

SQL> select/*+ opt_param('_optimizer_extended_cursor_sharing' 'udo')*/ count(distinct n2) from bacs where udo(n1, :v1) = 1;
...
SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
3nq0wp7qf6wp8            0       543507907 N                 N                 Y                       2

- or maybe my UDO realization is incorrect

Next parameter to check is _OPTIMIZER_EXTENDED_CURSOR_SHARING_REL («controls ECS for relational predicates»), and this was mentioned in MOS 11657468:

Disable adaptive cursor sharing by setting: _optimizer_adaptive_cursor_sharing = false AND _optimizer_extended_cursor_sharing_rel = «none»

With _optimizer_extended_cursor_sharing_rel=none the initial test query with rare or frequent bind sets is executed with the same plan:

PLAN_TABLE_OUTPUT
------------------------
EXPLAINED SQL STATEMENT:
------------------------
select/*+ opt_param('_optimizer_extended_cursor_sharing_rel' 'none')*/
count(distinct n2) from bacs where n1 = :v1 and n2 >= :v2

Plan hash value: 3778004472

----------------------------------------------------------------------------
| Id  | Operation                      | Name        | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |        |     3 (100)|
|   1 |  SORT AGGREGATE                |             |      1 |            |
|   2 |   VIEW                         | VW_DAG_0    |      2 |     3  (34)|
|   3 |    HASH GROUP BY               |             |      2 |     3  (34)|
|   4 |     TABLE ACCESS BY INDEX ROWID| BACS        |      2 |     2   (0)|
|   5 |      INDEX RANGE SCAN          | BACS_N1_IDX |      2 |     1   (0)|
----------------------------------------------------------------------------

, cursor is marked as NO-BIND-SENSITIVE:

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
4fdv1yngrgg82            0      3778004472 N                 N                 Y                          4

ECS (together with ACS) is disabled and query is not monitored (IS_BIND_SENSITIVE=N) not only for the simple relation operators (>,<,=,<>) but also for like (this was promised by Optimizer team for 11.2.0.2), substr and instr literal operators!

SQL> select/*+ opt_param('_optimizer_extended_cursor_sharing_rel' 'none')*/ count(distinct n2) from bacs where instr(to_char(n1),:v1) > 0 and c1 like '%'||:v2||'%';
...
Plan hash value: 543507907

---------------------------------------------------------------
| Id  | Operation            | Name     | E-Rows | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |        | 18739 (100)|
|   1 |  SORT AGGREGATE      |          |      1 |            |
|   2 |   VIEW               | VW_DAG_0 |    511 | 18739   (1)|
|   3 |    HASH GROUP BY     |          |    511 | 18739   (1)|
|   4 |     TABLE ACCESS FULL| BACS     |    512 | 18521   (1)|
---------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
...
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
...
SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
56hhxcaaqgfgd            0       543507907 N                 N                 Y                       3

SQL> select/*+ opt_param('_optimizer_extended_cursor_sharing_rel' 'none')*/ count(distinct n2) from bacs where instr(to_char(n1),:v1) > 0;
...
SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
4pjgd1q8hna53            0       543507907 N                 N                 Y                       1

Thus setting the parameter _OPTIMIZER_EXTENDED_CURSOR_SHARING_REL=NONE is suitable to disable ECS in many cases, and its influence is not limited to simple relation operators

Cardinality Feedback (CF)

From Oracle Optimizer blog post about CF:

…useful for queries where the data volume being processed is stable [one-off CF applying - the same affect was showed above for BACS]

For similar reasons, execution statistics for queries containing bind variables can be problematic

In Oracle Database 11gR2, cardinality feedback monitors and feeds back the following kinds of cardinalities:

  • Single table cardinality (after filter predicates are applied)
  • Index cardinality (after index filters are applied)
  • Cardinality produced by a group by or distinct operator

CF don’t required accurate db object statistics, so I used the same test schema and data without histograms:

SQL> select column_name, histogram from user_tab_col_statistics where table_name = 'BACS';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
N1                             NONE
N2                             NONE
C1                             NONE

First execution of the test query with frequent literal values:

SQL> select/*+ gather_plan_statistics cf*/ count(distinct n2) from bacs where n1 = 1 and n2 >= 1;

COUNT(DISTINCTN2)
-----------------
102400

Elapsed: 00:00:06.33

Plan hash value: 3778004472

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |      1 |        |     3 (100)|      1 |00:00:06.63 |   34316 |  34134 |
|   1 |  SORT AGGREGATE                |             |      1 |      1 |            |      1 |00:00:06.63 |   34316 |  34134 |
|   2 |   VIEW                         | VW_DAG_0    |      1 |      2 |     3  (34)|    102K|00:00:06.63 |   34316 |  34134 |
|   3 |    HASH GROUP BY               |             |      1 |      2 |     3  (34)|    102K|00:00:06.62 |   34316 |  34134 |
|   4 |     TABLE ACCESS BY INDEX ROWID| BACS        |      1 |      2 |     2   (0)|    102K|00:00:06.37 |   34316 |  34134 |
|   5 |      INDEX RANGE SCAN          | BACS_N1_IDX |      1 |      2 |     1   (0)|    102K|00:00:00.10 |     181 |      0 |
------------------------------------------------------------------------------------------------------------------------------

After second execution:

SQL> select/*+ gather_plan_statistics cf*/ count(distinct n2) from bacs where n1 = 1 and n2 >= 1;

Plan hash value: 3674053097

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |      1 |        |   955 (100)|      1 |00:00:00.19 |     565 |
|   1 |  SORT AGGREGATE       |                  |      1 |      1 |            |      1 |00:00:00.19 |     565 |
|   2 |   VIEW                | VW_DAG_0         |      1 |    102K|   955   (2)|    102K|00:00:00.20 |     565 |
|   3 |    HASH GROUP BY      |                  |      1 |    102K|   955   (2)|    102K|00:00:00.19 |     565 |
|   4 |     VIEW              | index$_join$_001 |      1 |    102K|   581   (1)|    102K|00:00:03.68 |     565 |
|   5 |      HASH JOIN        |                  |      1 |        |            |    102K|00:00:03.66 |     565 |
|   6 |       INDEX RANGE SCAN| BACS_N1_IDX      |      1 |    102K|   193   (1)|    102K|00:00:00.02 |     181 |
|   7 |       INDEX RANGE SCAN| BACS_N2_IDX      |      1 |    102K|  1499   (1)|    204K|00:00:00.03 |     384 |
-----------------------------------------------------------------------------------------------------------------

Note
-----
- cardinality feedback used for this statement

Optimizer trace (10053) shows next CF-related hints:

OPT_ESTIMATE (GROUP_BY ROWS=102400.000000 )
OPT_ESTIMATE (TABLE "BACS" ROWS=102401.000000 )
OPT_ESTIMATE (INDEX_SCAN "BACS" "BACS_N1_IDX" MIN=102401.000000 )
OPT_ESTIMATE (INDEX_FILTER "BACS" "BACS_N1_IDX" ROWS=102401.000000 ) */

- cardinality estimations exactly match V$SQL_PLAN_MONITOR data for this query (elapsed > 5 secs):

SQL_PLAN_HASH_VALUE PLAN_OPERATION    PLAN_OPTIONS    PLAN_OBJECT_NAME  PLAN_CARDINALITY OUTPUT_ROWS
------------------- ----------------- --------------- ----------------- ---------------- -----------
3778004472          SELECT STATEMENT                                                                1
3778004472          SORT              AGGREGATE                                         1           1
3778004472          VIEW                              VW_DAG_0                          2      102400
3778004472          HASH              GROUP BY                                          2      102400
3778004472          TABLE ACCESS      BY INDEX ROWID  BACS                              2      102401
3778004472          INDEX             RANGE SCAN      BACS_N1_IDX                       2      102401

OPT_ESTIMATE hint arguments come from other sources, which depend on STATISTICS_LEVEL: setting  STATISTICS_LEVEL=BASIC on system level disables ACS and CF (as well as sql plan monitoring feature) — Christian Antognini Impact of STATISTICS_LEVEL on Cardinality Feedback and Adaptive Cursor Sharing

CF usage (USE_FEEDBACK_STATS=Y) are clearly reflected in V$SQL_SHARED_CURSOR:

SQL_ID         CHILD USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
------------- ------ ------------------ ---------------- ---------------- -------------------------
5cvvajnzf6h03      0 Y                  N                N                Optimizer mismatch(13)  |
5cvvajnzf6h03      1 N                  N                N

Cardinality Feedback: how to disable?

Often described parameter _optimizer_use_feedback=false (true by default) may be used to disable CF on system/session level:

SQL> alter session set "_optimizer_use_feedback" = false;

as well as on statement level:

SQL> select /*+ opt_param('_optimizer_use_feedback' 'false')*/ ...

_fix_control 6699059 (used for CF enabling in latest 11.1.0.7.* patchsets) disables CF in 11.2 on system/session level:

SQL> alter session set "_fix_control"="6699059:OFF";

On statement level — hint /*+ OPT_PARAM(‘_fix_control’ ‘6699059:0′)*/ — works very strange and it is not suitable for CF disabling

SQL> select bugno, description, optimizer_feature_enable, value, is_default from v$session_fix_control where bugno = 6699059;
BUGNO DESCRIPTION                                                      OPTIMIZER_FEATURE_ENABLE       VALUE IS_DEFAULT
 ---------- ---------------------------------------------------------------- ------------------------- ---------- ----------
    6699059 enable cardinality feedback-based cursor sharing                 11.2.0.1                           1          1

Surprisingly, setting _OPTIMIZER_EXTENDED_CURSOR_SHARING_REL=NONE disables CF (as well as this disables ECS) for complicated relation operators

With default _OPTIMIZER_EXTENDED_CURSOR_SHARING_REL value:

SQL> @param_ _OPTIMIZER_EXTENDED_CURSOR_SHARING_REL

NAME                                       VALUE                                    IS_DEF
------------------------------------------ ---------------------------------------- ------
_optimizer_extended_cursor_sharing_rel     SIMPLE                                   TRUE

SQL> select/*+ gather_plan_statistics CF*/ count(distinct n2) from bacs where instr(to_char(n1),'1') > 0 and c1 like '%1%';

COUNT(DISTINCTN2)
-----------------
102400

1 row selected.

PLAN_TABLE_OUTPUT
--------------------------
Plan hash value: 543507907

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |      1 |        | 18736 (100)|      1 |00:00:03.06 |   68271 |  68267 |
|   1 |  SORT AGGREGATE      |          |      1 |      1 |            |      1 |00:00:03.06 |   68271 |  68267 |
|   2 |   VIEW               | VW_DAG_0 |      1 |    511 | 18736   (1)|    102K|00:00:03.06 |   68271 |  68267 |
|   3 |    HASH GROUP BY     |          |      1 |    511 | 18736   (1)|    102K|00:00:03.05 |   68271 |  68267 |
|   4 |     TABLE ACCESS FULL| BACS     |      1 |    512 | 18518   (1)|    145K|00:00:02.95 |   68271 |  68267 |
-----------------------------------------------------------------------------------------------------------------

SQL> select/*+ gather_plan_statistics CF*/ count(distinct n2) from bacs where instr(to_char(n1),'1') > 0 and c1 like '%1%';

PLAN_TABLE_OUTPUT
--------------------------
Plan hash value: 543507907

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |      1 |        | 67857 (100)|      1 |00:00:03.03 |   68271 |  68267 |
|   1 |  SORT AGGREGATE      |          |      1 |      1 |            |      1 |00:00:03.03 |   68271 |  68267 |
|   2 |   VIEW               | VW_DAG_0 |      1 |    102K| 67857   (1)|    102K|00:00:03.03 |   68271 |  68267 |
|   3 |    HASH GROUP BY     |          |      1 |    102K| 67857   (1)|    102K|00:00:03.02 |   68271 |  68267 |
|   4 |     TABLE ACCESS FULL| BACS     |      1 |    145K| 18518   (1)|    145K|00:00:02.90 |   68271 |  68267 |
-----------------------------------------------------------------------------------------------------------------

Note
-----
- cardinality feedback used for this statement

CF is successfully used, and this is clearly reflected:

  • in modified E-Rows column of execution plan
  • in «Note» plan section
  • in V$SQL_SHARED_CURSOR.USE_FEEDBACK_STATS value of course:
SQL_ID         CHILD USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
 ------------- ------ ------------------ ---------------- ---------------- -------------------------
 35vuppa4p6pyk      0 Y                  N                N                Optimizer mismatch(13)  |
 35vuppa4p6pyk      1 N                  N                N

Let’s try to change _optimizer_extended_cursor_sharing_rel value on statement level:

SQL> select/*+ gather_plan_statistics opt_param('_optimizer_extended_cursor_sharing_rel' 'none') CF*/ count(distinct n2) from bacs where instr(to_char(n1),'1') > 0 and c1 like '%1%';
...
SQL> /
SQL> /
SQL> /

PLAN_TABLE_OUTPUT
--------------------------
Plan hash value: 543507907

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |      1 |        | 18736 (100)|      1 |00:00:03.02 |   68271 |  68267 |
|   1 |  SORT AGGREGATE      |          |      1 |      1 |            |      1 |00:00:03.02 |   68271 |  68267 |
|   2 |   VIEW               | VW_DAG_0 |      1 |    511 | 18736   (1)|    102K|00:00:03.02 |   68271 |  68267 |
|   3 |    HASH GROUP BY     |          |      1 |    511 | 18736   (1)|    102K|00:00:03.01 |   68271 |  68267 |
|   4 |     TABLE ACCESS FULL| BACS     |      1 |    512 | 18518   (1)|    145K|00:00:02.92 |   68271 |  68267 |
-----------------------------------------------------------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
...
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')

«Outline Data» plan section shows that optimizer has accepted _optimizer_extended_cursor_sharing_rel modification, and E-Rows, v$sql, v$sql_shared_cursor confirm that CF was not used:

SQL> @v$sql 3f81v8dc7sk6u

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
3f81v8dc7sk6u            0       543507907 N                 N                 Y                          4

SQL> @v$sql_shared_cursor 3f81v8dc7sk6u

SQL_ID         CHILD USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
------------- ------ ------------------ ---------------- ---------------- ------------------------------------------------------------
3f81v8dc7sk6u      0 N                  N                N

Cardinality Feedback problem: low sensitivity

Let’s load the smallest data amount into the same test schema — only 8 rows instead of more than 200k in initial test:

SQL> create table bacs(n1 number, n2 number, c1 char(2000));

Table created.

SQL> create index bacs_n1_idx on bacs(n1) nologging tablespace users;

Index created.

SQL> create index bacs_n2_idx on bacs(n2) nologging tablespace users;

Index created.

SQL> insert /*+ append */ into bacs
2  select level, 1, 1
3  from dual
4  connect by level <= 4
5  union all
6  select 1, level, 1
7  from dual
8  connect by level <= 4;

8 rows created.

Executing the same test SQL twice:

SQL> select/*+ gather_plan_statistics cf4*/ count(distinct n2) from bacs where n1 = 1 and n2 >= 1;

COUNT(DISTINCTN2)
-----------------
4

1 row selected.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------

Plan hash value: 3674053097

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |      1 |        |     4 (100)|      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE       |                  |      1 |      1 |            |      1 |00:00:00.01 |       2 |
|   2 |   VIEW                | VW_DAG_0         |      1 |      2 |     4  (50)|      4 |00:00:00.01 |       2 |
|   3 |    HASH GROUP BY      |                  |      1 |      2 |     4  (50)|      4 |00:00:00.01 |       2 |
|   4 |     VIEW              | index$_join$_001 |      1 |      2 |     3  (34)|      5 |00:00:00.01 |       2 |
|   5 |      HASH JOIN        |                  |      1 |        |            |      5 |00:00:00.01 |       2 |
|   6 |       INDEX RANGE SCAN| BACS_N1_IDX      |      1 |      2 |     1   (0)|      5 |00:00:00.01 |       1 |
|   7 |       INDEX RANGE SCAN| BACS_N2_IDX      |      1 |      2 |     2  (50)|      8 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------------------------

SQL> /

COUNT(DISTINCTN2)
-----------------
4

1 row selected.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------

Plan hash value: 3674053097

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |      1 |        |     4 (100)|      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE       |                  |      1 |      1 |            |      1 |00:00:00.01 |       2 |
|   2 |   VIEW                | VW_DAG_0         |      1 |      2 |     4  (50)|      4 |00:00:00.01 |       2 |
|   3 |    HASH GROUP BY      |                  |      1 |      2 |     4  (50)|      4 |00:00:00.01 |       2 |
|   4 |     VIEW              | index$_join$_001 |      1 |      2 |     3  (34)|      5 |00:00:00.01 |       2 |
|   5 |      HASH JOIN        |                  |      1 |        |            |      5 |00:00:00.01 |       2 |
|   6 |       INDEX RANGE SCAN| BACS_N1_IDX      |      1 |      2 |     1   (0)|      5 |00:00:00.01 |       1 |
|   7 |       INDEX RANGE SCAN| BACS_N2_IDX      |      1 |      2 |     2  (50)|      8 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------------------------

Note
-----
- cardinality feedback used for this statement

After second execution Oracle notes the cardinality feedback usage, and new child cursor was successfully spawned:

SQL> @v$sql 69sxnafkttsbr
SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE     IS_SHAREABLE      EXECUTIONS
------------- ------------ --------------- ----------------- ----------------- ----------------- ----------
69sxnafkttsbr            0      3674053097 N                 N                 N                          1
69sxnafkttsbr            1      3674053097 N                 N                 Y                          1

SQL> @v$sql_shared_cursor 69sxnafkttsbr

SQL_ID         CHILD USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
------------- ------ ------------------ ---------------- ---------------- ---------------------------------
69sxnafkttsbr      0 Y                  N                N                Optimizer mismatch(13)  |
69sxnafkttsbr      1 N                  N                N

Despite new cursor generation the above plans are absolutely equal, and CBO trace don’t contain any OPT_ESTIMATE hints. As far as I understand, for the plan generation phase CBO «understands» any plan corrections redundancy.

Wrong decision to make new unnecessary parsing (new child cursor) was taken before plan generation.

Together with pointed above (and described by Optimizer team) one-off CF applying the low sensitivity are significant CF technology disadvantages

Practical conclusions

Adaptive Cursor Sharing (ACS) and Extended Cursor Sharing (ECS) are interconnected and consequential applying technologies

Adaptive Cursor Sharing may be disabled, and Extended Cursor Sharing may be forced:

  • by parameter _OPTIMIZER_ADAPTIVE_CURSOR_SHARING=FALSE — on system / session /statement levels
  • by hint /*+ BIND_AWARE*/ — on statement level

- additional child cursors generation may be ACS disabling disadvantage

Adaptive Cursor Sharing uses Cardinality Feedback data as one of arguments for decision to mark cursor as bind-aware, which leads to consequential ECS forcing

Extended Cursor Sharing uses standart db object statistics including column histograms, if possible, for a new plan generation

Extended Cursor Sharing (and ACS consequentially) and Cardinality Feedback may be disabled (for wide range of relation operators including like, instr, substr) by parameter _OPTIMIZER_EXTENDED_CURSOR_SHARING_REL=NONE — on system / session /statement levels

To disable Cardinality Feedback usage may be used parameters:

  • «_optimizer_use_feedback» = false — on system / session /statement levels
  • «_fix_control»=»6699059:OFF» — on system / session levels

I don’t know how to force Cardinality Feedback applying (and not sure this may be required)

Common BACS and CF disadvantage is single-off applying: this may be a problem if the data volume being processed is not stable

Additional significant CF disadvantage is low sensitivity (described above), which may results in CPU/memory usage overhead for unnecessary cursor generation (with unchanged execution plan)

1 комментарий »

  1. [...] From what I understand “Optimizer mismatch(13)” simply correlates with the USE_FEEDBACK_STATS = ‘Y’, and you would expect an ‘N’ in that column with a NULL reason for the next child cursor (as demonstrated here). [...]

    Уведомление от Excess Child Cursors and Cardinality Feedback | Mike Pargeter — 24.07.2012 @ 22:08 | Ответить


RSS-лента комментариев к этой записи. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Тема: Rubric. Блог на WordPress.com.

Отслеживать

Get every new post delivered to your Inbox.

Join 109 other followers

%d bloggers like this: