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² seildi.
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² seildi.
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² seildi.
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² seildi.
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² seildi.
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² seildi.
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² seildi.
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² seildi.
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 🙂
One thought on “Adaptive Cursor Sharing & Histograms”