Stale Stats

The question of how to list objects in need of stats collection appeared on the OTN database forum today from a DBA who had a system collecting stats at the weekend, but wanted to check whether there were any objects with stale stats during the week. It’s actually very easy to do this check because the schema and database calls to gather stats have an option to “list stale”; they also allow you to “list empty”, and “list auto” – the latter being the objects that would be subject to collection if you change the option to (the default) “gather”. “List auto” seems to be the concatenation of “list stale” and “list empty”, by the way.

So, after reading up the manual pages on dbms_stats ($ORACLE_HOME/rdbms/admin/dbmsstat.sql, or the PL/SQL Packages reference) all it takes is a little wrapping to do the job. The code below is a little overkill because it covers 6 different options in one go:

declare
	m_objects	dbms_stats.ObjectTab;
begin
--	dbms_stats.gather_schema_stats(
--		ownname	=> user,
	dbms_stats.gather_database_stats(
		options	=> 'LIST AUTO',
		objlist	=> m_objects
	);

	dbms_output.new_line;
	dbms_output.put_line('Auto: ' || m_objects.count);
	dbms_output.put_line('--------------------');
	for i in 1..m_objects.count loop
		dbms_output.put(rpad(m_objects(i).ownname,10));
		dbms_output.put(rpad(m_objects(i).objname,32));
		dbms_output.put(rpad(nvl(m_objects(i).partname,' '),32));
		dbms_output.put(rpad(nvl(m_objects(i).subpartname,' '),32));
		dbms_output.put(rpad(m_objects(i).objtype,6));
		dbms_output.new_line;
	end loop;

--	dbms_stats.gather_schema_stats(
--		ownname	=> user,
	dbms_stats.gather_database_stats(
		options	=> 'LIST STALE',
		objlist	=> m_objects
	);

	dbms_output.new_line;
	dbms_output.put_line('Stale: ' || m_objects.count);
	dbms_output.put_line('--------------------');
	for i in 1..m_objects.count loop
		dbms_output.put(rpad(m_objects(i).ownname,10));
		dbms_output.put(rpad(m_objects(i).objname,32));
		dbms_output.put(rpad(nvl(m_objects(i).partname,' '),32));
		dbms_output.put(rpad(nvl(m_objects(i).subpartname,' '),32));
		dbms_output.put(rpad(m_objects(i).objtype,6));
		dbms_output.new_line;
	end loop;

--	dbms_stats.gather_schema_stats(
--		ownname	=> user,
	dbms_stats.gather_database_stats(
		options	=> 'LIST EMPTY',
		objlist	=> m_objects
	);

	dbms_output.new_line;
	dbms_output.put_line('Empty: ' || m_objects.count);
	dbms_output.put_line('--------------------');
	for i in 1..m_objects.count loop
		dbms_output.put(rpad(m_objects(i).ownname,10));
		dbms_output.put(rpad(m_objects(i).objname,32));
		dbms_output.put(rpad(nvl(m_objects(i).partname,' '),32));
		dbms_output.put(rpad(nvl(m_objects(i).subpartname,' '),32));
		dbms_output.put(rpad(m_objects(i).objtype,6));
		dbms_output.new_line;
	end loop;

end;
/

The code inludes the three list options, and both database and schema calls – choose which one to comment out, the call to the schema gather needs the ownname parameter included.

There are a couple of irritating little details with the feature – best described through an example of the output, which I’ve taken from the call to “list empty” for the sys schema from a copy of 11.2.0.3 that I happened to have around.

SYS       WRI$_OPTSTAT_SYNOPSIS$          P_74334                                                      TABLE
SYS       WRI$_OPTSTAT_SYNOPSIS$          P_74333                                                      TABLE
SYS       SYS_IOT_TOP_66252                                                                            INDEX
SYS       SYS_IOT_TOP_66254                                                                            INDEX
SYS       SYS_IOT_TOP_66257                                                                            INDEX
SYS       SYS_IOT_TOP_66260                                                                            INDEX
SYS       SYS_IOT_TOP_66302                                                                            INDEX

I didn’t code in any headings, but you can see that the first two lines are reporting partitions that have no stats – but the object type (limited by the definition built into the dbms_stats package) is simply “TABLE” rather than “TABLE PARTITION”. The last five lines report objects that are the physical instantiation of index organized tables so, although they are indexes and we can call gather_index_stats(), we would probably prefer to know the table name that they came from. Neither problem is a big issue, of course; it wouldn’t be very difficult to modify the PL/SQL to return the values we would prefer to see.

For the enterprising and enthusiastic – tonight’s assignment.  Wrap code like this into a pipelined function so that you can issue a call like:

select * from table(stats_required('list stale'[,{schema}));

Update (3rd Jan)

Thanks to Guest at comment 4 for his offering which captures a critical point (the autonomous transaction problem). Here’s my quick solution:

create or replace function stats_required(
	i_status	in	varchar2	default 'LIST AUTO',
	i_schema	in	varchar2	default user
)
return dbms_stats.ObjectTab pipelined
as
	pragma autonomous_transaction;
	m_objects	dbms_stats.ObjectTab;
begin
	if upper(i_status) not in (
		'LIST AUTO', 'LIST STALE', 'LIST EMPTY'
	) then
		return;
	end if;

	if i_schema is null then
		dbms_stats.gather_database_stats(
			options	=> i_status,
			objlist	=> m_objects
		);
	else
		dbms_stats.gather_schema_stats(
			ownname	=> i_schema,
			options	=> i_status,
			objlist	=> m_objects
		);
	end if;
	commit;

	for i in 1..m_objects.count loop
		pipe row(m_objects(i));
	end loop;

	return;

end;
/