Cursor_sharing=SIMILAR取值与直方图（下）

最新推荐文章于 2021-12-02 20:13:18 发布
最新推荐文章于 2021-12-02 20:13:18 发布
阅读量109 收藏
点赞数
文章标签： 数据库

 

在上篇中，我们分析了在cursor_sharing=SIMILAR的情况下，Oracle不会考虑数据字典和列描述信息。这个过程中，SIMILAR的效果和FORCE效果相同。本篇我们继续研究SIMILAR的工作原理。

 

4、有直方图情况下的SIMILAR现象

 

直方图是描述数据偏移的重要指标。Oracle会重视直方图所体现出的数据描述内容。那么，直方图在促使SIMILAR其作用的过程中，是否起作用呢？

 

首先我们强制命令收集直方图。

 

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for columns id3 size 5');

 

PL/SQL procedure successfully completed

 

SQL> select column_name, num_distinct, NUM_BUCKETS, HISTOGRAM from dba_tab_col_statistics where wner='SYS' and table_name='T';

 

COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM

------------------------------ ------------ ----------- ---------------

ID1                                   53196           1 NONE

ID2                                   53196           1 NONE

ID3                                   53196           5 HEIGHT BALANCED

 

 

我们收集了id3列的高度直方图。下面观察执行效果。

 

 

SQL> select /*+ demo_id3_1 */ count(*) from t where id3=1000;

  COUNT(*)

----------

         1

 

 

SQL> select /*+ demo_id3_1 */ count(*) from t where id3=2000;

  COUNT(*)

----------

         1

 

SQL> select /*+ demo_id3_1 */ count(*) from t where id3=3000;

  COUNT(*)

----------

         1

 

SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like 'select /*+ demo_id3_1 */%';

SQL_TEXT             SQL_ID        VERSION_COUNT EXECUTIONS

-------------------- ------------- ------------- ----------

select /*+ demo_id3_ 1xydsanchug00             3          3

1 */ count(*) from t                            

 where id3=:"SYS_B_0 "                                               

 

 

SQL> select sql_id, child_number, executions from V$sql where sql_id='1xydsanchug00';

SQL_ID        CHILD_NUMBER EXECUTIONS

------------- ------------ ----------

1xydsanchug00            0          1

1xydsanchug00            1          1

1xydsanchug00            2          1

 

 

注意：情况有了不同，在对应不同的id3取值情况下，由于存在直方图的不同。SIMILAR为每一个id3取值生成一个单独的执行计划。当下次再次出现这个取值的时候，会共用相同取值的执行计划。

 

Id3列是一个普通列，那么对于有直方图（强令生成）的主键列和唯一索引列，效果是如何呢？

 

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for columns id2 size 5');

PL/SQL procedure successfully completed

 

SQL>  select column_name, num_distinct, NUM_BUCKETS, HISTOGRAM from dba_tab_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM

------------------------------ ------------ ----------- ---------------

ID1                                   53196           1 NONE

ID2                                   53196           5 HEIGHT BALANCED

ID3                                   53196           5 HEIGHT BALANCED

"                                                

 

SQL> select /*+ demo_id3_2 */ count(*) from t where id2=1000;

  COUNT(*)

----------

         1

 

SQL> select /*+ demo_id3_2 */ count(*) from t where id2=2000;

  COUNT(*)

----------

         1

 

SQL> select /*+ demo_id3_2 */ count(*) from t where id2=3000;

  COUNT(*)

----------

         1

 

SQL>  select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like 'select /*+ demo_id3_2 */%';

SQL_TEXT             SQL_ID        VERSION_COUNT EXECUTIONS

-------------------- ------------- ------------- ----------

select /*+ demo_id3_ 5gam31v1vf5a9             3          3

2 */ count(*) from t                            

 where id2=:"SYS_B_0                            

"                                                

 

 

唯一索引列的效果相同，主键列呢？

 

 

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for columns id1 size 5');

 

PL/SQL procedure successfully completed

 

SQL> select column_name, num_distinct, NUM_BUCKETS, HISTOGRAM from dba_tab_col_statistics where wner='SYS' and table_name='T';

 

COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM

------------------------------ ------------ ----------- ---------------

ID1                                   53196           5 HEIGHT BALANCED

ID2                                   53196           5 HEIGHT BALANCED

ID3                                   53196           5 HEIGHT BALANCED

 

 

此时使用的SQL进行查看。

 

 

SQL> select /*+ demo_id1_3 */ count(*) from t where id1=1000;

  COUNT(*)

