Oracle cursor_sharing，histogram对于sql version count的影响

最新推荐文章于 2023-03-25 15:20:54 发布
最新推荐文章于 2023-03-25 15:20:54 发布
阅读量121 收藏
点赞数
分类专栏： ORACLE管理 文章标签： SQL Oracle J#
本文章主要研究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]
zhoul77777
关注
专栏目录
window多个oracle查版本,Oracle SQL多版本VERSION_COUNT产生的可能性
weixin_29556943的博客
04-03 445
Oracle SQL多版本VERSION_COUNT产生的可能性Oracle数据库中执行的SQL，很多时候会因为种种原因产生多个不同的执行版本，一个游标的版本过多很容易引起数据库的性能问题，甚至故障。有时候一个SQL的版本数量可能多达数万个，以下是我之前在恩墨讲堂分享过的一个案例。产生SQL多版本的原因很多，通过如下的一些测试我们可以稍微来看看如何分析和找到可能的原因。以下作为一个基础测试数据，一...
Oracle数据倾斜问题,Oracle数据倾斜导致的问题-有绑定变量
weixin_34957608的博客
04-03 1356
Oracle数据倾斜导致的问题-有绑定变量参考整理---<<恩墨年货-SQL与性能优化>>场景二：在上一节实验可以知道，没有绑定变量时，数据倾斜问题在特定场景下可以用直方图解决，那么在有绑定变量情况下，数据倾斜问题单凭直方图可以解决吗？显然是不能的，Oracle绑定变量技术解决了SQL语句硬解析过多的问题，降低了资源的争用。但是绑定变量在引入cursor sharing，增...
Oracle 高 Version counts 问题说明
ijavagos
07-23 354
一.version count 说明 MOS上有文档说明此问题，参考：[ID296377.1] Oracle里的所有SQL 语句都是implicitly sharable的。 当用户提交了一个SQL语句，RDBMS 会对这个SQL 语句进行hash 运算，然后产生一个hash 值， 在拿这个hash 值与shared pool 里已经存在的SQL 进行hash 值比较，如果相同，就重用之前...
SQL ordered by Version Count
rgb_rgb的博客
08-01 5405
v$sql v$sqlarea v$sql_shared_cursor及父游标子游标 1.     v$sql和v$sqlarea的区别 v$sql和v$sqlarea从某种意义上具有父子关系。即v$sqlarea保存的是父游标的sql信息，而v$sql保存的是子游标的sql的信息。在v$sqlarea里面有一列VERSION_COUNT字段，其中代表的就是对于此父游标的子游标的数量，也就是在
oracle11g v$sql_v$sqlarea_version_count测试
06-13 281
--测试version_count相关原因 --业务会话SQL> conn scott/systemConnected.SQL> create table t_version(a int); Table crea...
记一次Oracle SQL高版本（high version count）引起性能故障处理
老A的自留地，欢迎加微信交流，微信号zhoul777
04-18 454
用户报告前台业务响应缓慢，登陆至数据库获取awr报告。kill掉相关会话后系统恢复，杀会话可参考metalink id 786507.1 Load Profile 除了Rollback per transaction %比较高之外，达到了95%。其他指标似乎正常。 [img]http://dl.iteye.com/upload/attachment/466012/3cc749e7-fc75-3...
oracle 19c statspack test
03-25 731
3. install。
oracle的AWR报告样式供参考学习
微风
02-22 4438
oracle的AWR报告样式供参考学习 WORKLOAD REPOSITORY report for(example for learning)
oracle AWR报告原文件
常语的专栏
12-06 4619
WORKLOAD REPOSITORY report for DB Name DB Id Instance Inst num Startup Time Release RAC MYPL 569030708 mypl 1 03-Dec-13 20:12 11.2.0.1.0 NO
Oracle事件诊断列表
10-13 1万+
oracle事件诊断列表，从0到20000，并输出到文件oracle.txt中。 SET linesize 120 SET feedback off SET SERVEROUTPUT ON spool "d:\oracl.txt" DECLARE err_msg VARCHAR2(120); BEGIN dbms_output.enable(1000000); FOR err
oracle实验记录 （histogram是否影响解析）
cuiye1492的博客
10-12 76
HISTOGRAM 与解析 如果oracle采用histogram信息 使得 有些信息改变 比如plan中 ROWS(CARD)之类 是否会造成 新的hard parse SQL> create table t1 (a...
转 如何诊断和解决high version count 10.2.0.4 and 11.2.0.4
weixin_33921089的博客
12-08 307
转自 http://blog.csdn.net/notbaron/article/details/50927492 在Oracle 10g以上的版本，High version count可谓是一个臭名昭著的问题。Hight version count不仅仅产生的原因多种多样，并且会导致各种令人头痛的问题，轻导致数据库的性能急剧下降，CPU利用率剧增，重则导致数据库挂起，触发ORA-040...
version怎么用oracle,【学习笔记】Oracle High Versions Count高版本游标的原因和解决办法...
weixin_42394088的博客
04-04 575
天萃荷净通过查看v$sqlarea视图的loaded_versions来判断当前这个SQL的version count，诊断oracle high version count(高版本游标)问题version_rpt3_21脚本:下载地址链接：http://pan.baidu.com/s/1pLeVwcN 密码：ubpq参考:Troubleshooting: High Version Count I...
玉面飞龙 oracle,version_count到底是怎么算的???(玉面飞龙请进)
weixin_30590247的博客
04-03 361
http://yumianfeilong.com/2007/04 ... %e5%a4%aa%e5%a4%9a/这是你的blog上的文章里面提到Oracle Bind Variable在Varchar2字段上存在一些坎儿，32，128，2000。意味着在应用中(N>2000)，一个Varchar2(N),可以贡献4个不同的子游标。2个Varchar2(N)可以贡献7个子游标。如果一个表上有1...
SQL version count
文档搬运工
03-15 896
RDBMS 11.2.0.4表名字相同，schema不同，在不同的schema下对于表进行查询。会导致sql version count增加例如：BB@orcl&gt;conn cc/oracle Connected. CC@orcl&gt;select count(*) from bb_t1; COUNT(*) ---------- 5581504 CC@orcl&g...
如何诊断和解决high version count
热门推荐
badman250的专栏
03-18 3万+
在Oracle 10g以上的版本，High version count可谓是一个臭名昭著的问题。Hight version count不仅仅产生的原因多种多样，并且会导致各种令人头痛的问题，轻导致数据库的性能急剧下降，CPU利用率剧增，重则导致数据库挂起，触发ORA-04031或者其它bug导致宕机。 什么是verion count，什么是high？ 在弄清楚诊断和解决这个问题之前，首
毕业设计论文Nodejs+Vue宠物店管理系统.docx
10-16
毕业设计论文
（自适应手机版）响应式化工工业企业模板_网站整站打包下载.zip
10-16
（自适应手机版）响应式化工工业企业模板_网站整站打包下载.zip
金蝶与C#对接操作手册.doc
最新发布
10-16
金蝶EAS凭证引入 “webservice接口”说明
geom_histogram
05-02
`geom_histogram` 是 ggplot2 包中的一个函数，用于绘制直方图。它可以将数据分成若干个等宽的区间（也称为“柱”或“箱”），并计算每个区间内数据的频率或计数。然后用柱形图来表示每个区间内的数据量。 以下是 `...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值