简单介绍ORACLE自动统计CBO统计信息

Oracle 10g隆重推出了自动统计CBO统计功能，但实际使用起来却是鸡肋，bug一大堆。现简单介绍一下。
1、查看GATHER_STATS_JOB执行情况
select JOB_NAME,LAST_START_DATE,ENABLED,state from dba_scheduler_jobs;
[quote]JOB_NAME LAST_START_DATE ENABL STATE
------------------------------ ---------------------------------------- ----- ----------
PURGE_LOG 20-MAR-10 03.00.00.150147 AM PST8PDT TRUE SCHEDULED
FGR$AUTOPURGE_JOB FALSE DISABLED
[color=red]GATHER_STATS_JOB[/color] 20-MAR-10 06.00.04.025944 AM +08:00 TRUE SCHEDULED
AUTO_SPACE_ADVISOR_JOB 20-MAR-10 06.00.04.025793 AM +08:00 TRUE SCHEDULED
RLM$EVTCLEANUP 21-MAR-10 02.09.59.099412 AM -07:00 TRUE SCHEDULED
RLM$SCHDNEGACTION 21-MAR-10 04.26.59.146700 PM +08:00 TRUE SCHEDULED

6 rows selected.[/quote]
可以看到GATHER_STATS_JOB在3月20日6点自动被调度执行。
查看调度脚本
[quote]SQL> SELECT program_action, number_of_arguments, enabled
2 FROM dba_scheduler_programs
3 WHERE owner = 'SYS'
4 AND program_name = 'GATHER_STATS_PROG';

PROGRAM_ACTION
--------------------------------------------------------------------------------

NUMBER_OF_ARGUMENTS ENABL
------------------- -----
dbms_stats.gather_database_stats_job_proc
0 TRUE[/quote]
查看调度窗口
[quote]SQL> SELECT w.window_name, w.repeat_interval, w.duration, w.enabled
2 FROM dba_scheduler_wingroup_members m, dba_scheduler_windows w
3 WHERE m.window_name = w.window_name
4 AND m.window_group_name = 'MAINTENANCE_WINDOW_GROUP';

WINDOW_NAME
------------------------------
REPEAT_INTERVAL
--------------------------------------------------------------------------------

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


WINDOW_NAME
------------------------------
REPEAT_INTERVAL
--------------------------------------------------------------------------------

DURATION
---------------------------------------------------------------------------
ENABL
-----
WEEKEND_WINDOW
freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0
+002 00:00:00
TRUE[/quote]

进一步可以查出数据库统计的历史信息
[quote]SQL> SELECT operation, start_time,
2 (end_time-start_time) DAY(1) TO SECOND(0) AS duration
3 FROM dba_optstat_operations
4 ORDER BY start_time DESC;

OPERATION
----------------------------------------------------------------
START_TIME
---------------------------------------------------------------------------
DURATION
---------------------------------------------------------------------------
gather_database_stats(auto)
18-6月 -10 05.57.02.501000 下午 +08:00
+0 00:00:04[/quote]
2、GATHER_STATS_JOB存在诸多bug，很多情况下需要关闭自动收集CBO统计信息
[quote]SQL> exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

PL/SQL procedure successfully completed.[/quote]
可以看到该job已经被禁用了
[quote]SQL> select ENABLED,state from dba_scheduler_jobs where job_name='GATHER_STATS_JOB';

ENABL STATE
----- ----------
FALSE DISABLED
[/quote]
或者更改隐含参数，注意更改隐含参数存在风险，不建议在生产系统上实施。

[quote]SQL> alter system set "_optimizer_autostats_job"=false scope=spfile;

System altered.[/quote]

更多关于GATHER_STATS_JOB信息可以查看metalink。