[ORACLE]自适应游标共享Adaptive Cursor Sharing
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了[ORACLE]自适应游标共享Adaptive Cursor Sharing，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含14050字，纯文字阅读大概需要21分钟。
内容图文
查看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 number column sql_id new value sql_id drop 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 1000 set autotrace traceonly explain select 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) ---------- 1000 select * 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) ---------- 10 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<=:id Plan 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 | ---------------------------------------------------------------------------------------------------------------------------------------
![[ORACLE]自适应游标共享Adaptive Cursor Sharing - 文章图片](/upload/getfiles/0001/2021/4/25/20210425125721209.jpg)
![[ORACLE]自适应游标共享Adaptive Cursor Sharing - 文章图片](/upload/getfiles/0001/2021/4/25/20210425125721355.jpg)
![[ORACLE]自适应游标共享Adaptive Cursor Sharing - 文章图片](/upload/getfiles/0001/2021/4/25/20210425125721630.jpg)
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<=:id Plan 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 | ---------------------------------------------------------------------------------------------------------------------------------------
![[ORACLE]自适应游标共享Adaptive Cursor Sharing - 文章图片](/upload/getfiles/0001/2021/4/25/20210425125721977.jpg)
可以看到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<=:id Plan 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 | ---------------------------------------------------------------------------------------------------------------------------------------
![[ORACLE]自适应游标共享Adaptive Cursor Sharing - 文章图片](/upload/getfiles/0001/2021/4/25/20210425125722329.jpg)
些时,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<=:id Plan 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 | ------------------------------------------------------------------------------------------------------------------------------
![[ORACLE]自适应游标共享Adaptive Cursor Sharing - 文章图片](/upload/getfiles/0001/2021/4/25/20210425125722652.jpg)
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<=:id Plan 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 -------------------------------------------------------------------------------------------------------------------------------------------- | <span 本文系统来源：https://www.cnblogs.com/tingxin/p/12776329.html
内容总结
以上是互联网集市为您收集整理的[ORACLE]自适应游标共享Adaptive Cursor Sharing全部内容，希望文章能够帮你解决[ORACLE]自适应游标共享Adaptive Cursor Sharing所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】