欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-06-10 10:29:55
...

《Oracle变量绑定与变量窥视合集》数据库环境LEO1@LEO1select*fromv$version;BANNER------------------------------------------------------------------------

《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