Do you know what your database is doing after dark and on the weekends? What happens between 10 P.M. and 6 A.M. on weekdays, and all weekend? Would you be surprised to know that Oracle, by default, schedules a job to gather optimizer statistics upon creation of a database?
The name of the job is GATHER_STATS_JOB. Referencing the Performance Tuning Guide, this job is “created automatically at database creation time and is managed by the Scheduler. The Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends.”
Let’s take a quick look at the Scott schema because that is typically created when a database is created. The first stop is to look at analyze information found in DBA_TABLES.
select table_name, to_char(liast_analyzed, ‘DD-MON-RR HH24:MI’)
“LAST DATE”, num_rows, sample_size
from dba_tables
where owner = ‘SCOTT’
order by last_analyzed;
TABLE_NAME LAST DATE NUM_ROWS SAMPLE_SIZE
—————————— —————- ———- ———–
DEPT 10-MAY-05 23:00 4 4
EMP 10-MAY-05 23:00 14 14
BONUS 10-MAY-05 23:00 0 0
SALGRADE 10-MAY-05 23:00 5 5
When was this database created? Assuming that this particular invocation of the Scott schema has not been touched or modified since it was created, does the date in LAST_DATE match the creation date of the database?
SQL> select name, to_char(created, ‘DD-MON-RR HH:MI:SS AM’) “WHEN”
2 from v$database;
NAME WHEN
——— ———————
PROD 10-MAY-05 10:35:22 AM
In this case, the answer is yes. If this job runs every night, then why is the LAST_DATE for Scott’s tables showing 10-MAY-05 and not something closer to today’s date (early October, 2006)? The reason is that Oracle considers Scott’s table data to be current (not stale), that is, less than 10% of the data (by table) has changed since the last time statistics were collected.
The specific mechanism or process which drives GATHER_STATS_JOB is DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC. The description of this is also given in the tuning guide:
The
GATHER_DATABASE_STATS_JOB_PROCprocedure collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows).TheDBMS_STATS.GATHER_DATABASE_STATS_JOB_PROCis an internal procedure, but its operates in a very similar fashion to theDBMS_STATS.GATHER_DATABASE_STATSprocedure using theGATHERAUTOoption. The primary difference is that theDBMS_STATS.GATHER_DATABASE_STATS_JOB_PROCprocedure prioritizes the database objects that require statistics, so that those objects which most need updated statistics are processed first. This ensures that the most-needed statistics are gathered before the maintenance window closes.
What does the GATHER_DATABASE_STATS_JOB_PROC do? Unfortunately, the package body is wrapped, so without access to the original source code, the implementation details are obscured. The DBMS_STATS package is created via the dbmsstat.sql script, and the package body (wrapped) is created via the prvtstat.plb script. Both scripts are found in the RDBMS/ADMIN directory under ORACLE_HOME.
Now that we know what is going on when, the next question concerns “how,” that is, how has the job been performing? The answer to this (and applies to other jobs as well) can be seen in DBA_SCHEDULER_JOBS.
JOB_NAME START STATE COUNT COUNT LAST START RUN TIME
——————– —————— —– —– ——- ——————- ——–
PURGE_LOG 11-AUG-04 03:00 AM SCHED 513 0 05-OCT-06 03:00 AM 00:00:00
GATHER_STATS_JOB SCHED 194 0 04-OCT-06 11:00 PM 06:23:54
ADV_SHRINK_1988719 SCHED 152 0 04-OCT-06 11:00 PM 00:00:04
ADV_SHRINK_2709655 SCHED 151 1 04-OCT-06 11:00 PM 01:39:04
MY_JOB 18-FEB-06 12:00 AM SCHED 244 24 05-OCT-06 04:00 AM 00:41:57
ADV_SHRINK_2457725 SCHED 151 0 04-OCT-06 11:00 PM 00:58:33
ADV_SQLACCESS2599661 SCHED 148 148 04-OCT-06 11:00 PM 00:00:00
The PURGE_LOG job has run 513 times since 11-AUG-04 and GATHER_STATS_JOB has 194 runs. The number of days does not add up, as the number of days between August, 2004 and October, 2006 is on the order of 770 plus days. One set of days which is fairly accurate is for MY_JOB at 244. At time of this article, there have been 230 days since 18-FEB-06, so the extra runs (both good and bad) are probably due to manual invocations (testing, ad hoc, etc.) of the job.
Recall that the nightly maintenance window is only eight hours, and the RUN TIME column shows that more than eight hours are required. It is possible that some jobs do not run because they are lower in priority (as determined by Oracle). There is an attribute/setting which may be the culprit of why a particular run count may off. As explained in the tuning guide:
The stop_on_window_close attribute controls whether the GATHER_STATS_JOB continues when the maintenance window closes. The default setting for the stop_on_window_close attribute is TRUE, causing Scheduler to terminate GATHER_STATS_JOB when the maintenance window closes.
The view also is telling in that it identifies jobs with serious problems (read as “does not work at all”) such as the SQL access advisory job at the bottom. If you don’t have alerts setup to notify you when a job succeeds (or fails), it is probably worthwhile to periodically inspect the STATE of a job via DBA_SCHEDULER_JOBS.
Another question related to “how” is how to enable or disable the automatic collection of statistics. The management of enabling/disabling the job is performed through the DBMS_SCHEDULER package. Chapter 27 of the Administrator’s Guide, “Using the Scheduler” of contains usage information.
Disabling a job, for example, is as simple as executing (in a PL/SQL block if you want) dbms_scheduler.disable(‘Enter_Job_Name’); in a SQL*Plus session (qualify with owner name if necessary). To disable the forever-failing ADV_SQLACCESS2599661 job (this is owned by SYS):
SQL> set serveroutput on
SQL> exec dbms_scheduler.disable(‘ADV_SQLACCESS2599661’);
PL/SQL procedure successfully completed.
No need to keep running a bad job, and the next steps are to find out who created it, is it necessary, and so on. The same step can be used on the GATHER_STATS_JOB if desired.
Are you stuck with the 10-6 and weekend schedule for GATHER_STATS_JOB? Let’s look at some of the details of the job via DBA_SCHEDULER_WINDOWS.
WINDOW_NAME NEXT_START_DATE COMMENTS
—————- ——————— ————————————-
WEEKNIGHT_WINDOW 05-OCT-06 10.00.00 PM Weeknight window for maintenance task
WEEKEND_WINDOW 07-OCT-06 12.00.00 AM Weekend window for maintenance task
The attribute that cannot be changed is WINDOW_NAME. Also included in Chapter 27 is information about altering a window. It is possible to increase or decrease the amount of time for the maintenance window.
You alter a window using the SET_ATTRIBUTE procedure or Enterprise Manager. With the exception of WINDOW_NAME, all the attributes of a window can be changed when it is altered. The attributes of a window are available in the *_SCHEDULER_WINDOWS views.
When a window is altered, it does not affect an active window. The changes only take effect the next time the window opens.
All windows can be altered. If you alter a window that is disabled, it will remain disabled after it is altered. An enabled window will be automatically disabled, altered, and then reenabled, if the validity checks performed during the enable process are successful.
In Closing
So now you know something about what your database is doing after hours. What else is going on that you may not know about, and how do you ensure that higher priority schemas (as opposed to what Oracle selects) always get analyzed in a maintenance window? The GATHER_STATS_JOB can be a valuable aid in optimizing performance if fed the right information, but you also have the option of creating your own (non-wrapped) gather statistics type of job and have it run in your own window.
