4 回覆 最後回覆日期:Nov 14, 2012 3:46 AM,建立者:965009

    绑定变量窥视bind peek与直方图相互作用

    LiuMaclean(刘相兵)
      关于直方图与 绑定窥视的演示
      1. 收集直方图并 不绑定变量 采用hard coding 硬编码
      
      SQL> select * from v$version;
      
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE    11.2.0.3.0      Production
      TNS for Linux: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production
      
      SQL> select t1,count(*) from checkme group by t1;
      
              T1   COUNT(*)
      ---------- ----------
               1     524288
              99          1
      
                
                
      SQL> exec dbms_stats.gather_table_stats('','CHECKME',estimate_percent=>100, method_opt=>'FOR ALL COLUMNS SIZE 100');
      
      PL/SQL procedure successfully completed.
      
      
      QL> select t1 from checkme where t1=99;
      
              T1
      ----------
              99
      
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 589285066
      
      -----------------------------------------------------------------------------
      | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
      -----------------------------------------------------------------------------
      |   0 | SELECT STATEMENT  |         |     1 |     3 |   457   (2)| 00:00:06 |
      |*  1 |  TABLE ACCESS FULL| CHECKME |     1 |     3 |   457   (2)| 00:00:06 |
      -----------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - filter("T1"=99)
      
      
      不适用用绑定变量 硬解析 依据直方图 可以获得很好的 cardinality 
      
      
      
      2. 以下是 使用绑定变量并窥视 + 直方图存在时的情况
      
      SQL> variable maclean number;
      SQL> exec :maclean:=99;
      
      PL/SQL procedure successfully completed.
      
      SQL> 
      SQL> oradebug setmypid;
      Statement processed.
      SQL> oradebug event trace[SQL_Compiler.*] disk highest;
      Statement processed.
      SQL> 
      SQL> select t1 from checkme where t1=:maclean;
      
              T1
      ----------
              99
      
      SQL> oradebug tracefile_name
      /s01/diag/rdbms/prodb/PRODB/trace/PRODB_ora_3054.trc
      
      
      *******************************************
      Peeked values of the binds in SQL statement
      *******************************************
      ----- Bind Info (kkscoacd) -----
       Bind#0
        oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
        oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
        kxsbbbfp=7fcf824a2fd0  bln=22  avl=02  flg=05
        value=99
      
      Final query after transformations:******* UNPARSED QUERY IS *******
      SELECT "CHECKME"."T1" "T1" FROM "SYS"."CHECKME" "CHECKME" WHERE "CHECKME"."T1"=:B1
      kkoqbc: optimizing query block SEL$1 (#0)
      
      
      
      SINGLE TABLE ACCESS PATH
        Single Table Cardinality Estimation for CHECKME[CHECKME]
        Column (#1):
          NewDensity:0.000001, OldDensity:0.000001 BktCnt:524289, PopBktCnt:524288, PopValCnt:1, NDV:2
        Column (#1): T1(
          AvgLen: 3 NDV: 2 Nulls: 0 Density: 0.000001 Min: 1 Max: 99
          Histogram: Freq  #Bkts: 2  UncompBkts: 524289  EndPtVals: 2
        Table: CHECKME  Alias: CHECKME
          Card: Original: 524289.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
        Access Path: TableScan
          Cost:  456.55  Resp: 456.55  Degree: 0
            Cost_io: 451.00  Cost_cpu: 116679390
            Resp_io: 451.00  Resp_cpu: 116679390
        Best:: AccessPath: TableScan
               Cost: 456.55  Degree: 1  Resp: 456.55  Card: 1.00  Bytes: 0
      
      
      
      
          Card: Original: 524289.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
           
           绑定变量窥视时 通过 直方图计算获得的 cardinality 为1 也很好
           
          Histogram: Freq  #Bkts: 2  UncompBkts: 524289  EndPtVals: 2
        Table: CHECKME  Alias: CHECKME
          Card: Original: 524289.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
      
      
      
           3. 绑定变量 但不窥视+  直方图的情况
      
      alter session set "_optim_peek_user_binds"=false;
      
      
      SQL> alter session set "_optim_peek_user_binds"=false;
      
      Session altered.
      
      SQL> 
      SQL>  variable maclean1 number;
      SQL> exec :maclean1:=99;
      
      PL/SQL procedure successfully completed.
      
      SQL> alter system flush shared_pool;
      
      System altered.
      
      SQL> oradebug setmypid;
      Statement processed.
      SQL> oradebug event trace[SQL_Compiler.*] disk highest;
      Statement processed.
      SQL> 
      SQL> select t1 from checkme where t1=:maclean1;
      
              T1
      ----------
              99
      
      SQL> oradebug tracefile_name
      /s01/diag/rdbms/prodb/PRODB/trace/PRODB_ora_3093.trc
      
      
      *******************************************
      Peeked values of the binds in SQL statement
      *******************************************
      ----- Bind Info (kkscoacd) -----
       Bind#0
        oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
        oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
        No bind buffers allocated
      
      Final query after transformations:******* UNPARSED QUERY IS *******
      SELECT "CHECKME"."T1" "T1" FROM "SYS"."CHECKME" "CHECKME" WHERE "CHECKME"."T1"=:B1
      kkoqbc: optimizing query block SEL$1 (#0)
      
        No bind buffers allocated=》 不窥视绑定变量
        
        
        SINGLE TABLE ACCESS PATH
        Single Table Cardinality Estimation for CHECKME[CHECKME]
        Column (#1):
          NewDensity:0.000001, OldDensity:0.000001 BktCnt:524289, PopBktCnt:524288, PopValCnt:1, NDV:2
        Column (#1): T1(
          AvgLen: 3 NDV: 2 Nulls: 0 Density: 0.000001 Min: 1 Max: 99
          Histogram: Freq  #Bkts: 2  UncompBkts: 524289  EndPtVals: 2
        Table: CHECKME  Alias: CHECKME
          Card: Original: 524289.000000  Rounded: 262145  Computed: 262144.50  Non Adjusted: 262144.50
        Access Path: TableScan
          Cost:  456.55  Resp: 456.55  Degree: 0
            Cost_io: 451.00  Cost_cpu: 116679390
            Resp_io: 451.00  Resp_cpu: 116679390
        Best:: AccessPath: TableScan
               Cost: 456.55  Degree: 1  Resp: 456.55  Card: 262144.50  Bytes: 0
                 
                 
                 不窥视绑定变量的情况下,虽然也会加载HISTOGRAM 信息 “ Histogram: Freq  #Bkts: 2  UncompBkts: 524289  EndPtVals: 2”
                 
                 
                 但是显然 cardinality = 262145= round(524289/2)=262145  这里的2 源于NDV 
                 
      
      如以上演示在 不窥视绑定变量的情况下, 直方图虽然被加载 但对计算Card基数不起作用           
      结论:

      1. 不绑定变量 的情况下 对于列倾斜严重的情况,直方图可以提供最好的数据分布参考

      2. 绑定变量窥视的情况下 可以利用到直方图,但是11g adaptive cursor sharing之前 无法区别 绑定敏感游标和非敏感游标

      3. 不窥视绑定变量的情况下 虽然加载直方图信息 ,但实际计算cardinality 不参考 HISTOGRAM

      Edited by: Liu Maclean(刘相兵) on 2012-11-12 上午3:30
        • 1. Re: 绑定变量窥视bind peek与直方图相互作用
          cittahan
          1、创建测试环境
          SQL> select * from v$version;
          
          BANNER
          ----------------------------------------------------------------
          Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
          PL/SQL Release 10.2.0.4.0 - Production
          CORE    10.2.0.4.0      Production
          TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
          NLSRTL Version 10.2.0.4.0 - Production
          
          SQL> create table testbp as select 1 id,object_name from dba_objects;
          
          Table created.
          
          SQL> update testbp set id=99 where rownum=1;
          
          1 row updated.
          
          SQL> commit;
          
          Commit complete.
          
          SQL> select id,count(*) from testbp group by id;
          
                  ID   COUNT(*)
          ---------- ----------
                  99          1
                   1      10876
          
          SQL> create index testbp_idx on testbp(id);
          
          Index created.
          
          SQL> exec dbms_stats.gather_table_stats(user,'testbp',cascade=>true);
          
          PL/SQL procedure successfully completed.
          2、有bind peeking时,10053 trace
          SQL> alter system flush shared_pool;
          
          System altered.
          
          SQL> var x number
          SQL> exec :x:=99
          
          PL/SQL procedure successfully completed.
          
          SQL> alter session set events '10053 trace name context forever,level 1';
          
          Session altered.
          
          SQL> select * from testbp where id=:x;
          
                  ID OBJECT_NAME
          ---------- --------------------------------------------------------------------------------------------------------------------------------
                  99 CON$
          
          SQL> alter session set events '10053 trace name context off';
          
          Session altered.
          3、关闭bind peeking,10053 trace
          SQL> alter system flush shared_pool;
          
          System altered.
          
          SQL> var x number
          SQL> exec :x:=99
          
          PL/SQL procedure successfully completed.
          
          SQL> alter session set "_optim_peek_user_binds"=false;
          
          Session altered.
          
          SQL> alter session set events '10053 trace name context forever,level 1';
          
          Session altered.
          
          SQL> select * from testbp where id=:x;
          
                  ID
          ----------
          OBJECT_NAME
          --------------------------------------------------------------------------------
                  99
          CON$
          
          
          SQL> alter session set events '10053 trace name context off';
          
          Session altered.
          4、对比两个10053 trace

          有bind peeking
          *******************************************
          Peeked values of the binds in SQL statement
          *******************************************
          kkscoacd
           Bind#0
            oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
            oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
            kxsbbbfp=110538a00  bln=22  avl=02  flg=05
            value=99
          
          ***************************************
          BASE STATISTICAL INFORMATION
          ***********************
          Table Stats::
            Table: TESTBP  Alias: TESTBP
              #Rows: 10877  #Blks:  44  AvgRowLen:  20.00
          Index Stats::
            Index: TESTBP_IDX  Col#: 1
              LVLS: 1  #LB: 22  #DK: 2  LB/K: 11.00  DB/K: 20.00  CLUF: 40.00
          ***************************************
          SINGLE TABLE ACCESS PATH
            -----------------------------------------
            BEGIN Single Table Cardinality Estimation
            -----------------------------------------
            Column (#1): ID(NUMBER)
              AvgLen: 3.00 NDV: 2 Nulls: 0 Density: 4.5969e-05 Min: 1 Max: 99
              Histogram: Freq  #Bkts: 2  UncompBkts: 10877  EndPtVals: 2
            Table: TESTBP  Alias: TESTBP     
              Card: Original: 10877  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
            -----------------------------------------
            END   Single Table Cardinality Estimation
            -----------------------------------------
            Access Path: TableScan
              Cost:  11.22  Resp: 11.22  Degree: 0
                Cost_io: 11.00  Cost_cpu: 2488763
                Resp_io: 11.00  Resp_cpu: 2488763
            Access Path: index (AllEqRange)
              Index: TESTBP_IDX
              resc_io: 2.00  resc_cpu: 15463
              ix_sel: 9.1937e-05  ix_sel_with_filters: 9.1937e-05
              Cost: 2.00  Resp: 2.00  Degree: 1
            Best:: AccessPath: IndexRange  Index: TESTBP_IDX
                   Cost: 2.00  Degree: 1  Resp: 2.00  Card: 1.00  Bytes: 0
          无bind peeking
          *******************************************
          Peeked values of the binds in SQL statement
          *******************************************
          kkscoacd
           Bind#0
            oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
            oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
            No bind buffers allocated
          
          ***************************************
          BASE STATISTICAL INFORMATION
          ***********************
          Table Stats::
            Table: TESTBP  Alias: TESTBP
              #Rows: 10877  #Blks:  44  AvgRowLen:  20.00
          Index Stats::
            Index: TESTBP_IDX  Col#: 1
              LVLS: 1  #LB: 22  #DK: 2  LB/K: 11.00  DB/K: 20.00  CLUF: 40.00
          ***************************************
          SINGLE TABLE ACCESS PATH
            -----------------------------------------
            BEGIN Single Table Cardinality Estimation
            -----------------------------------------
            Column (#1): ID(NUMBER)
              AvgLen: 3.00 NDV: 2 Nulls: 0 Density: 4.5969e-05 Min: 1 Max: 99
              Histogram: Freq  #Bkts: 2  UncompBkts: 10877  EndPtVals: 2
            Table: TESTBP  Alias: TESTBP     
              Card: Original: 10877  Rounded: 5439  Computed: 5438.50  Non Adjusted: 5438.50
            -----------------------------------------
            END   Single Table Cardinality Estimation
            -----------------------------------------
            Access Path: TableScan
              Cost:  11.23  Resp: 11.23  Degree: 0
                Cost_io: 11.00  Cost_cpu: 2597523
                Resp_io: 11.00  Resp_cpu: 2597523
            Access Path: index (AllEqRange)
              Index: TESTBP_IDX
              resc_io: 31.00  resc_cpu: 2234045
              ix_sel: 0.5  ix_sel_with_filters: 0.5
              Cost: 31.20  Resp: 31.20  Degree: 1
            Best:: AccessPath: TableScan
                   Cost: 11.23  Degree: 1  Resp: 11.23  Card: 5438.50  Bytes: 0
          以此来验证,关掉窥视,cbo只是会参考直方图的信息,但是认为索引的选择性是0.5,因此估算的card严重不准确。

          感谢Liu的帮忙:)
          • 2. Re: 绑定变量窥视bind peek与直方图相互作用
            965009
            刘的第一个查询hard coding,应该是走索引才对啊。
            cittahan 的gather_table_stats 根本没生成直方图
            • 3. Re: 绑定变量窥视bind peek与直方图相互作用
              LiuMaclean(刘相兵)
              我的演示中 根本没有建 索引, 我的演示仅仅为了证明 直方图 与绑定变量窥视 的关系 ,索引可有可无
              • 4. Re: 绑定变量窥视bind peek与直方图相互作用
                965009
                实在不好意思,记起来了,群聊里提过这个