【sql调优】绑定变量与CBO-阿里云开发者社区

开发者社区> 北在南方> 正文

【sql调优】绑定变量与CBO

简介:
+关注继续查看

SQL> var x1 number;
SQL> exec :x1 :=1;

PL/SQL 过程已成功完成。

SQL> set autot on
SQL> set autot traceonly
SQL> select count(*) from t where object_id =:x1 or :x1=0;


执行计划
----------------------------------------------------------
Plan hash value: 2966233522                                                   
---------------------------------------------------------------------------    
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |    
---------------------------------------------------------------------------    
|   0 | SELECT STATEMENT   |      |     1 |     3 |   278   (1)| 00:00:04 |    
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |    
|*  2 |   TABLE ACCESS FULL| T    |   761 |  2283 |   278   (1)| 00:00:04 |    
---------------------------------------------------------------------------          
Predicate Information (identified by operation id):                            
---------------------------------------------------                                                                                 
   2 - filter(TO_NUMBER(:X1)=0 OR "OBJECT_ID"=TO_NUMBER(:X1))                  

统计信息
----------------------------------------------------------                     
        223  recursive calls                                                   
          0  db block gets                                                     
       1050  consistent gets                                                   
       1014  physical reads                                                    
          0  redo size                                                         
        418  bytes sent via SQL*Net to client                                  
        416  bytes received via SQL*Net from client                            
          2  SQL*Net roundtrips to/from client                                 
          5  sorts (memory)                                                    
          0  sorts (disk)                                                      
          1  rows processed                                                    

SQL> select count(object_id) from t where object_id =:x1 or :x1=0;


执行计划
----------------------------------------------------------                     
Plan hash value: 3028837625                                                        
------------------------------------------------------------------------------ 
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
------------------------------------------------------------------------------ 
|   0 | SELECT STATEMENT      |      |     1 |     3 |   108   (1)| 00:00:02 | 
|   1 |  SORT AGGREGATE       |      |     1 |     3 |            |          | 
|*  2 |   INDEX FAST FULL SCAN| I_ID |   761 |  2283 |   108   (1)| 00:00:02 | 
-----------------------------------------------------------------------------         
Predicate Information (identified by operation id):                            
---------------------------------------------------                                                                           
   2 - filter(TO_NUMBER(:X1)=0 OR "OBJECT_ID"=TO_NUMBER(:X1))                  


统计信息
----------------------------------------------------------                     
         99  recursive calls            
          0  db block gets                    
        406  consistent gets                                                   
        389  physical reads    
          0  redo size                                                                   
          1  rows processed                                                    

SQL> select object_id from t where object_id =:x1 or :x1=0;

未选定行
执行计划
----------------------------------------------------------                     
Plan hash value: 1601196873                                                    
                                                                               
--------------------------------------------------------------------------     
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |     
--------------------------------------------------------------------------     
|   0 | SELECT STATEMENT  |      |   761 |  2283 |   278   (1)| 00:00:04 |     
|*  1 |  TABLE ACCESS FULL| T    |   761 |  2283 |   278   (1)| 00:00:04 |     
--------------------------------------------------------------------------     
    
Predicate Information (identified by operation id):                            
--------------------------------------------------- 
   1 - filter(TO_NUMBER(:X1)=0 OR "OBJECT_ID"=TO_NUMBER(:X1))                  

SQL> select object_id from t where object_id =:x1 and :x1=0;

未选定行
执行计划
----------------------------------------------------------                     
Plan hash value: 845274062                                                     
--------------------------------------------------------------------------     
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |     
--------------------------------------------------------------------------     
|   0 | SELECT STATEMENT  |      |    73 |   219 |     1   (0)| 00:00:01 |     
|*  1 |  FILTER           |      |       |       |            |          |     
|*  2 |   INDEX RANGE SCAN| I_ID |    73 |   219 |     1   (0)| 00:00:01 |     
--------------------------------------------------------------------------     
                                                                               
Predicate Information (identified by operation id):                            
--------------------------------------------------- 
   1 - filter(TO_NUMBER(:X1)=0)                                                
   2 - access("OBJECT_ID"=TO_NUMBER(:X1)) 

SQL> select object_id from t where object_id =:x1
  2  union
  3  select object_id from t where 0 =:x1;

未选定行


