11gAdaptive Cursor Sharing

11g之前的绑定变量偷窥，带来一些绑定变量导致执行计划问题。研究下11g的Adaptive Cursor Sharing

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

1：建立测试表，分析测试表

SQL> DROP TABLE t;

Table dropped.

SQL> CREATE TABLE t 
  2  AS 
  3  SELECT rownum AS id, rpad('*',100,'*') AS pad 
  4  FROM dual
  5  CONNECT BY level <= 1000;

Table created.

SQL> ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);

Table altered.

SQL> BEGIN
  2    dbms_stats.gather_table_stats(
  3      ownname          => user, 
  4      tabname          => 't', 
  5      estimate_percent => 100, 
  6      method_opt       => 'for all columns size 1'
  7    );
  8  END;
  9  /:

2：查看数据分布

SQL> SELECT count(id), count(DISTINCT id), min(id), max(id) FROM t;

 COUNT(ID) COUNT(DISTINCTID)    MIN(ID)    MAX(ID)
---------- ----------------- ---------- ----------
      1000              1000          1       1000

3：不使用绑定的情况，查询990的条件，走全表扫描。为期望的执行计划：

SQL> SELECT count(pad) FROM t WHERE id < 990;

COUNT(PAD)
----------
       989

SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT count(pad) FROM t WHERE id < 990

Plan hash value: 2966233522

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------

4：不使用绑定变量的情况。走条件10的，cbo也选择了正确的执行计划

SQL> SELECT count(pad) FROM t WHERE id < 10;

COUNT(PAD)
----------
         9
QL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT count(pad) FROM t WHERE id < 10

Plan hash value: 4270555908

---------------------------------------------
| Id  | Operation                    | Name |
---------------------------------------------
|   0 | SELECT STATEMENT             |      |
|   1 |  SORT AGGREGATE              |      |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |
|   3 |    INDEX RANGE SCAN          | T_PK |

5：使用绑定变量，偷窥一下。cbo发现绑定变量的值走全表好，于是走了全表扫描，正常

SQL> var id number;
SQL> EXECUTE :id := 990;

PL/SQL procedure successfully completed.

SQL> SELECT count(pad) FROM t WHERE id < :id;

COUNT(PAD)
----------
       989

SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT count(pad) FROM t WHERE id < :id

Plan hash value: 2966233522

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |

6：绑定变量变为10，其实这个时候应该走index 好。但是由于绑定偷窥。cbo傻了，仍然走全表扫描

SQL> EXECUTE :id := 10;

PL/SQL procedure successfully completed.

SQL> SELECT count(pad) FROM t WHERE id < :id;

COUNT(PAD)
----------
         9

SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT count(pad) FROM t WHERE id < :id

Plan hash value: 2966233522

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |

7：第2次在执行这个语句。注意这个时候的绑定变量带人的值仍然是10，

SQL>  SELECT count(pad) FROM t WHERE id < :id;

COUNT(PAD)
----------
         9

SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
 SELECT count(pad) FROM t WHERE id < :id

Plan hash value: 4270555908

---------------------------------------------
| Id  | Operation                    | Name |
---------------------------------------------
|   0 | SELECT STATEMENT             |      |
|   1 |  SORT AGGREGATE              |      |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |
|   3 |    INDEX RANGE SCAN          | T_PK |
---------------------------------------------

发现了执行计划改变为走索引。

这就是11g的新特性。

总结 ：

ACS是指Oracle在不同cursor sharing技术之间进行自主选择。Oracle支持两种cursor sharing技术：
传统的PCST（Previous Cursor Sharing Technique），在硬解析时生成cursor，后续的semantically equivalent sql总是共享这个cursor，不会重复硬解析。
Oracle11g新引入的ECST（Extended Cursor Sharing Technique），根据不同semantically equivalent sql的selectivity等因素，Oracle可能会硬解析和创建新的cursor。


采用ECST后，Oracle在解析sql语句时就根据绑定变量值来评估谓词的selectivity。如果在软解析阶段找到可用的child cursor，
并且这个child cursor的selectivity范围涵盖了先前评估得到的selectivity，那就共享这个cursor，否则就新建执行计划，也就是硬解析。
（child cursor的selectivity范围保存在V$SQL_CS_SELECTIVITY视图中）。
硬解析得到新的执行计划后再和原来的child cursor的执行计划做比较，如果两者相差很大，就产生新的child cursor，
并记录selectivity范围（例如selectivity是0.01，那么范围就是0.009~0.011。如果是0.2，那么范围可能就是0.15~0.25）。
如果执行计划相同，那就共享原来的cursor，并且调整这个cursor的selectivity范围。


Oracle缺省采用PCST，因为它代价低，并且也足够好。ECST只对数据倾斜得很厉害的表有效。而ACS就是判断在什么情况下由PCST转为ECST。
在用相同的child cursor执行多次semantically equivalent sql时，Oracle记录了每次执行时的性能，例如cpu等。这些信息被组织成buckets，
每个bucket中包括一个范围和相应的执行次数，例如cpu时间在0.1s~0.2s的次数是1次，在10s~50s的执行次数也是1次。
（bucket信息保存在V$SQL_CS_HISTOGRAM中）如果这些统计值相差很大，并且执行次数又差不多，那说明数据倾斜的很厉害，就转为ECST。


初始采用PCST时cursor处于monitored state，使用ECST时就是aware state。也就是v$sql的is_bind_aware