Adaptive Cursor Sharing分析

73次阅读

共计 10892 个字符，预计需要花费 28 分钟才能阅读完成。

这篇文章主要讲解了“Adaptive Cursor Sharing 分析”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“Adaptive Cursor Sharing 分析”吧！

ACS 最佳实践

ACS 虽然可以解决绑定变量窥探引起的问题，但是它也存在着一些缺点：
1）一旦一个 SQL 被标注为绑定敏感，优化器就要监视 SQL 语句处理的行数，一旦行数发生“巨变”，就要更新 v$sql_cs_histogram 视图。但是这代价似乎并不大，因为 v$sql_cs_histogram 视图只在 SQL 执行完成后才会被更新，不占用解析时间，因此这个更新完全可以是异步的。
2）Cursor 被标注为 bind aware 后，在解析阶段要窥探变量的值, 计算谓词的选择率，计算后的值要与对应的 v$sql_cs_selectivity 视图中的值做比较，看是否已经在已存的选择率范围内，如果不在会发生硬解析。由于要窥探变量的值，计算选择率，一定程度上加大了解析的时间。这里的解析，指的是软解析。但是这个影响可能也没有那么大，因为一个数据库中并不是所有的 SQL 都会被标注为 bind aware，只有操作的数据集发生过巨大变化的 SQL 才会被标注为 bind aware。
3）使用 ACS 存在一个不稳定期，也就是 SQL 执行计划要经历先变糟再变好的过程。v$sql_cs_histogram 视图记录了 SQL 处理的数据量，用 3 个 bucket 来表示，如果 SQL 处理的行数发生巨变，也就是说处理的行数散落在了至少 2 个桶内，下一次解析时，就要窥探绑定变量的值，重新硬解析生成执行计划。
4）一旦 SQL 被刷出共享池，这个 SQL 还需要重复经历 ACS 不稳定期到稳定器的过程。
5）PL/SQL 中存在 bug 如果不调整 session_cached_cursors 参数为 0 将不能使用到 ACS 特性。

在我所负责管理的生产环境下，ACS 都是关闭的，虽然我本人也对 ACS 做过一些研究和测试，但是始终还是有点敬而远之。那有没有什么办法既能使用到 ACS 的特性，又能一定程度避免这些缺点呢？首先我们需要介绍一个 hint-bind_aware。

bind_aware 的用法和作用

使用了 ACS 的功能后，一个游标从 bind sensitive 到 bind aware，中间有着不稳定期，如果在游标中使用 bind_aware hint 后，将会使游标的状态直接进入 bind aware，而不会经历 bind sensitive 状态。我们来通过一个例子看一看：

SQL CREATE TABLE test

2 AS

3 SELECT ROWNUM id,

4 DBMS_RANDOM.STRING(A , 12) name,

5 DECODE(MOD(ROWNUM, 500), 0, Inactive , Active ) status

6 FROM all_objects

7 WHERE ROWNUM = 50000;

Table created.

SQL CREATE INDEX test_id_ind ON test(status);

Index created.

SQL begin

2 dbms_stats.gather_table_stats(user,

3 test ,

4 method_opt = for columns status size 254 ,

5 cascade = true);

6 end;

7 /

PL/SQL procedure successfully completed.

SQL SELECT COUNT (*) cnt, status

2 FROM test

3 GROUP BY status

4 /

CNT STATUS

———- —————-

49900 Active

100 Inactive

上面的代码本章已经出现过几次，主要作用是：创建了一张表，表上有一列 STATUS 有数据倾斜，列上创建了索引，并在这列上收集直方图。我们来看看在对 SQL 增加 bind aware 的 hint 后，ACS 的表现会是什么样。

我们首先查询 STATUS 为 Inactive 的情况，这个值在表里占少数。

SQL alter system flush shared_pool;

System altered.

SQL var a varchar2(100)

SQL exec :a := Inactive

PL/SQL procedure successfully completed.

SQL

SQL select /*+ bind_aware */ count(name) from test where status=:a;

COUNT(NAME)

———–

100

SQL select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT

——————————————————————————————-

SQL_ID a5fy4g63j8vzr, child number 0

————————————-

select /*+ bind_aware */ count(name) from test where status=:a

Plan hash value: 2948918962

——————————————————————————————–

——————————————————————————————–

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

| 1 | SORT AGGREGATE | | 1 | 25 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 133 | 3325 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | TEST_ID_IND | 133 | | 1 (0)| 00:00:01 |

——————————————————————————————–

Predicate Information (identified by operation id):

—————————————————

3 – access(STATUS =:A)

SQL SELECT child_number, executions, buffer_gets, is_bind_sensitive,

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id= a5fy4g63j8vzr

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