执行计划
----------------------------------------------------------                     
Plan hash value: 2897536294   
------------------------------------------------------------------------------------- 
| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time  |   
-------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT     |      | 68940 |   201K|       |   467 (100)| 00:00:06 |  
|   1 |  SORT UNIQUE         |      | 68940 |   201K|  1640K|   467 (100)| 00:00:06 |
|   2 |   UNION-ALL          |      |       |       |       |            |          |
|*  3 |    INDEX RANGE SCAN  | I_ID |    73 |   219 |       |     1   (0)| 00:00:01 |
|*  4 |    FILTER            |      |       |       |       |            |          | 
|   5 |     TABLE ACCESS FULL| T    | 68867 |   201K|       |   277   (1)| 00:00:04 | 
                                                                               
-------------------------------------------------------------------------------------                                                                         
Predicate Information (identified by operation id):                            
---------------------------------------------------                           
   3 - access("OBJECT_ID"=TO_NUMBER(:X1))                                      
   4 - filter(TO_NUMBER(:X1)=0)                                                
                                                   

SQL> set linesize 999
SQL> select object_id from t where object_id =:x1
  2  union
  3  select object_id from t where 0 =:x1;

未选定行
执行计划
----------------------------------------------------------                                                                                                                                                
Plan hash value: 2897536294                                                                                                                                                                                                                                 
-------------------------------------------------------------------------------------                                    
| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                                                              
-------------------------------------------------------------------------------------                                     
|   0 | SELECT STATEMENT     |      | 68940 |   201K|       |   467 (100)| 00:00:06 |
|   1 |  SORT UNIQUE         |      | 68940 |   201K|  1640K|   467 (100)| 00:00:06 |
|   2 |   UNION-ALL          |      |       |       |       |            |          |
|*  3 |    INDEX RANGE SCAN  | I_ID |    73 |   219 |       |     1   (0)| 00:00:01 |
|*  4 |    FILTER            |      |       |       |       |            |          |
|   5 |     TABLE ACCESS FULL| T    | 68867 |   201K|       |   277   (1)| 00:00:04 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):      
--------------------------------------------------- 
   3 - access("OBJECT_ID"=TO_NUMBER(:X1))  
   4 - filter(TO_NUMBER(:X1)=0)                                                                                                                            
SQL> set linesize 130
SQL> select object_id from t where object_id =:x1
  2  union
  3  select object_id from t where 0 =:x1;

未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2897536294                                                                                                     
------------------------------------------------------------------------------------- 
| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 68940 |   201K|       |   467 (100)| 00:00:06 | 
|   1 |  SORT UNIQUE         |      | 68940 |   201K|  1640K|   467 (100)| 00:00:06 | 
|   2 |   UNION-ALL          |      |       |       |       |            |          | 
|*  3 |    INDEX RANGE SCAN  | I_ID |    73 |   219 |       |     1   (0)| 00:00:01 |
|*  4 |    FILTER            |      |       |       |       |            |          |
|   5 |     TABLE ACCESS FULL| T    | 68867 |   201K|       |   277   (1)| 00:00:04 | 
------------------------------------------------------------------------------------- 
Predicate Information (identified by operation id):                                                                              
--------------------------------------------------- 
   3 - access("OBJECT_ID"=TO_NUMBER(:X1))   
   4 - filter(TO_NUMBER(:X1)=0)

      
SQL> var x2  number;
SQL> exec :x2 :=4;

PL/SQL 过程已成功完成。

SQL> select object_id from t where object_id= :x1 or object_id =:x2;
执行计划
----------------------------------------------------------      
Plan hash value: 547095368    
--------------------------------------------------------------------------                                                       
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
--------------------------------------------------------------------------  
|   0 | SELECT STATEMENT  |      |   146 |   438 |     3   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR  |      |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| I_ID |   146 |   438 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------  
Predicate Information (identified by operation id):      
---------------------------------------------------  
   2 - access("OBJECT_ID"=TO_NUMBER(:X1) OR "OBJECT_ID"=TO_NUMBER(:X2))

SQL> select object_id from t where object_id= :x1 or 3 =:x2;
未选定行
执行计划
----------------------------------------------------------  
Plan hash value: 1601196873   
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   761 |  2283 |   278   (1)| 00:00:04 | 
|*  1 |  TABLE ACCESS FULL| T    |   761 |  2283 |   278   (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):   
---------------------------------------------------  
   1 - filter(TO_NUMBER(:X2)=3 OR "OBJECT_ID"=TO_NUMBER(:X1)) 
 
