Although the analyze command still exists, it is no longer relevant to the Cost Based Optimizer (CBO). The collection of statistics to be used by the CBO in Oracle 10g is now done automatically unless changed. An internal procedure called GATHER_DATABASE_STATS_JOB_PROC is run by a program named GATHER_STATS_PROG. I created a shell script to validate this job ran and to verify times and groups associated with it. ----------------------------------------------------------------------------------------------------------------------------- SQL> @ck_auto_stats.sql SQL> set linesize 80 SQL> col job_name format a17 SQL> col program_name format a18 SQL> col schedule_name format a25 SQL> col job_class format a20 SQL> col job_type format a8 SQL> col state format a9 SQL> col PROGRAM_ACTION format a41 SQL> show parameter instance_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ instance_name string MYDATABASE SQL> select job_name,enabled, state, program_name, schedule_name, job_class 2 from dba_scheduler_jobs 3 where job_name = 'GATHER_STATS_JOB'; JOB_NAME ENABL STATE PROGRAM_NAME ----------------- ----- --------- --------- SCHEDULE_NAME JOB_CLASS ------------------------- -------------------- GATHER_STATS_JOB TRUE SCHEDULED GATHER_STATS_PROG MAINTENANCE_WINDOW_GROUP AUTO_TASKS_JOB_CLASS SQL> select PROGRAM_ACTION, ENABLED 2 from dba_scheduler_programs 3 where PROGRAM_NAME = 'GATHER_STATS_PROG'; PROGRAM_ACTION ENABL ----------------------------------------- ----- dbms_stats.gather_database_stats_job_proc TRUE SQL> select * 2 from DBA_SCHEDULER_WINGROUP_MEMBERS 3 where WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP'; WINDOW_GROUP_NAME WINDOW_NAME ------------------------------ ------------------------------ MAINTENANCE_WINDOW_GROUP WEEKNIGHT_WINDOW MAINTENANCE_WINDOW_GROUP WEEKEND_WINDOW SQL> select window_name, repeat_interval, duration 2 from dba_scheduler_windows 3 where window_name in ('WEEKNIGHT_WINDOW', 'WEEKEND_WINDOW'); WINDOW_NAME ------------------------------ REPEAT_INTERVAL -------------------------------------------------------------------------------- DURATION --------------------------------------------------------------------------- WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 +000 08:00:00 WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00 ----------------------------------------------------------------------------------------------------------------------------- The REPEAT_INTERVAL shows the job will run at 10:00 PM Mon - Fri. (byhour=22). Since we bring the databases down just after midnight, I wasn't sure if this was running to completion, therefore I changed the time to start at 6:00 PM Mon - Fri. I used this script to make this change: ----------------------------------------------------------------------------------------------------------------------------- begin dbms_scheduler.set_attribute(name=>'WEEKNIGHT_WINDOW', attribute=>'REPEAT_INTERVAL', value=>'freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=18;byminute=0; bysecond=0' ); end; / ----------------------------------------------------------------------------------------------------------------------------- The DURATION shows it to have an 8 hour window to run. Since I am starting at 6 PM and bringing the database down approx 6 hours later, I changed the DURATION to be 6 hours with this script: ----------------------------------------------------------------------------------------------------------------------------- begin dbms_scheduler.set_attribute(name=>'WEEKNIGHT_WINDOW', attribute=>'duration', value=> '+000 06:00:00' ); end; / ----------------------------------------------------------------------------------------------------------------------------- I later found out that the job does not run that long. I ran the following and found the job to run approx 38 seconds. ----------------------------------------------------------------------------------------------------------------------------- SQL> col start_time format a35 SQL> col operation format a27 SQL> col end_time format a35 SQL> select operation, start_time, end_time 2 from dba_optstat_operations 3* order by 2 OPERATION START_TIME END_TIME --------------------------- ----------------------------------- ----------------------------------- gather_database_stats(auto) 10-SEP-09 06.00.03.245920 PM -04:00 10-SEP-09 06.00.41.921488 PM -04:00 ----------------------------------------------------------------------------------------------------------------------------- I was confused when I ran the following and only saw a few tables had been updated. Then I found that this information is cleaned out every 30 days. The MYDATABASE database is not active and is almost 30 days old. ----------------------------------------------------------------------------------------------------------------------------- SQL> col owner format a8 SQL> col table_name format a8 SQL> col stats_update_time format a36 SQL> select owner, table_name, stats_update_time 2 from dba_tab_stats_history 3 where owner not in ('SYS','SYSMAN','WKSYS') 4* order by 3; OWNER TABLE_NA STATS_UPDATE_TIME ----------- ---------------- ------------------------------------------------- G1 TAB1 12-AUG-09 10.00.05.863135 PM -04:00 G1 TAB2 12-AUG-09 10.00.06.409508 PM -04:00 G1 TAB3 12-AUG-09 10.00.09.497606 PM -04:00 C1 TAB4 31-AUG-09 10.00.02.999454 PM -04:00 C1 TAB5 31-AUG-09 10.00.03.088383 PM -04:00 ----------------------------------------------------------------------------------------------------------------------------- By all my investigation on this process, I believe that the statistics gathering is happening successfully.

