分析统计信息及动态采样

要对数据库对象生成统计信息，可以有以下方法：
select * from dba_tables;

一、使用analyze
analyze table scott.temp compute statistics (for all columns); --完全统计法
analyze table scott.temp compute statistics (for all indexes);
analyze table scott.temp estimate statistics sample 30 percent; --抽样估计法

analyze index scott.pk_emp validate structure;
select name,height,del_lf_rows,lf_rows,
round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)||'%' frag_pct
from index_stats;

重建浪费大于20%的索引
alter index scott.pk_emp rebuild;
or
alter index scott.pk_emp coalesce;

二、使用dbms_ddl.analyze_object包
dbms_ddl.analyze_object(
 type varchar2,
 schema varchar2,
 name varchar2,
 method varchar2,
 estimate_rows number,
 estimate_percent number
)

具体的参数说明：
type  --处理对象的类型，例如表，簇，索引，如果都不是，引发错误ora-20001
schema  --对象模式，null表示当前模式，注意大小写有区分，一般是用户名表示
name  --分析的对象名
method  --分析方法，包括estimate,compute或delete
estimate_rows   --采样样本的记录行数(样本大小)
estimate_percent  --样本所占比例
method_opt  --选项方法
partname  --采样分析去名称

例：
execute dbms_ddl.analyze_object('TABLE','SCOTT','TEMP','COMPUTE');
execute dbms_ddl.analyze_object('TABLE','SCOTT','TEMP','ESTIMATE',NULL,50);

三、使用dbms_utility.analyze_schema和dbms_utility.analyze_database

execute dbms_utility.analyze_schema('SCOTT','COMPUTE');
execute dbms_utility.analyze_schema('SCOTT','ESTIMATE',NULL,50);

execute dbms_utility.analyze_database('COMPUTE');

四、使用dbms_stats包，能良好地估计统计数据(尤其是针对较大的分区表)，并能获得更好的统计结果，最终制定出速度更快的SQL执行计划。

分别执行对索引、表、某个schema、数据库、系统的统计信息
dbms_stats.gather_index_stats  --分析索引
dbms_stats.gather_table_stats  --分析表
dbms_stats.gather_schema_stats  --分析用户所有的对象(包括表、索引、簇)
dbms_stats.gather_database_stats --分析数据库(包括所有的用户对象和系统对象)
dbms_stats.gather_system_stats
dbms_stats.delete_database_stats --删除数据库统计信息
dbms_stats.delete_schema_stats  --删除用户方案统计信息
dbms_stats.delete_table_stats  --删除表统计信息
dbms_stats.delete_index_stats  --删除索引统计信息
dbms_stats.delete_column_stats  --删除列统计信息
dbms_stats.set_table_stats  --设置表统计信息
dbms_stats.set_index_stats  --设置索引统计信息
dbms_stats.set_column_stats  --设置列统计信息

例：
--------------------------------------------------------------
execute dbms_stats.gather_table_stats('SCOTT','TEMP');
execute dbms_stats.gather_schema_stats('SCOTT');
--------------------------------------------------------------
begin
dbms_stats.gather_schema_stats(
 ownname => 'SCOTT',
 options => 'GATHER',
 estimate_percent => dbms_stats.auto_sample_size,
 method_opt  => 'for all columns size repeat',
 degree  => 15);
end;
/
--------------------------------------------------------------

cascade是否分析索引
======================================================================================================
exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);

options参数
======================================================================================================
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选项
======================================================================================================
auto_sample_size
estimate_percent参数是一种比较新的设计，它允许Oracle的dbms_stats在收集统计数据时，自动估计要采样的一个segment的最佳百分比：要验证自动统计采样的准确性，你可检视dba_tables sample_size列。一个有趣的地方是，在使用自动采样时，Oracle会为一个样本尺寸选择5到20的百分比。记住，统计数据质量越好，CBO做出的决定越好。

method_opt选项
======================================================================================================
for all columns
for table                --只统计表　
for all indexed columns  --只统计有索引的表列
for all indexes          --只分析统计相关索引
 如果你有一个高度倾斜的索引(某些值的行数不对称)，就可创建Oracle直方图统计。为了智能地生成直方图，Oracle为dbms_stats准备method_opt参数。
method_opt=>'for all columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size auto'

degree指定分析时使用的并行度
======================================================================================================
null
一个数值
default_degree

granularity分析的粒度
======================================================================================================
global 只做全局级别的分析
global and partition
partition 只在分区级别做分析
subpartition 只在子分区做分析

使用dbms_stats分析统计信息

1、创建统计信息历史保留表stat_table
begin
dbms_stats.create_stat_table
(
 ownname => 'SCOTT',
 stattab => 'stat_table'
);
end;
/

2、导出整个scheme的统计信息
begin
dbms_stats.export_schema_stats
(
 ownname => 'SCOTT',
 stattab => 'stat_table'
);
end;
/

3、分析scheme
begin
dbms_stats.gather_schema_stats(
 ownname => 'SCOTT',
 options => 'gather auto',
 estimate_percent => dbms_stats.auto_sample_size,
 method_opt => 'for all indexed columns',
 degree => 15);
end;
/

4、分析表
exec dbms_stats.gather_table_stats('SCOTT','EMP');

begin
dbms_stats.gather_table_stats
(
 ownname => 'SCOTT',
 tabname => 'EMP',
 estimate_percent => '10',
 method_opt=> 'for all indexed columns'
);
end;
/

例：
scott
create table t(id int,name char(10));
insert into t values (0,'hjp');
insert into t values (1,'hjp');
insert into t values (2,'hjp');
commit;

sys
select table_name,num_rows,blocks from dba_tables
where wner='SCOTT' and table_name='T';
(no)

