【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) 

时间： 2024-10-28 23:51:42

【sql调优】绑定变量与CBO的相关文章

SQL 调优1

                                             SQL 调优1 调整的方法调整的工具内存组件调整  shared_pool data_buffer pga IO性能文件确定 调整的方法:调整的流程 架构设计 建模 程序 数据库 硬件调整  每次只调整一个地方 top     消耗资源最多 执行的次数最多 执行的时间最长     达到优化的目标就不优化了     优化的过程中一定不要产生新的性能问题 ----->熟悉别人的生产环境 调整的工具:1   v$

oracel中sql语句和pl/sql语句使用绑定变量

关于绑定变量的用法: 之前的文章介绍了绑定变量对于系统的重要性,这里对绑定变量的使用做进一步的分析和说明. 1)在sql语句中如何带入bind value SQL> variable x number; SQL> exec :x:=100; PL/SQL procedure successfully completed. SQL> select * from t where id=:x; no rows selected SQL> exec :x:=101; SQL> sel

sql 调优 oracle 执行速度再快一点

问题描述 sql 调优 oracle 执行速度再快一点 select psf.pol_num, psf.bank_acct_num, cba_dda.bank_acct_nm from tbank_pos_slip_files psf, tclient_policy_links cpl_dda, tclient_bank_accounts cba_dda where cpl_dda.cli_num = cba_dda.cli_num and cpl_dda.bank_acct_typ = cba

ORACLE SQL调优之'PLAN_TABLE' is old version

  在为国投做SQL调优时,他们开发说不要动现在的SQL,调整一下执行计划即可,即查询某个表时执行特定的执行计划.乍一听,我是吓了一跳!  由于他们开发不让动SQL结构(该SQL经过PLSQL优化后有500多行,其是2层嵌套递归查询,外边一个SQL如图1-2,外层SQL的每一个列是一个子查询如下图1-1,递归子查询有32个),所以只能从SQL涉及的表.索引下手,查找问题的具体原因及解决办法.我的做法是,先查看了SQL涉及的表的统计信息,问题SQL涉及了8张表(最大的表有300M左右,小表只有几M

【SQL 调优】绑定变量窥测

绑定变量窥测:在物理优化阶段,查询优化器会窥测绑定变量的值,将它作为文本来使用.这种方法的问题是它生成的执行计划会依赖第一次生成执行计划时所提供的值.--建立索引                                                                           SQL> create index i_obj_id on t_var_peek(object_id); 索引已创建. 已用时间:  00: 00: 00.25 ---执行计划选择了 ra

使用sqld360进行特定SQL调优分析

    系统性能问题通常是一个综合性的问题,用户对系统的反馈,通常是一个"慢"字.对调优人员,要从网络.应用服务器.数据库服务器等多个层面进行定位,发现瓶颈.如果落实到数据库层面,执行SQL速度慢就是开发运维DBA所需要关注的范畴. 对系统SQL的调优,一般而言不是"一锤子"买卖.而且,性能问题SQL也都遵循"二八原则",即百分之八十的性能问题,是由百分之二十的SQL语句引起的.所以,每次集中处理两到三个SQL,之后看效果再决定下一步处理的方式,

关于pl/sql中的绑定变量

在看关于shared pool的文档时,必定会提到绑定变量,也能够通过几个简单的例子对绑定变量带来影响有深刻的认识,但是在工作中,可能有时候我们就忘了绑定变量的影响了,其实有时候一个很小的变动就会导致性能几十几百倍的提升. 简单用跟一个实例来说明. 我们先清空shared pool,排除其它的运行语句带来的影响. SQL>alter system flush shared_pool; 然后我们创建一个表t,使用cats的方式创建,只有2个字段.SQL>create table t as sel

STA(SQL Tuning Advisor) SQL调优顾问简介

原文转自:http://blog.csdn.net/tianlesoftware/article/details/5630888 在Oracle10g之前,优化SQL是个比较费力的技术活,不停的分析执行计划,加hint,分析统计信息等等.在10g中,Oracle推出了自己的SQL优化辅助工具: SQL优化器(SQL Tuning Advisor :STA),它是新的DBMS_SQLTUNE包.使用STA一定要保证优化器是CBO模式下. 执行DBMS_SQLTUNE包进行sql优化需要有advis

ORACLE SQL调优之统计信息缺失导致的逻辑读暴增

    2016年11月8日,接到广西负责人申告,说决策系统一条SQL平时执行2s左右能执行完,现在却要执行2:30多才能出结果,请求对其做处理.     操作系统:RHEL LINUX 6.4     数据库版本:11.2.0.4     首先,登录服务器查看数据库服务器的状态: [oracle@orcl ~]$ free -m              total       used       free     shared    buffers     cached Mem: