Adaptive Cursor Sharing & Histograms

Adaptive cursor sharing has been used in Oracle 11g first. This feature enables a single statement that contains bind variables to use multiple execution plans. According to the Oracle 11g docs, It is written that a histogram should be exist on the column containing the bind value. However, ACS can be used without the histograms. Let’s prove this with a simple example.

https://docs.oracle.com/cd/E11882_01/server.112/e41573/optimops.htm#PFGRF95247

This test case has been conducted in Oracle 11gR2.

Let’s create a table without an histogram.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production


SQL> CREATE TABLE TEST_TAB
  2  AS
  3  SELECT OBJECT_ID, OBJECT_TYPE FROM DBA_OBJECTS;

Tablo yarat²ld².
SQL> CREATE INDEX TEST_IND ON TEST_TAB(OBJECT_ID);

Dizin yarat²ld².
SQL> BEGIN
  2      DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER, TABNAME => 'TEST_TAB', METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
  3  END;
  4  /

PL/SQL yordam² ba■ar²yla tamamland².

As seen below, there is not any histogram exist on the columns.

SQL> SELECT COLUMN_NAME, HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME = 'TEST_TAB';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
OBJECT_ID                      NONE
OBJECT_TYPE                    NONE

SQL> SELECT COUNT(*), MIN(OBJECT_ID), MAX(OBJECT_ID) FROM TEST_TAB;

  COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID)
---------- -------------- --------------
     87653              2        1983888

Declare the bind variables

SQL> var b1 number;
SQL> var b2 number;
SQL>
SQL> exec :b1 := 10;

PL/SQL yordam² ba■ar²yla tamamland².

SQL> exec :b2 := 20;

PL/SQL yordam² ba■ar²yla tamamland².

SQL>
SQL> SELECT :b1, :b2 FROM dual;

       :B1        :B2
---------- ----------
        10         20


Now, time to run our query.

SQL> set arraysize 500
SQL> set autotrace traceonly
SQL> select * from test_tab where object_id >= :b1 and object_id <= :b2;

11 sat²rlar² seildi.


Y³r³tme Plan²
----------------------------------------------------------
Plan hash value: 2151068315

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |   219 |  3066 |     6   (0)| 00:00:01 |
|*  1 |  FILTER                      |          |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST_TAB |   219 |  3066 |     6   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TEST_IND |   394 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER(:B2)>=TO_NUMBER(:B1))
   3 - access("OBJECT_ID">=TO_NUMBER(:B1) AND "OBJECT_ID"<=TO_NUMBER(:B2))


¦statistikler
----------------------------------------------------------
         53  recursive calls
          0  db block gets
         52  consistent gets
          0  physical reads
          0  redo size
        751  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
         11  rows processed

SQL> set autotrace off
SQL> SELECT v.SQL_ID, v.IS_BIND_SENSITIVE, v.IS_BIND_AWARE, v.IS_SHAREABLE, v.EXECUTIONS, v.FETCHES FROM v$sql v where sql_text = 'select * from test_tab where object_id >= :b1 and object_id <= :b2';

SQL_ID        I I I EXECUTIONS    FETCHES
------------- - - - ---------- ----------
659fbnusrqxq1 Y N Y          1          2

Index Range Scan is used in the execution plan. As you can see, the value of IS_BIND_SENSITIVE column is Y. That means, adaptive cursor sharing might be considered. So far so good. Let’s change the :B2 bind variable and rerun the query.

SQL> exec :b2 := 1900000;

PL/SQL yordam² ba■ar²yla tamamland².
SQL> set autotrace traceonly
SQL> select * from test_tab where object_id >= :b1 and object_id <= :b2;

85347 sat²rlar² seildi.


Y³r³tme Plan²
----------------------------------------------------------
Plan hash value: 2151068315

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |   219 |  3066 |     6   (0)| 00:00:01 |
|*  1 |  FILTER                      |          |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST_TAB |   219 |  3066 |     6   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TEST_IND |   394 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER(:B2)>=TO_NUMBER(:B1))
   3 - access("OBJECT_ID">=TO_NUMBER(:B1) AND "OBJECT_ID"<=TO_NUMBER(:B2))


¦statistikler
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1189  consistent gets
          3  physical reads
          0  redo size
    1271818  bytes sent via SQL*Net to client
       2390  bytes received via SQL*Net from client
        172  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      85347  rows processed

