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