Adaptive Cursor Sharing(第二篇)

选择率和硬解析

我们上面提到了,在v$sql_cs_histogram视图中,如果此游标的3个桶中出现了两个桶中的count都有非0值,那么此后的解析都要窥探绑定变量的值计算谓词选择率,如果计算选择率不在现有的游标的选择率范围内,就会基于窥探到的绑定变量的值重新硬解析产生一个新的游标,当然这个新游标的执行计划可能与之前是一样的。我们还是来看一个例子就会非常明白这种机制了。

SQL>create table t as select 1 id,a.* from dba_objects a,dba_objects b where rownum<10;

 

Table created.

 

SQL>create index t_ind on t(id);

 

Index created.

 

SQL>insert into t select 2,a.* from dba_objects a,dba_objects b where rownum<1000;

 

999 rows created.

 

SQL>insert into t select 3 ,a.* from dba_objects a,dba_objects b where rownum<10000;

 

9999 rows created.

 

SQL>insert into t select 4 ,a.* from dba_objects a,dba_objects b where rownum<100000;

 

99999 rows created.

 

SQL>insert into t select 5 ,a.* from dba_objects a,dba_objects b where rownum<1000000;

 

999999 rows created.

 

SQL>commit;

 

Commit complete.

SQL>begin

  2    dbms_stats.gather_table_stats(user,

  3                                  't',

  4                                  method_opt => 'for columns status size 5',

  5                                   cascade    => true);

  6    

  7  end;

  8  /

 

SQL>select id,count(*) from t group by id order by id;

 

        ID   COUNT(*)

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

         1          9

         2        999

         3       9999

         4      99999

         5     999999

上面的代码精心构造了一个例子,表t上的id字段一共有5个唯一值,每个值的数量都不一样,id字段上有索引,分析了直方图。在这种情况下,如果我们直接使用字符变量不使用绑定变量的话,id在对1,2,3,4做查询的时候,都会使用索引扫描,这种情况下,索引扫描的成本要比全表扫描的成本低,id在对5做查询时,会使用全表扫描,这种情况下全表扫描的成本要比索引扫描成本低。如下表格,我是通过explain工具,使用文本变量后,得出的每个执行计划的cost,可以看到全表扫描的cost为2911,在查询id<5的情况下,由于索引扫描的cost都小于全表扫描的cost因此执行计划都选择了走索引扫描,只有在查询id等于5的时,才选择了走全表扫描。

ID

执行计划

COST

选择率

1

索引扫描

4

0.0000081

2

索引扫描

16

0.000899186

3

索引扫描

139

0.008999959

4

索引扫描

1370

0.090007696

5

索引扫描

13690

0. 900085058

5

全表扫描

2911

0. 900085058

上面的表格最后一列提供了谓词的选择率,此处选择率的计算公式为:

选择率=id=?的值在表中的数量/总数量

根据上面表格的cost我们可以知道,谓词的选择率在0.0000081到0.090007696之间都应该选择索引扫描,在0. 900085058的时候应该选择全表扫描,因为id在5的时候,索引扫描的成本13690已经远远大于了全表扫描的成本2911。我们看看下面的例子:

SQL>var a number;

SQL>exec :a :=1;

 

PL/SQL procedure successfully completed.

 

SQL>select count(object_id) from t where id=:a;

 

COUNT(OBJECT_ID)

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

               9

 

SQL>exec :a :=5;

 

PL/SQL procedure successfully completed.

 

SQL>select count(object_id) from t where id=:a;

 

COUNT(OBJECT_ID)

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

          999999

 

SQL>select count(object_id) from t where id=:a;

 

COUNT(OBJECT_ID)

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

          999999

 

SQL>col PREDICATE for a10

SQL>-- 选择率

SQL>SELECT   hash_value, sql_id, child_number, predicate, range_id, low, high

  2      FROM v$sql_cs_selectivity

  3     WHERE sql_id='56g5zg95hcxc1'

ORDER BY sql_id, child_number;  4  

 

SQL_ID             CHILD_NUMBER PREDICATE    RANGE_ID LOW                  HIGH

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

56g5zg95hcxc1                 1 =A                  0 0.810076             0.990093

