Oracle Scratchpad

January 1, 2013

Stale Stats

Filed under: Oracle,Statistics — Jonathan Lewis @ 6:02 pm GMT Jan 1,2013

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

14 Comments »

  1. Hi Jonathan,

    I think that if the goal is to determine the list of tables that need their optimizer stats updated, it would be more convenient to compare the actual number of blocks in the table to the optimizer estimate, e.g.:

    select t.table_name, s.blocks "A-blocks", T.BLOCKS "E-blocks", round(100*decode(t.blocks, 0, null, abs(s.blocks/t.blocks-1))) "Accuracy (%)", t.last_analyzed
    from dba_tables t,
            (select segment_name, sum(blocks) blocks from dba_segments group by segment_name) s
    where t.table_name = s.segment_name
    order by 4 desc nulls first;        
    

    Best regards,
    Nikolay

    Comment by savvinov — January 1, 2013 @ 6:50 pm GMT Jan 1,2013 | Reply

    • Hi Nikolay,
      This method is not quite reliable and not always can be used. For example, it does not consider multiple updates or deletes without changing segment size( though in such cases dba_tab_modifications could be useful ), or IOTs…

      Comment by Sayan Malakshinov — January 1, 2013 @ 8:13 pm GMT Jan 1,2013 | Reply

      • One of the things I’m not so keen on with the *_tab_modifications views is that it’s often the case that a large fraction of the updates you do are on columns which aren’t likely to be used in where clauses. Along with being able to set table-level preferences for what percentage change constitutes stale, it would be nice if we could specify which actions (inserts, updates, deletes) should be considered as relevant. (Count them all to keep the in-memory code easy, but have three flags decoded when deciding on staleness).

        Comment by Jonathan Lewis — January 1, 2013 @ 8:32 pm GMT Jan 1,2013 | Reply

    • Nikolai,

      With my tongue slightly in my cheek I think I’d have to say that if you need to determine the list of tables that need their statistics updated then you first need to understand your data. Fortunately this individual wanted to know which objects had stale stats, so asking Oracle seems to be the ideal strategy for answering the question.

      A couple of points about your query, though – it’s very hard to write a query that gets the right answers and doesn’t miss things out. In your case you’ve missed the join to owner, not allowed for nested tables, index organized tables, clustered tables or partitioned tables (which is fine if you happen to know there’s only one real data owner in the system and you don’t use anything but simple heap tables.

      One of the replies to the thread highlights the difficulty of getting it right – it joins dba_tables to dba_tab_modifications, which means losing rows or multiplying up various rows in all but the simplest systems.

      Comment by Jonathan Lewis — January 1, 2013 @ 8:14 pm GMT Jan 1,2013 | Reply

    • Hi Sayan,

      I agree that this method has many drawbacks, it’s just that I wasn’t aware of all the alternatives. It looks like that USER_TAB_MODIFICATIONS is the best way available (provided that database monitoring info was flushed recently).

      Thanks!

      Comment by savvinov — January 3, 2013 @ 8:23 am GMT Jan 3,2013 | Reply

  2. Hi Jonathan
    What’s wrong with checking dba_tab_statistics.stale_stats and dba_ind_statistics.stale_stats (10.2 onwards)? Have you found this to be inaccurate sometimes?
    Thx
    Owen

    Comment by Owen Ireland — January 1, 2013 @ 7:58 pm GMT Jan 1,2013 | Reply

    • Owen,

      Depending on the version of Oracle the underlying tables aren’t necessarily updated in a timely fashion, so you have to remember to call the procedure to flushing monitoring info before querying the views (Randolf Geist supplies the name of the procedure this in his comment on the thread). There are also a few gaps – for example the stats on a nested table need to be collected separately from the stats on the parent table (unless I’ve missed another new option somewhere). user_tab_statistics doesn’t report nested tables at all – the call to “list stale” can.

      Apart from that I’d simply forgotten to keep up to date with the various new columns and procedures that appear with monotonous regularity with Oracle upgrades and leave you doing things the hard way instead of using the new, easy way. (Although the new easy way sometimes isn’t quite as easy to get right as one hopes.)

      Comment by Jonathan Lewis — January 1, 2013 @ 8:27 pm GMT Jan 1,2013 | Reply

  3. Gee, I was really hoping someone else would have pipelined this by now. :)

    Comment by Jared — January 2, 2013 @ 1:13 am GMT Jan 2,2013 | Reply

    • Jared,

      Give it a few more hours for the effects of new year’s celebrations to wear off, and someone’s sure to succumb to temptation. (If not, I’ll do it this evening.)

      Comment by Jonathan Lewis — January 2, 2013 @ 2:01 pm GMT Jan 2,2013 | Reply

  4. CREATE OR REPLACE FUNCTION stats_required (i_option   IN VARCHAR2,
                                               i_user     IN VARCHAR2)
       RETURN DBMS_STATS.objecttab
       PIPELINED
    AS
       /*
        This function uses autonomous transaction pragma to avoid: ORA-04092: cannot COMMIT in a trigger
       */
       PRAGMA AUTONOMOUS_TRANSACTION;
       l_object_list   DBMS_STATS.objecttab;
       filter_list     DBMS_STATS.objecttab := DBMS_STATS.objecttab ();
    BEGIN
       filter_list.EXTEND (1);
       filter_list (1).objtype   := 'TABLE';
    
       DBMS_STATS.gather_schema_stats (ownname           => i_user,
                                       options           => i_option,
                                       objlist           => l_object_list,
                                       obj_filter_list   => filter_list);
    
       FOR i IN 1 .. l_object_list.COUNT
       LOOP
          PIPE ROW (l_object_list (i));
       END LOOP;
    
       RETURN;
    END stats_required;
    /
    

    Comment by Guest — January 2, 2013 @ 3:17 pm GMT Jan 2,2013 | Reply

    • Guest,

      Thanks for that.
      I like your use of the obj_filter_list (available only in 11g, I believe). I didn’t think of that in my version.
      I wrote a version with a couple of default values and a check on the options – otherwise very similar.

      Comment by Jonathan Lewis — January 3, 2013 @ 7:06 am GMT Jan 3,2013 | Reply

  5. […] Similar to “drawing an index”, but listing just the branch blocks in index order. Stale Stats: A simple piece of pl/sql to report objects with stale or missing […]

    Pingback by Simple scripts « Oracle Scratchpad — January 5, 2013 @ 10:09 am GMT Jan 5,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,429 other followers