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. |