SQL> set autotrace off
SQL> SELECT v.SQL_ID, v.IS_BIND_SENSITIVE, v.IS_BIND_AWARE, v.IS_SHAREABLE, v.EXECUTIONS, v.FETCHES FROM v$sql v where sql_text = 'select * from test_tab where object_id >= :b1 and object_id <= :b2';

SQL_ID        I I I EXECUTIONS    FETCHES
------------- - - - ---------- ----------
659fbnusrqxq1 Y N Y          2        174

Run the same query again and we will see if the plan is changed.

SQL> set autotrace traceonly
SQL> select * from test_tab where object_id >= :b1 and object_id <= :b2;

85347 sat²rlar² seildi.


Y³r³tme Plan²
----------------------------------------------------------
Plan hash value: 2151068315

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |   219 |  3066 |     6   (0)| 00:00:01 |
|*  1 |  FILTER                      |          |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST_TAB |   219 |  3066 |     6   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TEST_IND |   394 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER(:B2)>=TO_NUMBER(:B1))
   3 - access("OBJECT_ID">=TO_NUMBER(:B1) AND "OBJECT_ID"<=TO_NUMBER(:B2))


¦statistikler
----------------------------------------------------------
          1  recursive calls
          3  db block gets
        404  consistent gets
          0  physical reads
          0  redo size
    1270141  bytes sent via SQL*Net to client
       2390  bytes received via SQL*Net from client
        172  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      85347  rows processed

SQL> set autotrace off
SQL> SELECT v.SQL_ID, v.IS_BIND_SENSITIVE, v.IS_BIND_AWARE, v.IS_SHAREABLE, v.EXECUTIONS, v.FETCHES FROM v$sql v where sql_text = 'select * from test_tab where object_id >= :b1 and object_id <= :b2';

SQL_ID        I I I EXECUTIONS    FETCHES
------------- - - - ---------- ----------
659fbnusrqxq1 Y N N          2        174
659fbnusrqxq1 Y Y Y          1        172

According to the SQLPLUS’s autotrace feature, the plan hasn’t changed. However, if you look at the V$SQL view, you will see that adaptive cursor sharing has been used. Also, it can be seen that a new child cursor has been created. So, let’s check the first child of the execution plan manually.

SQL> SELECT sql_id, child_number FROM v$sql_shared_cursor v where sql_id = '659fbnusrqxq1';

SQL_ID        CHILD_NUMBER
------------- ------------
659fbnusrqxq1            0
659fbnusrqxq1            1

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id => '659fbnusrqxq1', CURSOR_CHILD_NO => 1, format=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  659fbnusrqxq1, child number 1
-------------------------------------
select * from test_tab where object_id >= :b1 and object_id <= :b2

Plan hash value: 1902350639

------------------------------------------------
| Id  | Operation          | Name     | E-Rows |
------------------------------------------------
|   0 | SELECT STATEMENT   |          |        |
|*  1 |  FILTER            |          |        |
|*  2 |   TABLE ACCESS FULL| TEST_TAB |  83937 |
------------------------------------------------

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

   1 - filter(:B2>=:B1)
   2 - filter(("OBJECT_ID"<=:B2 AND "OBJECT_ID">=:B1))

Note
-----

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
   - 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


26 sat²rlar² seildi.

Full Scan has been used. Somehow, SQLPLUS autotrace feaure and real execution plan has not match. Let’s set different values of bind varaibles and observe the change of child cursors.

SQL> exec :b1 := 50;

PL/SQL yordam² ba■ar²yla tamamland².

SQL> exec :b2 := 100;

PL/SQL yordam² ba■ar²yla tamamland².

SQL>
SQL> set autotrace traceonly
SQL> select * from test_tab where object_id >= :b1 and object_id <= :b2;

51 sat²rlar² seildi.


Y³r³tme Plan²
----------------------------------------------------------
Plan hash value: 2151068315

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |   219 |  3066 |     6   (0)| 00:00:01 |
|*  1 |  FILTER                      |          |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST_TAB |   219 |  3066 |     6   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TEST_IND |   394 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER(:B2)>=TO_NUMBER(:B1))
   3 - access("OBJECT_ID">=TO_NUMBER(:B1) AND "OBJECT_ID"<=TO_NUMBER(:B2))


¦statistikler
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
       1206  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         51  rows processed


SQL> exec :b1 := 1000;

PL/SQL yordam² ba■ar²yla tamamland².

SQL> exec :b2 := 1500000;

PL/SQL yordam² ba■ar²yla tamamland².

