Oracle 自适应游标共享--adaptive cursor sharing - 数据库编程

------------------------------
| 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
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3p66zbwtm19bs, child number 2
-------------------------------------
select sum(id) from acs_test_tab where record_type = :v

Plan hash value: 3987223107

Oracle 自适应游标共享--adaptive cursor sharing(二)