Oracle优化09-绑定变量

本文涉及的产品
公共DNS(含HTTPDNS解析),每月1000万次HTTP解析
云解析 DNS,旗舰版 1个月
云原生数据仓库AnalyticDB MySQL版,基础版 8ACU 100GB 1个月
简介: Oracle优化09-绑定变量

思维导图


20161218031240546.png


系列博文


Oracle-绑定变量binding variable解读

Oracle-Soft Parse/Hard Parse/Soft Soft Parse解读


概述


绑定变量是OLTP系统中一个非常值得关注的技术点。良好的变量绑定会使OLTP系统数据库中的SQL执行的飞快,内存效率极高。 不绑定变量有可能会使OLTP数据库不堪负重,资源被SQL解析严重消耗,系统显得缓慢。


本博文的案例基于Oracle Database 11g Enterprise Edition Release 11.2.0.4.0


SQL究竟是如何被执行的?


20161228133637537.png

在介绍绑定变量之前,我们需要知道SQL究竟是如何被执行的?


当一个用户与数据库建立连接后,向数据库发送SQL语句,Oracle在接到这条SQL后,首先会将这个SQL做一个Hash函数运算,得到一个Hash值,然后到共享池中寻找是否有和这个hash值匹配的SQL存在。


如果找到了,Oracle会直接使用已经存在的SQL的执行计划去执行当前的SQL,然后将结果返回给用户。


如果没有找到,Oracle会认为这是一条新的SQL, 将会按照下面的顺序来执行:


1 .语法分析

SQL 是否符Oracle规定的语法,如果有语法错误,则向用户抛出错误信息


20161217201141297.png

2. 语义分析

语法分析通过之后,Oracle会对这条SQL做一些对象、权限方面的检查,查看SQL中操作的表是否存在,表中的列是否正确,用户是否有权限操作这个对象的权限等

20161217201041155.png


3 .生成执行计划


这个过程Oracle在经过一些列的操作之后,来做出SQL最后的执行计划,比如查看操作对象的统计信息,动态采样等等。


如何生成执行计划的详细信息,可以参考10053事件


4.执行SQL


Oracle按照上一步生成的执行计划,执行SQL,并将结果返回给用户。


以上的这些工作,我们通常称为硬分析(hard parse),其实是十分消耗系统资源的。 而对于相同Hash值的SQL已经存在于共享池中则称为软分析(soft parse)。


绑定变量 what ,why


绑定变量就起本质而言就是说把本来需要Oracle做硬分析的SQL变成了软分析,以减少Oracle花费在SQL解析上的时间和资源。


是否采用绑定变量在资源消耗上对比


下面我们来对下同一条SQL被执行10000次,绑定变量和非绑定变量在资源消耗上的情况


采用绑定变量


打开SQL_TRACE

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as xx@xgj
##打开时间统计
SQL> set timing on;
##为防止干扰信息,先手动的清空共享池中的信息(若生产环境慎重)
SQL> alter system flush shared_pool;
System altered
Executed in 0.078 seconds
##建表
SQL> create table t as select * from dba_objects; 
Table created
Executed in 0.281 seconds
##设置trace文件标识
SQL> alter session set  tracefile_identifier='xgj_var_bind';
Session altered
Executed in 0 seconds
##打开SQL_TRACE
SQL> alter session set sql_trace=true;
Session altered
Executed in 0.015 seconds
##执行SQL块
SQL> begin
  2    for i  in 1 .. 10000  loop
  3        execute immediate 'select * from t where t.object_id = :i' using i ;
  4    end loop;
  5  end;
  6  /
PL/SQL procedure successfully completed
Executed in 0.578 seconds
##关闭SQL_TRACE
SQL> alter session set sql_trace=false ;
Session altered
Executed in 0 seconds
SQL> 


对原始trace文件进行tkprof分析汇总

在Oracle服务器端获取到trace文件后,使用tkprof进行分析汇总,查看

