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'));