Oracle cursor_sharing，histogram对于sql version count的影响-程序员宅基地

技术标签： J#  ORACLE管理  Oracle  SQL  

本文章主要研究cursor_sharing参数不同设置，histograms收集与否对SQL version count 产生的影响。
一、cursor_sharing参数与SQL version count
数据库研究版本：
[quote]SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi[/quote]

创建测试脚本
[quote]create table t1( i number, j number, k number);
begin
for i in 1..10000 loop
insert into t1 values( i, i, i);
end loop;
end;
/[/quote]
将cursor_sharing参数改为similar，可以看出谓词连接采用>（还有>, <, >=, <=, LIKE）时，SQL在共享parent cursor时不能共享child cursor
[quote]SQL> alter session set cursor_sharing=similar
2 ;

Session altered.

SQL> select count(*) from t1 where i > 10;

COUNT(*)
----------
9990

SQL>select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > :"SYS_B_0"
1


SQL> select count(*) from t1 where i > 20;

COUNT(*)
----------
9980

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > :"SYS_B_0"
2


SQL> select count(*) from t1 where i > 30;

COUNT(*)
----------
9970

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > :"SYS_B_0"
3[/quote]

将谓词连接改为=时，SQL在共享parent cursor时能共享child cursor
[quote]SQL> select count(*) from t1 where i=100;

COUNT(*)
----------
1

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > :"SYS_B_0"
3

select count(*) from t1 where i=:"SYS_B_0"
1

SQL> select count(*) from t1 where i=200;

COUNT(*)
----------
1

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > :"SYS_B_0"
3

select count(*) from t1 where i=:"SYS_B_0"
1[/quote]

将cursor_sharing参数改为force，我们看到cursor实现了共享。
[quote]SQL> alter session set cursor_sharing=force;

Session altered.

SQL> select count(*) from t1 where i > 40;

COUNT(*)
----------
9960

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > :"SYS_B_0"
4

select count(*) from t1 where i=:"SYS_B_0"
1

SQL> select count(*) from t1 where i > 50;

COUNT(*)
----------
9950

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > :"SYS_B_0"
4

select count(*) from t1 where i=:"SYS_B_0"
1[/quote]
进一步将cursor_sharing改为exact，cursor甚至不能在parent级别实现共享
[quote]SQL> alter session set cursor_sharing=exact;

Session altered.

SQL> select count(*) from t1 where i > 70;

COUNT(*)
----------
9930

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > :"SYS_B_0"
4

select count(*) from t1 where i >:"SYS_B_0"
1

select count(*) from t1 where i=:"SYS_B_0"
1


SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > 70
1


SQL> select count(*) from t1 where i > 80;

COUNT(*)
----------
9920

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > :"SYS_B_0"
4

select count(*) from t1 where i >:"SYS_B_0"
1

select count(*) from t1 where i=:"SYS_B_0"
1


SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i > 80
1

select count(*) from t1 where i > 70
1
[/quote]
二、cursor_sharing参数histogram与SQL version count

在table t1所有列上收集直方图（J列除外）
[quote]SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'sys',tabname=>'t1',method_opt=>'FOR ALL COLUMNS SIZE 10');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'sys',tabname=>'t1',method_opt=>'FOR COLUMNS J SIZE 1');

PL/SQL procedure successfully completed.

SQL> select COLUMN_NAME,NUM_BUCKETS,HISTOGRAM from dba_tab_columns where OWNER='SYS' and TABLE_NAME='T1';

COLUMN_NAME NUM_BUCKETS HISTOGRAM
------------------------------ ----------- ---------------
I 10 HEIGHT BALANCED
J 1 NONE
K 10 HEIGHT BALANCED[/quote]

将cursor_sharing改为similar，发现在有直方图的列上不能实现child cursor共享。
[quote]SQL> alter session set cursor_sharing = similar;

Session altered.

SQL> select count(*) from t1 where i = 10;

COUNT(*)
----------
1

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :"SYS_B_0"
1


SQL> select count(*) from t1 where i = 20;

COUNT(*)
----------
1

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :"SYS_B_0"
2


SQL> select count(*) from t1 where i = 30;

COUNT(*)
----------
1

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :"SYS_B_0"
3


SQL> alter system flush shared_pool;

System altered.

SQL> select count(*) from t1 where j = 10;

