`

oracle收集统计信息

 
阅读更多

转自http://blog.sina.com.cn/s/blog_69e7b8d701019cal.html

这里的统计信息指的是优化器(OPTIMIZER)统计信息,当Oracle数据库工作在CBO(COST BASED OPTIMIZER)模式下,优化器根据数据字典中记录的对象统计信息,评估SQL语句不同执行计划的成本,从而找到最优或者是相对最优的执行计划。从这一点来看统计信息的准确性就显得非常重要了,尤其是对于oracle10g以后的数据库,因为oracle10g以后,Oracle默认的优化器模式是all_rows,也就是基于成本的优化器。

优化器工作原理图:

Oracle优化器统计信息收集

统计信息包含的内容很多,比如表的,索引的,列的,列的直方图的等

Oracle优化器统计信息收集

收集Oracle数据库的统计信息,方法很多
1.oracle自动收集统计信息
     a.oracle 10g,调用用gather_stats_job自动收集统计信息
        工作日每天晚上10点到第二天早上六点,周末全天,oracle自动收集没有统计信息的和统计信息过

        旧的对象的统计信息
        这样做可能会对性能有影响,收集统计信息是要消耗大量资源的,所以很多DBA选择关掉这个job,

        采用手动收集统计信息
        默认这个job是打开的,关掉的方法如下:
        EXEC DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

     b.oracle 11g,使用自动任务来完成,并且窗口细化到每天一个,当然时间也可以调整
       SQL> select client_name from dba_autotask_client;

        CLIENT_NAME
        ----------------------------------------------------------------
        auto optimizer stats collection
        auto space advisor
        sql tuning advisor
        默认这个自动任务是打开的,当然也可以关掉
        禁用任务:
        BEGIN
        dbms_auto_task_admin.disable(
            client_name => 'auto optimizer stats collection',
            operation   => NULL,
            window_name => NULL);
        END;
        启用任务:
        BEGIN
        dbms_auto_task_admin.enable(
            client_name => 'auto optimizer stats collection',
            operation   => NULL, 
            window_name => NULL);
        END;
               

2.用户手动收集统计信息
      手动收集统计信息适合于对于新建表、刚做完数据装载的表和表里数据变化比较频繁的

      表(一天超过10%),如果仅仅是为了收集统计信息,建议使用dbms_stats包来实现
  a.使用DBMS_STATS程序包
         对于新建的表,默认是没有统计信息的
         SQL> create table e as select empno,ename,sal from emp where rownum<=8;
         Table created.
        
         SQL> select table_name,num_rows,

           2  blocks,to_char(last_analyzed,'yyyy-mm-ddhh24:mi:ss')          
           3  from user_tables
           4  where table_name='E';
        
         TABLE_NAME                       NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZ
         ------------------------------ ---------- ---------- -------------------
         E
         使用exec dbms_stats.gather_table_stats收集统计信息
         SQL> exec dbms_stats.gather_table_stats('SCOTT','E');
          PL/SQL procedure successfully completed.
        
         SQL> select table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss')
           2  from user_tables
           3  where table_name='E';
        
         TABLE_NAME                       NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZ
         ------------------------------ ---------- ---------- -------------------
         E                                       8          4 2012-10-31 11:06:56
        
  b.ANALYZE 命令
         往表里插入一条记录,在没有对表从新分析前,user_tables里的统计信息是不变的,或者说不准确的
         SQL> insert into e select empno,ename,sal from emp where rownum=1;
         1 row created.
        
         SQL> commit;
         Commit complete.
        
         SQL> select table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss')
           2  from user_tables
           3  where table_name='E';
         TABLE_NAME                       NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZ
         ------------------------------ ---------- ---------- -------------------
         E                                       8          4 2012-10-31 11:06:56
        
         使用analyze table 收集表的统计信息,下面的两个语句使用其中一个就可以了
         compute 完全采样,也就是100%,适合于小表和索引
         SQL> analyze table e compute statistics; 
              Table analyzed.
         或者
         estimate 部分采样,可以指定采样比率,本例采样20%,适合于大表
         SQL> analyze table e estimate statistics sample 20 percent; 
         Table analyzed.
        
         不管使用上面语句当中的哪一个,都会看到准确的统计信息
         SQL> select table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss')
           2  from user_tables
           3   where table_name='E';
         TABLE_NAME                       NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZ
         ------------------------------ ---------- ---------- -------------------
         E                                       9          8 2012-10-31 11:13:48
        

3.动态采样
    对于没有统计信息的表,oracle为了能够得到相对准确的执行计划,会在执行SQL语句之前对语句设计

    到的表做动态采样
    Oracle优化器统计信息收集

    从10g开始,oracle默认把optimizer_dynamic_sampling的等级设为2,那就会出现如果对象没有统计信

    息,oracle就会在每次查询的时候进行采样,所以oracle看起来会相当的智能。
    SQL> show parameter dyna
         NAME                                 TYPE        VALUE
         ------------------------------------ ----------- ------------------------------
         optimizer_dynamic_sampling           integer     2

分享到:
评论

相关推荐

    使用dbms_stats包手工收集统计信息

    Oracle 数据库中使用 dbms_stats 包手动收集统计信息 在 Oracle 数据库中,dbms_stats 包提供了一种手动收集统计信息的方式,包括基于表、用户和索引的统计信息。通过使用 dbms_stats 包,我们可以手动收集统计信息...

    oracle 性能数据自动收集脚本

    10.Oracle深度学习笔记——Oracle自动统计数据采集脚本 的附件

    oracle 自动收集统计信息 测试

    Oracle 自动收集统计信息测试 Oracle 自动收集统计信息是 Oracle 数据库中的一种功能,可以自动收集数据库中的统计信息,以便提高查询性能和优化数据库性能。在本文中,我们将对 Oracle 自动收集统计信息进行测试,...

    Oracle 11G统计信息自动收集及调整

    文档里包含Oracle 11G统计信息自动收集及调整,可以使用优化数据库性能,不合理的统计信息会影响数据库性能

    Oracle 11g收集各种统计信息(DBMS_STAT)

    在Oracle 7版本中,analyze语句被用来收集统计信息,但在Oracle 8.1.5引入DBMS_STATS后,Oracle开始推荐使用这个包,因为它提供了更高级的功能和控制。 DBMS_STATS的主要功能是分析表、索引和其他数据库对象,以...

    Oracle Statistic 统计信息

    Oracle建议使用`DBMS_STATS`包来收集统计信息,因为它能够提供更全面和准确的数据。例如: ```sql EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME'); ``` 其中,`SCHEMA_NAME`是模式名称,`...

    Oracle 统计用户下表的数据量实现脚本

    在Oracle数据库管理中,有时需要统计特定用户下的所有表的数据量。这有助于了解数据库的存储...同时,这种方法也鼓励用户定期检查和更新他们的统计信息,以保持与实际数据量的一致性,从而提高查询性能和计划存储需求。

    oracle 11g 统计信息

    ### Oracle 11g 自动收集统计信息的时间 在Oracle 11g中,自动收集统计信息是一项重要的数据库维护任务,它有助于优化查询性能并确保数据的准确性。本篇文章将详细介绍Oracle 11g如何自动收集统计信息以及如何管理...

    电信设备-一种Oracle统计信息自动收集方法.zip

    本资料主要探讨了一种针对Oracle数据库的自动收集统计信息的方法,这对于确保查询优化器做出正确的执行计划选择,进而提高整体系统性能具有重要意义。 Oracle统计信息主要包括表、索引、分区以及列的统计信息。这些...

    查看自动收集统计信息是否开启(1).sql

    查看自动收集统计信息是否开启(1).sql

    解析:ORACLE性能调整--统计信息的迁移

    统计信息包含表的行数、列的分布情况等,它们被用于Oracle的自动优化器来确定执行查询的最佳方式。 当面临测试环境与生产环境性能差异的问题时,迁移生产系统的统计信息就成为了一个有效的解决方案。以下是实现这一...

    ORACLE 数据库的统计数据及优化

    - **method_opt选项**:此参数用于指定如何收集统计信息,特别适用于处理数据变化较大的场景。例如,`FORALL COLUMNS SIZE REPEAT`表示对所有列重复收集统计信息,以适应列值分布的变化。 #### 六、示例代码 ```sql...

    结合实例深入讲解oracle中的直方图histogram

    直方图是一种统计图表,在多种领域中都有广泛的应用,它并非Oracle数据库所独有的功能。直方图通常用来描绘一组数据的分布情况,通过一系列相连的矩形表示不同区间的频数或频率。在Oracle数据库中,直方图被用来描述...

    oracle-认证之管理统计信息

    - **自动收集**:当参数`STATISTICS_LEVEL`设置为`TYPICAL`或`ALL`时,系统会在夜间自动收集统计信息。 - **手动收集**:对于更新频繁的对象,建议手动触发统计信息的收集以保持统计信息的新鲜度。 - 使用`BEGIN ...

    Oracle 11g收集多列统计信息详解

    Oracle 11g中的收集多列统计信息是一个关键特性,对于优化查询性能至关重要。在数据库管理系统中,查询优化器(如Oracle的CBO)通过计算SQL语句的执行成本来选择最佳执行计划。成本通常是基于CPU和I/O的消耗估计,以...

    ash-total.sql-收集Oracle ash统计信息

    @ash_total.sql 第一个参数:几个小时之前开始的,即开始时间。 第二个参数:收集多久的ash,即收集多长时间。 第三个参数:按多少分钟分类展示。

    Oracle表删除大量数据(千万)后查询变慢问题(原因分析)

    此外,Oracle 还提供了 dbms_stats 包,用于收集统计信息。dbms_stats 包可以替代 analyze 语句的部分功能,并且提供了更多的功能。 在实际应用中,了解 Oracle 分析表的知识对数据库管理员非常重要,可以帮助他们...

    平安Oracle CBO 资料.doc

    - 开发者须与DBA协商,评估DML操作、表结构变更是否需要收集统计信息。 - 收集脚本需由DBA提供,随版本测试后,方可生产部署。 - 脚本命名需遵循特定格式,确保可追溯性。 - 部署前需提交《生产库CBO统计信息...

Global site tag (gtag.js) - Google Analytics