经过上面的一系列的操作后我们已经让这个cursor变得bind aware,如何让SQL变得bind aware我们上面已经论述过,这里不再做详细说明。经过这些步骤后,优化器已经产生出了一个child_number为1的新游标,这个游标基于绑定变量为5的值生成,谓词的选择率范围是:0.810076到0.990093。这个选择率跟我们上面表格里提供的选择率的关系是:(0.810076+0.990093)/2约等于我们上面表格里提供的选择率0. 900085058,Oracle为选择率稍微的预留了一些余地,这样很好。我们再执行id为1的查询看看:

SQL>exec :a :=1;

 

PL/SQL procedure successfully completed.

 

SQL>select count(object_id) from t where id=:a;

 

COUNT(OBJECT_ID)

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

               9

 

SQL>-- 选择率

SQL>SELECT   hash_value, sql_id, child_number, predicate, range_id, low, high

  2      FROM v$sql_cs_selectivity

  3     WHERE sql_id='56g5zg95hcxc1'

ORDER BY sql_id, child_number;   

 

SQL_ID         CHILD_NUMBER PREDICATE    RANGE_ID LOW                  HIGH

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

56g5zg95hcxc1             1 =A                  0 0.810076             0.990093

56g5zg95hcxc1             2 =A                  0 0.000007             0.000009

已经产生了child_number为2的子游标,是基于id为1的值产生的,选择率范围为:0.000007到0.000009。下面就到了本节关键的时刻了,我们再次查询id为4看看会出现什么情况。

SQL>exec :a :=4

 

PL/SQL procedure successfully completed.

 

SQL>select count(object_id) from t where id=:a;

 

COUNT(OBJECT_ID)

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

           99999

 

SQL>

SQL>col PREDICATE for a10

SQL>-- 选择率

SQL>SELECT   hash_value, sql_id, child_number, predicate, range_id, low, high

  2      FROM v$sql_cs_selectivity

  3     WHERE sql_id='56g5zg95hcxc1'

  4  ORDER BY sql_id, child_number;

 

SQL_ID          CHILD_NUMBER PREDICATE    RANGE_ID LOW                  HIGH

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

56g5zg95hcxc1              1 =A                  0 0.810076             0.990093

56g5zg95hcxc1              2 =A                  0 0.000007             0.000009

56g5zg95hcxc1              3 =A                  0 0.000007             0.099008

 

SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive,  

  2        is_bind_aware,IS_SHAREABLE                                     

  3   FROM v$sql                                                          

  4  WHERE sql_id='56g5zg95hcxc1';                                        

 

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS IS

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

           0          2       13690 Y  N  N

           1          1       13162 Y  Y  Y

           2          1           4 Y  Y  N

           3          1        1495 Y  Y  Y

优化器已经重新生成了一个child_numer为3的子游标,同时选择率的范围已经扩大了,从0.000007到0.099008,也就是现在从id为1到4都被包含在child_number为3的子游标里了。child_number为2的子游标已经被标记为不能共享失效了,如果共享池有紧缺这块内存就可以被清除出去。那是不是意味着我们查询id为3的值时,将不用重新产生新游标,直接可以使用child_number为3的子游标了。我们来看看:

SQL>exec :a :=3

 

PL/SQL procedure successfully completed.

 

SQL>col PREDICATE for a10

SQL>-- 选择率

SQL>SELECT   hash_value, sql_id, child_number, predicate, range_id, low, high

  2      FROM v$sql_cs_selectivity

  3     WHERE sql_id='56g5zg95hcxc1'

  4  ORDER BY sql_id, child_number;

 

SQL_ID          CHILD_NUMBER PREDICATE    RANGE_ID LOW                  HIGH

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

56g5zg95hcxc1              1 =A                  0 0.810076             0.990093

56g5zg95hcxc1              2 =A                  0 0.000007             0.000009

56g5zg95hcxc1              3 =A                  0 0.000007             0.099008

 

SQL>select count(object_id) from t where id=:a;

 

COUNT(OBJECT_ID)

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

            9999

 

SQL>-- 选择率

