Oracle 绑定变量窥探

    Bind Peeking是Oracle 9i中引入的新特性,一直持续到Oracle 10g R2。它的作用就是在SQL语句硬分析的时候,查看一下当前SQL谓词的值 ,以便生成最佳的执行计划。而在oracle 9i之前的版本中,Oracle 只根据统计信息来做出执行计划。

一、绑定变量窥探     使用SQL首次运行时的值来生成执行计划。后续再次运行该SQL语句则使用首次执行计划来执行。     影响的版本:Oracle 9i, Oracle 10g     对于绑定变量列中的特殊值或非均匀分布列上的绑定变量会造成非高效的执行计划被选择并执行。         要注意的是,Bind Peeking只发生在硬分析的时候,即SQL被第一次执行的时候,之后的变量将不会在做peeking。我们可以看出,Bind     peeking并不能最终解决不同谓词导致选择不同执行计划的问题,它只能让SQL第一次执行的时候,执行计划选择更加准确,并不能帮助OLAP     系统解决绑定变量导致执行计划选择错误的问题。这也是OLAP不应该使用绑定变量的一个原因。

        更确切地说,绑定变量窥探是在SQL解析的物理阶段,查询优化器将会窥探绑定变量的值并将其作为字面量来使用。即ORACLE首次解析     SQL时会将变量的真实值代入产生执行计划,后续对所有使用该绑定变量SQL语句都采用首次生存的执行计划。如此这般?那性能究竟如何?     结果是并非最佳的执行计划的使用。此问题在Oracle 11g中得以解决。           请参考:Oracle自适应共享游标

二、示例绑定变量窥探     1、创建演示环境       

SQL> select * from v$version where rownum<2;    -->查看当前数据库版本                                         
                                                                                                              
BANNER                                                                                                        
----------------------------------------------------------------                                              
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production                                                     
                                                                                                              
SQL> create table t(id,owner,object_id) as       -->创建测试表t                                               
  2  select rownum,owner,object_id from all_objects where rownum<=1000;                                       
                                                                                                              
SQL> alter table t add constraint t_pk primary key(id);  -->为表t添加主键                                     
                                                                                                              
SQL> begin                                               -->收集统计信息,此处未生成直方图信息                 
  2  dbms_stats.gather_table_stats(                                                                           
  3  ownname=>'SCOTT',                                                                                        
  4  tabname=>'T',                                                                                            
  5  estimate_percent=>100,                                                                                   
  6  method_opt=>'for all columns size 1');                                                                   
  7  end;                                                                                                     
  8  /                                                                                                        
                                                                                                              
PL/SQL procedure successfully completed.                                                                      
                                                                                                              
SQL> select count(id),count(distinct id),min(id),max(id) from t;  -->查看值的分布情况                         
                                                                                                              
 COUNT(ID) COUNT(DISTINCTID)    MIN(ID)    MAX(ID)                                                            
---------- ----------------- ---------- ----------                                                            
      1000              1000          1       1000                                                            

    2、未使用绑定变量情形下SQL语句的执行计划       

SQL> select sum(object_id) from t where id<900;     -->发布SQL 查询语句                                              
                                                                                                                     
SUM(OBJECT_ID)                                                                                                       
--------------                                                                                                       
        446549                                                                                                       
                                                                                                                     
SQL> select * from table(dbms_xplan.display_cursor()); -->由其执行计划可知,当前的SQL语句使用了全表扫描              
/**************************************************/                                                                 
/* Author: Robinson Cheng                         */                                                                 
/* Blog:   http://blog.csdn.net/robinson_0612     */                                                                 
/* MSN:    robinson_0612@hotmail.com              */                                                                 
/* QQ:     645746311                              */                                                                 
/**************************************************/                                                                 
				                                                                                                             
PLAN_TABLE_OUTPUT                                                                                                    
---------------------------------------------------------------------------                                          
SQL_ID  bz6h6fdsxgjka, child number 0                                                                                
-------------------------------------                                                                                
select sum(object_id) from t where id<900                                                                            
                                                                                                                     
Plan hash value: 2966233522                                                                                          
                                                                                                                     
---------------------------------------------------------------------------                                          
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                          
---------------------------------------------------------------------------                                          
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                          
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                          
|*  2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |                                          
---------------------------------------------------------------------------                                          
                                                                                                                     
Predicate Information (identified by operation id):                                                                  
---------------------------------------------------                                                                  
                                                                                                                     
   2 - filter("ID"<900)                                                                                              
                                                                                                                     
SQL> select sum(object_id) from t where id<10;   -->发布另一条SQL 查询语句                                           
                                                                                                                     
SQL> select * from table(dbms_xplan.display_cursor()); -->此时的查询生成的执行计划走索引范围扫描                     
                                                       -->由于字面量不同,因此两条SQL语句生成了不同的SQL_ID与执行计划
