当前位置：首页 > 数据库 > Oracle教程 > 微课sql优化(2)-为什么需要收集统计信息 > 正文

微课sql优化(2)-为什么需要收集统计信息

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

1、为什么需要收集统计信息

Cost-based optimizer (CBO)，Based on object statistics，优化器统计信息描述数据库中的对象的详细信息， 查询优化器使用这些统计信息为每个SQL语句选择最佳执行计划。
数据库将优化器统计信息存储在数据字典中。您可以使用数据字典视图访问这些统计信息。
由于数据库中的对象可能会不断更改，因此必须定期更新统计信息，以便准确描述这些对象。Oracle数据库自动维护优化器统计信息。
11g：  Scheduled Maintenance Window Times ， In 11g daily maintenance windows are provided. by default these are defined as :
Weeknights: Starts at 10 p.m. and ends at 2 a.m.
Weekends: Starts at 6 a.m. is 20 hours long.
10g： With Oracle Database 10g the default maintenance window is configured to cover the following periods:
10 pm to 6 am every weekday
All weekend (Friday 10 pm to Monday 6 am)
可以使用该DBMS_STATS软件包手动维护优化器统计信息。

2、哪些表需要收集统计信息

自动统计任务能满足大部分数据库的业务需求，但存在特殊情况需要手工收集统计信息，
1、业务表数据被delete、truncate、impdp/imp大批量数据变理时。
2、数据变更量超过10%时。
查询哪些表需要收集统计信息
col table_name for a30
col object_type for a30
select owner, table_name, object_type, stale_stats, to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi') last_analyzed
from dba_tab_statistics
where  owner = 'HT'
and (stale_stats = 'YES' or last_analyzed is null);

3、练习题1、测试统计信息阈值： 变更量超过10%

  • 创建测试表： 
    create table ht.test as select * from dba_objects;
  • 收集统计信息：
    exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HT',tabname => 'TEST',method_opt => 'for all columns size repeat',no_invalidate => FALSE,degree => 4,cascade => TRUE);
  • 删除10%数据：
    SQL> DELETE FROM ht.TEST WHERE ROWNUM<=(select count(1) from ht.TEST)*0.1;
    8632 rows deleted.
    SQL> commit;
    Commit complete.
    exec dbms_stats.flush_database_monitoring_info;  --刷新变更信息
  • 查看数据变更信息
    set line 200
    col table_owner for a10
    col table_name for a30
    col pname for a20
    col spname for a20
    select table_owner,table_name,partition_name pname,subpartition_name spname,inserts,updates,deletes from dba_tab_modifications
    where table_owner='HT';
    TABLE_OWNE      TABLE_NAME   PNAME         SPNAME      INSERTS          UPDATES      DELETES
    ------ --- --- --- ----    ---------------- -----------   --------------    -- -- ----------    -- ----------     -- -- -- ----------
    HT                        TEST                                                      0                0                8632
  • 查看统计信息是否过期
    col table_name for a30
    col object_type for a30
    COL last_analyzed for a20
    select owner, table_name, object_type, stale_stats, to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi') last_analyzed
    from dba_tab_statistics
    where  owner = 'HT' AND TABLE_NAME='TEST';
    OWNER      TABLE_NAME           OBJECT_TYPE          STALE_STA     LAST_ANALYZED
    ---------- ---------------------- ------------------------------ ---------           --------------------
    HT               TEST                    TABLE                       NO              2017-08-18 21:26
  • 再删除一行数据：
    DELETE FROM ht.TEST WHERE ROWNUM=1;
    exec dbms_stats.flush_database_monitoring_info;  --刷新变更信息
  • 再次查看统计信息是否过期
    col table_name for a30
    col object_type for a30
    COL last_analyzed for a20
    select owner, table_name, object_type, stale_stats, to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi') last_analyzed
    from dba_tab_statistics
    where  owner = 'HT' AND TABLE_NAME='TEST';

    4、小结

    哪些情况需要从新收集统计信息,
    1、业务表数据被delete、truncate、impdp/imp大批量数据变更时。
    2、数据变更量超过10%时,统计信息状态标记为过期 。

    • 编辑推荐：

    相关推荐

    最新软件资讯

    微课sql优化(11) 、如何查看执行计划
    微课sql优化(11) 、如何查看执行计划

    热文推荐

    天极热推