SQL>SELECT   hash_value, sql_id, child_number, predicate, range_id, low, high

  2      FROM v$sql_cs_selectivity

  3     WHERE sql_id='56g5zg95hcxc1'

  4  ORDER BY sql_id, child_number;

 

SQL_ID         CHILD_NUMBER PREDICATE    RANGE_ID LOW                  HIGH

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

56g5zg95hcxc1             1 =A                  0 0.810076             0.990093

56g5zg95hcxc1             2 =A                  0 0.000007             0.000009

56g5zg95hcxc1             3 =A                  0 0.000007             0.099008

 

SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive,  

  2        is_bind_aware,IS_SHAREABLE                                     

  3   FROM v$sql                                                          

  4  WHERE sql_id='56g5zg95hcxc1';                                        

 

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS IS

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

           0          2       13690 Y  N  N

           1          1       13162 Y  Y  Y

           2          1           4 Y  Y  N

           3          2        1495 Y  Y  Y

没有再生成新的子游标了,同时v$sql中的child_number为3的子游标的执行次数已经加1了。

从上面的示例我们可以知道,在v$sql_cs_histogram视图中,如果此游标的3个桶中出现了两个桶中的count都有非0值,那么此后的解析都要窥探绑定变量的值计算谓词选择率,如果计算选择率不在现有的游标的选择率范围内,就会基于窥探到的绑定变量的值重新硬解析产生一个新的游标,记录此游标的可以代表的选择率范围,当然就像我们例子看到的,新游标的执行计划可能跟之前是一样的,只不过是选择率的范围更广了。

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

转载于:http://blog.itpub.net/22034023/viewspace-2153541/

oracle11g中SQL优化(SQL TUNING)新特性之Adaptive Cursor Sharing (ACS) oracle11g中最重要的特点之一——Adaptive Cursor Sharing (ACS),几乎完美的解决了之前版本不绑定不行,绑定也不行的尬尴问题,今天有机会对此特点再次进行研究和查阅资料,并将主要内容总结成文,记录于此,以便今后学习使用,也希望对各位朋友有所帮助。 阅读详情

相关推荐

PDF与OFD电子发票解析技术实战:从格式转换到精准识别

本文深入探讨了PDF与OFD电子发票解析技术的实战应用,从格式转换到精准识别,提供了全面的解决方案。通过OFD转PDF技术、动态定位算法和多重校验机制,显著提升电子发票识别效率与准确率,助力企业财务数字化转型。

weixin_42565652的博客 167

告别环境冲突!手把手教你用Anaconda为不同AI项目创建独立Python虚拟环境(Windows版)

本文详细介绍了如何使用Anaconda在Windows系统上为不同AI项目创建独立的Python虚拟环境,解决环境依赖冲突问题。通过图形化和命令行两种方式,帮助开发者高效管理多版本PyTorch等框架,并提供了VSCode环境配置技巧和高级管理策略,确保项目开发环境隔离且稳定。

cotchte0421的博客 524

Jetson设备EXT4文件系统修复实战:从superblock损坏到系统恢复

本文详细介绍了Jetson设备因异常断电等原因导致EXT4文件系统superblock损坏后的诊断与修复实战。通过从健康设备移植fsck工具、使用备份超级块等步骤,指导用户逐步恢复系统,并提供了数据备份与预防措施,帮助用户有效应对嵌入式AI开发中的存储故障。

theta的博客 595

【Oracle 优化器】自适应游标共享(Adaptive Cursor Sharing)功能

本文主要介绍Oracle优化器的自适应游标共享(Adaptive Cursor Sharing)功能和处理流程。

SQLplusDB的小院 9266

Windows电脑如何用MobaXterm连接宇树G1机器人?保姆级SSH配置指南

本文提供了一份详细的保姆级教程,指导Windows用户使用MobaXterm通过SSH连接宇树G1机器人。内容涵盖从物理网络配置、MobaXterm安装与SSH会话创建,到文件传输、X11转发等高级功能,并包含常见问题排查与安全加固建议,帮助开发者快速建立稳定可靠的远程开发连接。

purple的博客 878

Vatti clipping 算法:从概念到实现的图形裁剪核心

