Oracle自适应共享游标——Adaptive Cursor Sharing（上）

最新推荐文章于 2023-09-23 19:41:22 发布

ciqu9915

最新推荐文章于 2023-09-23 19:41:22 发布

阅读量166

点赞数

文章标签：数据库运维

Oracle的绑定变量技术，是解决内存SQL语句硬解析过多，防止出现资源争用的重要手段。但是，世上没有万灵药。绑定变量在引入cursor sharing与增加软解析的同时，也带来CBO环境下的bind peeking问题。从Oracle 11g开始，Oracle Database引入了Adaptive Cursor Sharing新特性，将绑定变量执行计划确定变化为一个基于统计量分析的自适应过程。

1、 从Oracle 10g下的bind peeking谈起

Bind peeking问题的根源在于CBO时代SQL执行计划的生成机制。CBO生成执行计划与RBO存在很大差异，CBO是依据数据分布的统计量信息，生成成本最优的执行计划。但是，使用绑定变量的SQL语句在这个环节似乎存在一些问题。如下一条SQL语句。

select * from t where wner=:x

owner列上存在索引。那么输入上面的一句SQL给优化器，Oracle生成何种类型的执行计划呢？走索引还是全表扫描？

答案是都有可能。owner的分布如果是非平均，存在偏移。那么不同的:x取值，生成的最优执行计划必然不同。这个时候，Oracle会在第一次解析的时候，“偷偷”查看peek一下输入的绑定变量值。根据peek到的数据值来确定执行计划，保存在liberary cache中，作为下一次执行的cursor sharing。如果下次输入的:X变量恰好是和第一次取值分布差异很大的数据值，那么执行计划就是有问题的，甚至就是错误的。

下面我们简单实验一下Oracel 10g上的bind peeking。首先还是准备环境：

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

SQL> create table t (id1 varchar2(10), id2 varchar2(10), id3 varchar2(10));

Table created

SQL> select id1, count(*) from t group by id1;

ID1 COUNT(*)

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

K 10000

M 20

A 10000

在id1列上存在索引，而且id1列取值分布及其不平均。

SQL> create index idx_t_id1 on t(id1);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

下面我们先使用非绑定变量，来精确获取应有的执行计划情况。由于篇幅原因直接列出结果。

SQL语句结构	执行关键路径	数据行数
select * from t where id1='K';	全表扫描	10000
select * from t where id1='M';	索引路径	20
select * from t where id1='A';	全表扫描	10000

由于数据值取值偏移的原因，id1取定’M’时执行索引路径，而取定’K’时执行的是全表扫描。下面观察使用绑定变量的情况：

//定义绑定变量取值；

SQL> var i varchar2(10);

SQL> exec :i := 'K';

PL/SQL 过程已成功完成。

SQL> select * from t where id1=:i;

已选择10000行。

此时，我们检查liberary cache中缓存的执行计划。

//父游标情况；

SQL> select sql_text, SQL_ID, VERSION_COUNT,BIND_DATA from v$sqlarea where sql_text like 'select * from t%';

SQL_TEXT SQL_ID VERSION_COUNT

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

select * from t where id1=:i a3y1yrq36v0gn 1

Executed in 0.094 seconds

//子游标中只有一个对应，也就是当前只有一个执行计划；

SQL> select PLAN_HASH_VALUE, CHILD_NUMBER from v$sql where sql_id='a3y1yrq36v0gn';

PLAN_HASH_VALUE CHILD_NUMBER

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

1601196873 0

Executed in 0.047 seconds

使用sql_id和chile_number抽取出可读的执行计划，如下：

//抽取出执行计划；