SQL> select object_id from t where object_id= :x1 or :x2=4;
已选择68867行。
执行计划
----------------------------------------------------------   
Plan hash value: 1601196873
--------------------------------------------------------------------------  
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
--------------------------------------------------------------------------   
|   0 | SELECT STATEMENT  |      |   761 |  2283 |   278   (1)| 00:00:04 |   
|*  1 |  TABLE ACCESS FULL| T    |   761 |  2283 |   278   (1)| 00:00:04 |   
-------------------------------------------------------------------------- 
Predicate Information (identified by operation id):   
---------------------------------------------------  
   1 - filter(TO_NUMBER(:X2)=4 OR "OBJECT_ID"=TO_NUMBER(:X1)) 
                                                                                                    

SQL> select object_id from t where object_id= :x1 or :x1=0;
未选定行
执行计划
----------------------------------------------------------                                                                       
Plan hash value: 1601196873       
--------------------------------------------------------------------------  
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   761 |  2283 |   278   (1)| 00:00:04 | 
|*  1 |  TABLE ACCESS FULL| T    |   761 |  2283 |   278   (1)| 00:00:04 | 
-------------------------------------------------------------------------- 
Predicate Information (identified by operation id):  
---------------------------------------------------                               
   1 - filter(TO_NUMBER(:X1)=0 OR "OBJECT_ID"=TO_NUMBER(:X1) 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
[20121102]PLSQL中的绑定变量.txt
[20121102]PLSQL中的绑定变量.txt     以前曾经遇到一个sql语句提交给开发,开发没有找到,最终确定是问题语句在PLSQL中,实际上PLSQL转化为大写, 加上自己没有注意.
579 0
mybatis SQL性能调优
Mybatis SQL性能调优         1.  Mapper层参数为Map,由Service层负责重载       Mapper由于机制的问题,不能重载,参数一般设置成Map,但这样会使参数变得模糊,如果想要使代码变得清晰,可以通过service层来实现重载的目的,对外提供的Service层是重载的,但这些重载的Service方法其实是调同一个Mapper,只不过相应
12009 0
绑定变量窥测
环境说明】 oracle版本:11.2.0 【一】进行测试环境的配置 1. 创建表test  create table test  as select rownum id ,a.
669 0
SQL 调优1
<div style="margin:0px; padding:0px; border:0px; line-height:1.57143em; font-family:gotham,helvetica,arial,sans-serif; font-size:14px; color:rgb(56,56,56)"> <span style="line-height:1.57143em; fo
1477 0
Mysql 声明变量并赋值
mysql中变量不用事前申明,在用的时候直接用“@变量名”使用就可以了。 第一种用法: set @num=1; 或set @num:=1; //这里要使用变量来保存数据,直接使用@num变量 第二种用法: select @num:=1; 或 sele...
1053 0
《SQL与关系数据库理论——如何编写健壮的SQL代码》一1.8 关系vs.关系变量
本节书摘来华章计算机《SQL与关系数据库理论——如何编写健壮的SQL代码》一书中的第1章 ,第1.8节 C. J. Date 著 单世民 何英昊 许侃 译 更多章节内容可以访问云栖社区“华章计算机”公众号查看。
990 0
使用OpenApi弹性释放和设置云服务器ECS释放
云服务器ECS的一个重要特性就是按需创建资源。您可以在业务高峰期按需弹性的自定义规则进行资源创建,在完成业务计算的时候释放资源。本篇将提供几个Tips帮助您更加容易和自动化的完成云服务器的释放和弹性设置。
7933 0
《SQL与关系数据库理论——如何编写健壮的SQL代码》一1.9 值vs.变量
本节书摘来华章计算机《交互式程序设计 第2版》一书中的第1章 ,第1.9节,Joshua Noble 著 毛顺兵 张婷婷 陈宇 沈鑫 任灿江 译更多章节内容可以访问云栖社区“华章计算机”公众号查看。 1.9 值vs.变量 关系(relation)和关系变量(relvar)的逻辑差异实际上是通常意义下值与变量之间逻辑差异的特例。
809 0
+关注
640
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载