开发者社区> yangyi_dba> 正文

【sql调优】绑定变量与CBO

简介: SQL> var x1 number;SQL> exec :x1 :=1; PL/SQL 过程已成功完成。 SQL> set autot onSQL> set autot traceonlySQL> select count(*) from t where ...
+关注继续查看

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)

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

相关文章
SQL优化二(SQL性能调优)
一·、前言:这篇博文内容非原创,是我们公司的架构师给我们做技术培训的时候讲的内容,我稍微整理了下,借花献佛。这篇博文只是做一个大概的科普介绍,毕竟SQL优化的知识太大了,几乎可以用一本书来介绍。另外,博主对SQL优化也是刚刚接触,也有很多不了解的地方,说的不对的地方,还请大家指正,共勉!   二、oracle服务器,所谓oracle服务器指的是一个数据库管理系统,它包括一个oracle实例(动态)和一个oracle数据库(静态)。
1277 0
DeDecms变量覆盖之SQL语句分析报告+修复方案
信息安全交流论坛 BBS.ITSEC.PW 作者:y0umer DEDECMS变量覆盖漏洞分析 昨天暗月凌晨找我搞基.他分析了此漏洞.所以才有得此文. DEDECMS为什么那么多变量覆盖呢? 是因为程序员懒啊! 我也不去批评程序员了.
1008 0
[20121102]PLSQL中的绑定变量.txt
[20121102]PLSQL中的绑定变量.txt     以前曾经遇到一个sql语句提交给开发,开发没有找到,最终确定是问题语句在PLSQL中,实际上PLSQL转化为大写, 加上自己没有注意.
600 0
寻找没有使用绑定变量的sql语句
SELECT plan_hash_value, COUNT (*) FROM v$sql WHERE plan_hash_value 0GROUP BY plan_hash_valueORDER BY 2 DESC;SELECT * FR...
558 0
使用OpenApi弹性释放和设置云服务器ECS释放
云服务器ECS的一个重要特性就是按需创建资源。您可以在业务高峰期按需弹性的自定义规则进行资源创建,在完成业务计算的时候释放资源。本篇将提供几个Tips帮助您更加容易和自动化的完成云服务器的释放和弹性设置。
18670 0
+关注
yangyi_dba
数据库相关技术专家
972
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载