Gather Stats – All The Information You Need To Know At First

What information related to statistics do you need to know when you look at the database. Below are the questions and their answers that I ask myself or the database:

Is the default stats job enabled, and when does it run?

select client_name , status from DBA_AUTOTASK_CLIENT order by 1;

select * from DBA_AUTOTASK_WINDOW_CLIENTS;

When was the last time that dictionary stats have been collected?

SELECT DISTINCT TRUNC(last_analyzed), COUNT(*)
FROM dba_tables
WHERE owner=’SYS’
GROUP BY TRUNC(last_analyzed);

When was the last time fixed object stats have been collected?

SELECT DISTINCT TRUNC(last_analyzed), COUNT(*)
FROM dba_tab_statistics
WHERE object_type = ‘FIXED TABLE’
GROUP BY TRUNC(last_analyzed);

Are there objects without statistic, and how many?

select owner, table_name from dba_tables where last_analyzed is null;
select owner, index_name from dba_indexes where last_analyzed is null;

What is the retention on the statistics? How far back can I restore statistics?

select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

I personally prefer to disable the default stats job, that is delivered with the database.WHY?

First of all the default stats job is running during a predefined maintenance window, and if the job doesn’t complete before the window closes, the job terminates, and there will be objects left without statistics. These objects should be picked up the next time the job runs, however it’s not always the case. From my experience, I noticed that there will be objects without statistics, even if the job completes.

Second of all, having your custom job that gathers statistics, means you are in control of what, when and how it is running.
Having said that, only disable the default stats job, if you have another job, to replace it with.

Part 3 of this series will cover the custom job for gathering statistics, what should be included in the script and why! Until then check the settings for stats in your databases!