oracle@entel1:[/oracle/diag/rdbms/cc/cc/trace]$ls *xgj_var_bind*
cc_ora_32363_xgj_var_bind.trc  cc_ora_32363_xgj_var_bind.trm
oracle@entel1:[/oracle/diag/rdbms/cc/cc/trace]$tkprof cc_ora_32363_xgj_var_bind.trc  xgj_var_bind.txt sys=no
TKPROF: Release 11.2.0.4.0 - Development on Sat Dec 17 21:13:11 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


我们截取分析汇总后的关键部分来看

20161217214511318.png

20161217214809806.png


整个过程加上产生的递归SQL,我们可以看到整个语句的:

ALL NON-RECURSIVE STATEMENTS + ALL RECURSIVE STATEMENTS

  • 执行时间(elapsed):0.48+0.10 = 0.58 (和刚才输出的时间大致一致)
  • CPU时间(cpu):0.48+0.09=0.57
  • 分析次数(parse):8+2=10
  • 执行次数(execute):9+10017=10025


不采用绑定变量

SQL> set timing on ;
SQL> alter session set  tracefile_identifier='xgj_var_unbind';
Session altered
Executed in 0.016 seconds
SQL> alter session set sql_trace=true;
Session altered
Executed in 0.016 seconds
SQL> begin
  2    for x in 1 .. 10000 loop
  3        execute immediate 'select * from t where t.object_id ='||x;
  4    end loop;
  5  end;
  6  /
PL/SQL procedure successfully completed
Executed in 16.672 seconds  -----耗时很长
SQL> 


执行tkprof分析汇总

20161217221541719.png

查看xgj_var_unbind.txt关键部分


20161217221856125.png


………… 中间省略一万万字


20161217222025975.png


可以看到 每一条都是hard parse,非常消耗系统资源,耗时很长。

20161217222230478.png


同样的我们统计下执行信息:

ALL NON-RECURSIVE STATEMENTS + ALL RECURSIVE STATEMENTS

  • 执行时间(elapsed):1.28+15.38 =16.66
  • CPU时间(cpu):1.22+15.31
  • 分析次数(parse): 3+20000
  • 执行次数(execute):4+20000


通过对比我们可以发现,在OLTP系统中,使用绑定变量的SQL资源消耗要与那远少于未绑定变量SQL的资源消耗,SQL执行的次数越多,差距越明显。


未绑定变量SQL的资源主要消耗在产生的递归SQL上,这些SQL主要是对SQL语句做hard parse时使用的。


试想,当一个数据库有成千上万甚至更多的用户同时执行这样的SQL,而ORACLE只做一次硬分析,后面相同的SQL只执行SQL的执行操作,势必将大大减轻数据库的资源开销。


这就是绑定变量的由来,它并不神秘,不过是拿一个变量来代替谓词常量,让ORACLE每次对用户发送的SQL做hash运算,运算出相同的hash值,于是Oracle便将这些SQL看做同一个SQL对待而已。


OLTP和OLAP系统中是否需要绑定变量分析


如果你使用Oracle的图形化工具DBCA创建数据库,应该有印象,其中有一步是要求你选择数据库的类型是OLTP还是OLAP。 其实这就说明了OLTP和OLAP数据库是有很大的差异的,Oracle需要知道你选择的系统架构,以便于按照系统的架构对相应的参数值做设定,比如初始化参数。


OLTP和OLAP的请参考之前梳理的文章 Oracle-OLAP和OLTP解读

http://blog.csdn.net/yangshangwei/article/details/52949378


OLTP栗子

数据:

SQL> drop table t;
Table dropped
SQL> create table t as select a.OBJECT_ID ,a.OBJECT_NAME from dba_objects a ;
Table created
SQL> select count(1)  from t;
  COUNT(1)
----------
     35234
SQL> 循环300次,写入更多的数据   每50次提交一次数据
SQL> begin
  2   for i in 1 .. 300  loop
  3     execute immediate 'insert into t select a.OBJECT_ID ,a.OBJECT_NAME from dba_objects a';
  4     if  mod(i,50)=0 then
  5       commit;
  6       end if;
  7   end loop;
  8  end;
  9  /
PL/SQL procedure successfully completed
SQL> select count(1)  from t;
  COUNT(1)
----------
  10605434
