Oracle 变量绑定与变量窥视合集系列一

最新推荐文章于 2024-09-19 21:31:42 发布
最新推荐文章于 2024-09-19 21:31:42 发布
阅读量77 收藏 1
点赞数
文章标签： 系统架构 数据库
原文链接：https://yq.aliyun.com/articles/421476
版权

《Oracle 变量绑定与变量窥视合集》

数据库环境

LEO1@LEO1> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production


 

一 用示例说明绑定变量的应用领域是OLTP而不是OLAP

变量绑定：这是一个老生常谈的话题，我所理解的绑定就是执行计划的绑定，我所理解的变量就是谓词替换的变量。

变量绑定机制：要说机制不得不说一下SQL执行的过程，三部曲：解析 –> 执行 -> 取操作，而绑定变量就发生在解析这一步，而解析又分成硬解析和软解析。

硬解析：当一条SQL语句第一次执行时，首先生成执行计划，并把这个执行计划存放到shared_pool的library cache中，这个过程叫做硬解析。

软解析：如果SQL语句已经被硬解析过了，那么可以直接从library cache中抽取现成的执行计划来重用，这个过程叫做软解析，目的减少生成执行计划这方面的资源消耗。为什么这么说呢，硬解析会消耗一些系统资源，尤其是CPU的资源，从而影响系统的效率，如果能把这方面的影响消除，那么对系统当然是多多益善了，哈 多侃了几句。

SQL详细执行过程：当oracle接收到一条sql语句时，首先会把这条sql语句字符做成哈希值，然后到library cache中寻找是否有和这个哈希值相匹配的sql存在，如果有就直接使用这个sql的执行计划去执行当前的sql语句，最后将结果返回给用户。如果没有找到相同的哈希值，oracle会认为这是一条新的sql，将会重新生成执行计划来执行（在这个过程中先要检查语法分析和语义分析），最后将结果返回给用户。

实验

下面我们演示一下绑定变量和非绑定变量在资源消耗上的差异

LEO1@LEO1> drop table leo1 purge;                清理环境

Table dropped.

LEO1@LEO1> drop table leo2 purge;               

Table dropped.

LEO1@LEO1> create table leo1 as select * from dba_objects;    创建leo1

Table created.

LEO1@LEO1> create table leo2 as select * from dba_objects;    创建leo2

Table created.

LEO1@LEO1> alter session set tracefile_identifier='bind_variable';    设置trace文件标识

Session altered.

LEO1@LEO1> alter session set sql_trace=true;      启动trace功能，追踪sql资源消耗情况

Session altered.

LEO1@LEO1> begin

for i in 1..100 loop

execute immediate 'select * from leo1 where object_id=:i' using i;

end loop;

end;

/

PL/SQL procedure successfully completed.

我们对一条sql执行了100次并采用了绑定变量技术，oracle对这条sql只有一次硬解析，没有软解析，反复执行100次。

LEO1@LEO1> alter session set sql_trace=false;           关闭trace功能

Session altered.

LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select * from leo1 where %';

SQL_TEXT                             PARSE_CALLS  LOADS   EXECUTIONS

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

select * from leo1 where object_id=:i         1          1        100

SQL_TEXT：我们跟踪的sql语句

PARSE_CALLS：硬解析+软解析次数       1次   只有硬解析没有软解析

LOADS：硬解析次数                    1次

EXECUTIONS：执行次数                 100次

虽说值隐藏在变量中，但在解析环节oracle认为是一样的

[oracle@leonarding1 trace]$ tkprof LEO1_ora_16433_bind_variable.trc bind_variable.txt sys=no

TKPROF: Release 11.2.0.1.0 - Development on Fri Feb 1 13:18:08 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

使用tkprof工具过滤和汇总trace文件的，sys=no 不输出sys用户递归语句，默认yes，实际上设置成no更具有可读性

--------- 下面是bind_variable.txt文件信息

********************************************************************************

SQL ID: 0b74y9utb0b6r             #这就是SQL语句字符的哈希值

Plan Hash: 2716644435

select *

from

leo1 where object_id=:i

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute    100      0.01       0.01          0          1          0           0

Fetch        0      0.00       0.00          0          0          0           0

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

total      101      0.01       0.01          0          1          0           0

Misses in library cache during parse: 1        只有1次硬解析，反复执行100次

