Analyze database


Most of the ANALYZE solutions around have a number of shortfalls that I don't like. Many insist that you either ESTIMATE or COMPUTE exclusively, you can't have a mixture and so forth. So I wrote my own. Unlike many others, I'm not claiming that this is the perfect solution for all, but it should be pretty close. Some of its features are:

Note that it is not currently architected for histograms, but in the classic cliche "This is left as an exercise for the reader". Its a PL/SQL procedure, the parameters of which are as follows:

procedure sys.analyze_db(
             p_segment_type varchar2,
             p_owner varchar2 default null,
             p_debug number default 0,
             p_restart number default 0,
             p_ana_or_dbms varchar2 default 'D',
             p_degree number default 1,
             p_instance number default 1)

where

p_segment_type Valid entries are TABLE or INDEX. Partitions and subpartitions will also be included.
p_owner If you only want a single owner to be processed. Default is all owners excepting 'SYS','SYSTEM', 'OUTLN','DBSNMP','TRACESVR', but you can change this trivially in the code
p_debug What kind of output you would like. 0 = just do the work, 1 = do the work and spit out messages for each object processed, 2 = same as 1 but no work is actually done
p_restart Objects are processed in OBJECT_ID order. So if the job fails for any reason, you can issue a starting object ID to resume from. Default is all objects
p_ana_or_dbms Whether to use the ANALYZE command or DBMS_STATS, specified as 'A' or 'D'. Under most circumstances this should be DBMS_STATS since the stats are better, but you'll still need the occasional ANALYZE for chained row detection
p_degree / p_instance This is for parallel executions of the procedure. This uses modulo arithmetic on the object ID to avoid clashes. For example, to run three procedures in parallel you would issue
( print "exec sys.analyze_db(p_degree=>3, p_instance=>1)" | sqlplus internal ) &
( print "exec sys.analyze_db(p_degree=>3, p_instance=>2)" | sqlplus internal ) &
( print "exec sys.analyze_db(p_degree=>3, p_instance=>3)" | sqlplus internal ) &

Source

grant analyze any to sys;

