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