logo资料库

dbms_stats.docx

发布时间：2022-06-24 发布人：admin 分类：说明书 资料大小：0.03M 资料格式：docx 举报 版权申诉
第1页 / 共8页
第2页 / 共8页
第3页 / 共8页
第4页 / 共8页
第5页 / 共8页
第6页 / 共8页
第7页 / 共8页
第8页 / 共8页
资料共8页，全文预览结束
    ORACLE 中 dbms_stats 的使用 dbms_stats 能良好地估计统计数据（尤其是针对较大的分区表），并能获得更好的统计结果， 最终制定出速度更快的 SQL 执行计划。 exec dbms_stats.gather_schema_stats( ownname => 'SCOTT', options => 'GATHER AUTO', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size repeat', degree => 15 ) 为了充分认识 dbms_stats 的好处，需要仔细体会每一条主要的预编译指令（directive）。下 面让我们研究每一条指令，并体会如何用它为基于代价的 SQL 优化器收集最高质量的统计数据。 options 参数 使用 4 个预设的方法之一，这个选项能控制 Oracle 统计的刷新方式： gather——重新分析整个架构（Schema）。 gather empty——只分析目前还没有统计的表。 gather stale——只重新分析修改量超过 10%的表（这些修改包括插入、更新和删除）。 gather auto——重新分析当前没有统计的对象，以及统计数据过期（变脏）的对象。注意，使 用 gather auto 类似于组合使用 gather stale 和 gather empty。 注意，无论 gather stale 还是 gather auto，都要求进行监视。如果你执行一个 alter table xxx monitoring 命令，Oracle 会用 dba_tab_modifications 视图来跟踪发生变动的表。这样一 来，你就确切地知道，自从上一次分析统计数据以来，发生了多少次插入、更新和删除操作。 estimate_percent 选项 estimate_percent 参数是一种比较新的设计，它允许 Oracle 的 dbms_stats 在收集统计数 据时，自动估计要采样的一个 segment 的最佳百分比： estimate_percent => dbms_stats.auto_sample_size
    要验证自动统计采样的准确性，你可检视 dba_tables sample_size 列。一个有趣的地方是， 在使用自动采样时，Oracle 会为一个样本尺寸选择 5 到 20 的百分比。记住，统计数据质量越 好，CBO 做出的决定越好。 method_opt 选项 method_opt：for table --只统计表 for all indexed columns --只统计有索引的表列 for all indexes --只分析统计相关索引 for all columns dbms_stats 的 method_opt 参数尤其适合在表和索引数据发生变化时刷新统计数据。 method_opt 参数也适合用于判断哪些列需要直方图（histograms）。 某些情况下，索引内的各个值的分布会影响 CBO 是使用一个索引还是执行一次全表扫描的决策。 例如，假如在 where 子句中指定的值的数量不对称，全表扫描就显得比索引访问更经济。 如果你有一个高度倾斜的索引（某些值的行数不对称），就可创建 Oracle 直方图统计。但在现 实世界中，出现这种情况的机率相当小。使用 CBO 时，最常见的错误之一就是在 CBO 统计中 不必要地引入直方图。根据经验，只有在列值要求必须修改执行计划时，才应使用直方图。 为了智能地生成直方图，Oracle 为 dbms_stats 准备了 method_opt 参数。在 method_opt 子句中，还有一些重要的新选项，包括 skewonly，repeat 和 auto： method_opt=>'for all columns size skewonly' method_opt=>'for all columns size repeat' method_opt=>'for all columns size auto' skewonly 选项会耗费大量处理时间，因为它要检查每个索引中的每个列的值的分布情况。 假如 dbms_stat 发现一个索引的各个列分布得不均匀，就会为那个索引创建直方图，帮助基于 代价的 SQL 优化器决定是进行索引访问，还是进行全表扫描访问。例如，在一个索引中，假定 有一个列在 50%的行中，如清单 B 所示，那么为了检索这些行，全表扫描的速度会快于索引扫 描。
    --************************************************************* -- SKEWONLY option—Detailed analysis -- -- Use this method for a first-time analysis for skewed indexes -- This runs a long time because all indexes are examined --************************************************************* begin dbms_stats.gather_schema_stats( ownname => 'SCOTT', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size skewonly', degree => 7 ); end; 重新分析统计数据时，使用 repeat 选项，重新分析任务所消耗的资源就会少一些。使用 repeat 选项（清单 C）时，只会为现有的直方图重新分析索引，不再搜索其他直方图机会。定期重新分 析统计数据时，你应该采取这种方式。 --************************************************************** -- REPEAT OPTION - Only reanalyze histograms for indexes -- that have histograms -- -- Following the initial analysis, the weekly analysis -- job will use the “repeat” option. The repeat option -- tells dbms_stats that no indexes have changed, and -- it will only reanalyze histograms for -- indexes that have histograms.
    --************************************************************** begin dbms_stats.gather_schema_stats( ownname => 'SCOTT', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size repeat', degree => 7 ); end; 使用 alter table xxx monitoring;命令来实现 Oracle 表监视时，需要使用 dbms_stats 中的 auto 选项。如清单 D 所示，auto 选项根据数据分布以及应用程序访问列的方式（例如通过监 视而确定的一个列的工作量）来创建直方图。使用 method_opt=>’auto’类似于在 dbms_stats 的 option 参数中使用 gather auto。 begin dbms_stats.gather_schema_stats( ownname => 'SCOTT', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size auto', degree => 7 ); end; 并行统计收集 degree 参数 Oracle 推荐设置 DBMS_STATS 的 DEGREE 参数为 DBMS_STATS.AUTO_DEGREE，该参 数允许 Oracle 根据对象的大小和并行性初始化参数的设置选择恰当的并行度。 聚簇索引，域索引，位图连接索引不能并行收集。
    如何使用 dbms_stats 分析统计信息？ --创建统计信息历史保留表 sql> exec dbms_stats.create_stat_table(ownname => 'scott',stattab => 'stat _table') ; --导出整个 scheme 的统计信息 sql> exec dbms_stats.export_schema_stats(ownname => 'scott',stattab => ' stat_table') ; --分析 scheme Exec dbms_stats.gather_schema_stats( ownname => 'scott', options => 'GATHER AUTO', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all indexed columns ', degree => 6 ) --分析表 sql> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'w ork_list',estimate_percent => 10,method_opt=> 'for all indexed columns') ; --分析索引 SQL> exec dbms_stats.gather_index_stats(ownname => 'crm2',indname => ' IDX_ADM_PERMISSION_PID_MID',estimate_percent => '10',degree => '4') ;
    --如果发现执行计划走错，删除表的统计信息 SQL>dbms_stats.delete_table_stats(ownname => 'scott',tabname => 'work_li st') ; --导入表的历史统计信息 sql> exec dbms_stats.import_table_stats(ownname => 'scott',tabname => 'w ork_list',stattab => 'stat_table') ; --如果进行分析后，大部分表的执行计划都走错，需要导回整个 scheme 的统计信息 sql> exec dbms_stats.import_schema_stats(ownname => 'scott',stattab => ' stat_table'); --导入索引的统计信息 SQL> exec dbms_stats.import_index_stats(ownname => 'crm2',indname => ' IDX_ADM_PERMISSION_PID_MID',stattab => 'stat_table') --检查是否导入成功 SQL> select table_name,num_rows,a.blocks,a.last_analyzed from all_tables a where a.table_name='WORK_LIST'; 分析数据库（包括所有的用户对象和系统对象）：gather_database_stats 分析用户所有的对象（包括表、索引、簇）：gather_schema_stats 分析表：gather_table_stats
    分析索引：gather_index_stats 删除数据库统计信息：delete_database_stats 删除用户方案统计信息：delete_schema_stats 删除表统计信息：delete_table_stats 删除索引统计信息：delete_index_stats 删除列统计信息：delete_column_stats 设置表统计信息：set_table_stats 设置索引统计信息：set_index_stats 设置列统计信息：set_column_stats 从 Oracle Database 10g 开始，Oracle 在建库后就默认创建了一个名为 GATHER_STATS_JOB 的定时任务，用于自动收集 CBO 的统计信息。 这个自动任务默认情况下在工作日晚上 10：00-6：00 和周末全天开启。调用 DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC 收集统计信息。 该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级，再开始进行统计信息。 可以通过以下查询这个 JOB 的运行情况： select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB' 其实同在 10 点运行的 Job 还有一个 AUTO_SPACE_ADVISOR_JOB： SQL> select JOB_NAME,LAST_START_DATE from dba_scheduler_jobs; JOB_NAME LAST_START_DATE ------------------------------ ---------------------------------------- AUTO_SPACE_ADVISOR_JOB 04-DEC-07 10.00.00.692269 PM +08:00 GATHER_STATS_JOB 04-DEC-07 10.00.00.701152 PM +08:00
    FGR$AUTOPURGE_JOB PURGE_LOG 05-DEC-07 03.00.00.169059 AM PRC 然而这个自动化功能已经影响了很多系统的正常运行，晚上 10 点对于大部分生产系统也并非空 闲时段。 而自动分析可能导致极为严重的闩锁竞争，进而可能导致数据库 Hang 或者 Crash。 所以建议最好关闭这个自动统计信息收集功能 方法之一: exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB'); 恢复自动分析: exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB'); 方法二： alter system set "_optimizer_autostats_job"=false scope=spfile; alter system set "_optimizer_autostats_job"=true scope=spfile; Pfile 可以直接修改初始化参数文件，重新启动数据库。
    分享到：
    赞 收藏

    相关推荐

    资料库

    数据库

    共收录1218份资料，累计14个分类，关注成员有19位，主要包括：NoSQL,MongoDB,Redis,SQLite,Memcached,DB2,Sybase,Informix,PostgreSQL,MySQL,SQLServer,Access,Oracle,其它

    热门标签

    NoSQL MongoDB Redis SQLite Memcached DB2 Sybase Informix PostgreSQL MySQL SQLServer Access Oracle 其它

    最新资料