Oracle CBO 统计信息的收集与执行计划的选择

时间:2022-06-06 03:56:01

--概要

主要总结一下Oracle是如何收集统计信息的是如何选择的,有一些好的Ref可以看看

柱状图(Histogram),绑定变量,bind peeking,cursor_sharing 之间的关系

--基本概念

首先要明确系统的自动收集机制 如果insert update delete truncate发生的数据量变化大于总记录的10%系统就会自动重新统计信息.如果统计信息为null时系统蚕食OPTOMIZER_DYNAMIC_SAMPLING 能够控制如何进行收集. Hint是 /*+ dynamic_sampling (table integer) */ 详见TOP第五章1.3节

以下为收集后用来存储统计信息的视图:

  • user_tab_statistics --统计之后用来显示表的统计信息 3.1.2
  • user_tables --查看schames下所有表的统计信息
        
        
        
    exec dbms_stats.gather_schema_stats(ownname => user , estimate_percent => 0.5 );
    SELECT table_name, sample_size, num_rows, round (sample_size / num_rows * 100 , 1 ) AS " % "
    FROM
    user_tables WHERE num_rows > 0 ORDER BY table_name;
    TABLE_NAME SAMPLE_SIZE NUM_ROWS
    %
    -- ---------------------------- ----------- -------- ----------
    BIG_TABLE 12435 ###### 5
    COLOCATED
    5087 98484 5.2
    T
    1001 1001 100
  • user_tab_col_statistics --统计之后用来显示列的统计信息 3.1.3
  • user_tab_histograms --统计之后用来显示直方图的统计信息 3.1.4
  • user_ind_statistics --统计之后用来显示索引的统计信息 3.1.5
  • user_indexes

Oracle的CBO的执行计划选择是基于柱状图(Histogram)的,柱状图用于记录表中的列的分布情况,有了柱状图CBO就可以选择最优的执行计划,否则就需要根据索引的选择性(selectivity)来判断是否使用该索引. 以下是建立测试环境

  •      
         
         
    CREATE TABLE t
    AS
    SELECT rownum AS id,
    round (dbms_random.normal * 1000 ) AS val1,
    100 + round (ln(rownum / 3.25 + 2 )) AS val2,
    100 + round (ln(rownum / 3.25 + 2 )) AS val3,
    dbms_random.string(
    ' p ' , 250 ) AS pad
    FROM dual
    CONNECT
    BY level <= 1000
    ORDER BY dbms_random.value;

    UPDATE t SET val1 = NULL WHERE val1 < 0 ;
    ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);
    CREATE INDEX t_val1_i ON t (val1);
    CREATE INDEX t_val2_i ON t (val2);

--收集方法 dbms_stats.gather_table_stats vs Analyze

众所周知,Table是分区的时候,analyze根据所有partition上的已有的统计信息“计算”出整个表级别上的统计信息;而 dbms_stats是实际去计算整个表范围的统计信息,因此表级别的统计信息比analyze更精确,反映表上真实的情况.

  •     
        
        
    alter system flush shared_pool;

    analyze
    table t compute statistics ;
    analyze
    table t delete statistics ;
    analyze
    table ljb_test compute statistics for table for all indexes for all indexed columns;

    exec dbms_stats.gather_table_stats( user , ' t ' );
    exec dbms_stats.gather_table_stats( user , ' t ' ,method_opt => ' for columns size 1 status ' ); 

--直方图 (n<6是等高度直方图 n>=6 是频率直方图(精准)) 我的一个讨论帖http://www.itpub.net/viewthread.php?tid=1247685&page=2#pid14860199

