Adaptive Cursor Sharing

今天做实验，用的资料来自于oracle文档的Database Performance Tuning Guide
第十一章The Query Optimizer

一、准备工作
准备一个emp表，简单一点，两个列就行了，每个列上都有index

点击(此处)折叠或打开

1. SQL> desc emp
2. Name Null Type
3. ----------------------------------------- -------- ----------------------------
4. EMPNO NOT NULL NUMBER(38)
5. DEPTNO NUMBER(38)

emp表一共包含11个部门，0到9号部门都是只有10行数据，10号部门有99900行数据
我们要执行的sql是这样的（是带有绑定变量的）

点击(此处)折叠或打开

1. VARIABLE deptno NUMBER
2. EXEC :deptno := 9
4. SELECT /*ACS_1*/ count(*), max(empno)
5. FROM emp
6. WHERE deptno = :deptno;

我们查看cursor的语句是这样的

点击(此处)折叠或打开

1. COL BIND_SENSI FORMAT a10
2. COL BIND_AWARE FORMAT a10
3. COL BIND_SHARE FORMAT a10
4. SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",
5. IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"
6. FROM V$SQL
7. WHERE SQL_TEXT LIKE 'SELECT /*ACS_1%';

二、第一个场景
准备好以上的语句和环境之后，开始测试
第一次执行sql，绑定变量值是9，第二次是10，第三次是9
然后我们看一下，cursor的情况
每次都会重用同样的执行计划

点击(此处)折叠或打开

1. CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
2. ------------ ---------- ----------- ---------- ---------- ----------
3. 0 1 141 N N Y
5. CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
6. ------------ ---------- ----------- ---------- ---------- ----------
7. 0 2 516 N N Y
9. CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
10. ------------ ---------- ----------- ---------- ---------- ----------
11. 0 3 519 N N Y

三、 搜集柱状图
接下来，我们收集柱状图，下面2种方法都可以

点击(此处)折叠或打开

1. BEGIN
2. DBMS_STATS.GATHER_table_STATS (
3. OWNNAME => 'WBG',
4. TABNAME => 'EMP',
5. METHOD_OPT => 'FOR COLUMNS SIZE 10 DEPTNO' );
6. END;
7. /
9. exec dbms_stats.gather_table_stats(ownname=>'wbg',tabname => 'emp',estimate_percent => null,method_opt => 'for all indexed columns',cascade => true);

四、第二个场景
之后，我们做一下类似的测试，绑定变量先赋值成9
然后我们看一下，cursor的情况

点击(此处)折叠或打开

1. CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
2. ------------ ---------- ----------- ---------- ---------- ----------
3. 0 1 145 Y N Y

我们可以看到，这一次IS_BIND_SENSITIVE是Y

第二次，绑定变量先赋值成10

点击(此处)折叠或打开

1. CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
2. ------------ ---------- ----------- ---------- ---------- ----------
3. 0 2 520 Y N Y

这一次，重用了执行计划，BUFFER_GETS好大

第三次，绑定变量先赋值成10

点击(此处)折叠或打开

1. CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
2. ------------ ---------- ----------- ---------- ---------- ----------
3. 0 2 520 Y N N
4. 1 1 185 Y Y Y

这次oracle因为上次的BUFFER_GETS好大，从而产生了新的执行计划，BIND_AWARE是Y了

第四次，绑定变量先赋值成9

点击(此处)折叠或打开

1. CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
2. ------------ ---------- ----------- ---------- ---------- ----------
3. 0 2 520 Y N N
4. 1 1 185 Y Y Y
5. 2 1 3 Y Y Y

这一次，又有新的执行计划了，这是因为BIND_SENSITIVE生效了

当绑定变量的值是9的时候，应该走这样的执行计划，查询的数据少

点击(此处)折叠或打开

1. -------------------------------------------------------------------------------------
2. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
3. -------------------------------------------------------------------------------------
4. | 0 | SELECT STATEMENT | | | | 2 (100)| |
5. | 1 | SORT AGGREGATE | | 1 | 8 | | |
6. | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 10 | 80 | 2 (0)| 00:00:01 |
7. |* 3 | INDEX RANGE SCAN | TEST | 10 | | 1 (0)| 00:00:01 |
8. -------------------------------------------------------------------------------------

当绑定变量的值是10的时候，应该走这样的执行计划，查询的数据少

点击(此处)折叠或打开

1. ---------------------------------------------------------------------------
2. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
3. ---------------------------------------------------------------------------
4. | 0 | SELECT STATEMENT | | | | 53 (100)| |
5. | 1 | SORT AGGREGATE | | 1 | 8 | | |
6. |* 2 | TABLE ACCESS FULL| EMP | 99900 | 780K| 53 (4)| 00:00:01 |
7. ---------------------------------------------------------------------------

以下是我查看执行计划的方法
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

没有柱状图的时候，cursor的重用很无脑，当有了柱状图，oracle可以根据不同的变量，走不通的执行计划

官方对于相关的技术，描述如下
1.Adaptive Cursor Sharing
The adaptive cursor sharing feature enables a single statement that contains bind variables to use multiple execution plans. Cursor sharing is "adaptive" because the cursor adapts its behavior so that the database does not always use the same plan for each execution or bind variable value.
For appropriate queries, the database monitors data accessed over time for different bind values, ensuring the optimal choice of cursor for a specific bind value. For example, the optimizer might choose one plan for bind value 9 and a different plan for bind value 10. Cursor sharing is "adaptive" because the cursor adapts its behavior so that the same plan is not always used for each execution or bind variable value.
2.Bind-Sensitive Cursors
A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable. The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.
The criteria used by the optimizer to decide whether a cursor is bind-sensitive include the following:
    The optimizer has peeked at the bind values to generate selectivity estimates.
    A histogram exists on the column containing the bind value.
3.Bind-Aware Cursors
A bind-aware cursor is a bind-sensitive cursor eligible to use different plans for different bind values. After a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its selectivity estimate.
When a statement with a bind-sensitive cursor executes, the database decides whether to mark the cursor bind-aware. The decision depends on whether the cursor produces significantly different data access patterns for different bind values. If the database marks the cursor bind-aware, then the next time that the cursor executes the database does the following:
    Generates a new plan based on the new bind value.
    Marks the original cursor generated for the statement as not shareable (V$SQL.IS_SHAREABLE is N). This cursor is no longer usable and will be among the first to be aged out of the shared SQL area.

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

转载于:http://blog.itpub.net/30202921/viewspace-2131147/