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)
Oracle mechanics 
[…] 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 |