This discussion is archived
4 Replies Latest reply: Nov 13, 2012 7:46 PM by 965009 RSS

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

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