【Oracle】DBMS_STATS.GATHER_SCHEMA_STATS详解

dbms_stats能好好地估计统计数据(尤其是针对性于充分的分区表),并会博取重新好之统计结果,最终制定有速度又快之SQL执行计划。

exec dbms_stats.gather_schema_stats

(
wwwlehu6.vip乐虎官网 1 ownname          => ‘SCOTT’,
  options          => ‘GATHER AUTO’,
  estimate_percent => dbms_stats.auto_sample_size,
  method_opt       => ‘for all columns size repeat’,
  degree           => 15
wwwlehu6.vip乐虎官网 2)

      
为了充分认识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所著,那么为追寻这些实践,全表扫描的速度会急忙为找引围观。

wwwlehu6.vip乐虎官网 3–*************************************************************
wwwlehu6.vip乐虎官网 4— SKEWONLY option—Detailed analysis
wwwlehu6.vip乐虎官网 5
wwwlehu6.vip乐虎官网 6— Use this method for a first-time analysis for skewed indexes
wwwlehu6.vip乐虎官网 7— This runs a long time because all indexes are examined
wwwlehu6.vip乐虎官网 8–*************************************************************
wwwlehu6.vip乐虎官网 9 
wwwlehu6.vip乐虎官网 10begin
wwwlehu6.vip乐虎官网 11  dbms_stats.gather_schema_stats(
wwwlehu6.vip乐虎官网 12     ownname          => ‘SCOTT’,
wwwlehu6.vip乐虎官网 13     estimate_percent => dbms_stats.auto_sample_size,
wwwlehu6.vip乐虎官网 14     method_opt       => ‘for all columns size skewonly’,
wwwlehu6.vip乐虎官网 15      degree           => 7
wwwlehu6.vip乐虎官网 16   );
wwwlehu6.vip乐虎官网 17end;

       
重新分析统计数据时,使用repeat选项,重新分析任务所吃的资源就会少一些。使用repeat选项(清单C)时,只见面为依存的直方图再次分析索引,不再找其他直方图机会。定期重新分析统计数据时,你该用这种措施。

wwwlehu6.vip乐虎官网 18–**************************************************************
wwwlehu6.vip乐虎官网 19— REPEAT OPTION – Only reanalyze histograms for indexes
wwwlehu6.vip乐虎官网 20— that have histograms
wwwlehu6.vip乐虎官网 21
wwwlehu6.vip乐虎官网 22— Following the initial analysis, the weekly analysis
wwwlehu6.vip乐虎官网 23— job will use the “repeat” option. The repeat option
wwwlehu6.vip乐虎官网 24— tells dbms_stats that no indexes have changed, and
wwwlehu6.vip乐虎官网 25— it will only reanalyze histograms for
wwwlehu6.vip乐虎官网 26— indexes that have histograms.
wwwlehu6.vip乐虎官网 27–**************************************************************
wwwlehu6.vip乐虎官网 28begin
wwwlehu6.vip乐虎官网 29   dbms_stats.gather_schema_stats(
wwwlehu6.vip乐虎官网 30      ownname          => ‘SCOTT’,
wwwlehu6.vip乐虎官网 31      estimate_percent => dbms_stats.auto_sample_size,
wwwlehu6.vip乐虎官网 32      method_opt       => ‘for all columns size repeat’,
wwwlehu6.vip乐虎官网 33      degree           => 7
wwwlehu6.vip乐虎官网 34   );
wwwlehu6.vip乐虎官网 35end;
wwwlehu6.vip乐虎官网 36

       使用alter table xxx
monitoring;命令来促成Oracle表监视时,需要以dbms_stats中的auto选项。如清单D所示,auto选项根据数据分布以及应用程序访问列的方式(例如通过监视而规定的一个排列的工作量)来创造直方图。使用method_opt=>’auto’类似于以dbms_stats的option参数中采取gather
auto。

wwwlehu6.vip乐虎官网 37begin
wwwlehu6.vip乐虎官网 38  dbms_stats.gather_schema_stats(
wwwlehu6.vip乐虎官网 39     ownname          => ‘SCOTT’,
wwwlehu6.vip乐虎官网 40      estimate_percent => dbms_stats.auto_sample_size,
wwwlehu6.vip乐虎官网 41      method_opt       => ‘for all columns size auto’,
wwwlehu6.vip乐虎官网 42      degree           => 7
wwwlehu6.vip乐虎官网 43   );
wwwlehu6.vip乐虎官网 44end;


