10g收集统计信息

10g收集统计信息

---------------------------------------

从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：

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;

---------------------------------------

查看统计

---------------------------------------

表/索引/列上的统计

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

DBA_SUBPART_HISTOGRAMS

对于数据分布统计（Histogram）建议

10g之后，统计信息采集脚本（例如dbms_stats.gather_table_stats)中参数method_opt默认为'FOR ALL COLUMNS SIZE AUTO',FOR ALL COLUMNS SIZE AUTO将自动进行数据分布信息的统计。

可以将method_opt设置为'FOR ALL COLUMNS SIZE 1'避免数据分布信息的统计（在dbms_stats.gather_table_stats中进行设置或通过dbms_stats.set_param(method_opt=>'FOR ALL COLUMNS SIZE 1')来设置）

然后采用手工的方式采集数据分布信息，只有在下面情况下考虑采集数据分布信息：

1.字段数据分布不均匀，并且有索引。

2.应用sql语句没有使用绑定变量

3.字段不是唯一键，并且查询条件不为'='操作

锁住统计信息

还有一些情况，当表或schema的数据记录基本处于稳定状态时，可以通过以下命令锁住统计信息：

exec DBMS_STATS.LOCK_TABLE_STATS('owner_name','table_name');

exec DBMS_STATS.LOCK_SCHEMA_STATS('schema_name');

这样这些表或schema的统计信息将不会被收集，从而节省资源消耗和时间。通过UNLOCK_TABLE_STATS,UNLOCK_SCHEMA_STATS解除锁定。

修改10g自动统计的时间属性

--GATHER_STATS_JOB

--查看SCHEDULER_JOBS

select job_name from dba_scheduler_running_jobs;

SELECT job_name , SCHEDULE_NAME FROM DBA_SCHEDULER_JOBS;

select * from dba_scheduler_wingroup_members where window_group_name='MAINTENANCE_WINDOW_GROUP';

/* WINDOW_GROUP_NAME WINDOW_NAME

1 MAINTENANCE_WINDOW_GROUP WEEKNIGHT_WINDOW

2 MAINTENANCE_WINDOW_GROUP WEEKEND_WINDOW

*/

select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2

where t1.window_name=t2.window_name and t2.window_group_name='MAINTENANCE_WINDOW_GROUP';

/*

WINDOW_NAME REPEAT_INTERVAL DURATION

1 WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 +000 08:00:00

2 WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00

*/

--下面修改配置(使用sys用户登录oracle执行)

sqlplus sys/xxx@xxxdb as sysdba

set linesize 120

--修改WEEKEND_WINDOW的配置 (改成和WEEKNIGHT_WINDOW相同,即周一~周五,每日的22:00向后8小时,至次日凌晨6点)

begin

dbms_scheduler.set_attribute('WEEKEND_WINDOW','REPEAT_INTERVAL','freq=daily;byday=SAT,SUN;byhour=22;byminute=0;bysecond=0');

dbms_scheduler.set_attribute('WEEKEND_WINDOW','DURATION','+000 08:00:00');

end;

/

--查看修改结果:

select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2

where t1.window_name=t2.window_name and t2.window_group_name='MAINTENANCE_WINDOW_GROUP';

/*

WINDOW_NAME REPEAT_INTERVAL DURATION

1 WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 +000 08:00:00

2 WEEKEND_WINDOW freq=daily;byday=SAT,SUN;byhour=22;byminute=0;bysecond=0 +000 08:00:00

*/

--若要还原成以前默认设置,可执行如下

--周末两天都是全天:

begin

dbms_scheduler.set_attribute('WEEKEND_WINDOW','REPEAT_INTERVAL','freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0');

dbms_scheduler.set_attribute('WEEKEND_WINDOW','DURATION','+002 00:00:00');

end;

/

网上也有手动收集统计信息的脚本，有时间可以揣摩一下。csdn博客系统现在仍在维护，不能访问，只能暂且放到QQ空间了，有空在移过去呀！！！

以上来自http://www.cnblogs.com/rootq/archive/2010/02/04/1663622.html

http://liurizhaooracle.blog.163.com/blog/static/12311087220091130105353326/

品悟性能优化