SQL> 在object_id字段上创建索引
SQL> create index  idx_t  on t(object_id);
Index created


操作步骤

SQL> alter session set tracefile_identifier='xgj_oltp';
Session altered
SQL> alter session set sql_trace =true;
Session altered
执行两遍SQL 
##强制走全表扫描执行计划
SQL> select /*+ full(t)*/  * from t where object_id = 188;
 OBJECT_ID OBJECT_NAME
---------- ---------------------
       188 SQLLOG$_PKEY
       ...........
  301 rows selected
##让Oracle自己选择执行计划
SQL>  select   * from t where t.object_id = 188;
 OBJECT_ID OBJECT_NAME
---------- ---------------------
       188 SQLLOG$_PKEY
       ...........
  301 rows selected
SQL> alter session set sql_trace=false;
Session altered


tkprof汇总分析


20161218004641927.png

20161218005923053.png

20161218010512577.png


可以看到 全表扫描执行计划的SQL扫描过的数据块明显大于使用索引执行的SQL计划。

从trace文件中可以看到,在fetch阶段,全表扫描读取了42093多个数据块,而走索引的,在fetch阶段,仅仅读取了308个数据块。


OLAP栗子


OLAP系统在SQL的操作中就复杂的多,OLAP数据库上大多数的时候运行是一些报表SQL,这些SQL经常会用到聚合查询(比如group by),而且结果集也是非常庞大,在这种情况下,索引并不是必然的选择,甚至有些时候全表扫描的性能会由于索引,即使相同的SQL,如果谓词条件不同,执行计划都可能不同


数据

SQL>  create table t2(object_id, object_name) partition by range (object_id)
  2    (partition p1 values less than (5000),
  3     partition p2 values less than (15000),
  4     partition p3 values less than (25000),
  5     partition p4 values less than (maxvalue))
  6   as select object_id, object_name from dba_objects;
Table created
SQL> 
SQL> begin
  2    for x in 1 .. 300 loop
  3      insert into t2 select object_id ,object_name from dba_objects ;
  4      if mod(x,50)=0 then
  5         commit;
  6      end if;
  7    end loop;
  8  end;
  9  /
PL/SQL procedure successfully completed
SQL> select count(1) from t2;
  COUNT(1)
----------
  10609046
SQL> select count(1) from t2 partition(p1);
  COUNT(1)
----------
   1504097
SQL> select count(1) from t2 partition(p2);
  COUNT(1)
----------
   2691241
SQL> select count(1) from t2 partition(p3);
  COUNT(1)
----------
         0
SQL> select count(1) from t2 partition(p4);
  COUNT(1)
----------
   6413708
SQL> 在分区表上建立本地索引
SQL> create index idx_t_id on t2(object_id)  local;
Index created
##对表做一次分析(cascade => true ,索引也会被一同分析)
SQL>  exec dbms_stats.gather_table_stats(user,'t2',cascade => true);
PL/SQL procedure successfully completed


操作步骤


我们使用如下命令

注释:以上的命令,是在plsql客户端执行的,可以支持, 但是autotrace命令,plsql并没有很好的支持,所以我登录到了服务器所在的主机执行的,当然也可以通过sqlplus客户端操作。

SQL> set autotrace traceonly explain ;


20161218025140390.png


20161218025232718.png

20161218025352948.png

从结果中我们可以看到,虽然只是谓词的不同,但是oracle却选择了不同的执行计划,因为Oracle认为那样的计划代价最小。


结论


OLAP系统完全没有必要设置绑定变量那样只会带来负面的影响,比如导致SQL选择了错误的执行计划,让Oracle对每条SQL做hard parse ,确切知道谓词条件的值,这对执行计划的选择至关重要。 这是因为在OLAP系统中,SQL硬分析的代价是可以忽略的,系统的资源基本上是用于做大的SQL查询,和查询比较起来,SQL解析消耗的资源显然微不足道,所以得到一个最优的执行计划变得尤为重要

在OLAP系统上,让Oracle确切知道谓词条件的值,它直接决定了SQL执行计划的选择,这样做的方式就是不要绑定变量

