请先阅读优化器革命之- adaptive cursor sharing （一）
http://blog.itpub.net/22034023/viewspace-1218980/
请先阅读优化器革命之- adaptive cursor sharing （二）
http://blog.itpub.net/22034023/viewspace-1222110/
请先阅读优化器革命之- adaptive cursor sharing （三）
http://blog.itpub.net/22034023/viewspace-1222146/

从上面的介绍我们可以看出，ACS虽然可以解决绑定变量窥探引起的问题，更好程度的解决绑定值数据倾斜导致的问题，但是它也存在着一些缺点：
1）一旦一个SQL被标注为绑定敏感，优化器就要监视SQL语句处理的行数，一旦行数发生“巨变”，就要更新v$sql_cs_histogram视图。
2）在被标注为bind aware后，在解析阶段要窥探变量的值,计算谓词的选择率，计算后的值要与v$sql_cs_selectivity视图中的值做比较，看是否在已存的选择率范围内。一定程度上加大了解析的时间。这里的解析，指的是软解析。
3）使用ACS存在一个不稳定期，也就是SQL执行计划一定先变糟被优化器识别到后，再次执行才能产生正确的执行计划。
4）一旦SQL被刷出共享池，这个SQL还需要重复经历ACS不稳定期到稳定器的过程。

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

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

CREATE TABLE test
AS
SELECT ROWNUM id,
DBMS_RANDOM.STRING('A', 12) name,
DECODE(MOD(ROWNUM, 500), 0, 'Inactive', 'Active') status
FROM all_objects
WHERE ROWNUM <= 50000;

CREATE INDEX test_id_ind ON test(status);
EXEC dbms_stats.gather_table_stats(user, 'test', method_opt => 'for columns status size 254', cascade => true);

SELECT COUNT (*) cnt, status
FROM test
GROUP BY status
/
CNT STATUS
---------- ----------------
14862 Active
29 Inactive

上面的代码本章已经出现过几次，主要作用是：创建了一张表，表上有一列STATUS有数据倾斜，在这列上收集直方图。
我们来看看在对SQL增加bind aware的hint后，ACS的表现会是什么样。
我们首先查询STATUS为Inactive的情况，这个值在表里占少数。
var a varchar2(100)
exec :a :='Inactive';

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

COUNT(NAME)
-----------
29

test@DLSP>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 | 24 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 29 | 696 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TEST_ID_IND | 29 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("STATUS"=:A)

test@DLSP>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 31 Y Y

从v$sql的is_bind_aware输出为Y可以看出，SQL仅执行了一次就已经被标注为bind aware，没有经历不稳定期。我们再看下执行STATUS为Active时的表现：
test@DLSP>exec :a :='Active'
PL/SQL procedure successfully completed.

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

COUNT(NAME)
-----------
14862

test@DLSP>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 | | | | 17 (100)| |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
|* 2 | TABLE ACCESS FULL| TEST | 14862 | 348K| 17 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("STATUS"=:A)

test@DLSP>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 31 Y Y
1 1 63 Y Y
非常棒！我们更换绑定变量的值为Active后，第一次执行就已经得到了正确的执行计划，在v$sql中也已经新增了一个entry，用来记录新产生的游标的执行计划。
因此我想说什么？
我们可以关闭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阶段里了解应用的SQL，对有需要的SQL增加ACS功能。如果不能第一时间增加hint进去，也可以通过sql profile，sql patch的方式在不修改SQL语句的情况下增加一些hint绑定到SQL语句上去。（SPM baseline无效在这里）。
如我们可以通过sql profile来对一个SQL增加ACS的功能：
test@DLSP>show parameter binds

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
_optim_peek_user_binds boolean FALSE

test@DLSP>alter system flush shared_pool;

System altered.
test@DLSP>select count(name) from test where status=:a;

test@DLSP>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 | | | | 17 (100)| |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
|* 2 | TABLE ACCESS FULL| TEST | 7446 | 174K| 17 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("STATUS"=:A)
test@DLSP>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 113 N N

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

test@DLSP>@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 | | | | 17 (100)| |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
|* 2 | TABLE ACCESS FULL| TEST | 7446 | 174K| 17 (0)| 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.

test@DLSP>select count(name) from test where status=:a;

COUNT(NAME)
-----------
14862

test@DLSP>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 123 Y Y
test@DLSP>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 | | | | 17 (100)| |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
|* 2 | TABLE ACCESS FULL| TEST | 14862 | 348K| 17 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("STATUS"=:A)

Note
-----
- SQL profile profile_7yjf9wt1rt8a6_dwrose used for this statement

test@DLSP>exec :a :='Inactive'

PL/SQL procedure successfully completed.

test@DLSP>select count(name) from test where status=:a;

COUNT(NAME)
-----------
29

test@DLSP>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7yjf9wt1rt8a6, child number 16
--------------------------------------
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 | 24 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 29 | 696 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TEST_ID_IND | 29 | | 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

但是可惜的是，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版本已经被修复。因此如果使用笔者所说的方式，使用前一定要做好测试。如果对于有数据倾斜的列，唯一值非常少，可以考虑直接使用文本变量，放弃绑定变量的使用。
sys@DLSP>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
6 Y
7 Y
8 Y

ITPub博客

优化器革命之- adaptive cursor sharing （四）

最新文章