相互之间统计收集degree参数

Oracle推荐设置DBMS_STATS的DEGREE参数为DBMS_STATS.AUTO_DEGREE,该参数允许Oracle根据目标的深浅及并行性初始化参数的安选择适宜的连行度。
聚簇索引,域索引,位图连接索引不可知互相收集。

 

怎么样使用dbms_stats分析统计信息?
–创建统计信息历史保留表 

wwwlehu6.vip乐虎官网 45sql> exec dbms_stats.create_stat_table(ownname => ‘scott’,stattab => ‘stat_table’) ; 

–导出所有scheme的统计信息 

wwwlehu6.vip乐虎官网 46sql> exec dbms_stats.export_schema_stats(ownname => ‘scott’,stattab => ‘stat_table’) ; 

–分析scheme

wwwlehu6.vip乐虎官网 47Exec dbms_stats.gather_schema_stats( 
wwwlehu6.vip乐虎官网 48ownname => ‘scott’, 
wwwlehu6.vip乐虎官网 49options => ‘GATHER AUTO’, 
wwwlehu6.vip乐虎官网 50estimate_percent => dbms_stats.auto_sample_size, 
wwwlehu6.vip乐虎官网 51method_opt => ‘for all indexed columns ‘, 
wwwlehu6.vip乐虎官网 52degree => 6 ) 

–分析表

wwwlehu6.vip乐虎官网 53sql> exec dbms_stats.gather_table_stats(ownname => ‘scott’,tabname => ‘work_list’,estimate_percent => 10,method_opt=> ‘for all indexed columns’) ; 

–分析索引

wwwlehu6.vip乐虎官网 54SQL> exec dbms_stats.gather_index_stats(ownname => ‘crm2’,indname => ‘IDX_ADM_PERMISSION_PID_MID’,estimate_percent => ’10’,degree => ‘4’) ;

–如果发现执行计划走错,删除表的统计信息

wwwlehu6.vip乐虎官网 55SQL>dbms_stats.delete_table_stats(ownname => ‘scott’,tabname => ‘work_list’) ;

–导入表的史统计信息

wwwlehu6.vip乐虎官网 56sql> exec dbms_stats.import_table_stats(ownname => ‘scott’,tabname => ‘work_list’,stattab => ‘stat_table’) ; 

–如果展开解析后,大部分表的实践计划还走错,需要导回整个scheme的统计信息

wwwlehu6.vip乐虎官网 57sql> exec dbms_stats.import_schema_stats(ownname => ‘scott’,stattab => ‘stat_table’);

–导入索引的统计信息

wwwlehu6.vip乐虎官网 58SQL> exec dbms_stats.import_index_stats(ownname => ‘crm2’,indname => ‘IDX_ADM_PERMISSION_PID_MID’,stattab => ‘stat_table’) 

–检查是否导入成功

wwwlehu6.vip乐虎官网 59SQL> 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的运转情况:

wwwlehu6.vip乐虎官网 60select * from Dba_Scheduler_Jobs where JOB_NAME =’GATHER_STATS_JOB’

实则与于10碰运行的Job还产生一个AUTO_SPACE_ADVISOR_JOB:

 

wwwlehu6.vip乐虎官网 61SQL> select JOB_NAME,LAST_START_DATE from dba_scheduler_jobs;
wwwlehu6.vip乐虎官网 62
wwwlehu6.vip乐虎官网 63JOB_NAME                          LAST_START_DATE
wwwlehu6.vip乐虎官网 64——————————   —————————————-
wwwlehu6.vip乐虎官网 65AUTO_SPACE_ADVISOR_JOB        04-DEC-07 10.00.00.692269 PM +08:00
wwwlehu6.vip乐虎官网 66GATHER_STATS_JOB                04-DEC-07 10.00.00.701152 PM +08:00
wwwlehu6.vip乐虎官网 67FGR$AUTOPURGE_JOB
wwwlehu6.vip乐虎官网 68PURGE_LOG                         
 05-DEC-07 03.00.00.169059 AM PRC
wwwlehu6.vip乐虎官网 69

但是这个自动化功能就影响了森系的正规运作,晚上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可以一直修改初始化参数文件,重新开动数据库。

 

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图