采集oracle总结音讯,PLSQL_总结消息种类01_总括消息的定义和重点

2014-12-18 Created By
BaoXinjian

 

优化器总括范围:

表计算;
–行数,块数,行平均长度;all_tables:NUM_ROWS,BLOCKS,AVG_ROW_LEN;
列总计; –列中唯一值的多少(NDV),NULL值的多寡,数据分布;
            
–DBA_TAB_COLUMNS:NUM_DISTINCT,NUM_NULLS,HISTOGRAM;
目录总计;–叶块数量,等级,聚簇因子;
            
–DBA_INDEXES:LEAF_BLOCKS,CLUSTERING_FACTOR,BLEVEL;
系统计算;–I/O质量与使用率;
             –CPU质量与使用率;
            
–存储在aux_stats$中,必要使用dbms_stats收集,I/O统计在X$KCFIO中;


图片 1一、摘要

analyze

急需选拔ANALYZE总结的总括:
使用LIST CHAINED ROWS和VALIDATE子句;
采访空闲列表块的总括;
Analyze table tablename compute statistics;
Analyze index|cluster indexname estimate statistics;
ANALYZE TABLE tablename COMPUTE STATISTICS
FOR TABLE
FOR ALL [LOCAL] INDEXES
FOR ALL [INDEXED] COLUMNS;
ANALYZE TABLE tablename DELETE STATISTICS
ANALYZE TABLE tablename VALIDATE REF UPDATE
ANALYZE TABLE tablename VALIDATE STRUCTURE [CASCADE]|[INTO
TableName]
ANALYZE TABLE tablename LIST CHAINED ROWS [INTO TableName]


ANALYZE 不符合做分区表的分析

Statistic 对Oracle 是不行重庆大学的。

dbms_stats

dbms_stats能好好地打量计算数据(特别是本着较大的分区表),并能得到更好的统计结果,最后制定出速度更快的SQL推行安顿。
这些包的下边多个存款和储蓄进度分别收载index、table、schema、database的计算消息:
dbms_stats.gather_table_stats     收集表、列和目录的总结消息;
dbms_stats.gather_schema_stats    收集SCHEMA下有所目的的总结音讯;
dbms_stats.gather_index_stats     收集索引的总计新闻;
dbms_stats.gather_system_stats    收集连串计算消息
dbms_stats.GATHER_DICTIONARY_STATS: 全数字典对象的计算;
DBMS_STATS.GATHER_DICTIONARY_STATS 其收集全数系统方式的总结

dbms_stats.delete_table_stats     删除表的总结新闻
dbms_stats.delete_index_stats     删除索引的总括音讯
dbms_stats.export_table_stats     输出表的总计音讯
dbms_stats.create_state_table
dbms_stats.set_table_stats     设置表的总括
dbms_stats.auto_sample_size

它会采集数据库中指标的详细新闻,并储存在相应的多少字典里。
根据这个计算音讯, optimizer 能够对各类SQL 去挑选最佳的实践安插。

计算收集的权位

非得给予普通用户权限
sys@ORADB> grant execute_catalog_role to hr;
sys@ORADB> grant connect,resource,analyze any to hr;

Statistic 对Oracle
是充足主要的,它会收集数据库中指标的详细信息,并储存在相应的数目字典里。

总计收集的年华考虑

当参数STATISTICS_LEVEL设置为TYPICAL大概ALL,系统会在夜间机关采集计算消息。
翻开系统活动收集总计消息的job:
SELECT * FROM dba_scheduler_jobs WHERE job_name =
‘GATHER_STATS_JOB’;
也得以disable自动收集统计消息:
BEGIN
dbms_scheduler.disable(‘GATHER_STATS_JOB’);
END;

接纳手工总括

对具有改变活动中等的目的活动计算应该丰富丰富,由于机动计算收集在夜间拓展,由此对此部分更新往往的对象其总结恐怕早已过期。三种典型的靶子:
惊人变化的表在芸芸众生的移动期间被TRUNCATE/DROP同等对待建;
块加载超越本人总大小一成的靶子;

对此第①种对象足以运用以下二种方法:
1 将那么些表上的总计设置为NULL,当Oracle遭逢没有总结的表时,将动态收集必要的计算作为查询优化的一局地;
动态收集特征由OPTIMIZELacrosse_DYNAMIC_SAMPLING控制,这几个参数应该安装为当先等于2,暗中同意为2。可以经过删除并锁住宅建设总公司计将总结设置为NULL:
DBMS_STATS.DELETE_TABLE_STATS(‘SCHEMA’,’TABLE’);
DBMS_STATS.LOCK_TABLE_STATS(‘SCHEMA’,’TABLE’);