SQL>
SQL> select * from test_tab where object_id >= :b1 and object_id <= :b2;

82484 sat²rlar² seildi.


Y³r³tme Plan²
----------------------------------------------------------
Plan hash value: 2151068315

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |   219 |  3066 |     6   (0)| 00:00:01 |
|*  1 |  FILTER                      |          |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST_TAB |   219 |  3066 |     6   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TEST_IND |   394 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER(:B2)>=TO_NUMBER(:B1))
   3 - access("OBJECT_ID">=TO_NUMBER(:B1) AND "OBJECT_ID"<=TO_NUMBER(:B2))


¦statistikler
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        392  consistent gets
          0  physical reads
          0  redo size
    1229139  bytes sent via SQL*Net to client
       2324  bytes received via SQL*Net from client
        166  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      82484  rows processed

SQL> exec :b1 := 5;

PL/SQL yordam² ba■ar²yla tamamland².

SQL> exec :b2 := 7;

PL/SQL yordam² ba■ar²yla tamamland².

SQL>
SQL> select * from test_tab where object_id >= :b1 and object_id <= :b2;


Y³r³tme Plan²
----------------------------------------------------------
Plan hash value: 2151068315

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |   219 |  3066 |     6   (0)| 00:00:01 |
|*  1 |  FILTER                      |          |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST_TAB |   219 |  3066 |     6   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TEST_IND |   394 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER(:B2)>=TO_NUMBER(:B1))
   3 - access("OBJECT_ID">=TO_NUMBER(:B1) AND "OBJECT_ID"<=TO_NUMBER(:B2))


¦statistikler
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        687  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed


Interestingly, autotrace shows the same plan for every execution. If you check the V$SQL you will see the truth.

SQL> set autotrace off
SQL> SELECT v.SQL_ID, v.CHILD_NUMBER, v.IS_BIND_SENSITIVE, v.IS_BIND_AWARE, v.IS_SHAREABLE, v.EXECUTIONS, v.FETCHES FROM v$sql v where sql_text = 'select * from test_tab where object_id >= :b1 and object_id <= :b2';

SQL_ID        CHILD_NUMBER I I I EXECUTIONS    FETCHES
------------- ------------ - - - ---------- ----------
659fbnusrqxq1            0 Y N N          2        174
659fbnusrqxq1            1 Y Y Y          1        172
659fbnusrqxq1            2 Y Y N          1          2
659fbnusrqxq1            3 Y Y Y          1          2

Let’s look at the first and third child. Because they are the only shareable cursors.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id => '659fbnusrqxq1', CURSOR_CHILD_NO => 1, format=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  659fbnusrqxq1, child number 1
-------------------------------------
select * from test_tab where object_id >= :b1 and object_id <= :b2

Plan hash value: 1902350639

------------------------------------------------
| Id  | Operation          | Name     | E-Rows |
------------------------------------------------
|   0 | SELECT STATEMENT   |          |        |
|*  1 |  FILTER            |          |        |
|*  2 |   TABLE ACCESS FULL| TEST_TAB |  83937 |
------------------------------------------------

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

   1 - filter(:B2>=:B1)
   2 - filter(("OBJECT_ID"<=:B2 AND "OBJECT_ID">=:B1))

Note
-----

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
   - 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


26 sat²rlar² seildi.

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id => '659fbnusrqxq1', CURSOR_CHILD_NO => 3, format=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  659fbnusrqxq1, child number 3
-------------------------------------
select * from test_tab where object_id >= :b1 and object_id <= :b2

Plan hash value: 2151068315

----------------------------------------------------------
| Id  | Operation                    | Name     | E-Rows |
----------------------------------------------------------
|   0 | SELECT STATEMENT             |          |        |
|*  1 |  FILTER                      |          |        |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST_TAB |      1 |
|*  3 |    INDEX RANGE SCAN          | TEST_IND |      1 |
----------------------------------------------------------

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

   1 - filter(:B2>=:B1)
   3 - access("OBJECT_ID">=:B1 AND "OBJECT_ID"<=:B2)

Note

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
-----
   - 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


27 sat²rlar² seildi.

It is obvious that we have two different plans.

As a result, we can say that,

  • Queries with bind variables in predicates will be marked bind sensitive.
  • Histograms are not mandatory for adaptive cursor sharing.
  • Autotrace feature of SQLPLUS does not always accurate

I hope this article will help you 🙂

Leave a Reply

Your email address will not be published.