method_opt=>'FOR ALL COLUMNS SIZE n' -- n<6是等高度直方图 n>=6 是频率直方图(精准) 但是列的唯一值大于254还是要使用等高直方图。其中n定义了bucket的取值范围1~254,oralce 根据这个列的distinct值来计算bucket的个数(size 1例外它表示不创建直方图)
如果distinct > n 那么就使用等高直方图,就是说当有一列的唯一值大于254(最大允许的桶的数量)就不能够使用频率直方图了如果distinct <=n 那么就是用频率直方图,bucket数目是distinct~除了这个选项还有几个可选:

  • size repeat       刷新可用直方图
  • size skewonly 只收集非均匀分布的直方图,系统自动决定桶数
  • size auto          类似skewonly加上where短语引用的列根据一个列使用历史P115 col_usage.sql统计表决定是否收集
  • cascade=>true DBMS_STATS will collect for all columns and the indexes
        
        
        
    select id , count ( * ) from t group by id order by id;
    ....................
    19978 1
    19979 1
    19982 1
    19996 1
    19997 1
    12500 rows selected.
    exec dbms_stats.gather_table_stats( user , ' T ' ,method_opt => ' FOR ALL COLUMNS SIZE 6 ' );
    -- n>=6 选择使用频率直方图
    Select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS, HISTOGRAM
    From user_tab_col_statistics where table_name = ' T ' and column_name = ' ID ' ;
    -- 这里的distinct是12500远远大于254所以如果distinct > n 那么就使用等高直方图
    TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
    -- -------- ----------- ------------ ----------- -----------------
    T ID 12500 6 HEIGHT BALANCED
    SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
    FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME = ' T ' AND COLUMN_NAME = ' ID ' ;
    TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
    -- ------------------------------------------------------
    T ID 0 1
    T ID
    1 1563
    T ID
    2 3126
    T ID
    3 4689
    T ID
    4 6252
    T ID
    5 7814
    T ID
    6 9376
    T ID
    7 13809
    T ID
    8 19997
    0 ~ 1 第一个桶
    1 ~ 2 第二个桶
    ....
    3 ~ 4 第四个桶
    等高直方图中endpoint_number就是每个通的端点号,而不是桶号说白了就是桶号从0开始
    频率直方图中endpoint_number就是桶中的累计个数每一个endpoint_number代表一个桶说白了就是桶号从1开始
    -- 频率直方图能够精准的返回基数cardinality,比等高直方图精准
    DELETE plan_table;
    EXPLAIN
    PLAN SET STATEMENT_ID ' 101 ' FOR SELECT * FROM t WHERE val2 = 101 ;
    EXPLAIN
    PLAN SET STATEMENT_ID ' 102 ' FOR SELECT * FROM t WHERE val2 = 102 ;
    EXPLAIN
    PLAN SET STATEMENT_ID ' 103 ' FOR SELECT * FROM t WHERE val2 = 103 ;
    EXPLAIN
    PLAN SET STATEMENT_ID ' 104 ' FOR SELECT * FROM t WHERE val2 = 104 ;
    EXPLAIN
    PLAN SET STATEMENT_ID ' 105 ' FOR SELECT * FROM t WHERE val2 = 105 ;
    EXPLAIN
    PLAN SET STATEMENT_ID ' 106 ' FOR SELECT * FROM t WHERE val2 = 106 ;
    COLUMN statement_id FORMAT A12
    SELECT statement_id, cardinality FROM plan_table WHERE id = 0 ORDER BY statement_id;
    -- 频率直方图能够精准的返回基数cardinality使用method_opt => 'for all columns size skewonly'进行收集
    STATEMENT_ID CARDINALITY
    -- ---------- -----------
    101 8
    102 25
    103 68
    104 185
    105 502
    106 212
    STATEMENT_ID CARDINALITY
    -- 等高直方图不准确的基数cardinality 使用method_opt =>5进行收集
    --
    ---------- -----------
    101 50
    102 50
    103 50
    104 50
    105 400
    106 300

-- 绑定变量

  • 这里涉及到几个概念,Bind Peeking:第一次硬解析,如果收集了直方图,并且使用了绑定变量或者设置了Cursor_Sharing这个变量,这时Bind Peeking就会开动了.
    • Exact书写完全一致
    • Similar非绑定变量自动转为绑定变量还会有peeking,where条件中没有柱状图就会peeking否则认为SQL不安全
    • Force不理会柱状图直接共享
  • 测试
        
        
        
    SELECT count (pad) FROM t WHERE id < 990 ; -- 查询表中大部分数据,所以全表扫描
    SELECT * FROM table (dbms_xplan.display_cursor( NULL , NULL , ' basic ' ));

    SELECT count (pad) FROM t WHERE id < 10 ; -- 而这个是Index Range Scan
    SELECT * FROM table (dbms_xplan.display_cursor( NULL , NULL , ' basic ' ));

    With bind variables the same execution plan is used. Depending on the
    peeked value (
    10 or 990 ), a full table scan or an index range scan is used.

    -- --------第一次Bind Peeking 为全表扫描那么之后都是用这个执行计划-----------
    variable id number ;

    EXECUTE :id : = 990 ;
    SELECT count (pad) FROM t WHERE id < :id;
    SELECT * FROM table (dbms_xplan.display_cursor( NULL , NULL , ' basic ' ));

    EXECUTE :id : = 10 ;
    SELECT count (pad) FROM t WHERE id < :id;
    SELECT * FROM table (dbms_xplan.display_cursor( NULL , NULL , ' basic ' ));

    -- --------第一次Bind Peeking 为 Index Range Scan 那么之后都是用这个执行计划-----------

    ALTER SYSTEM FLUSH SHARED_POOL;
    variable id
    number ;

    EXECUTE :id : = 10 ;
    SELECT count (pad) FROM t WHERE id < :id;
    SELECT * FROM table (dbms_xplan.display_cursor( NULL , NULL , ' basic ' ));

    EXECUTE :id : = 990 ;
    SELECT count (pad) FROM t WHERE id < :id;
    SELECT * FROM table (dbms_xplan.display_cursor( NULL , NULL , ' basic ' ));

    -- 验证一下Library Cache中的执行计划也是Index Range Scan
    Select operation,options, object_name ,id,parent_id,cost
    From v$sql_plan where object_name = ' T ' ;
    OPERATION OPTIONS
    OBJECT_NAME ID PARENT_ID COST
    -- ----------- --------------- ----------- --- ---------- ----
    TABLE ACCESS BY INDEX ROWID T 2 1 11

    SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware, is_shareable
    FROM v$sql
    WHERE sql_text = ' SELECT count(pad) FROM t WHERE id < :id '
    ORDER BY child_number;
    SQL_ID CHILD_NUMBER I I I
    -- ----------- ------------ - - -
    asth1mx10aygn 0 Y N Y

--自适应游标 11g

11g的自适应游标解决了上边的问题, 字典视图 v$SQL 已经修改,添加了两列:IS_BIND_SENSITIVE 和 IS_BIND_AWARE

  • 测试
        
        
        
    Select is_bind_sensitive, is_bind_aware, sql_id, child_number
    From v$sql where sql_text = ' select * from t where id < 990; '

    select * from v$sql_cs_histogram where sql_id = ' 7cv5271zx2ttg '