create or replace
procedure sys.analyze_db(
             p_segment_type varchar2,	         -- valid entries are TABLE,INDEX
             p_owner varchar2 default null,      -- if only one owner to be processed
             p_debug number default 0,           -- 0=do work, 1=msgs+work, 2=msgs only
             p_restart number default 0,         -- restart from this object id if you want
             p_ana_or_dbms varchar2 default 'D', -- A=use analyze command, D=use dbms_stats
             p_degree number default 1,          -- number of anticipated parallel runs
             p_instance number default 1) is     -- which child run this is
  v_start             date   := sysdate;
  v_obj_count         number := 0;
  v_cum_bytes         number := 0;
  v_last_object_ok    number := p_restart;

  v_total_blocks      number;
  v_total_bytes       number;
  v_unused_blocks     number;
  v_unused_bytes      number;
  v_last_file_id      number;
  v_last_block_id     number;
  v_last_block        number; 
  v_amount_to_analyze number;
  v_ana_command       varchar2(500);

  iot_table           exception;
    pragma exception_init(iot_table,-25191);
  snapshot_too_old    exception;
    pragma exception_init(snapshot_too_old,-1555);

  cursor c_object_list(p_start_id number ) is
     select u.name owner, o.name segment_name, o.subname partname, so.object_type segment_type,
       decode(o.subname,null,to_char(null),'('||o.subname||')') bracket_partname,
       dbms_space_admin.segment_number_blocks(ts.ts#, s.file#,
       s.block#, s.type#, s.cachehint, NVL(s.spare1,0),
       o.dataobj#, s.blocks)*ts.blocksize bytes ,o.obj# object_id
     from sys.user$ u, sys.obj$ o, sys.ts$ ts, 
          sys.sys_objects so, sys.seg$ s, sys.file$ f
     where u.name not in ('SYS',
                      'SYSTEM',
                      'OUTLN',
                      'DBSNMP',
                      'TRACESVR')
     and ( so.object_type in (p_segment_type,p_segment_type||' PARTITION')
          or ( so.object_type = p_segment_type||' SUBPARTITION' and
               p_ana_or_dbms = 'A' )
         )
     and mod(o.obj#,p_degree)+1=p_instance
     and o.obj# > p_start_id
     and u.name = nvl(p_owner,u.name)
     and s.file# = so.header_file
     and s.block# = so.header_block
     and s.ts# = so.ts_number
     and s.ts# = ts.ts#
     and o.obj# = so.object_id
     and o.owner# = u.user#
     and s.type# = so.segment_type_id
     and o.type# = so.object_type_id
     and s.ts# = f.ts#
     and s.file# = f.relfile#
     order by o.obj#

  i c_object_list%rowtype;

begin
  if p_instance not between 1 and p_degree then
     raise_application_error(-20000,'Instance must be between 1 and '||p_degree);
  end if;

  if p_ana_or_dbms not in ('D','A') then
     raise_application_error(-20000,'Must use (D)BMS_STATS or (A)NALYZE');
  end if;

  dbms_output.put_line('Processing: '||p_segment_type);
  if p_degree > 1 then
     dbms_output.put_line('Parallel run: '||p_instance||' of '||p_degree);
  else
     dbms_output.put_line('Serial run');
  end if;
 
  if p_restart > 0 then
     dbms_output.put_line('Restarting run at object id: '||p_restart);
  end if;

  dbms_output.put_line('---------------------');

  dbms_output.enable(999999);

  open c_object_list(v_last_object_ok);
  loop
    begin
      fetch c_object_list into i;
      exit when c_object_list%notfound;
    
      dbms_space.unused_space (
         i.owner, 
         i.segment_name,
         i.segment_type,
         v_total_blocks,
         v_total_bytes,
         v_unused_blocks,
         v_unused_bytes,
         v_last_file_id,
         v_last_block_id,
         v_last_block, 
         i.partname);
 

--
-- Adjust this to your needs
--
      v_amount_to_analyze := trunc(log(16,greatest(v_total_bytes-v_unused_bytes,1)));

      if v_amount_to_analyze > 7 then
         v_amount_to_analyze := 1;          -- 4G or greater means 1 percent
      elsif v_amount_to_analyze = 7 then
         v_amount_to_analyze := 10;         -- 256m or greater means 10 percent
      elsif v_amount_to_analyze = 6 then
         v_amount_to_analyze := 20;         -- 16M or greater means 30 percent
      else
         if p_ana_or_dbms = 'D' then
            v_amount_to_analyze := null;    -- smaller than that means compute
         else
            v_amount_to_analyze := 100;
         end if;
      end if;

      if p_debug > 0 then 
        dbms_output.put_line(i.segment_type);
        dbms_output.put_line(i.owner||'.'||i.segment_name||' '||i.partname);
        dbms_output.put_line(v_total_bytes||' bytes allocated');
        dbms_output.put_line((v_total_bytes-v_unused_bytes)||' bytes in use');
        dbms_output.put_line(nvl(v_amount_to_analyze,100)||'%');
        dbms_output.put_line('---------------------');
      end if;

      dbms_application_info.set_client_info('Object count: '||v_obj_count||
                                            ' (id# '||i.object_id||')');

      if p_debug < 2 then 
        if p_ana_or_dbms = 'D' then
          begin 
            if p_segment_type = 'TABLE' then
              sys.dbms_stats.gather_table_stats(
                 ownname=>i.owner, 
                 tabname=>i.segment_name, 
                 partname=>i.partname, 
                 granularity=>'ALL',
                 estimate_percent=>v_amount_to_analyze);
            else
              sys.dbms_stats.gather_index_stats(
                 ownname=>i.owner, 
                 indname=>i.segment_name, 
                 partname=>i.partname, 
                 estimate_percent=>v_amount_to_analyze);
            end if;
            v_obj_count := v_obj_count + 1;
            v_cum_bytes := v_cum_bytes + v_total_bytes-v_unused_bytes;
          exception 
            when iot_table then null;  -- ignore errors when trying to analyze
          end;                         -- an iot overflow segment
        elsif p_ana_or_dbms = 'A' then
          if p_segment_type = 'TABLE' then
              v_ana_command := 
                  'analyze table '||i.owner||'.'||i.segment_name||
                  substr(i.segment_type,6)||' '||i.bracket_partname||
                  ' estimate statistics sample '||v_amount_to_analyze||
                  ' percent for table for all columns';
          else
                  v_ana_command := 'analyze index '||i.owner||'.'||i.segment_name||
                  substr(i.segment_type,6)||' '||i.bracket_partname||
                  ' estimate statistics sample '||v_amount_to_analyze;
          end if;
          begin
            execute immediate v_ana_command;
            v_obj_count := v_obj_count + 1;
            v_cum_bytes := v_cum_bytes + v_total_bytes-v_unused_bytes;
          exception
            when others then
              dbms_output.put_line(v_ana_command);
              raise;
          end;
        end if;
      end if;

-- if we get a snapshot too old, close and re-open the cursor
-- and keep on going.  This is why we order by object id

    exception when snapshot_too_old then
      dbms_output.put_line('ora-1555 encountered, re-opening cursor and proceeding');
      close c_object_list; 
      open c_object_list(v_last_object_ok); 
    end;

    v_last_object_ok := i.object_id;

  end loop;
  close c_object_list;
  dbms_output.put_line('Objects Analyzed: '||v_obj_count);
  dbms_output.put_line('Bytes scanned:    '||v_cum_bytes);
  dbms_output.put_line('Elapsed Time:     '||round((sysdate-v_start)*86400,1));

end;
/
show errors