4 Replies Latest reply: Nov 13, 2012 11:50 PM by cittahan RSS

    绑定变量带来的执行计划问题

    cittahan
      数据库版本10204
      SQL> desc testbp
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       ID                                                 NUMBER
       OBJECT_NAME                                        VARCHAR2(128)
      
      
      SQL> select id,count(*) from testbp group by id;
      
              ID   COUNT(*)
      ---------- ----------
              99          1
               1      10876
      id字段有索引,表及索引做了统计信息收集
      select * from testbp where id=99;
      可以根据统计信息走索引;


      但是如果使用绑定变量
      variable x number
      exec :x:=99
      
      select * from testbp where id=:x;
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2449456748
      
      ----------------------------------------------------------------------------
      | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------
      |   0 | SELECT STATEMENT  |        |  5439 |   106K|    11   (0)| 00:00:01 |
      |*  1 |  TABLE ACCESS FULL| TESTBP |  5439 |   106K|    11   (0)| 00:00:01 |
      ----------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - filter("ID"=TO_NUMBER(:X))
      发现做了隐式转换,而且sql没有走索引

      做了10046,发现里面bind也已经识别出来value是99

      请问这是为什么呢?该如何写绑定变量的查询呢?谢谢

      帖子经 cittahan编辑过

      帖子经 cittahan编辑过
        • 1. Re: 绑定变量带来的执行计划问题
          LiuMaclean(刘相兵)
          你的 问题可以参考 下这篇文章

          autotrace在绑定变量情况下不准确的问题
          http://www.askmaclean.com/archives/autotrace%E5%9C%A8%E7%BB%91%E5%AE%9A%E5%8F%98%E9%87%8F%E6%83%85%E5%86%B5%E4%B8%8B%E4%B8%8D%E5%87%86%E7%A1%AE%E7%9A%84%E9%97%AE%E9%A2%98.html
          • 2. Re: 绑定变量带来的执行计划问题
            cittahan
            SQL> alter system flush shared_pool;
            
            System altered.
            
            SQL> var x number
            SQL> exec :x:=99
            
            PL/SQL procedure successfully completed.
            
            SQL> set autot trace exp
            SQL> alter session set events '10046 trace name context forever , level 4';
            
            Session altered.
            
            SQL> select * from testbp where id=:x;
            
            Execution Plan
            ----------------------------------------------------------
            Plan hash value: 2449456748
            
            ----------------------------------------------------------------------------
            | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
            ----------------------------------------------------------------------------
            |   0 | SELECT STATEMENT  |        |  5439 |   106K|    11   (0)| 00:00:01 |
            |*  1 |  TABLE ACCESS FULL| TESTBP |  5439 |   106K|    11   (0)| 00:00:01 |
            ----------------------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
               1 - filter("ID"=TO_NUMBER(:X))
            
            
            
            SQL> alter session set events '10046 trace name context off';
            
            Session altered.
            
            
            select * 
            from
             testbp where id=:x
            
            
            call     count       cpu    elapsed       disk      query    current        rows
            ------- ------  -------- ---------- ---------- ---------- ----------  ----------
            Parse        1      0.01       0.03          0         36          0           0
            Execute      0      0.00       0.00          0          0          0           0
            Fetch        0      0.00       0.00          0          0          0           0
            ------- ------  -------- ---------- ---------- ---------- ----------  ----------
            total        1      0.01       0.03          0         36          0           0
            
            Misses in library cache during parse: 1
            Optimizer mode: ALL_ROWS
            发现set autotrace trace exp在数据库做了一次硬解析,看来实际做解析也是用不到bind peeking的……

            等会在做几个测试搞清楚………………多谢刘大
            • 3. Re: 绑定变量带来的执行计划问题
              cittahan
              如果关了bind peeking,数据库是依赖什么生成执行计划呢?不是采样吗?

              关闭bind peeking
              alter session set "_optim_peek_user_binds"=false
              将变量改为99(选择性好的),看10046
              select * 
              from
               testbp where id=:x
              
              
              call     count       cpu    elapsed       disk      query    current        rows
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              Parse        1      0.00       0.01          0          0          0           0
              Execute      1      0.00       0.00          0          0          0           0
              Fetch        2      0.00       0.00          0         43          0           1
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              total        4      0.00       0.02          0         43          0           1
              
              Misses in library cache during parse: 1
              Optimizer mode: ALL_ROWS
              Parsing user id: 169  
              
              Rows     Row Source Operation
              -------  ---------------------------------------------------
                    1  TABLE ACCESS FULL TESTBP (cr=43 pr=0 pw=0 time=38 us)
              走的不正确执行计划

              关掉bind peeking到底影响的是什么呢?直方图信息也不参考了?用的动态采样?
              • 4. Re: 绑定变量带来的执行计划问题
                cittahan
                在另外一贴里面已经回答了该问题