Optimizer mode: ALL_ROWS

Parsing user id: 85     (recursive depth: 1)

Rows     Row Source Operation

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

      0  TABLE ACCESS FULL LEO1 (cr=0 pr=0 pw=0 time=0 us cost=288 size=2484 card=12)

********************************************************************************

下面是一个非绑定变量的sql执行情况

LEO1@LEO1> alter session set sql_trace=true;              启动trace功能

Session altered.

LEO1@LEO1> begin

for i in 1..100 loop

execute immediate 'select * from leo2 where object_id='||i;

end loop;

end;

/

PL/SQL procedure successfully completed.

我们对一条sql执行了100次没有采用绑定变量技术，oracle对这条sql要硬解析100次，执行100次，资源严重被sql解析所消耗，系统显得缓慢不堪。

LEO1@LEO1> alter session set sql_trace=false;             关闭trace功能

Session altered.

LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select * from leo2 where %' order by 1;

SQL_TEXT                                           PARSE_CALLS      LOADS EXECUTIONS

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

select * from leo2 where object_id=1                         1          1          1

select * from leo2 where object_id=10                        1          1          1

select * from leo2 where object_id=100                       1          1          1

select * from leo2 where object_id=11                        1          1          1

select * from leo2 where object_id=12                        1          1          1

select * from leo2 where object_id=13                        1          1          1

select * from leo2 where object_id=14                        1          1          1

select * from leo2 where object_id=15                        1          1          1

select * from leo2 where object_id=16                        1          1          1

select * from leo2 where object_id=17                        1          1          1

select * from leo2 where object_id=18                        1          1          1

select * from leo2 where object_id=19                        1          1          1

select * from leo2 where object_id=2                         1          1          1

select * from leo2 where object_id=20                        1          1          1

select * from leo2 where object_id=21                        1          1          1

select * from leo2 where object_id=22                        1          1          1

select * from leo2 where object_id=23                        1          1          1

select * from leo2 where object_id=24                        1          1          1

select * from leo2 where object_id=25                        1          1          1

select * from leo2 where object_id=26                        1          1          1

select * from leo2 where object_id=27                        1          1          1

select * from leo2 where object_id=28                        1          1          1

select * from leo2 where object_id=29                        1          1          1

select * from leo2 where object_id=3                         1          1          1

select * from leo2 where object_id=30                        1          1          1

select * from leo2 where object_id=31                        1          1          1

select * from leo2 where object_id=32                        1          1          1

select * from leo2 where object_id=33                        1          1          1

select * from leo2 where object_id=34                        1          1          1

select * from leo2 where object_id=35                        1          1          1

select * from leo2 where object_id=36                        1          1          1

select * from leo2 where object_id=37                        1          1          1

select * from leo2 where object_id=38                        1          1          1

select * from leo2 where object_id=39                        1          1          1

select * from leo2 where object_id=4                         1          1          1

select * from leo2 where object_id=40                        1          1          1

select * from leo2 where object_id=41                        1          1          1

select * from leo2 where object_id=42                        1          1          1

select * from leo2 where object_id=43                        1          1          1

select * from leo2 where object_id=44                        1          1          1

select * from leo2 where object_id=45                        1          1          1

select * from leo2 where object_id=46                        1          1          1

select * from leo2 where object_id=47                        1          1          1

select * from leo2 where object_id=48                        1          1          1

select * from leo2 where object_id=49                        1          1          1

select * from leo2 where object_id=5                         1          1          1

select * from leo2 where object_id=50                        1          1          1

select * from leo2 where object_id=51                        1          1          1

select * from leo2 where object_id=52                        1          1          1

select * from leo2 where object_id=53                        1          1          1

select * from leo2 where object_id=54                        1          1          1

select * from leo2 where object_id=55                        1          1          1

select * from leo2 where object_id=56                        1          1          1

select * from leo2 where object_id=57                        1          1          1

select * from leo2 where object_id=58                        1          1          1

select * from leo2 where object_id=59                        1          1          1

select * from leo2 where object_id=6                         1          1          1

select * from leo2 where object_id=60                        1          1          1

select * from leo2 where object_id=61                        1          1          1

select * from leo2 where object_id=62                        1          1          1

select * from leo2 where object_id=63                        1          1          1

select * from leo2 where object_id=64                        1          1          1

select * from leo2 where object_id=65                        1          1          1

