ORACLE 中dbms_stats的使用.doc

ORACLE 中dbms_stats的使用 Posted on 2009-02-07 15:11 Derek.Guo 阅读(153) 评论(0) 编辑 收藏 dbms_stats能良好地估计统计数据（尤其是针对较大的分区表），并能获得更好的统计结果，最终制定出速度更快的SQL执行计划。execdbms_stats.gather_schema_stats(ownname=SCOTT,options=GATHERAUTO,estimate_percent=dbms_stats.auto_sample_size,method_opt=forallcolumnssizerepeat,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 columnsdbms_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 skewonlymethod_opt=for all columns size repeatmethod_opt=for all columns size auto skewonly选项会耗费大量处理时间，因为它要检查每个索引中的每个列的值的分布情况。 假如dbms_stat发现一个索引的各个列分布得不均匀，就会为那个索引创建直方图，帮助基于代价的SQL优化器决定是进行索引访问，还是进行全表扫描访问。例如，在一个索引中，假定有一个列在50%的行中，如清单B所示，那么为了检索这些行，全表扫描的速度会快于索引扫描。-*-SKEWONLYoptionDetailedanalysis-Usethismethodforafirst-timeanalysisforskewedindexes-Thisrunsalongtimebecauseallindexesareexamined-*begindbms_stats.gather_schema_stats(ownname=SCOTT,estimate_percent=dbms_stats.auto_sample_size,method_opt=forallcolumnssizeskewonly,degree=7);end; 重新分析统计数据时，使用repeat选项，重新分析任务所消耗的资源就会少一些。使用repeat选项（清单C）时，只会为现有的直方图重新分析索引，不再搜索其他直方图机会。定期重新分析统计数据时，你应该采取这种方式。-*-REPEATOPTION-Onlyreanalyzehistogramsforindexes-thathavehistograms-Followingtheinitialanalysis,theweeklyanalysis-jobwillusethe“repeat”option.Therepeatoption-tellsdbms_statsthatnoindexeshavechanged,and-itwillonlyreanalyzehistogramsfor-indexesthathavehistograms.-*begindbms_stats.gather_schema_stats(ownname=SCOTT,estimate_percent=dbms_stats.auto_sample_size,method_opt=forallcolumnssizerepeat,degree=7);end; 使用alter table xxx monitoring;命令来实现Oracle表监视时，需要使用dbms_stats中的auto选项。如清单D所示，auto选项根据数据分布以及应用程序访问列的方式（例如通过监视而确定的一个列的工作量）来创建直方图。使用method_opt=auto类似于在dbms_stats的option参数中使用gather auto。begindbms_stats.gather_schema_stats(ownname=SCOTT,estimate_percent=dbms_stats.auto_sample_size,method_opt=forallcolumnssizeauto,degree=7);end;并行统计收集degree参数Oracle推荐设置DBMS_STATS的DEGREE参数为DBMS_STATS.AUTO_DEGREE，该参数允许Oracle根据对象的大小和并行性初始化参数的设置选择恰当的并行度。聚簇索引，域索引，位图连接索引不能并行收集。如何使用dbms_stats分析统计信息？-创建统计信息历史保留表 sqlexecdbms_stats.create_stat_table(ownname=scott,stattab=stat_table);-导出整个scheme的统计信息 sqlexecdbms_stats.export_schema_stats(ownname=scott,stattab=stat_table);-分析schemeExecdbms_stats.gather_schema_stats(ownname=scott,options=GATHERAUTO,estimate_percent=dbms_stats.auto_sample_size,method_opt=forallindexedcolumns,degree=6)-分析表sqlexecdbms_stats.gather_table_stats(ownname=scott,tabname=work_list,estimate_percent=10,method_opt=forallindexedcolumns);-分析索引SQLexecdbms_stats.gather_index_stats(ownname=crm2,indname=IDX_ADM_PERMISSION_PID_MID,estimate_percent=10,degree=4);-如果发现执行计划走错，删除表的统计信息SQLdbms_stats.delete_table_stats(ownname=scott,tabname=work_list);-导入表的历史统计信息sqlexecdbms_stats.import_table_stats(ownname=scott,tabname=work_list,stattab=stat_table);-如果进行分析后，大部分表的执行计划都走错，需要导回整个scheme的统计信息sqlexecdbms_stats.import_schema_stats(ownname=scott,stattab=stat_table);-导入索引的统计信息SQLexecdbms_stats.import_index_stats(ownname=crm2,indname=IDX_ADM_PERMISSION_PID_MID,stattab=stat_table)-检查是否导入成功SQLselecttable_name,num_rows,a.blocks,a.last_analyzedfromall_tablesawherea.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*fromDba_Scheduler_JobswhereJOB_NAME=GATHER_STATS_JOB其实同在10点运行的Job还有一个AUTO_SPACE_ADVISOR_JOB：SQLselectJOB_NAME,LAST_START_DATEfromdba_scheduler_jobs;JOB_NAMELAST_START_DATE-AUTO_SPACE_ADVISOR_JOB04-DEC-0710.00.00.692269PM+08:00GATHER_STATS_JOB04-DEC-0710.00.00.701152PM+08:00FGR$AUTOPURGE_JOBPURGE_LOG05-DEC-0703.00.00.169059AMPRC然而这个自动化功能已经影响了很多系统的正常运行，晚上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可以直接修改初始化参数文件，重新启动数据库。Oracle统计信息的导出、导入创建一个专用表保存各表的统计信息：exec dbms_stats.create_stat_table(ks38,fys);2 将各表的统计信息导入到ks38.fys表中；exec dbms_stats.export_table_stats(ks38,tcl_cust_base_info,null,fys); -可以创建不是登陆用户下的表；如用system登陆，可以创建表到ks38用户下或将指定schema的统计信息导入到ks38.fys表中exec dbms_stats.export_schema_stats(ks38,fys);注：若将整个数据库的统计信息导出到某个表必须按以下方式做：1）用SYSDBA角色的用户登陆（如system)2) exec dbms_stat.create_stat_table(system,tbl_name); -记住：此处创建的表必须是登陆用户下的表。 3) exec dbms_stat.export_database_stats(tbl_name)*导出表状态、SCHEMA统计信息，保存导出信息的表的SCHEMA可以和登陆用户名不一致；另外导入统计信息的表和原表尽可能是同一schema下的导出整个数据库统计信息，保存导出信息的表的SCHEMA必须和登陆用户名一致，必须是DBA角色。3 查看统计信息是否导入：select * from ks38.fys;4 删除某张表的统计信息：exec dbms_stats.delete_table_stats(ks38,tcl_cust_base_info);5 查看统计信息是否被删除：select table_name ,to_char(LAST_ANALYZED,yyyymmdd hh24:mi:ss) from ALL_tables where lower(table_name) LIKE tcl_cust_%_info%;6 导入先前导出的统计信息：exec dbms_stats.import_table_stats(ks38,tcl_cust_base_info,null,fys);注意：tcl_cust_base_info和fys都是在ks38用户下的表。 DBMS_STATS.GATHER_TABLE_STATS详解作用:DBMS_STATS.GATHER_TABLE_STATS统计表,列,索引的统计信息.DBMS_STATS.GATHER_TABLE_STATS的语法如下:DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, estimate_percent NUMBER, block_sample BOOLEAN, method_opt VARCHAR2, degree NUMBER, granularity VARCHAR2, cascade BOOLEAN, stattab VARCHAR2, statid VARCHAR2, statown VARCHAR2, no_invalidate BOOLEAN, force BOOLEAN); 参数说明:ownname:要分析表的拥有者tabname:要分析的表名.partname:分区的名字,只对分区表或分区索引有用.estimate_percent:采样行的百分比,取值范围0.000001,100,null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.block_sapmple:是否用块采样代替行采样.method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下:for all columns:统计所有列的histograms.for all indexed columns:统计所有indexed列的histograms.for all hidden columns:统计你看不到列的histogramsfor columns SIZE | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围1,254; REPEAT上次统计过的histograms;AUTO由oracle决定N的大小;SKEWONLY multiple end-points with the same value which is what we define by there is skew in the datadegree:决定并行度.默认值为null.granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned.cascace:是收集索引的信息.默认为falase.stattab指定要存储统计信息的表,statid如果多个表的统计信息存储在同一个stattab中用于进行区分.statown存储统计信息表的拥有者.以上三个参数若不指定,统计信息会直接更新到数据字典.no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.force:即使表锁住了也收集统计信息.例子:execute dbms_stats.gather_table_stats(ownname = owner,tabname = table_name ,estimate_percent = null ,method_opt = for all indexed columns ,cascade = true);如何收集及删除列的统计信息本文只涉及使用dbm_stats来收集或删除列的统计信息的一些命令，以备查询。测试表如下(实验环境为10.2.0.4):SQL create table test(i int,a varchar2(30);Table created.SQL insert into test select rownum,object_name from all_objects;9907 rows created.简单的说，列的统计信息，主要包括两种类型： 只有基本信息：收集的统计信息只有1个桶(bucket) 包含柱状图信息：收集的统计信息包含2到254个桶 也就是说，如果想收集列的基本信息，同时不希望收集柱状图，则需要指定bucket的size为1：SQL exec dbms_stats.gather_table_stats(user, TEST,cascade=false, method_opt=for columns i size 1);PL/SQL procedure successfully completed. SQL select column_name,num_distinct,num_buckets,low_value,high_value,density,avg_col_len,histogram 2 from user_tab_columns where table_name=TEST;COLUMN_NAM DISTINCT BUCKETS LOW_VALUE HIGH_VALUE DENSITY AVG_COL_LEN HISTOGRAM- - - - - - - -I 9907 1 C102 C26408 .000100939 4 NONEA NONE如果要收集列的柱状图信息，则bucket的个数必须大于等于2(最多不超过254)SQL exec dbms_stats.gather_table_stats(user, TEST,cascade=false, method_opt=for columns i size 2);PL/SQL procedure successfully completed.SQL select column_name,num_distinct,num_buckets,low_value,high_value,density,avg_col_len,histogram 2 from user_tab_columns where table_name=TEST;COLUMN DISTINCT BUCKETS LOW_VALUE HIGH_VALUE DENSITY AVG_COL_LEN HISTOGRAM- - - - - - - -I 9907 2 C102 C26408 .000100939 4 HEIGHT BALANCEDA NONE如果要删除列已有的柱状图信息而保留列的基本统计信息，则需要重新收集bucket为1的统计信息SQL exec dbms_stats.gather_table_stats(user, TEST,cascade=false, method_opt=for columns i size 1);PL/SQL procedure successfully completed.SQL select column_name,num_distinct,num_buckets,low_value,high_value,density,avg_col_len,histogram 2 from user_tab_columns where table_name=TEST;COLUMN DISTINCT BUCKETS LOW_VALUE HIGH_VALUE DENSITY AVG_COL_LEN HISTOGRAM- - - - - - - -I 9907 1 C102 C26408 .000100939 4 NONEA NONE这个操作明显不太合理，重新收集统计信息的代价有时候是很大的，所以Oracle11g对此做出了改进，允许只删除柱状图而保留基本统计信息，命令语法如下：exec dbms_stats.delete_column_stats(user, TEST,I,col_stat_type=HISTOGRAM);而要彻底删除整个列的统计信息，则需要调用delete_column_stats过程SQL exec dbms_stats.delete_column_stats(user, TEST, I);PL/SQL procedure successfully completed.SQL select column_name,num_distinct,num_buckets,low_value,high_value,density,avg_col_len,histogram 2 from user_tab_columns where table_name=TEST;COLUMN DISTINCT BUCKETS LOW_VALUE HIGH_VALUE DENSITY AVG_COL_LEN HISTOGRAM- - - - - - - -I NONEA NONE可以在同一个过程中收集多个列的统计信息，并且可以为不同的列指定不同的bucket个数：SQL exec dbms_stats.gather_table_stats(user, TEST,cascade=false, method_opt = for columns size 1 T for columns size 2 A);PL/SQL procedure successfully completed.SQL select column_name,num_distinct,num_buckets,low_value,high_value,density,avg_col_len,histogram 2 from user_tab_columns where table_name=TEST;COLUMN DISTINCT BUCKETS LOW_VALUE HIGH_VALUE DENSITY AVG_COL_LEN HISTOGRAM- - - - - - - -I 9907 1 C102 C26408 .000100939 4 NONEA 7376 2 41 5F75746C245F6C6E635F .000185239 18 HEIGHT BALANCED 696E645F7061727473值得注意的是，9i的dbms_stats中，method_opt的默认值是FOR ALL COLUMNS SIZE 1，也就是收集列的基本统计信息而不收集柱状图信息，而10g的默认值则变成了FOR ALL COLUMNS SIZE AUTO，则Oracle在收集列的基本信息之外，还会根据情况收集某些列的柱状图。注：由于排版的原因，部分查询结果的列名做了处理。Performance Tuning Guide的Managing Optimizer Statistics仍然是翻译加总结：*1 理解statisticsoptimizer statistics用于查询优化器为SQL语句选择最好的执行计划，包括：表统计：行数、块数、行平均长度列统计：不同的值的数目、null的数目、数据分布（直方图）索引统计：叶节点块的数目、深度、聚集因子系统统计：I/)性能和利用率、CPU性能和利用率注：这些统计是用于优化查询的，存储于数据字典中，不同于V$性能视图。由于数据库中的对象时刻在变动着，所以统计信息必须经常更新以反应数据库的实际状况，这可以通过dbms_stats包进行自动或手动维护。*2 自动收集统计信息建议以自动方式收集统计信息，可以通过job实现这个目标。实际上对于自己的应用应该自己手工统计。(1) gather_stats_job这个任务在维护窗口打开的时候自动运行，维护窗口打开的时间是每晚10点到次日6点和周末全天。GATHER_DATABASE_STATS_JOB_PROC过程能够选择性地收集和更新统计信息，它自动选择那些最需要统计的对象。操作方法类似于使用了GATHER AUTO选项的GATHER_DATABASE_STATS过程(2) 开始自动收集统计信息自动统计信息收集的任务在数据库创建后即开启，可以通过查询dba_sheduler_jobs视图确认其是否存在：SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = GATHER_STATS_JOB;如果要取消这个任务，只要禁止GATHER_STATS_JOB 即可：BEGINDBMS_SCHEDULER.DISABLE(GATHER_STATS_JOB);END;/注意：开启此功能需要设置STATISTICS_LEVEL 参数为 TYPICAL 或 ALL(3) 何时收集统计信息自动统计对于大多数对象足够了，但是有些情况下还不够。由于自动统计是在夜间进行的，如果对象在白天发生了显著的变化，那么统计信息就会过时，不能代表工作时间的负载。这种情况下有两种解决方法：一个是设置统计为NULL，这样当Oracle发现一个表没有统计信息时自动收集必要的统计。这种动态采样的特性通过OPTIMIZER_DYNAMIC_SAMPLING参数设置。BEGINDBMS_STATS.DELETE_TABLE_STATS(OE,ORDERS);DBMS_STATS.LOCK_TABLE_STATS(OE,ORDERS);END;/另一个是设置统计为典型值，这需要清楚代表性的数据。*3 手工收集统计信息如果禁用了自动收集统计信息，就要手工进行统计。数据经常发生变化的系统需要经常统计以保证统计信息的准确性。(1) 使用DBMS_STATS包统计当产生新的统计信息后，原有的统计信息会被保存。当对象的统计信息更新后，针对此对象的所有解析过的SQL语句将失效，下次执行这些SQL语句前必须对它们进行重新解析，优化器根据新的统计信息选择新的执行计划。新的统计值只对下次SQL语句执行时有效。Statistics Gathering Procedures in the DBMS_STATS PackageProcedure CollectsGATHER_INDEX_STATS Index statisticsGATHER_TABLE_STATS Table, column, and index statisticsGATHER_SCHEMA_STATS Statistics for all objects in a schemaGATHER_DICTIONARY_STATS Statistics for all dictionary objectsGATHER_DATABASE_STATS Statistics for all objects in a database使用它们时，需要考虑以下几个问题：estimate_percent：使用采样收集统计。采样是收集统计的重要手段，能够避免全表扫描，减少资源使用量。采样通过ESTIMATE_PERCENT参数确定，这个值可以为0.000001,100，建议设置为DBMS_STATS.AUTO_SAMPLE_SIZE，由Oracle根据统计属性来确定最合适的采样大