How Are Statistics Gathered In The Database?

Do you gather statistics regularly for your databases? Do you gather statistics explicitly, with your own script, or do you let the default job gather statistics?
Are you aware that such a job exists?

Most seasoned DBAs know and understand that database statistics are very important for the performance of the database. If you are not familiar with database statistics, you can read more details about it, in my previous article, 3 Things You Must Know About Oracle Optimizer Statistics.
Anyways, it is not enough to know about something, what maters is what you do about that thing! In other words, what maters is if you have something implemented to gather statistics in the database.

1.What Is The Default Gather Stats Task?
2.How To Enable/Disable Default Stats Task?
3.How To Check Tables With Stale/Empty Stats?

1.What Is The Default Gather Stats Task?

Out of the box, Oracle comes with a default job (better wording would be a task), that gathers statistics. This is a great thing!
Even if you don’t know how to setup statistics gathering, at least you have a default task, that gathers stats for you, without you doing anything.

And truth be told, lots of databases run with only the default task.

Is your database running with the default task? Leave a comment below!

What is this task?

Statistics gathering is implemented using the Automatic Optimizer Statistics Collection Maintenance task. The name of the task is ‘auto optimizer stats collection‘.
The task is scheduled to run during the maintenance window, and it is supposed to gather statistics on the objects with stale or missing stats.

Why are lots of people referring to this task as a job? It all goes back to the 10g days. There was a default job that was gathering statistics (a dba_jobs job).
Starting with 11g, and going on to 12c, there is no gather stats job. And when I say job, I am referring to a dba_scheduler_jobs.

The information below applies to 11g and 12c.

Oracle has implemented maintenance tasks, and one of them is the Automatic Optimizer Statistics Collection. This task runs a program, called gather_stats_prog, which in turn invokes the following plsql block: dbms_stats.gather_database_stats_job_proc

select client_name, task_name, status, program_action
from dba_autotask_task, dba_scheduler_programs
where upper(task_name)=upper(program_name)
and client_name='auto optimizer stats collection';

CLIENT_NAME			   TASK_NAME	      STATUS   PROGRAM_ACTION
---------------------------------- ------------------ -------- -----------------------------------------
auto optimizer stats collection    gather_stats_prog  ENABLED  dbms_stats.gather_database_stats_job_proc

2.How To Enable/Disable Default Stats Task?

If you want to verify whether the default task is enabled in your database, all you need to do is query DBA_AUTOTASK_CLIENT or DBA_AUTOTASK_TASK view.
If you are curious about the differences between the two views, below is the definition given by Oracle:

DBA_AUTOTASK_TASK displays information about current and past automated maintenance tasks.
DBA_AUTOTASK_CLIENT displays statistical data for each automated maintenance task over 7-day and 30-day periods.

select client_name, status from dba_autotask_client
where client_name='auto optimizer stats collection';

CLIENT_NAME		            STATUS
----------------------------------- --------
auto optimizer stats collection     ENABLED

To enable/disable this task, use the following procedure. This will disable the task for all the windows.

BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'auto optimizer stats collection',
    operation   => NULL,
    window_name => NULL);
end;
/
PL/SQL procedure successfully completed.

select client_name, status from dba_autotask_client
where client_name='auto optimizer stats collection';   

CLIENT_NAME			    STATUS
----------------------------------- --------
auto optimizer stats collection     DISABLED

BEGIN
  DBMS_AUTO_TASK_ADMIN.enable(
    client_name => 'auto optimizer stats collection',
    operation   => NULL,
    window_name => NULL);
end;
/
PL/SQL procedure successfully completed.

SQL> select client_name, status from dba_autotask_client
where client_name='auto optimizer stats collection';   2  

CLIENT_NAME		            STATUS
----------------------------------- --------
auto optimizer stats collection     ENABLED

3.How To Check Tables With Stale/Empty Stats?

Ever wondered how you can check what tables are candidates for new statistics?
By default Oracle considers the statistics to be stale if the table data has changed by 10% or more. The stale percent can be changed, at the database level, schema level or table level, with dbms_stats.

List tables with stale statistics:

select owner, table_name , partition_name from dba_tab_statistics where stale_stats=’YES’;
…
OWNER TABLE_NAME PARTITION_NAME
———- ——————————- ————–
SYS WRI$_ADV_ADDM_TASKS
SYS WRI$_ADV_ADDM_INST
SYS WRI$_ADV_ADDM_FDG
SYS SCHEDULER$_JOB
SYS SCHEDULER$_WINDOW
SYS SCHEDULER$_EVENT_LOG
SYS SCHEDULER$_JOB_RUN_DETAILS
SYS SCHEDULER$_WINDOW_DETAILS
…

List tables with empty statistics:

select owner, table_name from dba_tab_statistics where last_analyzed is null;
…
OWNER TABLE_NAME
———- —————————
SYS EDITIONING_TYPES$
SYS ATEMPTAB$
SYS MAP_OBJECT
SYS CLUSTER_DATABASES
SYS CLUSTER_NODES
SYS CLUSTER_INSTANCES
SYS FINALHIST$
SYS SUMDELTA$
SYS SNAP_XCMT$
SYSTEM LOGMNR_GT_TAB_INCLUDE$
SYSTEM LOGMNR_GT_USER_INCLUDE$
…

You could also check dba_tables, and if the last_analyzed column is null, it means the table has no stats.

There is another way of checking, which can easily be implemented into a script (this is what I use):

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;
/

…
SYS.COL_USAGE$ TABLE
SYS.DBMS_LOCK_ALLOCATED TABLE
SYS.HISTGRM$ TABLE
SYS.INDPART$ TABLE
SYS.KET$_AUTOTASK_STATUS TABLE
SYS.KET$_CLIENT_CONFIG TABLE
SYS.KET$_CLIENT_TASKS TABLE
SYS.LOBFRAG$ TABLE
SYS.MON_MODS$ TABLE
…

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;
/
…
SYS.WRP$_REPORTS_DETAILS TABLE SYS_P1728
SYS.WRP$_REPORTS_TIME_BANDS TABLE SYS_P1726
SYS.WRP$_REPORTS_DETAILS_IDX01 INDEX SYS_P1728
SYS.WRP$_REPORTS_DETAILS_IDX02 INDEX SYS_P1728
SYS.WRP$_REPORTS_IDX01 INDEX SYS_P1727
SYS.WRP$_REPORTS_IDX02 INDEX SYS_P1727
…

Follow me next week, on a new journey about database statistics. I will show you what type of stats you should gather regularly. I will also show you and walk you through the script I use to gather statistics!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana