ORACLE自适应游标共享--adaptivecursorsharing - 数据库编程

lect * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3p66zbwtm19bs, child number 0
-------------------------------------
select sum(id) from acs_test_tab where record_type = :v

Plan hash value: 3987223107

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

-----------------------------------------------------------------------------------------------------------

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

3 - access("RECORD_TYPE"=:V)

已选择20行。

SQL> select child_number,executions,buffer_gets,is_bind_sensitive,is_bind_aware
2 from v$sql
3 where sql_text like 'select sum(id)%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS I I
------------ ---------- ----------- - -
0 2 832 Y N

我们发现执行计划没有变化，但是统计信息却发生了比较大的跳跃。

再次执行上面的语句

SQL> select sum(id) from acs_test_tab where record_type = :v;

   SUM(ID)
----------
2500050000

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	3p66zbwtm19bs, child number 1
-------------------------------------
select sum(id) from acs_test_tab where record_type = :v

Plan hash value: 509473618

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

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

   2 - filter("RECORD_TYPE"=:V)


已选择19行。

SQL> select child_number,executions,buffer_gets,is_bind_sensitive,is_bind_aware
  2  from v$sql
  3  where sql_text like 'select sum(id)%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS I I
------------ ---------- ----------- - -
	   0	      2 	832 Y N
	   1	      1 	510 Y Y

这次执行计划发生了变化，这是因为通过前面两次的运行，oracle发现不同的变量值会引起不同的数据访问特征，因此将游标设置为bind_aware，此时oracle会根据绑定变量的选择性来选择不同的执行计划，如果某已选择性的执行计划不存在则生成新的执行计划，子游标为0的执行计划会被置为nosharable，不再被使用并逐渐丢弃。如下面

SQL> exec :v := 1

PL/SQL 过程已成功完成。

SQL> select sum(id) from acs_test_tab where record_type = :v;

   SUM(ID)
----------
	 1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------

ORACLE自适应游标共享--adaptivecursorsharing(二)