[ORACLE]自适应游标共享Adaptive Cursor Sharing -云博客

[ORACLE]自适应游标共享Adaptive Cursor Sharing

# 数据库 2024-05-05 12:48 0 26 来源：云博客

V$SQL

这个视图有3个和ACS相关的列
-- IS_BIND_SENSITIVE 用于监视SQL游标是否是绑定敏感,是否可用Adaptive Cursor Sharing
-- IS_BIND_AWARE 用于监视SQL游标是否需要根据绑定变量值来选择计划
-- IS_SHAREABLE 用于监视SQL游标是否可以共享

v$SQL_SHARED_CURSOR --查看SQL游标没有共享的原因v$SQL_CS_SELECTIVITY --查看SQL游标的各个变量选择范围(SELECTIVY CUBE),它包含着各个条件谓词,绑定变量值以及它的最大值和最小值等信息v$SQL_CS_HISTOGRAM --根据所操作行数,记录每个子游标执行次数直方图v$SQL_CS_STATISTICS --每个子游标执行的执行状况.(采样信息)

select IS_BIND_SENSITIVE,IS_BIND_AWARE,IS_SHAREABLE,SQL_TEXT,SQL_ID,CHILD_NUMBER,EXECUTIONS,BUFFER_GETS,HASH_VALUE,PLAN_HASH_VALUE from v$sql where SQL_TEXT like ‘select count(pad) from t where id<=:id‘;select SQL_ID,CHILD_NUMBER,BIND_EQUIV_FAILURE,LOAD_OPTIMIZER_STATS from V$SQL_SHARED_CURSOR where SQL_ID=‘3dn9naksd7chh‘;select * from v$SQL_CS_STATISTICS where SQL_ID=‘3dn9naksd7chh‘;select * from V$SQL_CS_SELECTIVITY where SQL_ID=‘3dn9naksd7chh‘;select * from v$SQL_CS_HISTOGRAM where SQL_ID=‘3dn9naksd7chh‘;

1.构建Ｔ表，数据及主键

variable id numbercolumn sql_id new value sql_iddrop table t;create table t as select rownum as id,rpad(‘*‘,100,‘*‘) as pad from dual connect by level <= 1000;alter table t add constraint t_pk primary key (id);

2.收集统计信息