COUNT(*)
----------
1

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where j = :"SYS_B_0"
1


SQL> select count(*) from t1 where j = 20;

COUNT(*)
----------
1

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where j = :"SYS_B_0"
1


SQL> select count(*) from t1 where j = 30;

COUNT(*)
----------
1

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where j = :"SYS_B_0"
1[/quote]

将cursor_sharing改为force时，无论在有直方图或无直方图的列上都实现了游标共享。
[quote]SQL> alter system flush shared_pool;

System altered.

SQL> alter session set cursor_sharing=force;

Session altered.

SQL> select count(*) from t1 where i = 10;

COUNT(*)
----------
1

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :"SYS_B_0"
1


SQL> select count(*) from t1 where i = 20;

COUNT(*)
----------
1

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :"SYS_B_0"
1


SQL> select count(*) from t1 where i = 30;

COUNT(*)
----------
1

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :"SYS_B_0"
1

SQL> select count(*) from t1 where j = 10;

COUNT(*)
----------
1

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :"SYS_B_0"
1

select count(*) from t1 where i = :"SYS_B_0"
1


SQL> select count(*) from t1 where j = 20;

COUNT(*)
----------
1

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :"SYS_B_0"
1

select count(*) from t1 where i = :"SYS_B_0"
1


SQL> select count(*) from t1 where j = 30;

COUNT(*)
----------
1

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :"SYS_B_0"
1

select count(*) from t1 where i = :"SYS_B_0"
1[/quote]
将cursor_sharing改为exact，并使用绑定变量，经测试实现了游标共享，但是需要注意的是如果使用绑定变量由于BIND PEEKING，会导致执行计划不稳定
[quote]SQL> alter system flush shared_pool;

System altered.

SQL> alter session set cursor_sharing=exact;

Session altered.

SQL> variable i number;
SQL> exec :i:=10

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where i = :i;

COUNT(*)
----------
1

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :i
1


SQL> exec :i:=20

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where i = :i;

COUNT(*)
----------
1

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :i
1


SQL> exec :i:=0

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where i = :i;

COUNT(*)
----------
0

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :i
1

SQL> variable j number;
SQL> exec :j:=10

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where j = :j;

COUNT(*)
----------
1

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where i = :i
1


SQL> exec :j:=100

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where j = :j;

COUNT(*)
----------
1

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where j = :j
1

select count(*) from t1 where i = :i
1


SQL> exec :j:=1000

PL/SQL procedure successfully completed.

SQL> select sql_text,version_count from v$sqlarea where
2 sql_text like 'select count(*) from t1 where%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select count(*) from t1 where j = :j
1

select count(*) from t1 where i = :i
1
[/quote]
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhoul77777/article/details/83598328

智能推荐

Mysql启用SSL以及JDBC连接Mysql配置_jdbc mysql ssl-程序员宅基地

文章浏览阅读6.7k次。一、Mysql启用SSL配置1.检查mysql是否支持ssl在linux端用root账号进入mysql命令行界面，查看当前版本mysql数据库是否支持ssl，如果出现以下结果表示支持，如果没有考虑更换版本，或者编译一个带有SSL版本的mysqlshell>show variables like ‘%ssl%’;2.设置用户是否使用ssl连接1.查看用户是否使用SSL连接she..._jdbc mysql ssl

java jwt使用，springboot 整合java-jwt，java jwt工具类-程序员宅基地

文章浏览阅读612次。java jwt使用，springboot 整合java-jwt，java jwt工具类================================Copyright 蕃薯耀2020-12-03https://www.cnblogs.com/fanshuyao/一、引入java-jwt的maven依赖<dependency> <groupId>..._jwtproperties

聊聊 Kafka： 在 Linux 环境上搭建 Kafka，Linux运维未来路在何方-程序员宅基地

文章浏览阅读753次，点赞21次，收藏15次。列出现有主题，创建主题，该主题包含一个分区，该分区为Leader分区，它没有Follower分区副本。启动成功，可以看到控制台输出的最后一行的started状态：此时kafka安装成功。查看zookeeper状态，zookeeper启动成功，再启动kafka。onsole-producer.sh用于生产消息**开启消费者和生产者，生产并消费消息。开启消费者和生产者，生产并消费消息。在Zookeeper中的根节点路径。创建主题，该主题包含多个分区。的地址，此处使用本地启动的。查看指定主题的详细信息。

