How to Turn Off Adaptive Cursor Sharing, Cardinality Feedback and Serial Direct Read

Scope: New features and improvements on the SQL (cost-based) execution engine are great. However sometimes the need comes to turn some of those new features off. Maybe it's because of a bug that has appeared or simply because we just want consistency, especially after an upgrade. This is often the case when upgrading an application that has been tuned already with heavy usage of hints for critical SQL.

When this is relevant: using a full set of hints (an outline) often provides consistent SQL execution for a given Oracle version (and set of session/instance parameter values). However this is not guaranteed to be stable across upgrades. The reason is that additional functionality of the cost based optimization engine can play a role. This is indeed the case when moving from 10g to 11g and in particular to 11gR2. It's a good idea to investigate the new features and also to keep a note aside on how to disable them in case of need. The discussion of pros and cons of using hints is a very interesting topic but outside the scope of this entry.

Three 11g features of cost-based optimization and SQL execution to consider when upgrading from 10g:

Cardinality feedback. This feature, enabled by default in 11.2, is intended to improve execution plans for repeated executions. See Support note 1344937.1 and documentation for additional info. You can be disable cardinality feedback with an underscore parameter which can be set at session or instance level. Usual caveats on setting underscore parameters apply:
alter session set "_OPTIMIZER_USE_FEEDBACK"=FALSE;
This can also be done with a hint, therefore at statement level, using the opt_param syntax: /*+ opt_param('_OPTIMIZER_USE_FEEDBACK','FALSE') */


Adaptive cursor sharing. This was introduced in 11gR1 to address SQL performance consistency issues related to bind variable peeking.  See support note 740052.1 and documentation for details. For the purposes of this doc, if you want to get the 10g behavior with  an Oracle version higher than 10g and overall if you want to disable this feature, you may want to add the hint:
/*+ NO_BIND_AWARE */. 
According to support note 11657468.8 adaptive cursor sharing can be disabled by setting the following 2 parameters (say at session level): _optimizer_adaptive_cursor_sharing = false, _optimizer_extended_cursor_sharing_rel = "none"

Serial direct read. This feature, introduced in 11gR2, is about SQL execution and not strictly speaking about cost-based optimization. Serial direct reads allow Oracle to perform full scan very efficiently, using asynchronous I/O if available and also bypassing the the buffer cache, much like what parallel query does, but in serial mode. The decision of when to use or not to use serial direct reads is not taken by the optimizer. It still makes sense to discuss this here as this feature can change the behavior of full scans: by forcing the use of direct read instead of 'normal cache-based access'. There are many interesting details to this feature that are outside the scope of this discussion. To revert to 10g behavior and overall to disable the feature you need to set the following parameter (there is no hint for this unfortunately, as this feature is not directly linked to CBO):
alter session set "_SERIAL_DIRECT_READ"=never;
Note, at the opposite side of the spectrum one can force serial direct reads with: alter session set "_serial_direct_read"=always;

Additional info and comments:

1. When testing Oracle version upgrades and facing regression of SQL, it can be useful to set cost-based optimization to behave as it did in the older version. For example when upgrading from 10.2.0.5 to 11gR2 we can use the following hint on SQL we are testing: SQL hint: /*+ OPTIMIZER_FEATURES_ENABLE('10.2.0.5') */ or set the parameter OPTIMIZER_FEATURES_ENABLE at session level (or in some cases even at system level).
However as discussed above, setting optimizer_features_enable parameter sometimes is not enough to ensure that a given SQL will execute in exactly the same way in the new system as it did in the older version. In particular related to the 3 examples above, adaptive cursor sharing and cardinality feedback are tuned off by reverting CBO compatibility to a 10g version, although the behavior of serial direct read is not affected by this hint (or any other hint for that matter).

2. How to get a full list of available hints? query V$SQL_HINT in Oracle 11.2 or higher

3a. Example of a logon trigger that can be used to set session-level parameters:

create or replace trigger change_session_parameters
AFTER LOGON ON mydbuser.SCHEMA --customize username, replace "mydbuser" with relevant user name
BEGIN
execute immediate 'alter session set "_serial_direct_read"=never';
END;
/

3b. Alternative trigger definition

create or replace TRIGGER trace_trig
AFTER LOGON
ON DATABASE
DECLARE
 sqlstr1 VARCHAR2(100) := 'alter session set "_serial_direct_read"=never';
BEGIN
  IF (USER IN ('USERNAME1', 'USERNAME2')) THEN -- customize user names as needed
    execute immediate sqlstr1;
  END IF;
END trace_trig;
/

4. Example of how to get the outline (full set of hints) for a given query:

select * from table(dbms_xplan.display_cursor('sql_id',null,'OUTLINE')); -- edit sql_id with actual value