计算新闻的采访与试行铺排的抉择wwwlehu6.vip乐虎官网

–概要

根本计算一下Oracle是如何搜集总结音信的是哪些选择的,有壹些好的Ref能够看看

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

–基本概念

第二要简明系统的机关收罗体制 假若insert update delete
truncate爆发的数据量变化大于总记录的1/10系统就会自行重新计算音信.假如计算消息为null时系统蚕食OPTOMIZER_wwwlehu6.vip乐虎官网,DYNAMIC_SAMPLING
能够支配什么举办搜聚. Hint是 /*+
dynamic_sampling (table integer) */
详见TOP第五章1.3节

以下为采访后用来储存总计音信的视图:

  • user_tab_statistics –计算之后用来呈现表的总括消息 3.1.二
  • 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.叁

  • user_tab_histograms –总括之后用来体现直方图的总结音讯 三.壹.四
  • user_ind_statistics –总结之后用来展现索引的总括新闻 3.一.伍
  • 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<陆是等惊人直方图 n>=六 是功效直方图(精准))
作者的3个议论帖http://www.itpub.net/viewthread.php?tid=1247685&page=2\#pid14860199

method_opt=>’FO中华V ALL COLUMNS SIZE n’ — n<六是等惊人直方图 n>=六是功效直方图(精准)
不过列的绝无仅有值大于25四依然要使用等高直方图。当中n定义了bucket的取值范围一~25肆,oralce
依据这几个列的distinct值来总计bucket的个数(size 1例外它象征不成立直方图)
设若distinct > n
那么就动用等高直方图,就是说当有一列的唯一值大于254(最大允许的桶的数额)就不可能使用频率直方图了一旦distinct
<=n
那么就是用功效直方图,bucket数目是distinct~除了那几个选项还有多少个可选:

  • size repeat       刷新可用直方图
  • size skewonly 只搜聚非均匀分布的直方图,系统自动决定桶数
  • size auto         
    类似skewonly加上where短语引用的列根据二个列使用历史P1一五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
    

–自适应游标 1一g

1一g的自适应游标化解了下边包车型客车标题, 字典视图 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'
    

     

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图