SQL> select * from table(dbms_xplan.display_cursor('a3y1yrq36v0gn',0,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID a3y1yrq36v0gn, child number 0

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

select * from t where id1=:i

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | | | 9 (100)| |

|* 1 | TABLE ACCESS FULL| T | 10000 | 60000 | 9 (12)| 00:00:01 |

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

Peeked Binds (identified by position):

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

1 - :I (VARCHAR2(30), CSID=852): 'K'

Predicate Information (identified by operation id):

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

1 - filter("ID1"=:I)

注意上面的执行计划，采用了全表扫描的正确计划。同时，也告诉我们采用的peeking值为’K’。此时，我们变换绑定变量值。

SQL> exec :i:='M';

PL/SQL 过程已成功完成。

SQL> select * from t where id1=:i;

已选择20行。

此时，我们再次观察父子游标状态。

SQL> select PLAN_HASH_VALUE, CHILD_NUMBER from v$sql where sql_id='a3y1yrq36v0gn';

PLAN_HASH_VALUE CHILD_NUMBER

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

1601196873 0

Executed in 0.031 seconds

注意！第二次执行的SQL语句和第一次完全相同，也就意味着父游标相同。绑定变量取值有所差别。但是我们之后观察子游标，没有出现新的子游标出现。说明第二次执行的还是原来的共享游标，也就是全表扫描执行计划。这个在变量取定’M’的情况下，完全是错误的！！

上面的实验告诉我们：在使用绑定变量的时候，虽然可以提高游标共享几率，减少硬解析的出现。但是由于bind peeking问题，使所有绑定变量采用相同的执行计划。这个显然是有问题的，在实际生产环境下会出现性能的无故递减问题。

Oracle 11g之前，bind peeking问题一直是一个很困扰的障碍。很多时候甚至迫使运维人员暂时性的终止bind peeking的使用。于是Oracle在11g开始，尝试使用一种自适应的游标共享方式（Adaptive Cursor Sharing），来提高绑定变量情况下的正确执行计划生成概率。

2、Adaptive Cursor Sharing初探

首先我们进行一系列的实验，给出直观的ACS（Adaptive Cursor Sharing）效果。作为对比，我们采用的数据构成环境与上面相同，差别就是在oracle11g版本上。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

在使用绑定变量的时候，我们逐步观测。

SQL> var i varchar2(10);

SQL> exec :i:='M';

PL/SQL 过程已成功完成。

SQL> select * from t where id1=:i;

已选择20行。

此时，我们观察父子游标情况。

//父游标

SQL> select sql_text, sql_id, VERSION_COUNT, EXECUTIONS, IS_BIND_SENSITIVE, IS_BIND_AWARE from v$sqlarea where sql_text like 'select * from t where id1=:i%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE

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

select * from t where id1=:i a3y1yrq36v0gn 1 1 Y N

Executed in 0.14 seconds

//子游标

SQL> select SQL_ID,CHILD_NUMBER,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE from v$sql where sql_id='a3y1yrq36v0gn';

SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE

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

a3y1yrq36v0gn 0 Y N Y

Executed in 0.046 seconds

由于第一次调用，执行后只生成了一个子游标对象。注意，从11g开始，在v$sql中加入了三个视图列为is_bind_sensitive、is_bind_aware和is_shareable。

ü is_bind_sensitive表示该子游标中是否使用了绑定变量要素，采用bind peeking方法进行执行计划生成；

ü is_bind_aware表示该子游标是否使用了extended cursor sharing技术，也就是ACE；

ü is_shareable表示该子游标可否被下次软解析所共享使用。如果设置为N，就表示该子游标失去了共享价值，等待被Age Out出内存；

当前我们只有一次bind peeking调用，没有其他的可能。所以is_bind_senstive为Y，is_shareable为Y，同时is_bind_aware未启动。该执行计划为：

SQL> select * from table(dbms_xplan.display_cursor('a3y1yrq36v0gn',format => 'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID a3y1yrq36v0gn, child number 0

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

select * from t where id1=:i

Plan hash value: 2247614985

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)|

| 1 | TABLE ACCESS BY INDEX ROWID| T | 20 | 120 | 2 (0)| 0

|* 2 | INDEX RANGE SCAN | IDX_T_ID1 | 20 | | 1 (0)| 0

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

Peeked Binds (identified by position):

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

1 - :I (VARCHAR2(30), CSID=873): 'M'

Predicate Information (identified by operation id):

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

2 - access("ID1"=:I)

Executed in 1.154 seconds

此时，启用bind peeking，生成索引执行计划。接下来进行第二次调用。

//选择了应该进行全表扫描的K值；

SQL> exec :i:='K';

PL/SQL 过程已成功完成。

SQL> select * from t where id1=:i;

已选择10000行。

查看父游标情况。

SQL> select sql_text, sql_id, VERSION_COUNT, EXECUTIONS, IS_BIND_SENSITIVE, IS_BIND_AWARE from v$sqlarea where sql_text like 'select * from t where id1=:i%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE

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

select * from t where id1=:i a3y1yrq36v0gn 1 2 Y N

Executed in 0.125 seconds

从父游标的情况看，当前还是只有一个子游标，呈现bind peeking老现象。我们再次调用的时候，出现了不同。

SQL> exec :i:='K';

PL/SQL 过程已成功完成。

SQL> select * from t where id1=:i;

已选择10000行。

SQL> select sql_text, sql_id, VERSION_COUNT, EXECUTIONS, IS_BIND_SENSITIVE, IS_BIND_AWARE from v$sqlarea where sql_text like 'select * from t where id1=:i%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE

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

select * from t where id1=:i a3y1yrq36v0gn 2 3 Y Y

Executed in 0.125 seconds

SQL> select SQL_ID,CHILD_NUMBER,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE from v$sql where sql_id='a3y1yrq36v0gn';

SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE

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

a3y1yrq36v0gn 0 Y N Y

a3y1yrq36v0gn 1 Y Y Y

Executed in 0.031 seconds

注意，在第二次使用’K’的时候，Oracle没有选择第一条错误的执行子游标，而是产生出一个新的游标（child_number=1）。而且，这个新生成的游标具有可以共享和extended cursor sharing特性。

那么，我们抽取出第二条执行计划查看。

SQL> select * from table(dbms_xplan.display_cursor('a3y1yrq36v0gn',1,format => 'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID a3y1yrq36v0gn, child number 1

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

select * from t where id1=:i

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | | | 10 (100)| |

|* 1 | TABLE ACCESS FULL| T | 10000 | 60000 | 10 (0)| 00:00:01 |

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

Peeked Binds (identified by position):

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

1 - :I (VARCHAR2(30), CSID=873): 'K'

Predicate Information (identified by operation id):

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

1 - filter("ID1"=:I)

48 rows selected

Executed in 0.421 seconds

此时，终于生成了一个全新的全表扫描执行计划，使用的bind peeking值为’K’。如果我们在使用新的绑定变量值，如何呢？

//使用应该进行全表扫描的数据值；

SQL> exec :i:='A';

PL/SQL 过程已成功完成。

SQL> select * from t where id1=:i;

已选择10000行。

SQL> select SQL_ID,CHILD_NUMBER,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE, executions from v$sql where sql_id='a3y1yrq36v0gn';

SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE EXECUTIONS

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

a3y1yrq36v0gn 0 Y N Y 2

a3y1yrq36v0gn 1 Y Y Y 2

Executed in 0.046 seconds

注意子游标0的执行次数变化，可以猜出来刚才的那次’A’是使用子游标0，索引路径执行计划。应该说是错误和不合适的。

接着进行测试：

SQL> exec :i:='L';

PL/SQL 过程已成功完成。

SQL> select * from t where id1=:i;

未选定行

SQL> select SQL_ID,CHILD_NUMBER,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE, executions, last_load_time from v$sql where sql_id='a3y1yrq36v0gn';

SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE EXECUTIONS LAST_LOAD_TIME

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

a3y1yrq36v0gn 0 Y N N 2 2011-07-27/16:32:21

a3y1yrq36v0gn 1 Y Y Y 2 2011-07-27/16:45:07

a3y1yrq36v0gn 2 Y Y Y 1 2011-07-27/16:52:10

Executed in 0.063 seconds

采用了新值，Oracle再次生成了一个新游标。同时child_number为0的子游标is_shareable字段为N，表示退出共享周期。剩下的两个子游标为可以进行共享的extended cursor sharing子游标。新生成游标执行计划如下：