本文主要讲解“自适应光标共享分析”，简单明了，易学易懂。现在，请跟随边肖的思路，一起学习《自适应光标共享分析》！
ACS最佳实践
ACS可以解决窥探绑定变量带来的问题，但它也有一些缺点：
1)一旦一个SQL被标记为绑定敏感，优化器就应该监视该SQL语句处理的行数，并在行数发生巨大变化时更新v $ sql _ cs _直方图视图。但是，这个代价似乎并不大，因为v $ sql _ cs _直方图视图只会在sql执行完成后更新，这并不会占用解析时间，所以这个更新可以完全异步进行。
2)在2)Cursor标记为bind aware之后，应该在解析阶段监听变量的值，并计算谓词的选择率。应将计算值与相应的v $ sql _ cs _选择性视图中的值进行比较，以查看它是否在保存的选择率范围内，如果不在，将进行硬解析。为了窥探变量的值，计算选择率，一定程度上增加了分析时间。这里的解析是指软解析。但是这种影响可能没有那么大，因为不是数据库中的所有SQL都会被标记为绑定感知，只有操作数据集中变化较大的SQL才会被标记为绑定感知。
3)使用ACS有一个不稳定期，即SQL执行计划会经历先变坏再变好的过程。v $ sql _ cs _直方图视图记录了sql处理的数据量，由三个桶表示。如果SQL处理的行数发生剧烈变化，也就是说处理的行数分散在至少两个桶中，那么在下一次解析时应该窥探绑定变量的值，再通过硬解析生成执行计划。
4)一旦SQL被刷出共享池，这个SQL需要反复经历ACS不稳定到稳定器的过程。
5)PL/SQL有bug。如果session _ cached _ cursors参数未调整为0，则不能使用ACS功能。
在我负责管理的生产环境中，ACS是关闭的。虽然我自己对ACS做过一些研究和测试，但还是敬而远之。有没有办法利用ACS的特性，在一定程度上避免这些缺点？首先，我们需要引入一个提示绑定感知。bind_aware的用法和作用
使用ACS的功能后，光标从绑定敏感到绑定感知有一段不稳定的时间。如果在游标中使用bind_aware提示，游标将直接进入bind aware，而不经过绑定敏感状态。让我们看一个例子：
SQLCREATE表测试
2自动化系统
3 SELECT ROWNUM id，
4 DBMS _ RANDOM。字符串(' A '，12)名称，
5 DECODE(MOD(ROWNUM，500)，0，“非活动”、“活动”)状态
6从所有对象
7其中ROWNUM=50000
表已创建。
SQLCREATE INDEX test_id_ind ON测试(状态)；
索引已创建。
SQLbegin
2 dbms_stats.gather_table_stats(用户，
3 '测试'，
4 nbsp
; 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
-------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 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
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 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
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 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
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 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
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 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
-------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 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分析这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！
内容来源网络，如有侵权，联系删除，本文地址：https://www.230890.com/zhan/93982.html