RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM inde

From: Post, Ethan <Ethan.Post_at_ps.net>
Date: Mon, 07 Oct 2002 11:54:11 -0800
Message-ID: <F001.004E2B33.20021007115411@fatcity.com>

Here is my contribution...schedule with DBMS_JOB. It will gather stale stats using XXX_TAB_MODIFICATIONS, then get stats for tables and indexes without stats using DBMS_STATS or for tables where stats are older then "N" days. If DBMS_STATS has an error it will use the trusty "analyze statistics" command. It will quit after a specified period. This is a nice feature because I can have procedure run multiple times per week and I don't have to get everything done at once during a maintenance window. I like to run my stat analysis about 1-2 hours every night. Keeps everything up to date and running smooth. I ahve daily reporting the is generated with a section that shows me how old stats are in the database. That way I rarly miss anything. Just finished writing it and have not done much testing. Let me know if you see anything obvious.

• analyze_stats - Basic stats gatherer.
  --
  
• p_days - Calculate stats if they are >= "n" days old.
• p_minutes - Quit running after "n" minutes.
• p_monitor - Activate or deactivate table monitoring.

create or replace procedure analyze_stats (

   p_days         number default 50,
   p_minutes      number default 600,
   p_monitor      boolean default FALSE) as

   cursor c_tables is (
      select a.owner,
             a.table_name,
             a.last_analyzed,
             a.num_rows,
             a.monitoring,
             b.bytes
        from dba_tables a,
             dba_segments b
       where b.segment_type='TABLE'
         and a.table_name=b.segment_name);
         
   cursor c_indexes(p_owner varchar2,p_table varchar2) is (
      select a.owner,
             a.index_name,
             a.last_analyzed
        from dba_indexes a);

   l_gather_stats boolean;
   l_start_time date := sysdate;

   l_percent number(3);
   l_error boolean;    

begin    

• Get stale database statistics at 5%. dbms_stats.gather_database_stats(5,FALSE,'FOR ALL COLUMNS SIZE 1', NULL,'DEFAULT',TRUE,NULL,NULL,'GATHER STALE');

   for t in c_tables loop

      
      l_gather_stats := false;
      l_error := false;
      
      if (t.bytes/1048576) > 1000 then
         l_percent := 5;
      elsif (t.bytes/1048576) > 100 then
         l_percent := 10;
      elsif (t.bytes/1048578) > 10 then
         l_percent := 25;
      else
         l_percent := 50;
      end if;
      
      if t.owner in ('SYS','SYSTEM') then
         if t.num_rows is not null then
             execute immediate 'analyze table ' || t.owner || '.' || 
                t.table_name || ' delete statistics';
             -- dbms_stats.delete_table_stats(t.owner,t.table_name);
         end if;
      else
      
         if t.num_rows is null then
             l_gather_stats := true;
         else
            if trunc(sysdate-t.last_analyzed) > p_days then
                l_gather_stats := true;
            end if;
         end if;
         
         -- Activate monitoring if it is suppose to be monitored.
         if p_monitor then
            if t.monitoring = 'NO' then
               execute immediate 'alter table ' || t.owner || '.' ||
t.table_name || ' monitoring';
            end if;
         else
            if t.monitoring = 'YES' then
               execute immediate 'alter table ' || t.owner || '.' ||
t.table_name || ' nomonitoring';
            end if; 
         end if;
         
      end if;
      
      if l_gather_stats = true then
      
         begin
 
dbms_stats.gather_table_stats(ownname=>t.owner,tabname=>t.table_name,
               estimate_percent=>l_percent,cascade=>true);
         exception
            when others then
               l_error := true;
         end;
         
         -- DBMS_STATS is buggy so if error occurs go back to the old
method.
         if l_error then
            execute immediate 'analyze table ' || t.owner || '.' ||
t.table_name || 
               ' estimate statistics sample ' || l_percent || ' percent';
            -- Do indexes also.
            for i in c_indexes(t.owner,t.table_name) loop
               execute immediate 'analyze index ' || i.owner || '.' ||
i.index_name || 
               ' estimate statistics sample ' || l_percent || ' percent';

            end loop;
         end if;
         
         -- If monitoring is active then reset it manually to overcome
various bugs.
         if p_monitor then
            execute immediate 'alter table ' || t.owner || '.' ||
t.table_name || ' nomonitoring';
            execute immediate 'alter table ' || t.owner || '.' ||
t.table_name || ' monitoring';
         end if;
      end if;
      
      if l_start_time + (p_minutes/1440) <= sysdate then
         exit;
      end if;
      

   end loop;    

exception

   when others then

      dbms_output.put_line(dbms_utility.format_error_stack); end;
/
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Post, Ethan
  INET: Ethan.Post_at_ps.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Oct 07 2002 - 14:54:11 CDT