———— ———- ———– — —

0 1 254 Y Y

从 v$sql 的 is_bind_aware 输出为 Y 可以看出，SQL 仅执行了一次就已经被标注为 bind aware，没有经历不稳定期。我们再看下执行 STATUS 为 Active 时的表现：

SQL exec :a := Active

PL/SQL procedure successfully completed.

SQL select /*+ bind_aware */ count(name) from test where status=:a;

COUNT(NAME)

———–

49900

SQL select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT

——————————————————————————-

SQL_ID a5fy4g63j8vzr, child number 1

————————————-

select /*+ bind_aware */ count(name) from test where status=:a

Plan hash value: 1950795681

—————————————————————————

—————————————————————————

| 0 | SELECT STATEMENT | | | | 51 (100)| |

| 1 | SORT AGGREGATE | | 1 | 25 | | |

|* 2 | TABLE ACCESS FULL| TEST | 49862 | 1217K| 51 (2)| 00:00:01 |

—————————————————————————

Predicate Information (identified by operation id):

—————————————————

2 – filter(STATUS =:A)

19 rows selected.

SQL SELECT child_number, executions, buffer_gets, is_bind_sensitive,

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id= a5fy4g63j8vzr

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

———— ———- ———– — —

0 1 1486 Y Y

1 1 210 Y Y

非常棒！我们更换绑定变量的值为 Active 后，第一次执行就已经得到了正确的执行计划，在 v$sql 中也已经新增了一个 entry，用来记录新产生的游标的执行计划。因此使用 bind aware 这个 hint 后，游标将不会经历不稳定期，SQL 每次解析的时候都要窥探绑定变量的值，然后计算选择率，如果计算选择率与现有的游标的选择率不符，就会基于窥探到的绑定变量的值硬解析重新产生了一个新的游标。如果你确认一个 SQL 需要使用 ACS 功能，但是又不想让它经历不稳定期，那么你可以通过 bind aware 这个 hint 做到这一点。还有着一些手段可以尝试，例如我们可以关闭 ACS 的功能，对有需要的 SQL 单独打开 ACS 的功能。可能是从阿里做 DBA 沿袭来的习惯，喜欢直接关闭绑定变量窥探，绑定变量窥探被关闭后，ACS 也就自动关闭了。然后对有需要使用 ACS 的 SQL，通过增加 hint，OPT_PARAM(_optim_peek_user_binds true) bind_aware 来使用到 ACS 的特性，OPT_PARAM(_optim_peek_user_binds true) 用来在 SQL 语句级别打开绑定变量窥探的功能。绑定变量窥探和 ACS 关闭后，就规避了上面提到的 ACS 的缺点。DBA 可以有选择性的对某些 SQL 使用 ACS。当然这个对 DBA 要求较高，需要了解应用，了解表的数据分布特点，了解表上的 SQL 的查询特点。有些大公司已经配备了应用 DBA 的角色，负责开发的 SQL REVIEW 等工作，可以在 SQL REVIEW 阶段里 DBA 通过了解应用的 SQL，对有需要的 SQL 增加 ACS 功能。如果不能第一时间增加 hint 进去，也可以通过 sql profile，sql patch 的方式在不修改 SQL 语句的情况下增加这些 hint 绑定到 SQL 语句上去。（SPM baseline 无效在这里）。
如我们可以通过 sql profile 来对一个 SQL 增加 ACS 的功能：

SQL show parameter binds

NAME TYPE VALUE

———————————— ———————- ——————-

_optim_peek_user_binds boolean FALSE

SQL var a varchar2(100)

SQL exec :a := Active

PL/SQL procedure successfully completed.

SQL select count(name) from test where status=:a;

COUNT(NAME)

———–

49900

SQL select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT

——————————————————————————-

SQL_ID 7yjf9wt1rt8a6, child number 0

————————————-

select count(name) from test where status=:a

Plan hash value: 1950795681

—————————————————————————

—————————————————————————

| 0 | SELECT STATEMENT | | | | 51 (100)| |

| 1 | SORT AGGREGATE | | 1 | 25 | | |

|* 2 | TABLE ACCESS FULL| TEST | 25000 | 610K| 51 (2)| 00:00:01 |

—————————————————————————

Predicate Information (identified by operation id):

—————————————————

2 – filter(STATUS =:A)

SQL SELECT child_number, executions, buffer_gets, is_bind_sensitive,

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id= 7yjf9wt1rt8a6

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

———— ———- ———– — —

0 2 540 N N

关闭绑定变量窥探后，也就关闭了 ACS 的功能，SQL 的 bind sensitive 语句被标注为 N。我们通过 sql profile 增加 hint 看看。

