Gather Stats: The Script That Keeps Your Users Happy

How can a script keep the users of the database happy? Well, up-to-date stats on the database=good performance, and the reverse is also true: outdated stats, or no stats=bad performance. In my experience, I noticed that 90% of the performance problems, are related to optimizer statistics.  That being said, you can understand why statistics in your database are so important, and why having up to date statistics would keep the users happy. This post is all about that custom gather stats script, that I have scheduled for my database. Beware, final script is not provided 🙂

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, or stale 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 runs.
Having said that, only disable the default stats job, if you have another job, to replace it with.

How do you disable the default stats job?

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => ‘auto optimizer stats collection’, operation => NULL, window_name => NULL);

Now that you disabled the default job, you need to replace it with something else, a custom script that does the following, but is not limited to:

1. gather dictionary stats
2. gather stats on tables, table partitions, and indexes with stale statistics (LIST STALE)
3. gather stats on tables, table partitions, and indexes with no stats (LIST EMPTY)
4. capture the duration of stats gathering for each object, so you are aware on which table is the statistics gathering the most time consuming
5. capture the gather stats statement for each object, so you know exactly what was run. I usually output this to the log file
6. capture any errors that you might receive
7. gather custom stats for specific objects, if needed.

Why do you need to gather stats for both, objects with stale statistics and objects with no statistics? List stale will not list objects that have no statistics. If you create a new table, and
don’t explicitly gather stats on it, then this table will never be reported by LIST STALE.
How often should you run the custom script? I personally schedule it once a week, on Saturday or Sunday, when there is not much going on in the database. However it all depends on how volatile the table data is, how much it changes every day. I also prefer keeping the log files for at least the last 4 runs. Below is an example, on how to report objects that have stale stats, and no stats at all.

SET SERVEROUTPUT ON
DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>’LIST STALE’);
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || ‘.’ || ObjList(i).ObjName || ‘ ‘ || ObjList(i).ObjType || ‘ ‘ || ObjList(i).partname);
END LOOP;
END;
/

SET SERVEROUTPUT ON
DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>’LIST EMPTY’);
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || ‘.’ || ObjList(i).ObjName || ‘ ‘ || ObjList(i).ObjType || ‘ ‘ || ObjList(i).partname);
END LOOP;
END;
/