2
将这么些表上的计算设置为代表表典型气象的值。在表具有有个别有代表性的值时征集总括,然后锁住宅建设总公司括;
出于夜间征集的计算未必适合于白日的载重,因而那几个情状下选拔手工业收集比GATHEXC60_STATS_JOB更有效。
对于块加载,总括应该在加载后立马收集,经常合并在加载语句的末尾制止遗忘。
对于外部表,总计不能由此GATHEHaval_DATABASE_STATS,GATHER_SCHEMA_STATS以及自行总计收集收集。由此要求使用GATHE君越_TABLE_STATS在单个表上采集计算,并且在表面表上不扶助取样,ESTIMATE_PEPAJEROCENT应该被显示设置为NULL。
如果STATISTICS_LEVEL设置为BASIC禁止使用了监督特征,自动总结收集将不会检查和测试过期的总括,此时供给手工业收集。

3 必要手工业收集的另二个地点是系统总计,其不会自行收集。
对此固定表,如动态品质表,要求接纳GATHELacrosse_FIXED_OBJECTS_STATS收集,那么些表上的计算应该在数据库拥有有代表性的活动后采访。

基于这一个计算新闻, optimizer
可以对各样SQL 去挑选最棒的实践安插。

计算收集考虑

1 总计收集使用取样

不利用抽样的总计收集须求全表扫描并且排序整个表,抽样最小化收集总括的必备财富。
Oracle推荐设置DBMS_STATS的ESTIMATE_PERCENT参数为DBMS_STATS.AUTO_SAMPLE_SIZE在达到规定的标准须求的总结精确性的同时最大化质量。

2 并行总结收集
Oracle推荐设置DBMS_STATS的DEGREE参数为DBMS_STATS.AUTO_DEGREE,该参数允许Oracle依照指标的轻重和并行性开端化参数的安装选取合适的并行度。
聚簇索引,域索引,位图连接索引不能够相互收集。

3 分区对象的总计收集
对于分区表和目录,DBMS_STATS能够收集单独分区的总括和全局分区,对于构成分区,能够搜集子分区,分区,表/索引上的总结,分区总计的采集能够透过注明参数GRANULAOdysseyITY。依据将优化的SQL语句,优化器能够选择使用分区总计或全局总括,对于多数类别那三种总计都是很重点的,Oracle推荐将GRANULA奥迪Q3ITY设置为AUTO同时采集一切信息。

4 列总结和直方图
当在表上收集总结时,DBMS_STATS收集表中列的数据分布的音讯,数据分布最基本的音讯是最大值和纤维值,可是借使数据分布是倾斜的,那种级别的总结对于优化器来说不够的,对于倾斜的数据分布,直方图日常用来作为列总括的一局地。
直方图通过METHOD_OPT参数评释,Oracle推荐设置METHOD_OPT为FO奥迪Q7 ALL
COLUMNS SIZE
AUTO,使用该值时Oracle自动决定要求直方图的列以及各样直方图的桶数。也足以手工业安装必要直方图的列以及桶数。
若是在采纳DBMS_STATS的时候必要删除表中的全部行,必要运用TRUNCATE代替drop/create,不然自动总括收集特征使用的负荷消息以及RESTORE_*_STATS使用的保留的总计历史将遗失。那个特色将不恐怕平日发挥效率。

5 明确过期的计算
对此那么些随着时光转移的靶子必须周期性收集计算,为了明确过期的总结,Oracle提供了3个表监察和控制那个改动,那些监督暗许景况下在STATISTICS_LEVEL为TYPICAL/ALL时启用,该表为USER_TAB_MODIFICATIONS。使用DBMS_STATS.FLUSH_DATABASE
_MONITORING_INFO能够及时反映内部存款和储蓄器Chinese Football Association Super League过监控的新闻。在OPTIONS参数设置为GATHE奔驰M级STALE or GATHE本田CR-V AUTO时,DBMS_STATS收集过期总结的靶子的总括。

6 用户定义总计
在成立了依据索引的总计后,应该在表上收集新的列总计,那足以经过调用进度设置METHOD_OPT的FOR
ALL HIDDEN COLUMNS。

7 什么日期收集总结
对此增量更改的表,大概每种月/周周只需求收集壹回,而对此加载后表,日常在加载脚本中加进收集总括的脚本。对于分区表,如若只是是2个分区有了较大改变,只须要收集多个分区的总计,但是收集一切表的分区也是必备的。

Oracle Statistic 的搜集,可以行使analyze
命令,也能够行使DBMS_STATS 包来采访。

系统总括

