11g Adaptive Cursor Sharing
11g Adaptive Cursor Sharing11g之前的绑定变量易做图,带来一些绑定变量导致执行计划问题。研究下11g的Adaptive Cursor Sharing[sql]SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE 11.2.0.3.0 ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - Production1:建立测试表,分析测试表[sql]SQL> DROP TABLE t;Table dropped.SQL> CREATE TABLE t2 AS3 SELECT rownum AS id, rpad('*',100,'*') AS pad4 FROM dual5 CONNECT BY level <= 1000;Table created.SQL> ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);Table altered.SQL> BEGIN2 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 10003:不使用绑定的情况,查询990的条件,走全表扫描。为期望的执行计划:[sql]SQL> SELECT count(pad) FROM t WHERE id < 990;COUNT(PAD)----------989SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------EXPLAINED SQL STATEMENT:------------------------SELECT count(pad) FROM t WHERE id < 990Plan 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)----------9QL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------EXPLAINED SQL STATEMENT:------------------------SELECT count(pad) FROM t WHERE id < 10Plan 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)----------989SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------EXPLAINED SQL STATEMENT:------------------------SELECT count(pad) FROM t WHERE id < :idPlan 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)----------9SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------EXPLAI上一个:oracle10g数据库类型
下一个:事务的并发控制