Adaptive Cursor Sharing分析

这篇文章主要讲解了“Adaptive Cursor Sharing分析”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“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

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

| 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分析这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！