系统总结描述系统硬件的风味,包罗I/O和CPU。在选用执行安插时,优化器考虑查询所需的CPU和I/O代价。系统总计允许优化器特别准确的评说CPU和IO代价,选拔更好的查询陈设。
使用DBMS_STATS.GATHER_SYSTEM_STATS收集种类总结,Oracle推荐收集种类计算。收集系统计算须要DBA权限。
征集的优化器系统总计包含:
cpuspeedNW:代表无负载CPU速度,CPU速度为每分钟CPU周期数;通过安装gathering_mode
= NOWOLX570KLOAD或手工业安装计算;单位Millions/sec。
ioseektim:I/O查找时间=查找时间+延迟时间+OS负载时间;通过安装gathering_mode
= NOWO福睿斯KLOAD或手工业安装总计;单位为ms。
Iotfrspeed:I/O传输速度;通过设置gathering_mode =
NOWORubiconKLOAD或手工安装总括;单位为Bytes/ms.
Cpuspeed:代表有负载CPU速度,CPU速度为每分钟CPU周期数;通过设置gathering_mode
=NOWOPAJEROKLOAD,INTELX570VAL, STA冠道T|STOP或手工业安装总结;单位Millions/sec。
马克斯thr:最大I/O吞吐量;通过安装gathering_mode =NOWO安德拉KLOAD,INTELacrosseVAL,
STA陆风X8T|STOP或手工业安装计算;单位Bytes/sec.
Slavethr:服务I/O吞吐量是平均并行服务I/O吞吐量;通过设置gathering_mode
= INTE奥迪Q5VAL,STA奇骏T|STOP或手工业安装总计;Bytes/sec.
Sreadtim:随机读取单块的平分时间;通过设置gathering_mode
=INTELANDVAL,STA奥迪Q3T|STOP或手工业安装总结;单位为ms。
Mreadtim:顺序读取多块的平分时间,通过设置通过设置gathering_mode =
INTE福睿斯VAL,STAXC60T|STOP或手工业安装总结;单位为ms。
Mbrc: 多块读平均每便读取的块数量;通过设置通过设置gathering_mode =
INTE奥迪Q5VAL,STAEscortT|STOP或手工业安装总结;单位为blocks。

系统总结的双重收集不会造成当前的SQL无效,只是有所的新SQL语句使用新的总括。

Oracle提供四个挑选收集计算:负载总结;非负载总计。

Oracle
建议选取DBMS_STATS包来搜集总括消息,因为DBMS_STATS包收集的更广,并且更纯粹,Analyze
在其后的本子中或然会被移除。

负载计算

在负载窗口的伊始运维dbms_stats.gather_system_stats(’start’),然后运营dbms_stats.gather_system_stats(’stop’)截止负载窗口。
运行dbms_stats.gather_system_stats(’interval’,
interval=>N),N表示N分钟后系统总计收集甘休。
运行dbms_stats.delete_system_stats()删除负载总括。

 

非负载总结

运营不带参数的dbms_stats.gather_system_stats()收集非负载计算,运行非负载计算时会有肯定的I/O负载。在一些景况下,非负载计算的值或许会保持暗许,此时内需动用dbms_stats.set_system_stats设置。

图片 2② 、总结音信内容和层次

管理统计

转储先前版本的总括
应用RESTORE过程转储先前版本的计算,这一个进程使用一个日子戳作为参数,包括总计时间的视图包含:
1
DBA_OPTSTAT_OPERATIONS:当中蕴藏了动用DBMS_STATS在情势/系统级别实施的总括操作;
2 *_TAB_STATS_HISTO奥德赛Y:包罗了表计算更改的野史。
旧的总计定期刷新,依照DBMS_STATS的ALTER_STATS_HISTORY_RETENTION进度设置而定,私下认可为31天。
暗中认可处境下,倘若STATISTICS_LEVEL为TYPICAL/ALL,自动刷新启用;不然须求使用PU索罗德GE_STAT手工业刷新。

其余转储与刷新相关的新闻包罗:
PURGE_STATS:     手工业刷新抢先有个别时刻戳的旧总计;
GET_STATS_HISTORY_RENTENTION:   获得当前正史总括保留值;
GET_STATS_HISTORY_AVAILABILTY: 得到可用的最旧的总结的年月戳。
转储的界定:
1 不能够转储用户定义总计;
2 假使使用了ANALYZE收集,旧的总结将无法转储。


导入/导出总结

导出总括前须要采纳DBMS_STATS.CREATE_STAT_TABLE创造二个总括表保留总括,在表创立后方可动用DBMS_STATS.EXPORT_*_STATS导出总结到自定义表,那几个总括可以应用DBMS_STATS.IMPORT_*_STATS重新导入。
也足以运用IMP/EXP导到别的数据库。

转储总结与导入导出总计

使用转储的状态:
1 恢复生机旧版本的总结;
2 希望数据库管理计算历史的保留和刷新;
使用EXPORT/IMPORT_*_STATS的情况:
1 实验种种值的不等景色;
2 移动计算到不一致数据库;
3 保留总计数据更长的时间。

  1. Table statistics

锁住表和格局的总括

如若总计被锁住,将不可能在改变那个总计直到被解锁。DBMS_STAT提供多个经过用于解锁,五个用于加锁:
1 LOCK_SCHEMA_STATS;¡¤LOCK_TABLE_STATS;
2 UNLOCK_SCHEMA_STATS;¡¤UNLOCK_TABLE_STATS;

(1). 行数,块数,行平均长度

安装计算

能够应用SET_*_STATISTICS设置表,索引,列,系统总括。