本文深入解析Vatti clipping算法在图形处理中的核心应用,从基础概念到高效实现,涵盖多边形裁剪、合并、差集和交集等布尔运算。通过实战案例和优化技巧,展示该算法在处理自相交多边形、带孔洞图形等复杂场景中的卓越性能,适合CAD、路径规划等工业级应用。

weixin_30621959的博客 696

自适应游标共享(Adaptive Cursor Sharing)功能

因此,优化器在11.1以后的版本上,为了解决绑定变量窥视(Bind Peeking)的问题,推出了自适应游标共享(Adaptive Cursor Sharing 以后简称ACS)功能,使包含绑定变量的同一条SQL语句在多次执行时,不会盲目的共享执行计划,而会根据绑定变量值和执行过程中收集信息的反馈,可以使用多个不同执行计划,避免性能问题。当SQL文第一次被执行的时候,因为绑定变量窥视(Bind Peeking)功能的影响,所以硬解析选择执行计划时,会把绑定变量值3代入到SQL文中计算选择率。...

jnrjian的博客 649

CentOS 7.9安装python3.11

CentOS 7.9安装python3.11

leafseelight的专栏 1343

Oracle自适应游标共享(Adaptive Cursor Sharing)

1、自适应游标说明     在Oracle 10g 和11g中对绑定变量的处理,已经有所不同, 在Oracle 10g中,绑定变量相对比较简单,当使用绑定变量的SQL 第一次执行时,会进行硬解析,生成plan 和cursor。 在这个过程中,Oracle 会使用bind peeking,即将绑定变量的值带入,从而选择最优的一个plan。以后每次执行都使用这个plan。     在以后的执行时,如...

oraly的博客 3111

[HBM] RAM Cell Self Repair | 内存单元自我修复

对抗错误的大都是冗余,内存颗粒在设计的时候也考虑增加冗余。具体的说:添加冗余或备用的存储单元行和列,及时将测试中的故障进行修复和替换,存储器修复包括行修复,列修复或两者的组合。

串起点滴 786

【Docker安装】Ubuntu 24.04.2 LTS系统下安装Docker环境——指定APT源安装方式

【Docker安装】Ubuntu 24.04.2 LTS系统下安装Docker环境——指定APT源安装方式

jks212454的博客 628

Oracle 11g 新特性 -- 自适应游标共享(Adaptive Cursor Sharing: ACS) 说明

一.自适应游标共享(Adaptive Cursor Sharing) 说明1.1 ACS概述绑定变量使Oracle DB 可以为多条SQL 语句共享单个游标,以减少分析SQL 语句所使用的共享内存量。然而,游标共享和SQL 优化是两个相互冲突的目标。用文字编写SQL 语句为优化程序提供了更多的信息,这无疑会导致更好的执行计划,但大量的硬分析会导致内存和CPU 开销增加。Oracle9i...

weixin_30802171的博客 343

【ubuntu24.04】auto remove后,重启,kernel panic ,please reboot your computer vfs unable to mount root fs

Ubuntu 24.04系统在运行auto remove后出现内核恐慌(kernel panic)无法启动,显示"VFS: unable to mount root fs"错误。该问题是由于当前内核被清除导致系统无法挂载根分区。解决步骤包括:1)尝试用GRUB菜单中的旧内核启动;2)在GRUB中临时编辑启动参数;3)使用Ubuntu启动盘进行chroot修复;4)使用Boot-Repair工具一键修复。成功启动后,需要重新安装linux-generic等元包,重建initramfs并更新

突围 1218

Oracle Adaptive Cursor Sharing(ACS)

Adaptive Cursor Sharing(ACS) 解决了绑定变量窥探问题 开启自适应游标共享后测试绑定变量窥探: 确保开启自适应共享游标相关参数: alter system set "_optim_peek_user_binds"=true scope=spfile; alter system set "_optimizer_adaptive_cursor_sharing"=TRUE scope=spfile; alter system set "_optimizer_extended_c

dbhang的博客 542

锂电池充电器电路设计 里面有电路

锂电池充电器电路设计 里面有电路 3.7V升压到5V电路设计。

VB摄像头监控

该VB模块可对摄像头捕获的画面进行录像等,可在创建的窗口进行控制

Adaptive Cursor Sharing(ACS)的研究