PLAN_TABLE_OUTPUT                                                                                                    
--------------------------------------------------------------------------                                           
SQL_ID  6y2280pyvacfq, child number 0                                                                                
-------------------------------------                                                                                
select sum(object_id) from t where id<10                                                                             
                                                                                                                     
Plan hash value: 4270555908                                                                                          
                                                                                                                     
-------------------------------------------------------------------------------------                                
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                
-------------------------------------------------------------------------------------                                
|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |                                
|   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |                                
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     9 |    72 |     3   (0)| 00:00:01 |                                
|*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     2   (0)| 00:00:01 |                                
-------------------------------------------------------------------------------------                                
                                                                                                                     
Predicate Information (identified by operation id):                                                                  
---------------------------------------------------                                                                  
                                                                                                                     
   3 - access("ID"<10)                                                                                               

    3、使用绑定变量情形下的执行计划             

SQL> variable v_id number;   -->定义绑定变量                                                                         
SQL> exec :v_id:=900;        -->给绑定变量赋值                                                                       
                                                                                                                     
PL/SQL procedure successfully completed.                                                                             
                                                                                                                     
SQL> select sum(object_id) from t where id<:v_id;                                                                    
                                                                                                                     
SUM(OBJECT_ID)                                                                                                       
--------------                                                                                                       
        446549                                                                                                       
                                                                                                                     
SQL> select * from table(dbms_xplan.display_cursor());   -->此时上一条SQL语句走了全表扫描,其SQL_ID 为7qcp6urqh7d2j  
                                                                                                                     
PLAN_TABLE_OUTPUT                                                                                                    
------------------------------------------------------------------------------                                       
SQL_ID  7qcp6urqh7d2j, child number 0                                                                                
-------------------------------------                                                                                
select sum(object_id) from t where id<:v_id                                                                          
                                                                                                                     
Plan hash value: 2966233522                                                                                          
                                                                                                                     
---------------------------------------------------------------------------                                          
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                          
---------------------------------------------------------------------------                                          
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                          
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                          
|*  2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |                                          
---------------------------------------------------------------------------                                          
                                                                                                                     
Predicate Information (identified by operation id):                                                                  
---------------------------------------------------                                                                  
                                                                                                                     
   2 - filter("ID"<:V_ID)    -->谓词信息表明此时使用了绑定变量                                                       
                                                                                                                     
SQL> exec :v_id:=10;         -->对绑定变量重新赋值                                                                   
                                                                                                                     
PL/SQL procedure successfully completed.                                                                             
                                                                                                                     
SQL> select sum(object_id) from t where id<:v_id;   -->再次执行SQL语句                                               
                                                                                                                     
SUM(OBJECT_ID)                                                                                                       
--------------                                                                                                       
           254                                                                                                       
                                                                                                                     
SQL> select * from table(dbms_xplan.display_cursor());  -->此时执行计划中依然选择的是全表扫描                        
                                                        -->其SQL_ID同上一次执行的SQL语句相同,即实现了完全共享       
PLAN_TABLE_OUTPUT                                       -->对于未使用绑定变量时id<10的情形则为走索引范围扫描         
-----------------------------------------------         -->由此可知,并非最佳的执行计划被执行                        
SQL_ID  7qcp6urqh7d2j, child number 0                                                                                
-------------------------------------                                                                                
select sum(object_id) from t where id<:v_id                                                                          
                                                                                                                     
Plan hash value: 2966233522                                                                                          
                                                                                                                     
---------------------------------------------------------------------------                                          
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                          
---------------------------------------------------------------------------                                          
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                          
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                          
|*  2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |                                          
---------------------------------------------------------------------------                                          
                                                                                                                     
Predicate Information (identified by operation id):                                                                  
---------------------------------------------------                                                                  
                                                                                                                     
   2 - filter("ID"<:V_ID)                                                                                            
                                                                                                                     
SQL> alter system flush shared_pool;    -->清空共享池,此时共享的父游标与子游标全部释放                              
		                                                                                                                 
SQL> print v_id;                                                                                                     
                                                                                                                     
      V_ID                                                                                                           
----------                                                                                                           
        10                                                                                                           
                                                                                                                     
SQL> select round(avg(object_id)) from t where id<:v_id;   -->使用id<10来执行SQL语句                                 
                                                                                                                     
ROUND(AVG(OBJECT_ID))                                                                                                
---------------------                                                                                                
                   28                                                                                                
                                                                                                                     
SQL> select * from table(dbms_xplan.display_cursor());    -->此时该SQL语句使用了最佳的执行计划,即走索引范围扫描     
                                                                                                                     
PLAN_TABLE_OUTPUT                                                                                                    
---------------------------------------------------------------------------------------                              
SQL_ID  0bx53mgt4qqnt, child number 0                                                                                
-------------------------------------                                                                                
select round(avg(object_id)) from t where id<:v_id                                                                   
                                                                                                                     
Plan hash value: 4270555908                                                                                          
                                                                                                                     