(2).
DBA_TBALES:NUM_ROWS,BLOCKS,AVG_ROW_LEN;

行使动态取样评价总计

动态取样的目的是经过为谓词选择性和表/索引总括明确进一步准确的估价提升服务器质量,猜想越规范发生的习性更好。
能够动用动态取样的情况:
1 在搜集的计算无法动用或会招致惨重的估价错误时估量单表的谓词选拔性;
2 估摸没有总括的表/索引的总括;
3 揣测计算过期的表和索引的计算;
动态取样特征由参数OPTIMIZE大切诺基_DYNAMIC_SAMPLING控制,暗中同意级别为2。

动态取样的工作体制
根本的属性特点是编写翻译时,Oracle在编写翻译时间控制制3个查询是或不是能因此取样收益,若是得以,将用递归SQL随计算机扫描描一小部分表块,然后使用相关的单表谓词评价谓词选拔性。

选拔动态取样的年月
应用动态取样将收入的情形:
1 能够发现更好的施行安排;
2 取样时间仅占总时间的一小部分;
3 查询将实施数十次;

  • Number of rows
  • Number of blocks
  • Average row length

抽样级别

范围从1..10

  1. Column statistics

缺点和失误总括处理

当Oracle遭遇丢失总括时,优化器动态须要的总计。在少数情状下,Oracle无法执行动态取样,包罗:远程表/外部表,此时将选拔暗中同意总结。
缺点和失误计算时的表私下认可值:
1 Cardinality:num_of_blocks * (block_size – cache_layer) /
avg_row_len
2 Average row length:100字节;
3 Number of blocks:100或根据分区映射的实际值;
4 Remote cardinality:2000行;
5 Remote average row length:100字节;
缺失计算时的索引暗许值:
Levels:1
Leaf blocks:25
Leaf blocks/key:1
Data
blocks/key:1
Distinct keys:100
Clustering factor:800

(1).
列中绝无仅有值的数据(NDV),NULL值的数量,数据分布;

gather_schema_stats

begin
dbms_stats.gather_schema_stats( wnname => ‘SCOTT’,
                                 ptions => ‘GATHER AUTO’,
                                 estimate_percent =>
dbms_stats.auto_sample_size,
                                 method_opt => ‘for all columns size
repeat’,
                                 degree => 15 );
end;
options参数使用6个预设的法门:
gather——重新分析任何架构(Schema)。
gather empty——只分析当前还从未总计的表。
gather
stale——只重复分析修改量当先1/10的表(这几个改动包涵插入、更新和删除)。
gather
auto——重新分析当前从不计算的靶子,以及总括数据过期(变脏)的对象。类似于整合使用gather
stale和gather empty。

只顾,无论gather stale依旧gather auto,都务求实行监视。
若是你执行2个alter table xxx
monitoring命令,Oracle会用dba_tab_modifications视图来跟踪产生变更的表。
那样一来,你就适合地驾驭,自从上三回分析总括数据以来,爆发了略微次插入、更新和删除操作。
SELECT * FROM Sys.Dba_Tab_Modifications WHERE Table_Owner =
‘SCOTT’;
运用alter table xxx
monitoring命令来完成Oracle表监视时,需求选取dbms_stats中的auto选项。
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;

estimate_percent选项
以下estimate_percent参数是一种相比较新的设计,它允许Oracle的dbms_stats在收集总结数据时,自动估量要采集样品的1个segment的极品百分比:
estimate_percent => dbms_stats.auto_sample_size
要注明自动总括采集样品的准确性,你可查究dba_tables
sample_size列。3个有趣的地点是,在运用机动采集样品时,Oracle会为二个样本尺寸选拔5到20的比重。记住,总计数据品质越好,CBO做出的主宰越好。

method_opt选项
dbms_stats的method_opt参数尤其适合在表和目录数据产生变化时刷新总计数据。method_opt参数也适合用来判断哪些列须要直方图(histograms)。
有个别意况下,索引内的顺序值的分布会影响CBO是应用一个索引照旧履行3回全表扫描的裁定。例如,假诺在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优化器决定是展开索引访问,仍旧举办全表扫描访问。例如,在3个索引中,假定有三个列在八分之四的行中,那么为了寻觅那一个行,全表扫描的速度会快于索引围观。
–*************************************************************

(2).
DBA_TAB_COLUMNS:NUM_DISTINCT,NUM_NULLS,HISTOGRAM;

— 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选项时,只会为现有的直方图再一次分析索引,不再搜索别的直方图机会。定期重新分析计算数据时,你应当选择那种措施。
–**************************************************************
— REPEAT OPTION – Only reanalyze histograms for indexes

  • Number of distinct values (NDV) in
    column
  • Number of nulls in column
  • Data distribution (histogram)

— 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;

Oracle中有关表的总括消息是在数码字典中的,能够下SQL查询到:
SELECT
Table_Name,Num_Rows,Blocks,Empty_Blocks,Avg_Space,Chain_Cnt,Avg_Row_Len,Sample_Size,Last_Analyzed
FROM Dba_Tables WHERE wner = ‘SCOTT’ ;