Although the analyze command still exists, it is no longer relevant to the Cost Based Optimizer (CBO). The collection of statistics to be used by the CBO in Oracle 10g is now done automatically unless changed. An internal procedure called GATHER_DATABASE_STATS_JOB_PROC is run by a program named GATHER_STATS_PROG. I created a shell script to validate this job ran and to verify times and groups associated with it. ----------------------------------------------------------------------------------------------------------------------------- SQL> @ck_auto_stats.sql SQL> set linesize 80 SQL> col job_name format a17 SQL> col program_name format a18 SQL> col schedule_name format a25 SQL> col job_class format a20 SQL> col job_type format a8 SQL> col state format a9 SQL> col PROGRAM_ACTION format a41 SQL> show parameter instance_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ instance_name string MYDATABASE SQL> select job_name,enabled, state, program_name, schedule_name, job_class 2 from dba_scheduler_jobs 3 where job_name = 'GATHER_STATS_JOB'; JOB_NAME ENABL STATE PROGRAM_NAME ----------------- ----- --------- --------- SCHEDULE_NAME JOB_CLASS ------------------------- -------------------- GATHER_STATS_JOB TRUE SCHEDULED GATHER_STATS_PROG MAINTENANCE_WINDOW_GROUP AUTO_TASKS_JOB_CLASS SQL> select PROGRAM_ACTION, ENABLED 2 from dba_scheduler_programs 3 where PROGRAM_NAME = 'GATHER_STATS_PROG'; PROGRAM_ACTION ENABL ----------------------------------------- ----- dbms_stats.gather_database_stats_job_proc TRUE SQL> select * 2 from DBA_SCHEDULER_WINGROUP_MEMBERS 3 where WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP'; WINDOW_GROUP_NAME WINDOW_NAME ------------------------------ ------------------------------ MAINTENANCE_WINDOW_GROUP WEEKNIGHT_WINDOW MAINTENANCE_WINDOW_GROUP WEEKEND_WINDOW SQL> select window_name, repeat_interval, duration 2 from dba_scheduler_windows 3 where window_name in ('WEEKNIGHT_WINDOW', 'WEEKEND_WINDOW'); WINDOW_NAME ------------------------------ REPEAT_INTERVAL -------------------------------------------------------------------------------- DURATION --------------------------------------------------------------------------- WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 +000 08:00:00 WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00 ----------------------------------------------------------------------------------------------------------------------------- The REPEAT_INTERVAL shows the job will run at 10:00 PM Mon - Fri. (byhour=22). Since we bring the databases down just after midnight, I wasn't sure if this was running to completion, therefore I changed the time to start at 6:00 PM Mon - Fri. I used this script to make this change: ----------------------------------------------------------------------------------------------------------------------------- begin dbms_scheduler.set_attribute(name=>'WEEKNIGHT_WINDOW', attribute=>'REPEAT_INTERVAL', value=>'freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=18;byminute=0; bysecond=0' ); end; / ----------------------------------------------------------------------------------------------------------------------------- The DURATION shows it to have an 8 hour window to run. Since I am starting at 6 PM and bringing the database down approx 6 hours later, I changed the DURATION to be 6 hours with this script: ----------------------------------------------------------------------------------------------------------------------------- begin dbms_scheduler.set_attribute(name=>'WEEKNIGHT_WINDOW', attribute=>'duration', value=> '+000 06:00:00' ); end; / ----------------------------------------------------------------------------------------------------------------------------- I later found out that the job does not run that long. I ran the following and found the job to run approx 38 seconds. ----------------------------------------------------------------------------------------------------------------------------- SQL> col start_time format a35 SQL> col operation format a27 SQL> col end_time format a35 SQL> select operation, start_time, end_time 2 from dba_optstat_operations 3* order by 2 OPERATION START_TIME END_TIME --------------------------- ----------------------------------- ----------------------------------- gather_database_stats(auto) 10-SEP-09 06.00.03.245920 PM -04:00 10-SEP-09 06.00.41.921488 PM -04:00 ----------------------------------------------------------------------------------------------------------------------------- I was confused when I ran the following and only saw a few tables had been updated. Then I found that this information is cleaned out every 30 days. The MYDATABASE database is not active and is almost 30 days old. ----------------------------------------------------------------------------------------------------------------------------- SQL> col owner format a8 SQL> col table_name format a8 SQL> col stats_update_time format a36 SQL> select owner, table_name, stats_update_time 2 from dba_tab_stats_history 3 where owner not in ('SYS','SYSMAN','WKSYS') 4* order by 3; OWNER TABLE_NA STATS_UPDATE_TIME ----------- ---------------- ------------------------------------------------- G1 TAB1 12-AUG-09 10.00.05.863135 PM -04:00 G1 TAB2 12-AUG-09 10.00.06.409508 PM -04:00 G1 TAB3 12-AUG-09 10.00.09.497606 PM -04:00 C1 TAB4 31-AUG-09 10.00.02.999454 PM -04:00 C1 TAB5 31-AUG-09 10.00.03.088383 PM -04:00 ----------------------------------------------------------------------------------------------------------------------------- By all my investigation on this process, I believe that the statistics gathering is happening successfully.