PTA 数据结构与算法题目集（中文）6-7_pta数据结构6-7-程序员宅基地

文章浏览阅读695次。6-7 在一个数组中实现两个堆栈（20 分）本题要求在一个数组中实现两个堆栈。函数接口定义：Stack CreateStack( int MaxSize );bool Push( Stack S, ElementType X, int Tag );ElementType Pop( Stack S, int Tag );其中Tag是堆栈编号，取1或2；Max_pta数据结构6-7

只要三步！阿里云DLA帮你处理海量JSON数据-程序员宅基地

文章浏览阅读123次。概述 您可能有大量应用程序产生的JSON数据，您可能需要对这些JSON数据进行整理，去除不想要的字段，或者只保留想要的字段，或者仅仅是进行数据查询。 那么，利用阿里云Data Lake Analytics或许是目前能找到的云上最为便捷的达到这一目标的服务了。仅仅需要3步，就可以完成对海量..._什么云服务可以直接存储json数据

react常见面试题_react diff 面试题-程序员宅基地

文章浏览阅读413次。diff 算法 虚拟dom 理论_react diff 面试题

随便推点

【QT】QT从零入门教程(七)：鼠标滚轮实现图像的放大缩小_qt滚轮放大缩小-程序员宅基地

文章浏览阅读9.6k次，点赞11次，收藏89次。鼠标滚轮实现图像放大缩小的主要思想：通过wheelEvent来获得鼠标滚轮的angleDelta，即滚轮转角。然后通过数据类型转换，将读取的值转换成整型数值叠加到图像的尺寸长和宽上，从而实现图像的放大和缩小。注意：滚轮向上滑转角为正，所以图像放大。滚轮向下滑转角为负，所以图像缩小。下边直接上代码，头文件中只需要加上使用鼠标滚轮的声明函数就行：void wheelEvent(QWheelEve..._qt滚轮放大缩小

Qt开发笔记：QGLWidget、QOpenGLWidget详解及区别_qt 用qopenglwidget生成release版,依赖什么库-程序员宅基地

文章浏览阅读7.9w次，点赞53次，收藏235次。若该文为原创文章，未经允许不得转载原博主博客地址：https://blog.csdn.net/qq21497936本文章博客地址：https://blog.csdn.net/qq21497936/article/details/94585803目录前话相关博客QGLWidget概述QGLWidget子类示例更新绘制覆盖层绘制技术线程方案一：在线程中进..._qt 用qopenglwidget生成release版,依赖什么库

C 语言的浮点数类型_c语言float和double保留小数点后几位-程序员宅基地

文章浏览阅读5.3k次。C 语言的浮点数类型_c语言float和double保留小数点后几位

gradle打包报错Using insecure protocols with repositories..._gradle using insecure protocols with repositories,-程序员宅基地

文章浏览阅读3k次，点赞4次，收藏2次。gradle 打包时报以下错误：二、解决方法在 build.gradle 文件中找到 http://mirrors.huaweicloud.com/repository/maven/ 所在的位置，增加 allowInsecureProtocol = true 一行：_gradle using insecure protocols with repositories, without explicit opt-in,

java程序员微信群，欢迎准java行业人员加入,会一直更新_java开发接单群-程序员宅基地

文章浏览阅读7.3k次。微信群,请扫描二维码加入 本人在北京，主场北京,位置不限, 仅限java行业交流，C C##以及python请另外加群，谢谢欢迎准 java行业的进入，杜绝假冒程序员加入，精兵简政群内与java无关私事请私聊,任何java的问题,欢迎讨论——————————————————————————————————如若二维码失效，请加微信拉群..._java开发接单群

【数据库】数据、数据库、数据库管理系统、数据库系统_系统的数据管理逻辑-程序员宅基地

文章浏览阅读3.7k次，点赞2次，收藏43次。一、数据库系统概述数据库的四个基本概念：数据、数据库、数据库管理系统、数据库系统：1、数据：描述事物的符号记录称为数据。 （1）、数据是数据库中存储的基本对象。 （2）、数据是分类型的。 （3）、数据的含义称为数据的语义，数据与其语义是不可分的。 2、数据库：数据库是长期储存在计算机内、有组织的、可共享的大量数..._系统的数据管理逻辑

推荐文章

热门文章

相关标签