select * from leo2 where object_id=66                        1          1          1

select * from leo2 where object_id=67                        1          1          1

select * from leo2 where object_id=68                        1          1          1

select * from leo2 where object_id=69                        1          1          1

select * from leo2 where object_id=7                         1          1          1

select * from leo2 where object_id=70                        1          1          1

select * from leo2 where object_id=71                        1          1          1

select * from leo2 where object_id=72                        1          1          1

select * from leo2 where object_id=73                        1          1          1

select * from leo2 where object_id=74                        1          1          1

select * from leo2 where object_id=75                        1          1          1

select * from leo2 where object_id=76                        1          1          1

select * from leo2 where object_id=77                        1          1          1

select * from leo2 where object_id=78                        1          1          1

select * from leo2 where object_id=79                        1          1          1

select * from leo2 where object_id=8                         1          1          1

select * from leo2 where object_id=80                        1          1          1

select * from leo2 where object_id=81                        1          1          1

select * from leo2 where object_id=82                        1          1          1

select * from leo2 where object_id=83                        1          1          1

select * from leo2 where object_id=84                        1          1          1

select * from leo2 where object_id=85                        1          1          1

select * from leo2 where object_id=86                        1          1          1

select * from leo2 where object_id=87                        1          1          1

select * from leo2 where object_id=88                        1          1          1

select * from leo2 where object_id=89                        1          1          1

select * from leo2 where object_id=9                         1          1          1

select * from leo2 where object_id=90                        1          1          1

select * from leo2 where object_id=91                        1          1          1

select * from leo2 where object_id=92                        1          1          1

select * from leo2 where object_id=93                        1          1          1

select * from leo2 where object_id=94                        1          1          1

select * from leo2 where object_id=95                        1          1          1

select * from leo2 where object_id=96                        1          1          1

select * from leo2 where object_id=97                        1          1          1

select * from leo2 where object_id=98                        1          1          1

select * from leo2 where object_id=99                        1          1          1

100 rows selected.

我们从动态性能视图上可以看出oracle每执行一次sql，都要先硬解析1次之后在执行。这种没有使用绑定变量技术在硬解析消耗上就比使用绑定变量技术多损耗100倍，如果执行的次数上万 上亿对系统性能的影响可想而知。

--------- 我们来看看trace文件的内容

[oracle@leonarding1 trace]$ tkprof LEO1_ora_16433_bind_variable.trc bind_variable.txt sys=no

TKPROF: Release 11.2.0.1.0 - Development on Fri Feb 1 13:49:52 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

********************************************************************************

SQL ID: 22r47f3t6w0td

Plan Hash: 2258638698

select *

from

leo2 where object_id=1

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          1          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        0      0.00       0.00          0          0          0           0

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

total        2      0.00       0.00          0          1          0           0

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 85     (recursive depth: 1)

Rows     Row Source Operation

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

      0  TABLE ACCESS FULL LEO2 (cr=0 pr=0 pw=0 time=0 us cost=288 size=2484 card=12)

********************************************************************************

SQL ID: 9nb3n54fy0z8m

Plan Hash: 2258638698

select *

from

leo2 where object_id=2

********************************************************************************

SQL ID: 8mc705qymd7qs

Plan Hash: 2258638698

select *

from

leo2 where object_id=3

如上所示每个sql语句的SQL_ID都是不一样的，都是相对独立的，因此每执行1次就要解析1次，两种情况对比结果显示，绑定变量要比没有绑定变量消耗的资源少的少，sql执行的次数越多，这种效果越明显。所以我们说绑定变量本质就是用一个变量来代替谓词常量，让oracle只需要硬解析一次，后续sql都直接使用之前执行计划来执行，这样就省却了很消耗资源的硬解析过程

下面讨论绑定变量为什么适合于OLTP而不是OLAP

OLTP

1.适合OLTP系统架构

2.SQL简单非常相似，结果集非常小，例如 只有谓词部分不同，余下部分全部相同的SQL语句，这种情况下执行计划都是一样的，在执行计划几乎不变的情况下，oracle使用变量来代替谓词常量，使用同一个执行计划是非常合理的

3.SQL重复率很高，或者只有谓词条件不同而已

4.DML操作频繁

5.SQL语句执行条数多，条数越多减少硬解析越有意义