----------

         1

 

 

SQL> select /*+ demo_id1_3 */ count(*) from t where id1=2000;

  COUNT(*)

----------

         1

 

SQL> select /*+ demo_id1_3 */ count(*) from t where id1=3000;

  COUNT(*)

----------

         1

 

SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like 'select /*+ demo_id1_3 */%';

 

SQL_TEXT             SQL_ID        VERSION_COUNT EXECUTIONS

-------------------- ------------- ------------- ----------

select /*+ demo_id1_ 3vys9sd50v2sw             3          3

3 */ count(*) from t                            

 where id1=:"SYS_B_0                            

"                                               

 

 

连主键列都被判定为“Unsafe SQL”了。

 

上面的实验，也就证明了SIMILAR工作的原理：只看列统计量是否存在直方图。当SQL条件列存在直方图的时候，并且cursor_sharing取值为SIMILAR，这样就认为该SQL是unsafe的。就为每一个值确定一个新的执行计划。

 

那么，SIMILAR效果取决于是否存在直方图。我们的问题就变成如何才能让列产生直方图。

 

 

5、列与直方图

 

我们使用dbms_stats命令收集统计量的时候，通过method_opts参数指定生成直方图的方式。在9i版本中，这个参数设置为NONE，表示默认不生成直方图。所以在那个时代，我们经常需要手工设置method_opts参数生成偏移列的直方图。

 

到了Oracle 10g，method_opts参数的默认值变成为“for all columns size auto”。简单的说，由Oracle自己去决定是不是生成直方图。

 

 

SQL> create table t as select object_id id1, object_id id2, object_id id3 from dba_objects;

Table created

 

SQL> select object_id, data_object_id from dba_objects where wner='SYS' and object_name='T';

 OBJECT_ID DATA_OBJECT_ID

---------- --------------

     56256          56256

 

SQL> select * from col_usage$ where obj#=56256;

 

      OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -----------

 

 

我们重新构建了数据表T，内部表col_usage$记录了数据表使用情况，主要是列出现的情况。如果我们在SQL中使用了列，就会对应信息出现在该内部表中。

 

此时，必然没有直方图信息。

 

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all columns size auto');

PL/SQL procedure successfully completed

 

SQL> select column_name,HISTOGRAM from dba_tab_cols where wner='SYS' and table_name='T';

COLUMN_NAME                    HISTOGRAM

------------------------------ ---------------

ID3                            NONE

ID2                            NONE

ID1                            NONE

 

无直方图被收集。Oracle首先会去看一看该列是否使用过，如果从没有使用过，就不会生成直方图。因为直方图毕竟是要消耗额外的CPU和存储资源的。

 

--使用一次数据列

SQL> select count(*) from t where id1=1000;

 

  COUNT(*)

----------

         1

 

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all columns size auto');

PL/SQL procedure successfully completed

 

SQL> select * from col_usage$ where obj#=56256;

 

      OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -----------

     56256          1              1              0                 0           0          0          0 2011-10-18

 

--存在使用信息之后，收集统计量。

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all columns size auto');

PL/SQL procedure successfully completed

 

SQL> select column_name,HISTOGRAM from dba_tab_cols where wner='SYS' and table_name='T';

 

COLUMN_NAME                    HISTOGRAM

------------------------------ ---------------

ID3                            NONE

ID2                            NONE

ID1                            NONE

 

 

我们使用了数据列id1，并且在col_usage$中包含了相应记录。但是依然没有生成直方图。是否潜在意味着描述列取值的其他统计量，如密度等因素，也在起作用？我们进行额外处理id3。

 

 

SQL> update t set id3=mod(id3,10);

50682 rows updated

 

SQL> commit;

Commit complete

 

 

对id3取余数处理，增加数据列重复值出现的概率。

 

--使用一次id3

SQL> select count(*) from t where id3=0;

  COUNT(*)

----------

      5100

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all columns size auto');

PL/SQL procedure successfully completed

 

--出现对id3列使用记录；

SQL> select * from col_usage$ where obj#=56256;

 

      OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -----------

     56256          1              1              0                 0           0          0          0 2011-10-18

     56256          3              1              0                 0           0          0          0 2011-10-18

 

 

SQL> select column_name,HISTOGRAM from dba_tab_cols where wner='SYS' and table_name='T';

 

COLUMN_NAME                    HISTOGRAM

------------------------------ ---------------

ID3                            FREQUENCY

ID2                            NONE

ID1                            NONE

 

 

直方图出现！

 

