11g Adaptive Cursor Sharing


		11g Adaptive Cursor Sharing
 
11g之前的绑定变量偷窥，带来一些绑定变量导致执行计划问题。研究下11g的Adaptive Cursor Sharing
 
[sql] 
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] 
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] 
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] 
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] 
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] 
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] 
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  
------------------------------------------------------------------------------------------------------------------------------------------------------  
EXPLAI