SQL @profile

Enter value for sql_id: 7yjf9wt1rt8a6

PLAN_TABLE_OUTPUT

——————————————————————————-

SQL_ID 7yjf9wt1rt8a6, child number 0

————————————-

select count(name) from test where status=:a

Plan hash value: 1950795681

—————————————————————————

—————————————————————————

| 0 | SELECT STATEMENT | | | | 51 (100)| |

| 1 | SORT AGGREGATE | | 1 | 25 | | |

|* 2 | TABLE ACCESS FULL| TEST | 25000 | 610K| 51 (2)| 00:00:01 |

—————————————————————————

Outline Data

————-

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE(11.2.0.3)

DB_VERSION(11.2.0.3)

OPT_PARAM(_optim_peek_user_binds false)

OPT_PARAM(_optimizer_skip_scan_enabled false)

ALL_ROWS

OUTLINE_LEAF(@ SEL$1)

FULL(@ SEL$1 TEST @ SEL$1)

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

—————————————————

2 – filter(STATUS =:A)

35 rows selected.

Enter value for hint_text: OPT_PARAM(_optim_peek_user_binds true) bind_aware

Profile profile_7yjf9wt1rt8a6_dwrose created.

SQL select count(name) from test where status=:a;

COUNT(NAME)

———–

49900

1 row selected.

SQL SELECT child_number, executions, buffer_gets, is_bind_sensitive,

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id= 7yjf9wt1rt8a6

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

———— ———- ———– — —

0 1 270 Y Y

1 row selected.

SQL select * from table(dbms_xplan.display_cursor( 7yjf9wt1rt8a6 ,null));

PLAN_TABLE_OUTPUT

——————————————————————————-

SQL_ID 7yjf9wt1rt8a6, child number 0

————————————-

select count(name) from test where status=:a

Plan hash value: 1950795681

—————————————————————————

—————————————————————————

| 0 | SELECT STATEMENT | | | | 51 (100)| |

| 1 | SORT AGGREGATE | | 1 | 25 | | |

|* 2 | TABLE ACCESS FULL| TEST | 49862 | 1217K| 51 (2)| 00:00:01 |

—————————————————————————

Predicate Information (identified by operation id):

—————————————————

2 – filter(STATUS =:A)

Note

—–

– SQL profile profile_7yjf9wt1rt8a6_dwrose used for this statement

23 rows selected.

SQL exec :a := Inactive

PL/SQL procedure successfully completed.

SQL select count(name) from test where status=:a;

COUNT(NAME)

———–

100

1 row selected.

SQL select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT

——————————————————————————-

SQL_ID 7yjf9wt1rt8a6, child number 5

————————————-

select count(name) from test where status=:a

Plan hash value: 2948918962

——————————————————————————————–

——————————————————————————————–

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

| 1 | SORT AGGREGATE | | 1 | 25 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 133 | 3325 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | TEST_ID_IND | 133 | | 1 (0)| 00:00:01 |

——————————————————————————————–

Predicate Information (identified by operation id):

—————————————————

3 – access(STATUS =:A)

Note

—–

– SQL profile profile_7yjf9wt1rt8a6_dwrose used for this statement

24 rows selected.

但是可惜的是，11.2.0.3 版本存在 BUG（其他版本没做测试），在 session 或 system 级关闭绑定变量窥探的情况下，如果使用了 hint OPT_PARAM(_optim_peek_user_binds true) bind_aware 来使用 ACS 功能，每执行一次 SQL，就会在共享池中新生成一个执行计划，之前产生的计划被标注为不能共享，不能共享的原因是：user_bind_peek_mismatch。此 BUG 在 12.0.1 版本已经被修复。因此如果使用笔者所说的方式，使用前一定要做好测试，防止产生过多的子游标。如果对于有数据倾斜的列，唯一值非常少，可以考虑直接使用文本变量，放弃绑定变量的使用。

SQL select child_number,user_bind_peek_mismatch from v$sql_shared_cursor where sql_id= 7yjf9wt1rt8a6

CHILD_NUMBER US

———— —

0 N

1 Y

2 Y

3 Y

4 Y

5 Y

如何关闭 ACS 的特性：

alter system set _optimizer_extended_cursor_sharing_rel =none scope=both;

如果你的系统关闭了绑定变量窥探的功能也会自动关闭 ACS。

alter system set _optim_peek_user_binds =false scope=both;

感谢各位的阅读，以上就是“Adaptive Cursor Sharing 分析”的内容了，经过本文的学习后，相信大家对 Adaptive Cursor Sharing 分析这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！

正文完