begin dbms_stats.gather_table_stats(ownname => ‘C##SAPR3‘, tabname => ‘T‘, estimate_percent => 100, method_opt => ‘for all columns size skewonly‘ );end;/

3. 查表T当前的分布情况

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

4.发现当前情况下,要吧区分出数据分布而正确使用执行计划

set linesize 1000set autotrace traceonly explainselect count(pad) from test where id< 990; Execution Plan----------------------------------------------------------Plan hash value: 1950795681------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 105 | 3 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 105 | | ||* 2 | TABLE ACCESS INMEMORY FULL| TEST | 990 | 101K| 3 (0)| 00:00:01 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - inmemory("ID"<990) filter("ID"<990)select count(pad) from test where id< 10;Execution Plan----------------------------------------------------------Plan hash value: 2239902560---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 105 | 3 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 105 | | || 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 9 | 945 | 3 (0)| 00:00:01 ||* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("ID"<10)

5现将id的值改为变量实验一下绑定变量的SQL是否能使用真直方图
首先代入990,发现走全表扫描,正确

execute :id :=990;select count(pad) from t where :id<=:id;SQL> select count(pad) from TEST where :id<=:id;COUNT(PAD)---------- 1000select * from table(dbms_xplan.display_cursor(null,null,‘ADVANCED ALLSTATS LAST PEEKED_BINDS‘));Plan hash value: 1617223730-------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |-------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 1 |00:00:00.01 | 3 || 1 | SORT AGGREGATE | | 1 | 1 | 101 | | | 1 |00:00:00.01 | 3 |PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------|* 2 | FILTER | | 1 | | | | | 1000 |00:00:00.01 | 3 || 3 | TABLE ACCESS INMEMORY FULL| TEST | 1 | 1000 | 98K| 3 (0)| 00:00:01 | 1000 |00:00:00.01 | 3 |-------------------------------------------------------------------------------------------------------------------------------SQL> select SQL_ID,CHILD_NUMBER,BIND_EQUIV_FAILURE,LOAD_OPTIMIZER_STATS from V$SQL_SHARED_CURSOR where SQL_ID=‘4c2ncvqa02kpm‘;SQL_ID CHILD_NUMBER B L------------- ------------ - -4c2ncvqa02kpm 0 N N

--接着代入10,发现仍走index,SQL_ID 发现更变

execute :id :=10;select count(pad) from TEST where id<=:id;COUNT(PAD)---------- 10select * from table(dbms_xplan.display_cursor(null,null,‘ADVANCED ALLSTATS LAST PEEKED_BINDS‘));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------SQL_ID d44bhvafdpckv, child number 0-------------------------------------select count(pad) from TEST where id<=:idPlan hash value: 2239902560---------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |---------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 1 |00:00:00.01 | 3 || 1 | SORT AGGREGATE | | 1 | 1 | 105 | | | 1 |00:00:00.01 | 3 |PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 1 | 10 | 1050 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 3 ||* 3 | INDEX RANGE SCAN | T_PK | 1 | 10 | | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 2 |---------------------------------------------------------------------------------------------------------------------------------------

6.共享池清空,很重要一步,保证硬解析

alter system flush shared_pool; #清空之后,所有的如下视图为空

v$SQL_SHARED_CURSOR --查看SQL游标没有共享的原因 v$SQL_CS_SELECTIVITY --查看SQL游标的各个变量选择范围(SELECTIVY CUBE),它包含着各个条件谓词,绑定变量值以及它的最大值和最小值等信息 v$SQL_CS_HISTOGRAM --根据所操作行数,记录每个子游标执行次数直方图 v$SQL_CS_STATISTICS --每个子游标执行的执行状况.(采样信息)

--接着代入10,发现可以使用直方图,执行计划为索引,很正常使用SQLID : d44bhvafdpckv

execute :id :=10;select count(pad) from t where id<=:id;select * from table(dbms_xplan.display_cursor(null,null,‘ADVANCED ALLSTATS LAST PEEKED_BINDS‘));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------SQL_ID d44bhvafdpckv, child number 0-------------------------------------select count(pad) from TEST where id<=:idPlan hash value: 2239902560---------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |---------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 1 |00:00:00.01 | 3 || 1 | SORT AGGREGATE | | 1 | 1 | 105 | | | 1 |00:00:00.01 | 3 |PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 1 | 10 | 1050 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 3 ||* 3 | INDEX RANGE SCAN | T_PK | 1 | 10 | | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 2 |---------------------------------------------------------------------------------------------------------------------------------------

可以看到IS_BIND_SENSITIVE 和IS_SHAREABLE 都变成Y, IS_BIND_AWARE还是N，　出现一条SQL_SHARED_CURSOR 语句,出瑞三条v$SQL_CS_HISTOGRAM记录

--代入990,发现异常,仍然走索引,这个时间应走全表扫描

execute :id :=990;select count(pad) from t where id<=:id;select * from table(dbms_xplan.display_cursor(null,null,‘ADVANCED ALLSTATS LAST PEEKED_BINDS‘));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------SQL_ID d44bhvafdpckv, child number 0-------------------------------------select count(pad) from TEST where id<=:idPlan hash value: 2239902560---------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |---------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 1 |00:00:00.01 | 5 || 1 | SORT AGGREGATE | | 1 | 1 | 105 | | | 1 |00:00:00.01 | 5 |PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 1 | 10 | 1050 | 3 (0)| 00:00:01 | 990 |00:00:00.01 | 5 ||* 3 | INDEX RANGE SCAN | T_PK | 1 | 10 | | 2 (0)| 00:00:01 | 990 |00:00:00.01 | 3 |---------------------------------------------------------------------------------------------------------------------------------------

些时,v$sql_shared_cursor 中LOAD_OPTIMIZER_STATS 为Y,表示可以优化.

再次执行,走全表扫描,出现child number 1

select count(pad) from t where id<=:id;select * from table(dbms_xplan.display_cursor(null,null,‘ADVANCED ALLSTATS LAST PEEKED_BINDS‘));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------SQL_ID d44bhvafdpckv, child number 1-------------------------------------select count(pad) from TEST where id<=:idPlan hash value: 1950795681------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 1 |00:00:00.01 | 3 || 1 | SORT AGGREGATE | | 1 | 1 | 105 | | | 1 |00:00:00.01 | 3 |PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------|* 2 | TABLE ACCESS INMEMORY FULL| TEST | 1 | 991 | 101K| 3 (0)| 00:00:01 | 990 |00:00:00.01 | 3 |------------------------------------------------------------------------------------------------------------------------------

sql_shared_cursor 中BIND_EQUIV_FAILURE 失效.

再次执行,出现child number 2

execute :id :=10;select count(pad) from t where id<=:id;select * from table(dbms_xplan.display_cursor(null,null,‘ADVANCED ALLSTATS LAST PEEKED_BINDS‘));SQL_ID d44bhvafdpckv, child number 2-------------------------------------select count(pad) from TEST where id<=:idPlan hash value: 2239902560---------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |---------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 1 |00:00:00.01 | 3 || 1 | SORT AGGREGATE | | 1 | 1 | 105 | | | 1 |00:00:00.01 | 3 |PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 1 | 10 | 1050 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 3 ||* 3 | INDEX RANGE SCAN | T_PK | 1 | 10 | | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 2 |---------------------------------------------------------------------------------------------------------------------------------------