那是对命令与工具包的局地计算
壹 、对于分区表,建议利用DBMS_STATS,而不是行使Analyze语句。
a) 可以相互进行,对多少个用户,多个Table
b) 可以赢得全部分区表的多少和单个分区的多少。
c) 能够在分歧级别上Compute Statistics:单个分区,子分区,全表,全体分区
,但不采访聚簇总结
d) 能够倒出总结音讯
e) 可以用户自动采集计算音信
2、DBMS_STATS的缺点
a) 不能Validate Structure
b) 不可能搜集CHAINED ROWS, 不能够收集CLUSTE昂科威TABLE的音讯,那多少个依然必要选用Analyze语句。
c) DBMS_STATS
暗许不对索引进行Analyze,因为暗中认可Cascade是False,须要手工业钦命为True
叁 、对于External Table,Analyze无法选取,只好使用DBMS_STATS来采访音信。

  1. Index statistics

GATHER_TABLE_STATS

DBMS_STATS.gather_table_stats
    (ownname varchar2,
     tabname varchar2,
     partname varchar2 default null,
     estimate_percent number default  
to_estimate_percent_type(get_param(‘ESTIMATE_PERCENT’)),
     block_sample boolean default FALSE,
     method_opt varchar2 default get_param(‘METHOD_OPT’),
     degree number default to_degree_type(get_param(‘DEGREE’)),
     granularity varchar2 default get_param(‘GRANULARITY’),
     cascade boolean default to_cascade_type(get_param(‘CASCADE’)),
     stattab varchar2 default null, statid varchar2 default null,
     statown varchar2 default null,
     no_invalidate boolean default
to_no_invalidate_type(get_param(‘NO_INVALIDATE’)),
     stattype varchar2 default ‘DATA’,
     force boolean default FALSE);

参数表明:
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:总结你看不到列的histograms
for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:
                                             
总括钦点列的histograms.N的取值范围[1,254]; R
                                             
EPEAT上次计算过的histograms;
                                              AUTO由oracle决定N的大小;
                                              SKEWONLY multiple
end-points with the same value which is what we define by “there is skew
in the data
degree:              设置收集总结音讯的互相度.默许值为null.
granularity:Granularity of statistics to collect ,only pertinent if the
table is partitioned.
cascade:       是采访索引的音讯.默许为falase.
stattab       
钦点要存款和储蓄总结音讯的表,statid借使多个表的总结新闻存款和储蓄在同1个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);

(1). 叶块数量,等级,聚簇因子;

GATHER_INDEX_STATS

BEGIN
SYS.DBMS_STATS.GATHER_INDEX_STATS (OwnName => ‘ABC’,
                                   IndName => ‘IDX_FUNC_ABC’,
                                   Estimate_Percent => 10,
                                   Degree =>
SYS.DBMS_STATS.DEFAULT_DEGREE,
                                   No_Invalidate => FALSE);
END;


(2).
DBA_INDEXES:LEAF_BLOCKS,CLUSTERING_FACTOR,BLEVEL;

10g机关采集总结音讯

从10g开班,Oracle在建库后就暗中同意创造了八个名为GATHEPAJERO_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还有2个AUTO_SPACE_ADVISOR_JOB:
SELECT Job_Name, Last_Start_Date FROM Dba_Scheduler_Jobs;

JOB_NAME                       LAST_START_DATE


AUTO_SPACE_ADVISOR_JOB         30-OCT-08 10.00.01.463000 PM +08:00
GATHER_STATS_JOB               30-OCT-08 10.00.01.463000 PM +08:00

不过那几个自动化成效已经影响了诸多种类的平常运营,深夜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;


  • Number of leaf blocks
  • Levels
  • Clustering factor

翻开计算

表/索引/列上的总括
DBA_TABLES
DBA_OBJECT_TABLES
DBA_TAB_STATISTICS
DBA_TAB_COL_STATISTICS
DBA_TAB_HISTOGRAMS
DBA_INDEXES
DBA_IND_STATISTICS
DBA_CLUSTERS
DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
DBA_PART_COL_STATISTICS
DBA_PART_HISTOGRAMS
DBA_SUBPART_COL_STATISTICS

  1. System statistics

DBA_SUBPART_HISTOGRAMS

(1).
存储在aux_stats$中,需求使用dbms_stats收集,I/O统计在X$KCFIO中;

直方图总括

直方图的种类存款和储蓄在*TAB_COL_STATISTICS视图的HISTOGRAM列上。


  • I/O performance and
    utilization
  • CPU performance and
    utilization

bde_last_analyzed.sql – Verifies CBO Statistics

bde_last_analyzed.sql verifies the CBO statistics in the data
dictionary for all tables, indexes, and partitions. It also validates
the statistics on tables and indexes owned by ‘SYS’.