说明：Oracle 10g的method_opts参数默认取值“for all columns size auto”的是相当复杂的判定过程。首先，Oracle会看这个数据列是否使用过，其次会检查该列的一些统计量细节，如密度和重复值等，之后就会判断是否生成直方图。

 

6、结论

 

经过上下两篇的研究，我们已经对cursor_sharing=SIMILAR取值的工作原理很清楚了。

 

首先，当一个数据列不存在直方图统计信息时候，cursor_sharing=SIMILAR的效果与cursor_sharing=FORCE相同。都会发生条件列自动绑定变量替换，都会为所有的SQL使用相同的执行计划。

 

但是，当一个数据列存在直方图的时候，无论该直方图是何种方式生成，cursor_sharing=SIMILAR会认为这个SQL是unsafe的，需要对每个列取值生成单独的执行计划。所以，在cursor_sharing=SIMILAR的时候，一个显著现象就是一些SQL带有非常大量的version_count取值。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-709376/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-709376/

ciqu9915
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
系统学习Pytorch笔记三：Pytorch数据读取机制(DataLoader)与图像预处理模块(transforms)
Miracle8070
04-17 6万+
Pytorch官方英文文档：https://pytorch.org/docs/stable/torch.html? Pytorch中文文档：https://pytorch-cn.readthedocs.io/zh/latest/ 1. 写在前面 疫情在家的这段时间，想系统的学习一遍Pytorch基础知识，因为我发现虽然直接Pytorch实战上手比较快，但是关于一些内部的原理知识其实并不是太懂，这样...
C语言——函数的综合运用。自定义函数，gotoxy清屏函数与HideCursor隐藏光标，防闪屏，共同制作打飞机游戏。
冰小九的博客
09-06 5752
①在变量中，我们必须进行定义赋值初始化后，才能在程序中使用，所以需要一个“地方”，整理好这些变量，在程序中整洁一些void startup() //数据初始化②打出画面中所显示的所需代码，也给一个函数void show() //显示画面③在游戏运行中，飞机的移动，发射等操作必须使用键盘，此时就要进行输入判断，给出相应的函数，实行不同的命令 ，其中分为两种，一种是程序运行时对输入做出变化，另一种是不变化void updateWithoutInput() //与用户输入无关的更新。
自定义的sql相似性函数
wangminqi的专栏
06-18 499
Create FUNCTION xiangsi (     @str1 varchar(2500),@str2 varchar(2500) ) RETURNS int AS BEGIN declare @i int,@c int,@i1 int,@c1 int select @c=len(@str1),@c1=len(@str2),@i=1,@i1=1 declare @temp
cursor_sharing='SIMILAR'将被废弃
weixin_34318326的博客
03-19 172
根据metalink文档<ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting [ID 1169017.1]>在11g中将逐渐废弃cursor_sharing参数的SIMILAR选项，原因是在今后的版本中Exact和Force选项可以满足游标共享的需求了，使用SIMILAR选项可能引发额外的version_co...
关于cursor_sharing=similar
cri5768的博客
10-12 95
关于cursor_sharing=similar 2009-02-12 09:41 biti_rainy关于cursor_sharing=similar 我们先看看在表没有分析无统计数据情况下的表现 ...
CURSOR_SHARING=SIMILAR
cotchte0421的博客
11-08 107
CURSOR_SHARING=SIMILAR With CURSOR_SHARING=SIMILAR whenever the optimizer looks at a replaced bind value to mak...
游标共享之cursor_sharing=similar
关注系统性能调优
11-05 1936
cursor_sharing=similar   ，exact和force折中的参数。当探测到谓词会导致执行计划的改变，就会重新解析，否则不会。       适用场景：在无法将应用的代码修改为绑定变量情况下，oracle提供的又一种解决方法，similar的好处是最大限度地避免CBO在绑定变量的情况下做出的错误的判断，但它的代价是分析的次数变大。        参数还有两种选择：
oracle cursor_sharing 参数设置测试
木子的博客
08-06 2491
题目：Oracle的cursor_sharing参数告诉Oracle数据库何时重用SQL语句，有Exact、force和similar三种设置，请阐述三种设置的区别，并通过一个例子距离说明三者之间的区别。最后请设计一个实际应用场景，执行一系列操作，说明这个参数设置的意义（提示，可以设计一个循环操作语句来验证），分别说明在什么情况下用何种参数设置。     答： Cursor_sharing
CONSOLE_CURSOR_INFO类型详解
qq_42591783的博客
12-02 5351
光标信息的结构体类型 typedef struct _CONSOLE_CURSOR_INFO { DWORD dwSize; BOOL bVisible; } CONSOLE_CURSOR_INFO, *PCONSOLE_CURSOR_INFO; 成员类型 其中typedef unsigned long DWORD; typedef int BOOL; 成员代表的含义...
halcon画出灰度直方图_halcon读取一张照片，并转化为灰度图像
weixin_34391934的博客
01-14 4342
dev_close_window () read_image (Image, 'E:/图片/123.jpg') get_image_size (Image, Width, Height) dev_open_window (, , Width, Height, 'black', WindowHandle) rgb1_to_gray(Image,GrayImage) dev_display (Gray...
关于cursor_sharing = similar
《CSDN社区电子杂志——Oracle杂志》官方blog
02-01 8686
本文作者: biti_rainy (biti_rainy@itpub.net) 摘要：本文通过简单实验来尝试说明cursor_sharing=similar的含义。1.1.        实验现象我们先看看在表没有分析无统计数据情况下的表现  SQL> alter session set cursor_sharing = similar;   Session alter
关于 cursor_sharing = similar
热门推荐
biti_rainy的专栏
07-12 1万+
我们先看看在表没有分析无统计数据情况下的表现  SQL> alter session set cursor_sharing = similar;   Session altered. SQL> select name,value from v$sysstat where name like %parse%;   NAME                                   
CURSOR_SHARING=SIMILAR 时用Expdp导出数据变的bug已修复
jolly10的专栏
07-31 251
通过对CURSOR_SHARING三个参数的了解，将此参数设为similar还是比较可靠的，前提是一定要定期收集统计信息及直方图。 不过今天偶然看到用similar的一个问题，会导致expdp导出变慢，原文如下： http://www.eygle.com/archives/2011/01/cursor_sharing_expdp.html   通过查询这个是一个bug，oracle已在10...
cursor_sharing=similar深度剖析
03-24 314
在metalink ID 1169017.1文档中，提到建议客户不设置cursor_sharing= similar，因为存在许多性能问题，该参数将在12g中废除该参数，具体给出的理由如下：1、对于语句中包含...
cursor_sharing = similar , exact 区别
cuixie2370的博客
09-03 115
cursor_sharing=similar exact 的区别: 若存在object_id的 histograms ,则每次是不同的值的时候都产生硬解析 ,若不存在 histograms，则不产...
【cursor_sharing】cursor_sharing参数设置exact,similar,force的区别
12-25 1717
Oracle中为了提高sql的执行效率，需要减少硬解析，实现shared cursor共享，最常见的方法是使用绑定变量，但很多时候由于各种原因未能在开发初期使用绑定变量，对于减少硬解析的目的，退而求其次地方法是设置cursor_sharing.
SQL的两个字符串的相似度的计算函数
啊哈的前进
10-06 1万+
不知道你的相似度判断的规则是什么，通过数据库实现的话，可以考虑定义一个相似度比较函数，然后在update时调用并判断，下面是MSSQL的思路，不过需要把相似度判断的函数写完整。 其他数据库应该也可以用相同思路来做吧。 FUNCTION fn_GetSimilar ( @strA As Varchar(255), --传入的待比较的字符 @StrB As Varchar(255 --期望字
Postgresql学习笔记之——模糊匹配LIKE、SIMILAR TO和POSIX正则表达式
qq_32838955的博客
04-12 5868
1、介绍 Postgresql数据库中提供三种实现模糊匹配的方式： 1.传统的SQL的LIKE操作。 2.SQL99中SIMILAR TO操作符。 3.POSIX风格的正则表达式。 另外还有一个模式匹配函数 substring 也可供使用。 2、LIKE操作符 传统的LIKE操作符比较简单，其中百分号 **“%”代表了0个或任意个字符，而下划线“_”**代表任意一个字符： postgres=# c...
Value* Next() { if (cursor_ == NULL || ((Node*)(cursor_->pValue))->used_flag_ == 0) { return NULL; } DOUBLE_NODE* tmp_node = DoubleLinkGetNext(&double_link_, cursor_); if (tmp_node == NULL) { return NULL; } cursor_ = tmp_node; return ((Node*)(tmp_node->pValue))->value_; }什么意思
最新发布
06-09
该函数的作用是获取当前节点的下一个节点的值，并将当前节点设置为下一个节点。 具体而言，该函数首先判断当前节点是否为空，或者该节点对应的值是否被标记为未使用（即used_flag_等于0），如果是，则说明当前节点...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值