在OLAP系统中,表索引的分析显得至关重要,因为它是Oracle为SQL做出正确的执行计划的信息来源和一句。


bind peeking


谈到变量绑定,我们不得不提一下从Oracle9i开始引入的一个新的特性,叫做bind peaking ,顾名思义,就是在SQL语句硬解析的时候,Oracle会看一下当前SQL谓词的值,以便于生成最佳的执行计划。


需要强调的是,bind peaking 只发生在hard parse的时候,即SQL被第一次执行的时候,之后的变量将不会再做peeking.


bind peeking 并不能最终解决不同谓词导致不同执行计划的问题,它只能让SQL第一次执行的时候,执行计划更加准确,并不能帮助OLAP系统解决绑定变量导致执行计划选择错误的问题,所以,OLAP依然不应该使用绑定变量。


对于OLTP系统来讲,相同的SQL重复频率非常搞,如果优化器范湖解析SQL,势必极大地消耗资源。 另外OLTP系统用户请求的结果通常都是非常小,说基本上都会考虑使用索引。 bind peeking在第一次hard parse的时候获得了一个正确的执行计划,后面的SQL都按照这个计划来执行,可以极大地改善系统的性能,这是由OLTP系统的特性决定的。

相关实践学习
AnalyticDB MySQL海量数据秒级分析体验
快速上手AnalyticDB MySQL,玩转SQL开发等功能!本教程介绍如何在AnalyticDB MySQL中,一键加载内置数据集,并基于自动生成的查询脚本,运行复杂查询语句,秒级生成查询结果。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
相关文章
|
8天前
|
SQL Oracle 关系型数据库
Oracle数据库优化方法
【10月更文挑战第25天】Oracle数据库优化方法
19 7
|
8天前
|
存储 Oracle 关系型数据库
Oracle数据库优化策略
【10月更文挑战第25天】Oracle数据库优化策略
13 5
|
3月前
|
监控 Oracle 关系型数据库
"深度剖析:Oracle SGA大小调整策略——从组件解析到动态优化,打造高效数据库性能"
【8月更文挑战第9天】在Oracle数据库性能优化中,系统全局区(SGA)的大小调整至关重要。SGA作为一组共享内存区域,直接影响数据库处理能力和响应速度。本文通过问答形式介绍SGA调整策略:包括SGA的组成(如数据缓冲区、共享池等),如何根据负载与物理内存确定初始大小,手动调整SGA的方法(如使用`ALTER SYSTEM`命令),以及利用自动内存管理(AMM)特性实现智能调整。调整过程中需注意监控与测试,确保稳定性和性能。
266 2
|
5月前
|
SQL Oracle 关系型数据库
Oracle游标的使用和优化技巧
Oracle游标的使用和优化技巧
|
4月前
|
SQL Oracle 关系型数据库
Oracle游标的使用和优化技巧
Oracle游标的使用和优化技巧
|
4月前
|
SQL 存储 Oracle
Oracle数据库中游标的工作原理与优化方法
Oracle数据库中游标的工作原理与优化方法
|
6月前
|
存储 Oracle 数据管理
Oracle 12c的自动数据优化(ADO)与热图:数据管理的“瘦身”与“透视”艺术
【4月更文挑战第19天】Oracle 12c的ADO和热图技术革新数据管理。ADO智能清理无用数据,优化存储,提升查询速度,实现数据"瘦身";热图则以直观的视觉表示展示数据分布和状态,助力识别性能瓶颈,犹如数据的"透视"工具。这两项技术结合,强化数据管理,为企业业务发展保驾护航。
|
6月前
|
Oracle 关系型数据库 数据库
Flink Sink to Oracle 存在字段CLOB类型,如何处理错误”ORA-01461: 仅能绑定要插入LONG的LONG值“
做Flink CDC同步数据过程中,目标是Oracle数据库,其中某个字段较大被设置为CLOB类型,其中会遇到异常,”ORA-01461: 仅能绑定要插入LONG的LONG值“
|
存储 SQL Oracle
Oracle优化避免索引失效
Oracle优化避免索引失效
357 0
|
SQL Oracle 关系型数据库
Oracle优化问题
Oracle优化问题