Bolte IT Consulting

Unfortunately you will not always have all rights on the database you would want or need to fulfill your tasks.

Especially when you have to do performance tuning on Queries and you do not have the right to use the Oracle Advisor you can get frustrated because the DBA is not available as you only do stuff on a UAT or even SIT. "Sorry, the DBA is busy with PRD." Nice!

When in addition your company decided to switch off automated update of statistics for the Optimizer, you want to pull out your hair.

Why would someone do this you might ask. Well, one of the applications we are running is stating in its requirements that the optimizer should be switched off. It is updating stats on its own.

When you are developing new reports, this is of course not very nice. If you are suffering from this as well, the following script might be of interest for you.

You can run the script in SQLDeveloper and you will have to activate the DBMS_OUTPUT view to see its results.

Important is that you only update statistics based on this if it makes sense in your application.

For example it does not make sense to update the stats for one table to a lower value than the usual if it occasionally contains only few entries.

Sometimes it is better to leave the stats on a higher count than currently available in a checked table.

Source Code

The following script lists all tables, which shows a stat value lower than the current count as stale.

All other table stats are listed as fresh.

DECLARE
  CURSOR all_tab IS
    SELECT table_name, num_rows
    FROM all_tables 
    WHERE TABLE_NAME in (
		'table_1',
		'table_2'
    );
    --like upper('%ft_%');
            
  tab_name varchar2(30);
  cur_name varchar2(30);
  qu varchar2(200 char);
  TYPE cur_typ IS REF CURSOR;
    c cur_typ;
  stat_count number;
  actual_count number;
  stat_state varchar2(30);
  
begin
  /*Get all tables of interest. */
  open all_tab;
  Loop
  Fetch all_tab Into tab_name, stat_count;
      Exit When all_tab%NOTFOUND;
      
      /* Count the records in a table */
      qu := 'select count(*) from ' || tab_name;
      --qu := 'select count(*) from avuser.t_av_rules';
      execute immediate qu into actual_count;
      if ( stat_count < actual_count ) then
        stat_state := 'stale';
      else
        stat_state := 'fresh';
      end if;
      dbms_output.put_line(stat_state || ': ' || tab_name || ' stats ' || stat_count || ' actual ' || actual_count);
  End Loop;
  close all_tab;
end;

Enjoy.