6.基于主键做查询，还有等值查询，唯一性查询，这类查询相对适合绑定变量

select   *   from  leonarding   where   id=:leo;

 

OLAP

1.不适合OLAP系统架构

2.SQL的执行计划多变，会因为值的不同导致执行计划的不同，可能第一次执行是一种执行计划，第二次执行是另一种执行计划，所以不适合进行绑定变量操作，会让oracle盲目浪费大量资源消耗，SQL语句即使只有谓词条件不同，oracle应然可能采取不同的执行计划。

3.SQL重复率较低，大部分都是批量加载批量检索的操作

4.数据聚合操作频繁

5.SQL语句执行条数少，SQL硬解析对系统性能影响较小，绑定没有意义

6.分区表相对不太适合绑定变量技术


 本文转自 leonarding151CTO博客，原文链接：http://blog.51cto.com/leonarding/1131507，如需转载请自行联系原作者


weixin_34355715
关注
Oracle 变量绑定与变量窥视合集系列二
weixin_34050519的博客
02-03 93
二 用示例演示一次硬分析（hard parse)和一次软分析（soft parse），以及一次更软的分析（softer soft parse)，并对给出演示结果 我们先看一个硬解析和软解析关系测试，什么时候硬解析，什么时候软解析 LEO1@LEO1> drop table leo6 purge;                          清理环境 Table dropped. ...
动态SQL与绑定变量
大巧不工
02-19 4249
转自http://space.itpub.net/12474069/viewspace-621157 动态SQL与绑定变量 　　　                                       (初稿) 1什么是动态SQL(Dynamic SQL) 动态SQL使你在运行时，以字符串的形式构造SQL语句。这些语句包含在PL/SQL块中，并且常常包含占位符来使用绑定变量。这一
构造绑定含有绑定变量的JAVA的SQL语句执行计划
N
02-07 1646
情景：11G R2，有条SQL执行计划有异常，内存中并没有好的现存的执行计划(baseline用不了),经确认可通过sql profile 绑定固定； ---------------------------------------------------- SQL的样式： select * from * where a=:1 and b=:2 and c=:3
oracle 视图带变量条件,oracle视图(带参数)
weixin_33940469的博客
04-12 2266
具体的Oracle参数视图实践(1)2010-04-19 10:20 佚名 CSDN博客我要评论(0)字号：T | T一般情况下Oracle数据库是不带参数的视图。有时，我们想使用Oracle参数视图，可以给我们方便查询数据。下面就来简单介绍下。AD：一般情况下Oracle数据库是不带参数的视图。有时，我们想使用Oracle参数视图，可以给我们方便查询数据。比如，眼下我面对这这样一个问题：要打印一...
Oracle 变量绑定与变量窥视合集系列五
weixin_34342905的博客
11-15 110
五演示ACS（adaptiver cursor sharing)的效果 变量窥视bind peeking：当一条SQL语句在硬解析时如果有绑定变量，会查看这个变量值，有了变量值便于得出正确的执行计划，在后续软解析过程中将不再查看这个变量值了。 变量窥视目的：就是为了得出正确的执行计划，第一条sql的执行计划的准确性是至关重要的，后面反复执行即可 变量窥...
Oracle 变量绑定与变量窥视合集系列三
weixin_34067980的博客
11-08 70
三用示例演示一次分析，多次执行的示例，并对给出演示结果 我们分别演示硬解析软解析执行不同组合 实验 没有绑定变量 LEO1@LEO1> drop table leo3 purge; 清理环境 Table dropped. LEO1@LEO1> drop table leo4 purge; Table dropped. LE...
Oracle 变量绑定与变量窥视合集系列四
weixin_33938733的博客
11-15 80
四演示一个父游标产生3个子游标的示例，并分别说明每个子游标产生的原因 父游标：说白了就是SQL语句字符的哈希值，SQL文本一样它生成的哈希值就一样 子游标：区别相同SQL语句的一些其他不同 例如 1.不同用户下的相同SQL语句 2.不同权限下的相同SQL语句 父子游标：oracle要知道它们是同一个语句可共享相同的信息，还是不是同一个语句要...
Oracle绑定变量窥视功能深度分析.pdf
10-09
Oracle 绑定变量窥视功能是 Oracle 数据库中的一种功能，可以使 SQL 语句共享执行计划，从而提高数据库性能。该功能通过在绑定变量中存储执行计划，从而避免了每次执行 SQL 语句时都需要重新生成执行计划的过程。 ...
开启和关闭绑定变量窥探
zhaoyangjian724的专栏
05-14 648
绑定变量窥视cbo的一个附属功能,关闭他不会影响是用cbo还是rbo,就算关闭了绑定变量窥视,cbo还是会利用别的统计信息(num_distinct,density等)来评估cost和cardinality,只 是无法使用直方图信息而已: SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM...
KingbaseES 绑定变量窥探机制
lyu1026的博客
04-26 1331
概述： 对于数据严重倾斜的，极端如以下例子，不同的传入值，可能执行计划不同，制定执行计划时，就要求知道变量的值。对于绑定变量的情况，我们知道Oracle 有_optim_peek_user_binds 参数，控制是否启用变量窥探。KingbaseES 也有类似参数，控制是否启用变量窥探。 窥探机制 KingbaseES 采用以下判断机制，决定是否固定执行计划： 前5次执行时，每次都会根据实际传入的实际绑定变量新生成执行计划进行执行，即每次都是硬解析，同时会记录这5次的执行计划； 当第.
绑定变量窥探和直方图
zhaoyangjian724的专栏
12-13 694
一、绑定变量的窥探(peek) 1、Oracle在处理带有绑定变量的SQL时候，只会在硬解析的时候才会“窥探”一下SQL中绑定变量的值，然后会根据窥探到的值来决定整个SQL的执行计划。参数： _optim_peek_user_binds 2、绑定变量窥探的案例 create table t8(id int ,name varchar2(100)); begin
系统架构设计师：软件可靠性
简简单单Onlinezuozuo
09-17 178
系统架构设计师：软件可靠性系统架构设计师：软件可靠性系统架构设计师：软件可靠性。
等保测评中的关键技术挑战与应对策略
A526847的博客
09-14 480
在信息安全领域，等保测评（信息安全等级保护测评）作为确保信息系统安全性的重要手段，其过程中不可避免地会遇到一系列技术挑战。这些挑战不仅考验着企业的技术实力，也对其安全管理水平提出了更高要求。本文将深入探讨等保测评中的关键技术挑战，并提出相应的应对策略。
系统架构设计师 云原生架构篇
最新发布
Ammmmmmmmn的博客
09-19 1870
基础设施即服务（IaaS）：提供虚拟化的计算资源，如虚拟机、存储和网络，让用户可以灵活地构建和管理自己的IT基础设施。平台即服务（PaaS）：提供开发和部署环境，包括操作系统、数据库和中间件，帮助开发者快速构建、测试和部署应用程序。软件即服务（SaaS）：提供基于云的应用软件，用户无需管理底层基础设施，只需直接使用应用，例如邮件服务、CRM系统等。通过云服务，企业可以降低初始投资和运营成本，获得弹性、可扩展性和高可用性，从而更专注于业务创新。市场占有率：约30-40%
系统架构设计师 大数据架构篇二
Ammmmmmmmn的博客
09-17 1499
Kappa 架构在Lambda的基础上进行了优化，删除了 Batch Layer，将数据通道以消息队列进行替代。因此对于Kappa 架构来说，依旧以流处理为主，数据在数据湖层面进行了存储，当需要进行离线分析或者再次计算的时候，则将数据湖的数据再次经过消息队列重播一次则可。大数据架构的核心在于处理大规模数据集，提供高效率的数据处理和分析能力。Lambda架构和Kappa架构提供了不同的方法来实现这一目标，每种架构都有其优缺点。
【系统架构设计】软件的知识产权保护+标准化概论+应用数学+云计算
weixin_48618536的博客
09-14 567
根据制定机构和适用范围的不同，标准可分为若干个级别。按类型划分，标准可分为。是制定标准、组织实施标准和对标准的实施进行监督；是通过制定、发布和实施标准，达到统一；应用数学涉及的内容很多，在本章中集中在。是获得最佳秩序和社会效益。，其他标准是推荐性标准。
Oracle数据库绑定变量：提升SQL性能的关键
Oracle数据库中的绑定变量是一种优化技术，对于提升SQL性能和实现应用程序的可扩展性至关重要。绑定变量的使用旨在解决数据库系统在处理大量相似但参数不同的SQL语句时面临的性能瓶颈问题。 首先，我们要理解为什么...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值