The 5 generated reports bde_last_analyzed_xxx.html, present the total
of tables and indexes analyzed per module and per date.

Script. bde_last_analyzed.sql provided in this Note can be used on any
8i, 9i, 10g, 11g or higher database, including Oracle Apps 11i and R12
instances

固然是ECRUISERP数据库,则用APPS连接,不然用别的任何SYS权限用户连接都得以
#sqlplus <user>/<pwd>
     SQL> START bde_last_analyzed.sql

Review spool output files bde_last_analyzed_xxx.html files. Spool
files get created on same directory from which this script. is executed.
On NT, files may get created under $ORACLE_HOME/bin.

If some modules have not been analyzed, or they have but not recently,
these Apps objects must be analyzed using FND_STATS or coe_stats.sql
if belonging to Oracle Apps. Otherwise use DBMS_STATS.
If Oracle Apps, use corresponding concurrent program with an estimate of
10%, or execute equivalent FND_STATS procedure from SQL*Plus:
SQL> exec fnd_stats.gather_schema_statistics(‘APPLSYS’); Where
‘APPLSYS’ is the module (schema) that requires new statistics.

If only a few tables require to have their statistics gathered, use the
corresponding concurrent program to gather stats by table, or execute
equivalent FND_STATS procedure from SQL*Plus:
SQL> exec
fnd_stats.gather_table_stats(‘MRP’,’MRP_FORECAST_DATES’);
Where ‘MRP’ is the schema owner, and ‘MRP_FORECAST_DATES’ is the table
name. This syntax is only for non-partitioned Tables.

If any Partitioned Table requires its Global Stats being rebuilt, it is
because at some point you gathered Stats on the table using a
granularity of PARTITION. See second method below:
begin
dbms_stats.delete_table_stats(ownname => ‘APPLSYS’, tabname =>
‘WF_ITEM_ACTIVITY_STATUSES’);
fnd_stats.gather_table_stats (ownname => ‘APPLSYS’, tabname =>
‘WF_ITEM_ACTIVITY_STATUSES’,
                                granularity => ‘DEFAULT’);
end;
/

Once you fix your stats, be sure to ALWAYS use the granularity of
DEFAULT for partitioned tables.

If you want to execute this bde_last_analyzed.sql script. against only
one schema, modify DEF SCHEMA code line.


分区表的总计音讯实例
————————————— 
ORATEA
ORACLE的总结消息在推行SQL的长河中扮演着格外首要的效能,而且ORACLE在表的次第层次都会有不一致的总括新闻,通过那几个总括新闻来描述表的,列的丰盛多彩的计算消息。下边通过三个复合分区表来表明部分大规模的和大规模的计算音讯。

SQL>
create table test
partition by range(object_id)
subpartition by hash(object_type) subpartitions 4
(partition p1 values less than(10000),
partition p2 values less than(20000),
partition p3 values less than(30000),
partition p4 values less than(maxvalue))
as
select * from dba_objects;

表已开立。
sql>
BEGIN
dbms_stats.gather_table_stats(ownname          => ‘SCOTT’,
                                tabname          => ‘TEST’,
                                estimate_percent => 100,
                                block_sample     => FALSE,
                                method_opt       => ‘FOR ALL COLUMNS
SIZE 10’,
                                granularity      => ‘ALL’,
                                cascade          => TRUE);
END;

1,表级的总结新闻

SQL> select table_name,num_rows,blocks,empty_blocks,avg_space
from user_tables where table_name = ‘TEST’;

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
AVG_SPACE



TEST                                50705        788           
0          0

2,表上列的总结消息

SQL> select table_name,column_name,num_distinct,density from
user_tab_columns where table_name = ‘TEST’;

TABLE_NAME                     COLUMN_NAME                   
NUM_DISTINCT    DENSITY



TEST                           OWNER                                   
25 .365014295
TEST                           OBJECT_NAME                          
30275 .000039205
TEST                           SUBOBJECT_NAME                         
191 .015657993
TEST                           OBJECT_ID                            
50705 .000019722
TEST                           DATA_OBJECT_ID                        
4334 .000248075
TEST                           OBJECT_TYPE                             
42 .271207855
TEST                           CREATED                               
2305 .001608457
TEST                           LAST_DDL_TIME                         
2369 .001566737
TEST                           TIMESTAMP                             
2412 .001610251
TEST                           STATUS                                   
2 .000009861
TEST                           TEMPORARY                                
2 .000009861
TEST                           GENERATED                                
2 .000009861
TEST                           SECONDARY                                
2 .000009861

13 rows selected.

3,表上列的直方图新闻

SQL>
select table_name,column_name,endpoint_number,endpoint_value
from user_tab_histograms
where table_name = ‘TEST’
and column_name = ‘OBJECT_ID’;

TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE


