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

最新推荐文章于 2022-11-10 17:59:13 发布

ciqu9915

最新推荐文章于 2022-11-10 17:59:13 发布

阅读量128

点赞数

文章标签：数据库

自适应游标共享是Oracle11g推出的一个新特性。通过ACS，我们可以一定程度上缓解由于bind peeking和cursor_sharing参数设置带来的子游标共享问题。在前篇（http://space.itpub.net/17203031/viewspace-703280）中，我们已经观察过Oracle 10g下的bind peeking和游标共享，以及11g上的ACS特性。本篇，我们打算深入分析一下与ACS有关的视图和参数，最后综述一下ACS的优劣。

1、 ACS相关参数和视图对象

Oracle ACS是有相应的技术背景的。在CBO环境下，ACS是解决bind peeking对bad SQL statement解析的一种方式。所以，存在相关系统参数与ACS对应。

ü optimizer_mode：优化器模式，表示选择使用CBO还是RBO。在目前使用的10g和11g中，由于dynamic sampling技术的存在，大部分情况下，我们是在CBO优化模式下。通常使用choose就可以了；

ü optimizer_features_enable：使用优化器特性版本。启用ACS使用11.2.0.1即可；

ü optimizer_capture_sql_plan_baselines=false：关闭SQL 执行计划baseline管理机制；

ü cursor_sharing=EXACT：完全匹配的绑定变量机制；

ü _optim_peek_user_binds：是否开启绑定变量机制；

之后的三个参数与ACS开启与否相关：

ü _optimizer_adaptive_cursor_sharing=TRUE：

ü _optimizer_extended_cursor_sharing=UDO；

ü _optimizer_extended_cursor_sharing_rel=SIMPLE；

除了这些与ACS相关的参数，还新增加了三个度量视图用于ACS的正常工作。

ü v$sql_cs_histogram：Oracle 11g使用分配的频率确定SQL是否是bind sensitive。其中包括执行次数和落在查询上的游标个数；

ü v$sql_cs_selectitiy：包括有关SQL语句谓词的相对选择性信息。也就是选择率立方体；

ü v$sql_cs_statistics：列出自适应游标是否被共享以及如何共享统计量信息；

通过这些新增加的参数和度量视图，Oracle实现了ACS的自适应过程。

2、 自适应游标过程

下面我们进行试验，来观察对ACS的测算机制。注意：我们采用前篇相同的数据表和分布情况。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

SQL> select dbms_stats.get_param('method_opt') from dual;

