自适应游标共享技术02(一个简单的例子来走近ACS)

时间：2023-03-09 15:29:08
自适应游标共享技术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 ），并且CHILD 0的IS_SHAREABLE=N了，表明这个cursor不再被使用了。