-------------------------------------------------------------------------------------                                
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                
-------------------------------------------------------------------------------------                                
|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |                                
|   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |                                
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     9 |    72 |     3   (0)| 00:00:01 |                                
|*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     2   (0)| 00:00:01 |                                
-------------------------------------------------------------------------------------                                
                                                                                                                     
Predicate Information (identified by operation id):                                                                  
---------------------------------------------------                                                                  
                                                                                                                     
   3 - access("ID"<:V_ID)                                                                                            
                                                                                                                     
SQL> exec :v_id:=900;                   -->为变量赋新值                                                              
                                                                                                                     
PL/SQL procedure successfully completed.                                                                             
                                                                                                                     
SQL> select round(avg(object_id)) from t where id<:v_id;                                                             
                                                                                                                     
ROUND(AVG(OBJECT_ID))                                                                                                
---------------------                                                                                                
                  497                                                                                                
                                                                                                                     
SQL> select * from table(dbms_xplan.display_cursor()); -->此次运行的SQL语句本该使用全表扫描,而此时选择了索引范围扫描
                                                                                                                     
PLAN_TABLE_OUTPUT                                                                                                    
--------------------------------------------------------------------------------------                               
SQL_ID  0bx53mgt4qqnt, child number 0                                                                                
-------------------------------------                                                                                
select round(avg(object_id)) from t where id<:v_id                                                                   
                                                                                                                     
Plan hash value: 4270555908                                                                                          
                                                                                                                     
-------------------------------------------------------------------------------------                                
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                
-------------------------------------------------------------------------------------                                
|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |                                
|   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |                                
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     9 |    72 |     3   (0)| 00:00:01 |                                
|*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     2   (0)| 00:00:01 |                                
-------------------------------------------------------------------------------------                                
                                                                                                                     
Predicate Information (identified by operation id):                                                                  
---------------------------------------------------                                                                  
                                                                                                                     
   3 - access("ID"<:V_ID)                                                                                            
                                                                                                                     
SQL> drop table t;                                                                                                   

三、总结     从上面的演示可以,由于绑定变量窥探特性,对于后续生成的执行计划,不仅套用了首次生成的执行计划,而且执行计划中的Row,Bytes, Cost(%CPU)等都与首次生存执行计划得值相同。由此可知,尽管可以使用绑定变量解决OLTP系统中大量重复SQL的反复解析的问题。但绑定变量 可能会导致SQL语句选择非最佳的执行计划。尤其是对于存在数据倾斜的列,且生成了直方图更不宜于使用绑定变量。在Oracle 11g 中,自适 应特性从一定程度解决了绑定变量窥探所导致的问题。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏PHP在线

Mysql索引和性能优化

使用索引的原则 1. 如果没有唯一性要求,可以选择普通索引 2. 如果列上有唯一性要求,可以选择唯一索引 3. 如果是需要模糊搜索,建议选择全文索引 4. 如果...

31180
来自专栏Jackson0714

聚集索引VS非聚集索引

31060
来自专栏程序猿

SQL 注入语句特征

语句特征 1.判断有无注入点 ; and 1=1 and 1=2 2.猜表一般的表的名称无非是admin adminuser user pass passwor...

526110
来自专栏后端技术探索

MySQL中的两种临时表 外部临时表

通过CREATE TEMPORARY TABLE 创建的临时表,这种临时表称为外部临时表。这种临时表只对当前用户可见,当前会话结束的时候,该临时表会自动关闭。这...

9700
来自专栏杨建荣的学习笔记

临时表相关 (r4笔记第52天)

临时表在日常工作中可能使用比较多,但是大家都对临时表相关的一些知识了解比较少。我们来简单说数理一下。 首先是临时表空间,临时表都存储在临时表空间中,对于临时表...

28150
来自专栏C/C++基础

MySQL问题集锦

(1)SELECT子句是必选的,其它子句如WHERE子句、GROUP BY子句等是可选的。

9420
来自专栏Linyb极客之路

SQL优化指南

slow_launch_time:表示如果建立线程花费了比这个值更长的时间,slow_launch_threads 计数器将增加

13020
来自专栏北京马哥教育

MySQL/MariaDB基础性知识及DDL操作详解

前言 MySQL/MariaDB是一个开放源码的小型关联式数据库管理系统,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降...

52660
来自专栏腾讯云数据库团队的专栏

phpMyAdmin 中 sql-parser 组件的使用

phpMyAdmin 是一款基于 Web 端运行的开源数据库管理工具,它的安装使用都比较简单而且已有很多相关介绍不再重复,今天要介绍的是源码中的一个核心组件 s...

2.6K10
来自专栏MYSQL轻松学

MySQL replace用法简介

今天在工作的过程中碰到一个问题,要把数据库中某个列的所有值中含有"ceshi.test.com"的字符去掉,本来可以写个脚本,把所有的值都取出再导入进行处理,但...

39890

扫码关注云+社区

领取腾讯云代金券