我们先来看看跟ACS相关的三个隐藏参数,是用来控制是否启用ACS col ksppinm for a30 col ksppstvl for a20 col ksppdesc for a35 SELECT ksppinm, ksppstvl, ksppdesc FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND kspp

kiwi的专栏 841

IEM模型,地表粗糙度模拟

利用微波反演土壤水分时候,利用IEM模型可以实现对随机地表的模拟

Postgresql的CURSOR SHARING

在Oracle 9iR2之前,所有执行计划都是在变量绑定之前完成的,从9.2开始,Oracle将执行计划的生成放到了变量绑定之后,这样就让执行计划的生成更为精准了,不过这也带来了另外一个问题,那就是SQL第一次执行时的变量成为生成执行计划的依据,因此CURSOR共享会导致存在多种最优执行计划的SQL语句的运行性能变得不稳定。在PostgreSQL的一个会话中,一条SQL的前五次执行,每次都会重新生成执行计划,这样就可以避免因为绑定变量的差异导致存在多种最优执行计划的问题无法被发现的问题出现。

DBAIOps的博客 662

ET2012新版免狗免安装绿色软件

ET2012新版免狗免安祖昂绿色软件。,新界面,新视觉,

探秘oracle11g acs (adaptiver cursor sharing)

一、什么是ACS(adaptiver cursor sharing) Oracle通过绑定变量技术解决了SQL语句硬解析过多的问题,降低了资源的争用。但是绑定变量在引入cursor sharing,增加了软解析的同时,也带来了CBO环境下的bind peeking问题。 所谓bind peeking是指,oracle在第一次解析sql的时候,会“偷偷”地(peek)查看一下输入的绑定变量值

Vincent的专栏 1548

Adaptive Cursor Sharing: Overview (文档 ID 740052.1)

In this Document   Purpose   Scope   Details   Introduction   Advantages and Disadvantages of Adaptive Cursor Sharing  

mybluetiankong的专栏 858

V$SQL_游标、adaptive cursor sharing、bind peeking、直方图的概念

这里讨论的游标主要是共享游标(shared cursor),跟pl/sql语句中定义的游标(session cursor)不是一个概念。 共享游标:是用户提交SQL或PL/SQL程序块到O...

cjh81378066的博客 219

[20120327]Adaptive Cursor Sharing 的问题

[20120327]Adaptive Cursor SharingG 的问题11G的新特性里面Adaptive Cursor Sharing采用新特性来解决PEEKED BIND的问题,但是必须要经过一次执行后,来改变执行计划。...

168

验证11g的adaptive cursor sharing功能

当oracle开启绑定变量窥视功能,会在sql硬解析时窥探变量的实际值以求生成更为精确的执行计划,但是若该列数据分布极度不均,则可能适得其反,11g引入了adaptive cursor sharing. 查询优化器会不断做出调...

congdiku5262的博客 179

Introduction to Adaptive Cursor Sharing Concepts and Multimedia Demo [Video] (文档 ID 1115994.1)

In this Document   Goal   Fix This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to

mybluetiankong的专栏 584

Adaptive Cursor Sharing(ACS)

Adaptive Cursor Sharing(ACS)是又一个大胆而吸引人的11G新特性。 说它大胆是因为它试图解决一个CBO最令人头疼的问题:数据倾斜(data skew)和绑定变量窥视导致SQL PLAN太差。说它吸引人是因为想知道Oracle采用何种神秘的算法让Oracle变得更加智能。     之所以在11GR2出来之后才开始研究,是因为这个new feture在11GR1时

luckyman100的专栏 965

How to check and disable Adaptive Cursor Sharing in 11g

_optimizer_adaptive_cursor_sharing=false disables the feature. There are 2 new columns in V$sql , IS_BIND_SENSITIVE and IS_BIND_AWARE that indicate the status for individual cursors. 1.) The paramet

zxx2403的专栏 1227
上一篇: 性能神化,聊聊Exadata 的“七宗罪”
下一篇: Adaptive Cursor Sharing(第四篇)
cotchte0421
博客等级 码龄12年 5粉丝 110原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符  | 博主筛选后可见
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明:

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

余额充值