Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Adaptive Cursor Sharing in Oracle Database 11g Release 1

DBAs are always encouraging developers to use bind variables, but when bind variables are used against columns containing skewed data they sometimes lead to less than optimum execution plans. This is because the optimizer peeks at the bind variable value during the hard parse of the statement, so the value of a bind variable when the statement is first presented to the server can affect every execution of the statement, regardless of the bind variable values.

Oracle 11g uses Adaptive Cursor Sharing to solve this problem by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values. If it notices suboptimal plans, it allows certain bind variable values, or ranges of values, to use alternate execution plans for the same statement. This functionality requires no additional configuration. The following code provides an example of adaptive cursor sharing.

Setup

First we create and populate a test table.

DROP TABLE acs_test_tab;

CREATE TABLE acs_test_tab (
  id          NUMBER,
  record_type NUMBER,
  description VARCHAR2(50),
  CONSTRAINT acs_test_tab_pk PRIMARY KEY (id)
);

CREATE INDEX acs_test_tab_record_type_i ON acs_test_tab(record_type);

DECLARE
  TYPE t_acs_test_tab IS TABLE OF acs_test_tab%ROWTYPE;
  l_tab t_acs_test_tab := t_acs_test_tab();
  
BEGIN
  FOR i IN 1 .. 100000 LOOP
    l_tab.extend;
    IF MOD(i,2)=0 THEN
      l_tab(l_tab.last).record_type := 2;
    ELSE
      l_tab(l_tab.last).record_type := i;
    END IF;
    
    l_tab(l_tab.last).id          := i;
    l_tab(l_tab.last).description := 'Description for ' || i;
  END LOOP;

  FORALL i IN l_tab.first .. l_tab.last
    INSERT INTO acs_test_tab VALUES l_tab(i);

  COMMIT;
END;
/

EXEC DBMS_STATS.gather_table_stats(USER, 'acs_test_tab', method_opt=>'for all indexed columns size skewonly', cascade=>TRUE);

The data in the RECORD_TYPE column is skewed, as shown by the presence of a histogram against the column.

SELECT column_name, histogram FROM user_tab_cols WHERE  table_name = 'ACS_TEST_TAB';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
ID                             NONE
RECORD_TYPE                    HEIGHT BALANCED
DESCRIPTION                    NONE

3 rows selected.

SQL>

Test Adaptive Cursor Sharing

Next, we query the table and limit the rows returned based on the RECORD_TYPE column with a literal value of "1".

SET LINESIZE 200

SELECT MAX(id) FROM acs_test_tab WHERE record_type = 1;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

   MAX(ID)
----------
         1

1 row selected.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID  cgt92vnmcytb0, child number 0
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = 1

Plan hash value: 3987223107

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |                            |     1 |     9 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB               |     1 |     9 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | ACS_TEST_TAB_RECORD_TYPE_I |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

This query has used the index as we would expect. Now we repeat the query, but this time use a bind variable.

VARIABLE l_record_type NUMBER;
EXEC :l_record_type := 1;

SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

   MAX(ID)
----------
         1

1 row selected.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID  9bmm6cmwa8saf, child number 0
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type

Plan hash value: 3987223107

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |                            |     1 |     9 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB               |     1 |     9 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | ACS_TEST_TAB_RECORD_TYPE_I |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

So we ran what amounted to the same query, and got the same result and execution plan. The optimizer picked an execution plan that it thinks is optimium for query by peeking at the value of the bind variable. The only problem is, this would be totally the wrong thing to do for other bind values.

VARIABLE l_record_type NUMBER;
EXEC :l_record_type := 2;

SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

   MAX(ID)
----------
    100000

1 row selected.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID  9bmm6cmwa8saf, child number 0
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type

Plan hash value: 3987223107

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |                            |     1 |     9 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB               |     1 |     9 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | ACS_TEST_TAB_RECORD_TYPE_I |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