TEST       OBJECT_ID                0              2
TEST       OBJECT_ID                1           5160
TEST       OBJECT_ID                2          10587
TEST       OBJECT_ID                3          15658
TEST       OBJECT_ID                4          20729
TEST       OBJECT_ID                5          25800
TEST       OBJECT_ID                6          30870
TEST       OBJECT_ID                7          35940
TEST       OBJECT_ID                8          41089
TEST       OBJECT_ID                9          46821
TEST       OBJECT_ID               10          53497

4,分区的总括音讯

SQL>
select partition_name,num_rows,blocks,empty_blocks,avg_space
from user_tab_partitions
where table_name = ‘TEST’;

PARTITION_NAME    NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_SPACE


P1                    9581        140            0          0
P2                    9973        164            0          0
P3                   10000        158            0          0
P4                   21151        326            0          0

5,分区上列的总括音信

SQL> select column_name,num_distinct,density,num_nulls
from user_part_col_statistics
where table_name = ‘TEST’
and partition_name = ‘P1’;

COLUMN_NAME     NUM_DISTINCT    DENSITY NUM_NULLS


OWNER                      7 .000052187          0
OBJECT_NAME             7412 .000156925          0
SUBOBJECT_NAME            26 .47017301       9496
OBJECT_ID               9581 .000104373          0
DATA_OBJECT_ID          1765 .000664385       7780
OBJECT_TYPE               34 .18494854          0
CREATED                  913 .001977449          0
LAST_DDL_TIME            994 .001882695          0
TIMESTAMP                982 .001928775          0
STATUS                     2 .000052187          0
TEMPORARY                  2 .000052187          0
GENERATED                  2 .000052187          0
SECONDARY                  1 .000052187          0

6,分区上列的直方图音信

SQL> select column_name,bucket_number,endpoint_value
from user_part_histograms
where table_name = ‘TEST’
and partition_name = ‘P1’
and column_name = ‘OBJECT_ID’;

COLUMN_NAME     BUCKET_NUMBER ENDPOINT_VALUE


OBJECT_ID                   0              2
OBJECT_ID                   1           1005
OBJECT_ID                   2           1963
OBJECT_ID                   3           2921
OBJECT_ID                   4           3888
OBJECT_ID                   5           4859
OBJECT_ID                   6           5941
OBJECT_ID                   7           6899
OBJECT_ID                   8           7885
OBJECT_ID                   9           8864
OBJECT_ID                  10           9999

7,子分区的计算消息

SQL> select subpartition_name,num_rows,blocks,empty_blocks
from user_tab_subpartitions
where table_name = ‘TEST’
and partition_name = ‘P1’;

SUBPARTITION_NAME                NUM_ROWS     BLOCKS EMPTY_BLOCKS


SYS_SUBP21                           3597         50            0
SYS_SUBP22                           3566         52            0
SYS_SUBP23                            637         11            0
SYS_SUBP24                           1781         27            0

8,子分区上的列的总计消息

SQL> select column_name,num_distinct,density
from user_subpart_col_statistics
where table_name = ‘TEST’
and subpartition_name = ‘SYS_SUBP21’;
COLUMN_NAME     NUM_DISTINCT    DENSITY


OWNER                      6 .000139005
OBJECT_NAME             3595 .000278319
SUBOBJECT_NAME             4 .014285714
OBJECT_ID               3597 .000278009
DATA_OBJECT_ID           155 .006451613
OBJECT_TYPE                8 .000139005
CREATED                  751 .002392334
LAST_DDL_TIME            784 .002302524
TIMESTAMP                768 .00235539
STATUS                     1 .000139005
TEMPORARY                  2 .000139005
GENERATED                  2 .000139005
SECONDARY                  1 .000139005

9,子分区上的列的直方图音讯

SQL> select column_name,bucket_number,endpoint_value
from user_subpart_histograms
where table_name = ‘TEST’
and subpartition_name = ‘SYS_SUBP21’
and column_name = ‘OBJECT_ID’;
COLUMN_NAME     BUCKET_NUMBER ENDPOINT_VALUE


OBJECT_ID                   0            208
OBJECT_ID                   1           1525
OBJECT_ID                   2           2244
OBJECT_ID                   3           2892
OBJECT_ID                   4           3252
OBJECT_ID                   5           4047
OBJECT_ID                   6           5238
OBJECT_ID                   7           6531
OBJECT_ID                   8           7661
OBJECT_ID                   9           8474
OBJECT_ID                  10           9998

大家对这几个复合分区分析之后发生了上边那九种分歧层次的总结音讯。CBO想要得要三个赶快的实践布置必要这么多的总括消息.

 

图片 3③ 、总计新闻语法


  1. analyze

需求运用ANALYZE总结的总计:使用LIST
CHAINED ROWS和VALIDATE子句收集空闲列表块的总结;

analyze 不符合做分区表的分析

  • analyze table tablename compute
    statistics;
  • analyze index|cluster indexname
    estimate statistics;
  • analyze table tablename compute
    statistics for table /for all [local] indexes / for all
    [indexed] columns
  • analyze table tablename delete
    statistics
  • analyze table tablename validate ref
    update
  • analyze table tablename validate
    structure [cascade]|[into tablename]
  • analyze table tablename list chained
    rows [into tablename]
  1. dbms_stats

