自适应游标共享技术02(一个简单的例子来走近ACS)
为了不让其他因素干扰实验，参数设置如下:
optimizer_mode=ALL_ROWS（使用CBO） optimizer_features_enable=11.2.0.3（使用最新的优化参数） optimizer_capture_sql_plan_baselines=false（关闭SPM） cursor_sharing=EXACT（使用真正的绑定变量） _optim_peek_user_binds=true（一定要开启绑定变量窥视） _optimizer_adaptive_cursor_sharing=TRUE（以下三个参数默认开启ACS） _optimizer_extended_cursor_sharing=UDO _optimizer_extended_cursor_sharing_rel=SIMPLE --查询隐含参数: SELECT ksppinm, ksppstvl, ksppdesc FROM sys.x$ksppi x, sys.x$ksppcv y WHERE x.indx = y.indx AND ksppinm = '_optimizer_extended_cursor_sharing';
建立测试场景：
#创建表 SQL> create table tab_acs(id int,value int); #导入数据 SQL> begin 2 for i in 1 .. 20000 3 loop 4 execute immediate 'insert into tab_acs values(1,'||i||')'; 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. SQL> begin 2 for i in 1 .. 10 3 loop 4 execute immediate 'insert into tab_acs values(2,'||i||')'; 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. SQL> commit; #创建索引 SQL> create index idx_tab_acs on tab_acs(id); #收集统计信息 SQL> exec dbms_stats.gather_table_stats(user,'TAB_ACS',method_opt=>'for all columns size skewonly'); #查看数据直方图分布 SQL> select COLUMN_NAME,HISTOGRAM from user_tab_columns where TABLE_NAME='TAB_ACS'; COLUMN_NAME HISTOGRAM ------------------------------ --------------- ID FREQUENCY --等频直方图 VALUE NONE SQL>
先生成一个最简单的执行计划index range scan
对于id=2来说，是相当合适的。 SQL> var v number; SQL> exec :v :=2; SQL> select /*comments*/ * from TAB_ACS 2 where id = :v; 查询SQL_ID，HASH_VALUE： SQL> select sql_id, hash_value,sql_text from v$sql 2 where sql_text like '%from%TAB_ACS%'; SQL_ID HASH_VALUE SQL_TEXT ------------- ---------- -------------------------------------------------------------------------------- 9s0fn2xffhk6z 1558726879 select /*comments*/ * from TAB_ACS where id = :v 获取真实的执行计划： --SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR(:sqlid, NULL, 'TYPICAL LAST')) SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('9s0fn2xffhk6z', NULL, 'TYPICAL LAST')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 9s0fn2xffhk6z, child number 0 ------------------------------------- select /*comments*/ * from TAB_ACS where id = :v Plan hash value: 360359870 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | 1 | TABLE ACCESS BY INDEX ROWID| TAB_ACS | 10 | 70 | 2 (0)| |* 2 | INDEX RANGE SCAN | IDX_TAB_ACS | 10 | | 1 (0)| -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=:V) 19 rows selected SQL> 从v$SQL中，可以看到这个cursor的数据： IS_BIND_SENSITIVE=Y，表明使用绑定变量窥视来生成这次执行计划，这次执行计划是取决于这个绑定变量的。如果Oracle发现有其他的绑定变量出现，是可能生成其他的执行计划的。 IS_BIND_AWARE=N，表明Oracle还没有使用extended cursor sharing IS_SHAREABLE=Y，表明这个cursor可以被再次使用，即能够共享；反之，设为N代表着这个cursor已经过时了，不会被再用了，这个cursor将会等待被age out出shared pool。 SQL> select CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS,BUFFER_GETS/EXECUTIONS BG_PER_EX,IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S 2 from v$sql where hash_value=1558726879; CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S ------------ --------------- ---------- ---------- -- -- - 0 360359870 1 16 Y N Y SQL>
更换绑定变量，使用id=1执行同样的SQL. SQL> exec :v := 1; SQL> select /*comments*/ * from TAB_ACS 2 where id = :v; 结果，使用绑定变量id=1的SQL使用了同样的index range scan的cursor。这其实不是我们希望的，因为id=1时明显走全表扫描cost更低。 v$SQL没怎么变，只是同样的cursor执行次数为2了。 SQL> select CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS,BUFFER_GETS/EXECUTIONS BG_PER_EX,IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S 2 from v$sql where hash_value=1558726879; CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S ------------ --------------- ---------- ---------- -- -- - 0 360359870 2 246 Y N Y SQL>
--再次执行同样的id=1的SQL。 SQL> exec :v := 1; SQL> select /*comments*/ * from TAB_ACS 2 where id = :v; ....... --获取sql_id,child_number,hash_value SQL> select sql_id,child_number, hash_value,sql_text from v$sql 2 where sql_text like '%from%TAB_ACS%'; SQL_ID CHILD_NUMBER HASH_VALUE SQL_TEXT ------------- ------------ ---------- -------------------------------------------------------------------------------- 9s0fn2xffhk6z 0 1558726879 select /*comments*/ * from TAB_ACS where id = :v 9s0fn2xffhk6z 1 1558726879 select /*comments*/ * from TAB_ACS where id = :v SQL> --获取真是执行计划 SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('9s0fn2xffhk6z', NULL, 'TYPICAL LAST')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 9s0fn2xffhk6z, child number 1 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- select /*comments*/ * from TAB_ACS where id = :v Plan hash value: 4258990176 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 17 (100)| | |* 1 | TABLE ACCESS FULL| TAB_ACS | 20000 | 136K| 17 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=:V) 37 rows selected SQL> 终于，我们期望的事情发生了，新的全表扫描的执行计划产生了！（对应于CHILD_NUMBER=1,PLAN_HASH_VALUE=4258990176） v$SQL里，新的cursor的IS_BIND_AWARE=Y。 SQL> select SQL_ID,CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS,BUFFER_GETS/EXECUTIONS BG_PER_EX,IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S 2 from v$sql where SQL_ID='9s0fn2xffhk6z'; SQL_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S ------------- ------------ --------------- ---------- ---------- -- -- - 9s0fn2xffhk6z 0 360359870 2 246 Y N N 9s0fn2xffhk6z 1 4258990176 1 266 Y Y Y SQL>
--再次执行id=1的SQL SQL> exec :v := 1; SQL> select /*comments*/ * from TESTBYHAO 2 where id = :v; CHILD 1执行次数增加为2 SQL> select SQL_ID,CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS,BUFFER_GETS/EXECUTIONS BG_PER_EX,IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S 2 from v$sql where SQL_ID='9s0fn2xffhk6z'; SQL_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S ------------- ------------ --------------- ---------- ---------- -- -- - 9s0fn2xffhk6z 0 360359870 2 246 Y N N 9s0fn2xffhk6z 1 4258990176 2 266 Y Y Y SQL> 再次执行id=2的SQL SQL> exec :v := 2; SQL> select /*comments*/ * from TESTBYHAO 2 where id = :v; SQL> select SQL_ID,CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS,BUFFER_GETS/EXECUTIONS BG_PER_EX,IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S 2 from v$sql where SQL_ID='9s0fn2xffhk6z'; SQL_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S ------------- ------------ --------------- ---------- ---------- -- -- - 9s0fn2xffhk6z 0 360359870 2 246 Y N N 9s0fn2xffhk6z 1 4258990176 2 266 Y Y Y 9s0fn2xffhk6z 2 360359870 1 4 Y Y Y SQL> 奇怪的事情发生了，又新生成了一个index range scan的cursor（CHILD 2），并且CHILD 0的IS_SHAREABLE=N了，表明这个cursor不再被使用了。
