当前位置：首页 > 数据库 > Oracle教程 > 微课sql优化(3)、统计信息收集(2)-如何收集统计信息 > 正文

微课sql优化(3)、统计信息收集(2)-如何收集统计信息

来源：这里教程网 时间：2026-03-03 15:13:30 作者：

3、如何收集统计信息

基于成本的优化器（CBO）使用统计信息来确定特定查询的执行计划。随着采样量的变化，采样可能产生不同的统计数据，改变SQL语句执行计划。
在11g时，建议使用计划的统计信息收集脚本收集统计信息。在大多数情况下，默认脚本提供足够的采样级别，并考虑以下建议：
  • 足够大的采样率
    在11g的支持下，建议对ESTIMATE_PERCENT使用默认DBMS_STATS.AUTO_SAMPLE_SIZE。这将为表生成100％的估计样本大小（如果可能的话可以在维护窗口中进行）。如果数据量较大，100%采样无法完成，至少使用30％的估计值，由于11g使用散列算法来计算统计量，因此在大多数情况下性能应该是可以接受的。
  • 所有对象收集统计信息
    确保所有对象（表和索引）都已收集统计信息。实现这一点的简单方法是使用CASCADE参数。
  • 倾斜数据列收集直方图
    确保任何具有偏斜数据分布的列都收集了直方图，并使用METHOD_OPT参数以足够的分辨率进行。通常，支持建议使用“AUTO”的默认列统计信息设置，这意味着DBMS_STATS将决定哪些列添加直方图，以确定它们可能有助于生成更好的计划。“，只有在已知需要的情况下才添加直方图”是一种保守和更加规划稳定的方法，而不是在所有列上收集列统计信息。
  • 收集分区对象的全局统计信息
    如果分区正在使用，如果可能，由于时间限制，收集全局统计信息。全球统计数据非常重要，但由于需要的大小和时间长短，往往会避免收集。如果100％的样品是不可能的，那么支持将建议至少为1％。收集小样本（例如0.001，0.0001，0.00001等）可以非常有效，但同样地，大部分数据将不被检查，这可能对优化器的计划选择是决定性的。请注意，ESTIMATE_PERCENT参数的可用范围是非常灵活的[0.000001 - > 100]，可以使用非常小的适用于巨大分区表的样本大小。测试将显示每个系统最合适的设置。

    4、统计信息收集语句

    非分区表：
    begin
     DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HT',
    tabname => 'TEST',
    estimate_percent => 30,
    method_opt => 'for all columns size repeat',
    no_invalidate => FALSE,
    degree => 4,
    cascade => TRUE);
    end;
    分区表：
    begin DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HT',
    tabname => 'TEST',
    estimate_percent => 30, --关于采集率：1、小于5G，100%收集;2、大于5G，30%收集，根据数据量逐渐降低
    method_opt => 'for all columns size repeat',--稳定系统建议使用repeat,有确定直方图收集的，单独执行
    no_invalidate => FALSE,
    degree => 8,                                             --关于并行度，建议系统总CPU/2,个人经验，建议不超过16
    granularity => 'ALL',
    cascade => TRUE
    );
    end;
    /

    参考文档：

    Oracle? Database PL/SQL Packages and Types Reference 11 g  Release 2 (11.2)

    5、练习2：统计信息收集

    CREATE TABLE ht.a_amt_p1
    (amt_id number primary key,
    cons_no number,
    amt_ym varchar2(20),
    amt number)
    PARTITION BY LIST (amt_ym)
    (
    PARTITION p01 VALUES ('201701'),
    PARTITION p02 VALUES ('201702'),
    PARTITION p03 VALUES ('201703'),
    PARTITION p04 VALUES ('201704'),
    PARTITION p05 VALUES ('201705'),
    PARTITION p06 VALUES  ('201706'));
    insert into ht.a_amt_p1 select * from ht.a_amt;
    请分别对表ht.a_amt、ht.a_amt_p1收集统计信息?
    SQL> set line 200
    col owner for a10
    col table_name for a20 
    col LAST_ANALYZED for a30
    select owner,table_name,NUM_ROWS,BLOCKS,PARTITIONED,SAMPLE_SIZE,to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi') LAST_ANALYZED from dba_tables where owner='HT';SQL> SQL> SQL> SQL> 
    OWNER       TABLE_NAME      NUM_ROWS     BLOCKS PARTITION SAMPLE_SIZE LAST_ANALYZED
    ---------- -------------------- ---------- ---------- --------- ----------- ------------------------------
    HT       A_AMT         59968      244 NO          59968 2017-08-17 22:01
    HT       C_CONS          9817       58 NO           2945 2017-08-17 13:17
    HT       A_AMT_BAK         59967      247 NO          59967 2017-08-17 22:01
    HT       TEST           77695     1261 NO          77695 2017-08-18 22:03
    HT       A_AMT_P1         59968      276 YES          59968 2017-08-19 18:02
    SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HT',tabname => 'A_AMT',estimate_percent => 100,method_opt => 'for all columns size repeat',no_invalidate => FALSE,degree => 4,cascade => TRUE);
    PL/SQL procedure successfully completed.
    SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HT',tabname => 'A_AMT_P1',estimate_percent => 100,method_opt => 'for all columns size repeat',no_invalidate => FALSE,degree => 4,granularity => 'ALL',cascade => TRUE);
    PL/SQL procedure successfully completed.
    SQL> set line 200
    col owner for a10
    col table_name for a20 
    col LAST_ANALYZED for a30
    select owner,table_name,NUM_ROWS,BLOCKS,PARTITIONED,SAMPLE_SIZE,to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi') LAST_ANALYZED from dba_tables where owner='HT';SQL> SQL> SQL> SQL> 
    OWNER       TABLE_NAME      NUM_ROWS     BLOCKS PARTITION SAMPLE_SIZE LAST_ANALYZED
    ---------- -------------------- ---------- ---------- --------- ----------- ------------------------------
    HT       A_AMT         59968      244 NO          59968 2017-08-19 18:33
    HT       C_CONS          9817       58 NO           2945 2017-08-17 13:17
    HT       A_AMT_BAK         59967      247 NO          59967 2017-08-17 22:01
    HT       TEST           77695     1261 NO          77695 2017-08-18 22:03
    HT       A_AMT_P1         59968      276 YES          59968 2017-08-19 18:33
    SQL> !date
    Sat Aug 19 18:34:27 CST 2017
    SQL> 

    6、小结

    1、统计信息采样率建议100%，如果数据量较大，至少建议30%。-- estimate_percent => 30, --关于采集率：1、小于5G，100%收集;2、大于5G，30%收集，根据数据量逐渐降低

    2、所有对象收集统计信息，使用 CASCADE参数。-- cascade => TRUE

    3、稳定系统直方图收集，建议 method_opt => 'for all columns size repeat'，否则根据需求收集。

    4、 分区表， 收集全局统计信息， -- granularity => 'ALL'。

    5、 关于并行度，建议系统总CPU/2,个人经验，建议不超过32 -- degree => 8,  

    • 编辑推荐：

    相关推荐

    最新软件资讯

    微课sql优化(6)、统计信息收集(4)-关于动态采样
    微课sql优化(6)、统计信息收集(4)-关于动态采样

    热文推荐

    天极热推