If we look at the V$SQL view entry for this query, we can see the IS_BIND_SENSITIVE column is marked as 'Y', so Oracle is aware this query may require differing execution plans depending on the bind variable values, but currently the IS_BIND_AWARE column is marked as 'N', so Oracle has not acted on this yet.

SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
FROM   v$sql
WHERE  sql_text = 'SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type';

SQL_ID        CHILD_NUMBER I I
------------- ------------ - -
9bmm6cmwa8saf            0 Y N

1 row selected.

SQL>

If we run the statement using the second bind variable again, we can see that Oracle has decided to use an alternate, more efficient plan for this statement.

VARIABLE l_record_type NUMBER;
EXEC :l_record_type := 2;

SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

   MAX(ID)
----------
    100000

1 row selected.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  9bmm6cmwa8saf, child number 1
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type

Plan hash value: 509473618

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |       |       |   138 (100)|          |
|   1 |  SORT AGGREGATE    |              |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| ACS_TEST_TAB | 48031 |   422K|   138   (2)| 00:00:02 |
-----------------------------------------------------------------------------------

This change in behavior is also reflected in the V$SQL view, which now has the IS_BIND_AWARE column maked as "Y".

SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
FROM   v$sql
WHERE  sql_text = 'SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type';

SQL_ID        CHILD_NUMBER I I
------------- ------------ - -
9bmm6cmwa8saf            0 Y N
9bmm6cmwa8saf            1 Y Y

2 rows selected.

SQL>

Information about the cursor sharing histograms, statistics and selectivity is displayed using the V$SQL_CS_HISTOGRAM, V$SQL_CS_STATISTICS and V$SQL_CS_SELECTIVITY views respectively.

SQL> SELECT * FROM v$sql_cs_histogram WHERE sql_id = '9bmm6cmwa8saf';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
-------- ---------- ------------- ------------ ---------- ----------
319A4A1C 4171522382 9bmm6cmwa8saf            1          0          0
319A4A1C 4171522382 9bmm6cmwa8saf            1          1          1
319A4A1C 4171522382 9bmm6cmwa8saf            1          2          0
319A4A1C 4171522382 9bmm6cmwa8saf            0          0          1
319A4A1C 4171522382 9bmm6cmwa8saf            0          1          1
319A4A1C 4171522382 9bmm6cmwa8saf            0          2          0

6 rows selected.

SQL> SELECT * FROM v$sql_cs_statistics WHERE sql_id = '9bmm6cmwa8saf';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME
-------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
319A4A1C 4171522382 9bmm6cmwa8saf            1          2064090006 Y          1          50001         499          0
319A4A1C 4171522382 9bmm6cmwa8saf            0          2342552567 Y          1              3           3          0

2 rows selected.

SQL> SELECT * FROM v$sql_cs_selectivity WHERE sql_id = '9bmm6cmwa8saf';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
-------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
319A4A1C 4171522382 9bmm6cmwa8saf            1 =L_RECORD_T                                       0 0.432283   0.528346

1 row selected.

SQL>

BIND_AWARE and NO_BIND_AWARE Hints

From 11.1.0.7 onward it is possible to skip the monitoring that is required to detect bind-sensitive queries by using the BIND_AWARE hint. In the following example, the presence of the hint tells the optimizer that we believe the query is bind-sensitive, so it should use bind-aware cursor sharing from the first execution.

SELECT /*+ BIND_AWARE */ MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;

The hint will only work if the query uses bind variables in WHERE clause predicates referencing columns with histograms.

There is also a NO_BIND_AWARE hint that tells the optimizer to ignore bind-sensitive queries, effectively hiding the query from the adaptive cursor sharing functionality.

Bind-aware cursor sharing has a small overhead associated with it, which is why Oracle use the "adaptive" approach to identifying queries that would benefit from bind-aware cursor sharing. Adding the hint to queries that will not benefit from it is a waste.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.