dbms_stats能完美地打量计算数据(越发是针对性较大的分区表),并能获得更好的总计结果,最终制定出速度更快的SQL执行安排。

其一包的上边三个存款和储蓄进度分别采访index、table、schema、database的总计音信:

  • dbms_stats.gather_table_stats    
    收集表、列和目录的总计音信;
  • dbms_stats.gather_schema_stats   
    收集SCHEMA下有所目的的总结音讯;
  • dbms_stats.gather_index_stats    
    收集索引的总结消息;
  • dbms_stats.gather_system_stats   
    收集系统计算消息
  • dbms_stats.gather_dictioinary_stats   全体字典对象的计算;
  • dbms_stats.delete_table_stats    
    删除表的计算音讯
  • dbms_stats.delete_index_stats    
    删除索引的总括音信
  • dbms_stats.export_table_stats    
    输出表的总括消息
  • dbms_stats.create_state_table
  • dbms_stats.set_table_stats    
    设置表的总计
  • dbms_stats.auto_sample_size

 

图片 4四 、总括新闻语法


4.1 总括消息搜集如下数据:

(1)表本身的辨析:
包括表中的行数,数据块数,行长等信息。

(2)列的辨析:包涵列值的重复数,列上的空值,数据在列上的分布景况。

(3)索引的辨析:
包含索引叶块的多寡,索引的深浅,索引的汇集因子等。

4.2
那些统计消息寄存在多少字典里,如:

(1).  DBA_TABLES

(2).  DBA_OBJECT_TABLES

(3).  DBA_TAB_STATISTICS

(4).  DBA_TAB_COL_STATISTICS

(5).  DBA_TAB_HISTOGRAMS

(6).  DBA_INDEXES

(7).  DBA_IND_STATISTICS

(8).  DBA_CLUSTERS

(9).  DBA_TAB_PARTITIONS

(10). DBA_TAB_SUBPARTITIONS

(11). DBA_IND_PARTITIONS

(12). DBA_IND_SUBPARTITIONS

(13). DBA_PART_COL_STATISTICS

(14). DBA_PART_HISTOGRAMS

(15).
DBA_SUBPART_COL_STATISTICS

(16). DBA_SUBPART_HISTOGRAMS

4.3 表的总结新闻:

涵盖表行数,使用的块数,空的块数,块的使用率,行迁移和链接的数目,pctfree,pctused的数目,行的平均大小:

SELECT NUM_ROWS, --表中的记录数
        BLOCKS, --表中数据所占的数据块数
        EMPTY_BLOCKS, --表中的空块数
        AVG_SPACE, --数据块中平均的使用空间
        CHAIN_CNT, --表中行连接和行迁移的数量
        AVG_ROW_LEN --每条记录的平均长度
FROM USER_TABLES 

4.4 索引列的总结音讯   

饱含索引的深浅(B-Tree的级别),索引叶级的块数量,集群因子(clustering_factor),
唯一值的个数。

SELECT BLEVEL, --索引的层数
    LEAF_BLOCKS, --叶子结点的个数
    DISTINCT_KEYS, --唯一值的个数
    AVG_LEAF_BLOCKS_PER_KEY, --每个KEY的平均叶块个数
    AVG_DATA_BLOCKS_PER_KEY, --每个KEY的平均数据块个数
    CLUSTERING_FACTOR --群集因子
FROM USER_INDEXES

4.5 列的总结新闻  

饱含
唯一的值个数,列最大小值,密度(选拔率),数据分布(直方图音讯),NUll值个数

SELECT NUM_DISTINCT, --唯一值的个数
    LOW_VALUE, --列上的最小值
    HIGH_VALUE, --列上的最大值
    DENSITY, --选择率因子(密度)
    NUM_NULLS, --空值的个数
    NUM_BUCKETS, --直方图的BUCKET个数
    HISTOGRAM --直方图的类型
FROM USER_TAB_COLUMNS

 

图片 5五、案例


案例: 查询表时,解析安排回去结果集Rows完全不正确,表经过大气的DML后,未举办分析导致总计音信过久

Step1. 确立测试SQL

图片 6

Step2. 查看结果集的Cardinality

图片 7

Step3.
查看表的总计安排,最终分析时间过久

图片 8

Step4. 分析表

BEGIN
   DBMS_STATS.gather_table_stats ('SH', 'SALES');
END;

Step5. 分析表后总结音信变更为最新

图片 9

Step6.
解析安插Cardinality变更更为精确

图片 10

 

Thanks and Regards

参考: 一江水 –
http://www.cnblogs.com/rootq/archive/2010/02/04/1663622.html

参考: David –
http://blog.csdn.net/tianlesoftware/article/details/4668723

参考: Edwardking888 –
http://blog.itpub.net/8183550/viewspace-666335/

图片 11

相关文章