scott
exec dbms_stats.gather_table_stats('SCOTT','T');

sys
select table_name,num_rows,blocks from dba_tables
where wner='SCOTT' and table_name='T';
(3)
select column_name,num_distinct,num_nulls,avg_col_len from user_tab_col_statistics where table_name='EMP' and column_name='EMPNO';

5、分析索引
exec dbms_stats.gather_index_stats('SCOTT','PK_EMP');

begin
dbms_stats.gather_index_stats
(
 ownname => 'SCOTT',
 indname => 'PK_EMP',
 estimate_percent => '10',
 degree => '4'
);
end;
/

6、如果发现执行计划走错，删除表的统计信息
begin
dbms_stats.delete_table_stats
(
 ownname => 'SCOTT',
 tabname => 'EMP'
);
end;
/

7、导入表的历史统计信息
begin
dbms_stats.import_table_stats
(
 ownname => 'SCOTT',
 tabname => 'EMP',
 stattab => 'stat_table'
);
end;
/

8、需要导回整个scheme的统计信息
begin
dbms_stats.import_schema_stats
(
 ownname => 'SCOTT',
 stattab => 'stat_table'
);
end;
/

9、导入索引的统计信息
begin
dbms_stats.import_index_stats
(
 ownname => 'SCOTT',
 indname => 'PK_EMP',
 stattab => 'stat_table'
);
end;
/

10、检查是否导入成功
select table_name,num_rows,blocks,last_analyzed
from all_tables
where table_name='EMP';

11、GATHER_STATS_JOB定时任务
从Oracle 10g开始，Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务，调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息。
select * from dba_Scheduler_Jobs where job_name ='GATHER_STATS_JOB';

建议最好关闭这个自动统计信息收集功能
方法之一:
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可以直接修改初始化参数文件，重新启动数据库。

 

五、直方图

    假如dbms_stat发现一个索引的各个列分布得不均匀，就会为那个索引创建直方图，帮助基于代价的SQL优化器决定是进行索引访问，还是进行全表扫描访问。例如，在一个索引中，假定有一个列在50%的行中，如清单B所示，那么为了检索这些行，全表扫描的速度会快于索引扫描。

    重新分析统计数据时，使用repeat选项，重新分析任务所消耗的资源就会少一些。使用repeat选项(清单C)时，只会为现有的直方图重新分析索引，不再搜索其他直方图机会。定期重新分析统计数据时，你应该采取这种方式。

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

degree并行统计收集参数
    Oracle推荐设置DBMS_STATS的DEGREE参数为DBMS_STATS.AUTO_DEGREE，该参数允许Oracle根据对象的大小和并行性初始化参数的设置选择恰当的并行度。聚簇索引，域索引，位图连接索引不能并行收集。

创建直方图
select * from dba_histograms where wner = 'SCOTT' and table_name = 'T1' and column_name = 'ID';

execute dbms_stats.gather_table_stats ('SCOTT','T1',method_opt => 'for columns size 10 ID')

execute dbms_stats.gather_table_stats ('SCOTT','T1',method_opt => 'for columns size auto ID')

updata t1 set id = 66 where id > 23 and id < 80;

execute dbms_stats.gather_table_stats ('SCOTT','T1',method_opt => 'for columns size 10 ID')

select * from dba_histograms where wner = 'SCOTT' and table_name = 'T1' and column_name = 'ID';

 

六、使用monitoring监控表和索引

监控表
1、开始监控命令：alter table test monitoring
2、实际对表的一些操作
3、通过包来刷新数据库监控信息，dbms_stats.flush_database_monitoring_info
4、查询对表的操作信息，sys.dba_tab_modifications
5、取消监控命令alter table test nomonitoring，这个命令会把sys.dba_tab_modifications表中的监控信息清空

scott
create table test(a number);
alter table test monitoring;

insert into test values(1);
insert into test values(2);
insert into test values(3);
insert into test values(4);
insert into test values(5);
insert into test values(6);
insert into test values(7);
update test set a = 10 where a = 5;
delete test where a < 5;

sys
exec dbms_stats.flush_database_monitoring_info;

select table_name,inserts,updates,deletes,truncated
from sys.dba_tab_modifications
where table_name='TEST';

TABLE_NAME   INSERTS   UPDATES   DELETES   TRUNCATED
TEST         9         1         4         NO

scott
alter table scott.test nomonitoring;

监控索引
scott
alter index scott.pk_emp monitoring usage;
select * from scott.emp where empno=8000;
alter index scott.pk_emp nomonitoring usage;
select * from v$object_usage;

sys
select object_owner, object_name, options, count(*)
from v$sql_plan
where peration='INDEX'
and object_owner='SCOTT'
group by object_owner, object_name, operation, options
order by count(*) desc;

监控Oracle索引的使用情况

开启监控
alter index scott.ind_tid monitoring usage;

select * from scott.t where id = 123;
select * from scott.t where id >10 and id < 100;

v$object_usage视图限制了只显示当前用户下被监控的索引的情况
select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

select u.name owner,
       io.name index_name,
       t.name table_name,
       decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring,
       decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,
       ou.start_monitoring start_monitoring,
       ou.end_monitoring end_monitoring
  from sys.user$        u,
       sys.obj$         io,
       sys.obj$         t,
       sys.ind$         i,
       sys.object_usage ou
 where i.obj# = ou.obj#
   and io.obj# = ou.obj#
   and t.obj# = i.bo#
   and u.user# = io.owner#;

取消对索引使用情况的监控
alter index scott.ind_tid nomonitoring usage;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17012874/viewspace-693784/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17012874/viewspace-693784/