DBMS_STATS.GET_PARAM('METHOD_O

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

FOR ALL COLUMNS SIZE AUTO

对数据表收集统计信息之后，频度直方图在id1列出现。说明统计量中已经包括了直方图信息。

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

ID1 COUNT(*)

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

U 20

K 10000

M 20

O 30000

SQL> select table_name, column_name, HISTOGRAM from dba_tab_col_statistics where wner='SYS' and table_name='T';

TABLE_NAME COLUMN_NAME HISTOGRAM

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

T ID1 FREQUENCY

T ID2 NONE

T ID3 NONE

取值原因，id1列的直方图只有4个bucket。

SQL> select ENDPOINT_NUMBER, ENDPOINT_VALUE, ENDPOINT_ACTUAL_VALUE from dba_tab_histograms where table_name='T' and wner='SYS' and column_name='ID1';

ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE

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

1407 3.894222643901

1414 3.998068581071

5594 4.101914518242

5596 4.413452329754

ü 第一次绑定变量测试

id1=’O’，筛选数据行30000行，应该走全表扫描路径。

SQL> var i varchar2(10);

SQL> exec :i:='O';

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.01

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

已选择30000行。

此时父子游标状况为。生成了一个父游标和第一个子游标。生成执行计划过程中使用了bind peeking技术。

SQL> select sql_text, SQL_ID, VERSION_COUNT, 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 VERSION_COUNT EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE

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

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

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

SQL_TEXT CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE

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

select * from t where id1=:i 0 Y N Y

各列含义在前篇中已经陈述，不加以累述了。此时的执行计划如下：

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

PLAN_TABLE_OUTPUT

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

SQL_ID a3y1yrq36v0gn, child number 0

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

select * from t where id1=:i

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | | | 19 (100)| |

|* 1 | TABLE ACCESS FULL| T | 29908 | 175K| 19 (0)| 00:00:01 |

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

Peeked Binds (identified by position):

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

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

Predicate Information (identified by operation id):

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

1 - filter("ID1"=:I)

48 rows selected

使用了绑定变量’O’，采用了全表扫描执行计划。三个CS视图如下：

SQL> select * from v$sql_cs_histogram where sql_id='a3y1yrq36v0gn';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

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

433F59CC 2255323636 a3y1yrq36v0gn 0 0 0

433F59CC 2255323636 a3y1yrq36v0gn 0 1 1

433F59CC 2255323636 a3y1yrq36v0gn 0 2 0

SQL> select * from v$sql_cs_selectivity where sql_id='a3y1yrq36v0gn';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

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

SQL> select * from v$sql_cs_statistics where sql_id='a3y1yrq36v0gn';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE PEEKED EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME

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

433F59CC 2255323636 a3y1yrq36v0gn 0 3804331937 Y 1 30000 2094 0

第一次执行生成了编号为0的子游标，执行值对应落在bucket_id=1的计数上。

ü 第二次绑定变量测试

第二次选择一个索引执行计划。

SQL> exec :i:='U';

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.02

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

已选择20行。

对应执行计划信息：

SQL> select sql_text, SQL_ID, VERSION_COUNT, 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 VERSION_COUNT EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE

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

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

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

SQL_TEXT CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE EXECUTIONS

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

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

注意！虽然这个执行显然是走索引计划比较好，但是Oracle并没有为它生成索引执行计划。统计量如下：

SQL> select * from v$sql_cs_histogram where sql_id='a3y1yrq36v0gn';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

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

433F59CC 2255323636 a3y1yrq36v0gn 0 0 1

433F59CC 2255323636 a3y1yrq36v0gn 0 1 1

433F59CC 2255323636 a3y1yrq36v0gn 0 2 0

SQL> select * from v$sql_cs_selectivity where sql_id='a3y1yrq36v0gn';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

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

SQL> select * from v$sql_cs_statistics where sql_id='a3y1yrq36v0gn';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE PEEKED EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME

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

433F59CC 2255323636 a3y1yrq36v0gn 0 3804331937 Y 1 30000 2094 0

第二次执行就落入了第一个bucket中。

ü 第三次绑定变量实验

此时，我们再次使用’U’作为绑定变量取值进行调用。

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

已选择20行。

已用时间: 00: 00: 00.03

此时，出现新的子游标对应。

SQL> select sql_text, SQL_ID, VERSION_COUNT, 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 VERSION_COUNT EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE

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

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

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

SQL_TEXT CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE EXECUTIONS

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

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

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

这个时候，命中统计量出现变化。

SQL> select * from v$sql_cs_histogram where sql_id='a3y1yrq36v0gn';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

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

433F59CC 2255323636 a3y1yrq36v0gn 1 0 1

433F59CC 2255323636 a3y1yrq36v0gn 1 1 0

433F59CC 2255323636 a3y1yrq36v0gn 1 2 0

433F59CC 2255323636 a3y1yrq36v0gn 0 0 1

433F59CC 2255323636 a3y1yrq36v0gn 0 1 1

433F59CC 2255323636 a3y1yrq36v0gn 0 2 0

6 rows selected

SQL> select * from v$sql_cs_selectivity where sql_id='a3y1yrq36v0gn';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

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

433F59CC 2255323636 a3y1yrq36v0gn 1 =I 0 0.000241 0.000295

SQL> select * from v$sql_cs_statistics where sql_id='a3y1yrq36v0gn';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE PEEKED EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME

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

433F59CC 2255323636 a3y1yrq36v0gn 1 1852677911 Y 1 40 7 0

433F59CC 2255323636 a3y1yrq36v0gn 0 3804331937 Y 1 30000 2094 0

对新生成的游标。Oracle将刚才的命中率放在第一个直方图下。并且归纳该新游标对应的统计量分布数量。

此时，执行计划为：

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: 2247614985

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

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

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

| 1 | TABLE ACCESS BY INDEX ROWID| T | 11 | 66 | 2 (0)| 0

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

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

Peeked Binds (identified by position):

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

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

50 rows selected

ü 第四次执行绑定变量

此时，我们还是选择’U’作为执行的绑定变量取值。

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

已选择20行。

SQL> select sql_text, SQL_ID, VERSION_COUNT, 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 VERSION_COUNT EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE

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

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

SQL> col sql_text for a30;

SQL> select sql_text, SQL_ID, VERSION_COUNT,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 VERSION_COUNT EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE

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

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

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

SQL_TEXT CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE EXECUTIONS

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

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

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

SQL> select * from v$sql_cs_histogram where sql_id='a3y1yrq36v0gn';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

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

433F59CC 2255323636 a3y1yrq36v0gn 1 0 2

433F59CC 2255323636 a3y1yrq36v0gn 1 1 0

433F59CC 2255323636 a3y1yrq36v0gn 1 2 0

433F59CC 2255323636 a3y1yrq36v0gn 0 0 1

433F59CC 2255323636 a3y1yrq36v0gn 0 1 1

433F59CC 2255323636 a3y1yrq36v0gn 0 2 0

6 rows selected

相同命中子游标child_number=2。

ü 第五次绑定变量执行

当执行一次同分布数据值’Ｍ’的时候。

SQL> exec :i:='M';

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.02

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

已选择20行。

此时，生成了一个新的游标。进行索引计划的调用。

SQL>

SQL_TEXT SQL_ID VERSION_COUNT VERSION_COUNT EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE

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

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

SQL_TEXT CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE EXECUTIONS

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

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

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

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

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

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

433F59CC 2255323636 a3y1yrq36v0gn 2 0 1

433F59CC 2255323636 a3y1yrq36v0gn 2 1 0

433F59CC 2255323636 a3y1yrq36v0gn 2 2 0

433F59CC 2255323636 a3y1yrq36v0gn 1 0 2

433F59CC 2255323636 a3y1yrq36v0gn 1 1 0

433F59CC 2255323636 a3y1yrq36v0gn 1 2 0

433F59CC 2255323636 a3y1yrq36v0gn 0 0 1

433F59CC 2255323636 a3y1yrq36v0gn 0 1 1

433F59CC 2255323636 a3y1yrq36v0gn 0 2 0

9 rows selected

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

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

433F59CC 2255323636 a3y1yrq36v0gn 2 =I 0 0.000241 0.001376

433F59CC 2255323636 a3y1yrq36v0gn 1 =I 0 0.000241 0.000295

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE PEEKED EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME

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

433F59CC 2255323636 a3y1yrq36v0gn 2 1492060625 Y 1 40 7 0

433F59CC 2255323636 a3y1yrq36v0gn 1 1852677911 Y 1 40 7 0

433F59CC 2255323636 a3y1yrq36v0gn 0 3804331937 Y 1 30000 2094 0

同时子游标1也失去了被共享的能力。

3、ACS的优劣

通过上面的实验，我们观察到了基于直方图和成本不断试算的ACS工作过程。ACS的引入，带来了下面几个好处：

ü 缓解bind peeking带来的性能问题

在oracle 11g之前，使用绑定变量的时候难免出现bind peeking副作用。也就是对于一些bad SQL游标执行计划过度共享，而且性能不稳定的现象。即使通过调整cursor_sharing，非绑定变量语句也会或者生成过多的子游标、或者同样进入bind peeking过度共享。使用ACS后，可以自动的对bind peeking问题进行一系列的补救方式，以其生成适合的执行计划配比。

ü 综合化最优性能

使用ACS的最理想情况，就是Oracle对每一个使用绑定变量的父游标生成多个适当的子游标执行计划。当输入不同的绑定变量值时，Oracle依据查看的结果，“匹配”到合适的执行计划中。这样，既没有消耗过多的library cache空间，又有匹配的SQl执行计划对应。

同样，世上没有万灵药，任何事物都有其劣势的一面。ACS也一样。

ü 消耗更多的CPU和空间资源

进行ACS，Oracle意味着需要消耗更多的CPU进行成本配比试算和评估。同时，生成过多个执行计划也意味着消耗内存library cache资源；

ü 自适应过程也会走弯路

自适应过程是一个事后实验调节的过程。依据统计量和成本值，Oracle要出现若干次错误执行计划的情况。

4、结论

作为oracle 11g推出的新特性，ACS一定程度上缓解了bind peeking，特别是生产环境下的，所带来的问题。笔者认为，虽然不能强调是一个万能的方案，但是一个试图解决问题的有益尝试。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-703373/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-703373/

ciqu9915

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle自适应共享游标——Adaptive Cursor Sharing（下）

自适应游标共享是Oracle11g推出的一个新特性。通过ACS，我们可以一定程度上缓解由于bind peeking和cursor_sharing参数设置带来的子游标共享问题。在前篇（http://space.itpu...
复制链接

扫一扫