/********************************************************************** * File: summarize_stats.sql * Type: SQL*Plus script * Author: Tim Gorman (Evergreen Database Technologies, Inc.) * Date: 17-Apr-2006 * * Description: * DDL script to create the SUMMARIZE_STATS stored procedure, * which summarizes or aggregates statistics gathered at the * sub-partition level (when the boolean parameter P_IN_CALC_PART * is set to TRUE) to set into the (table/index) partition level. * * Then, it summarizes or aggregates statistics at the partition * level to set at the global (table/index) level. * * Modifications: * TGorman 02sep09 - populate DISTINCT_KEYS for indexes/index-partitions * TGorman 02sep09 - fixed some inaccuracies in how average values * (i.e. AVG_ROW_LEN, etc) are summarized, using * the analytic windowing function RATIO_TO_REPORT * and in-line views * TGorman 03sep09 - added COLUMN statistics summarization, in * addition to TABLE- and INDEX-statistics * summarization *********************************************************************/ set echo on feedback on timing on trimout on trimspool on pagesize 100 linesize 130 pause off spool summarize_stats create or replace procedure summarize_stats ( p_in_owner in varchar2, p_in_table in varchar2, p_in_calc_part in boolean default FALSE) is -- cursor table_partition_stats (in_owner in varchar2, in_table in varchar2) is select partition_name, sum(num_rows) num_rows, sum(blocks) blocks, round(sum(avg_row_len*pct),0) avg_row_len from (select partition_name, num_rows, blocks, avg_row_len, ratio_to_report(num_rows) over (partition by partition_name) pct from all_tab_subpartitions where table_owner = upper(in_owner) and table_name = upper(in_table) and nvl(num_rows,0) > 0) group by partition_name order by partition_name; -- cursor table_global_stats (in_owner in varchar2, in_table in varchar2) is select sum(num_rows) num_rows, sum(blocks) blocks, round(sum(avg_row_len*pct),0) avg_row_len from (select num_rows, blocks, avg_row_len, ratio_to_report(num_rows) over () pct from all_tab_partitions where table_owner = upper(in_owner) and table_name = upper(in_table) and nvl(num_rows,0) > 0); -- cursor index_partition_stats (in_owner in varchar2, in_table in varchar2) is select index_name, partition_name, sum(num_rows) num_rows, sum(leaf_blocks) leaf_blocks, round(sum(distinct_keys*pct),0) + (sum(distinct_keys) - round(sum(distinct_keys*pct),0)) distinct_keys, round(sum(avg_leaf_blocks_per_key*pct),0) avg_leaf_blocks_per_key, round(sum(avg_data_blocks_per_key*pct),0) avg_data_blocks_per_key, round(sum(clustering_factor*pct),0) clustering_factor, round(sum(blevel*pct),0) blevel from (select p.index_name, p.partition_name, p.num_rows, p.distinct_keys, p.leaf_blocks, p.avg_leaf_blocks_per_key, p.avg_data_blocks_per_key, p.clustering_factor, p.blevel, ratio_to_report(p.num_rows) over (partition by p.index_name, p.partition_name) pct from all_ind_subpartitions p, all_part_indexes i where i.owner = upper(in_owner) and i.table_name = upper(in_table) and i.partitioning_type is not null and i.partition_count > 1 and p.index_owner = i.owner and p.index_name = i.index_name and nvl(p.num_rows,0) > 0) group by index_name, partition_name order by index_name, partition_name; -- cursor index_global_stats (in_owner in varchar2, in_table in varchar2) is select index_name, sum(num_rows) num_rows, sum(leaf_blocks) leaf_blocks, round(sum(distinct_keys*pct),0) + (sum(distinct_keys) - round(sum(distinct_keys*pct),0)) distinct_keys, round(sum(avg_leaf_blocks_per_key*pct),0) avg_leaf_blocks_per_key, round(sum(avg_data_blocks_per_key*pct),0) avg_data_blocks_per_key, round(sum(clustering_factor*pct),0) clustering_factor, round(sum(blevel*pct),0) blevel from (select p.index_name, p.num_rows, p.distinct_keys, p.leaf_blocks, p.avg_leaf_blocks_per_key, p.avg_data_blocks_per_key, p.clustering_factor, p.blevel, ratio_to_report(p.num_rows) over (partition by p.index_name) pct from all_ind_partitions p, all_part_indexes i where i.owner = upper(in_owner) and i.table_name = upper(in_table) and i.partitioning_type is not null and i.partition_count > 1 and p.index_owner = i.owner and p.index_name = i.index_name and nvl(p.num_rows,0) > 0) group by index_name order by index_name; -- cursor col_partition_stats (in_owner in varchar2, in_table in varchar2) is select s.column_name, s.partition_name, c.data_type, round(sum(s.num_distinct*pct),0) distcnt, sum(s.density*s.pct) density, sum(s.num_nulls) nullcnt, round(sum(s.avg_col_len*pct),0) avgclen from (select cc.column_name, pp.partition_name, cc.num_distinct, cc.density, cc.num_nulls, cc.avg_col_len, ratio_to_report(cc.num_distinct) over (partition by cc.column_name, pp.partition_name) pct from all_subpart_col_statistics cc, all_tab_subpartitions pp where cc.owner = upper(in_owner) and cc.table_name = upper(in_table) and nvl(cc.num_distinct,0) > 0 and pp.table_owner = cc.owner and pp.table_name = cc.table_name and pp.subpartition_name = cc.subpartition_name) s, all_tab_columns c where c.owner = upper(in_owner) and c.table_name = upper(in_table) and c.column_name = s.column_name group by s.column_name, s.partition_name, c.data_type order by s.column_name, s.partition_name, c.data_type; -- cursor get_subpart_lowhigh(in_owner in varchar2, in_table in varchar2, in_column in varchar2, in_partition in varchar2) is select c.subpartition_name, c.low_value, c.high_value from all_subpart_col_statistics c, all_tab_subpartitions s where c.owner = upper(in_owner) and c.table_name = upper(in_table) and c.column_name = in_column and s.table_owner = c.owner and s.table_name = c.table_name and s.partition_name = in_partition; -- cursor col_global_stats (in_owner in varchar2, in_table in varchar2) is select s.column_name, c.data_type, round(sum(s.num_distinct*pct),0) distcnt, sum(s.density*s.pct) density, sum(s.num_nulls) nullcnt, round(sum(s.avg_col_len*pct),0) avgclen from (select column_name, num_distinct, density, num_nulls, avg_col_len, ratio_to_report(num_distinct) over (partition by column_name) pct from all_part_col_statistics where owner = upper(in_owner) and table_name = upper(in_table) and nvl(num_distinct,0) > 0) s, all_tab_columns c where c.owner = upper(in_owner) and c.table_name = upper(in_table) and c.column_name = s.column_name group by s.column_name, c.data_type order by s.column_name, c.data_type; -- cursor get_part_lowhigh(in_owner in varchar2, in_table in varchar2, in_column in varchar2) is select partition_name, low_value, high_value from all_part_col_statistics where owner = upper(in_owner) and table_name = upper(in_table) and column_name = in_column; -- v_srec dbms_stats.StatRec; v_srec_default dbms_stats.StatRec; v_num number; v_dt date; v_txt varchar2(4000); v_numarray dbms_stats.numarray; v_dtarray dbms_stats.datearray; v_txtarray dbms_stats.chararray; -- already_raised exception; pragma exception_init(already_raised,-20001); -- v_errcontext varchar2(500); v_errmsg varchar2(1000); -- begin -- dbms_application_info.set_module('SUMMARIZE_STATS('|| p_in_owner||','|| p_in_table||')', 'Starting...'); -- /* * If the "p_in_calc_part" flag is set to TRUE, then we are * being directed to summarize all table sub-partition stats * up to the partition-level... */ if p_in_calc_part = TRUE then -- v_errcontext := 'open/fetch table_partition_stats'; dbms_application_info.set_action(v_errcontext); for p in table_partition_stats(p_in_owner, p_in_table) loop -- v_errcontext := 'SET_TABLE_STATS('|| p_in_owner||'.'|| p_in_table|| '('||p.partition_name||')'; dbms_application_info.set_action(v_errcontext); dbms_stats.set_table_stats(ownname=>upper(p_in_owner), tabname=>upper(p_in_table), partname=>p.partition_name, numrows=>p.num_rows, numblks=>p.blocks, avgrlen=>p.avg_row_len); -- v_errcontext := 'fetch/close table_partition_stats'; -- end loop; -- end if; -- /* * Summarize all table partition stats to the global table level... */ v_errcontext := 'open/fetch table_global_stats'; dbms_application_info.set_action(v_errcontext); for t in table_global_stats(p_in_owner, p_in_table) loop -- v_errcontext := 'SET_TABLE_STATS('||p_in_owner||'.'||p_in_table||')'; dbms_application_info.set_action(v_errcontext); dbms_stats.set_table_stats(ownname=>upper(p_in_owner), tabname=>upper(p_in_table), numrows=>t.num_rows, numblks=>t.blocks, avgrlen=>t.avg_row_len); -- v_errcontext := 'fetch/close table_global_stats'; -- end loop; -- /* * If the "p_in_calc_part" flag is set to TRUE, then we are * being directed to summarize all index sub-partition stats * up to the partition-level... */ if p_in_calc_part = TRUE then -- v_errcontext := 'open/fetch index_partition_stats'; dbms_application_info.set_action(v_errcontext); for i in index_partition_stats(p_in_owner, p_in_table) loop -- v_errcontext := 'SET_INDEX_STATS('|| p_in_owner||'.'|| i.index_name|| '('||i.partition_name||')'; dbms_application_info.set_action(v_errcontext); dbms_stats.set_index_stats(ownname=>upper(p_in_owner), indname=>i.index_name, partname=>i.partition_name, numrows=>i.num_rows, numlblks=>i.leaf_blocks, numdist=>i.distinct_keys, avglblk=>i.avg_leaf_blocks_per_key, avgdblk=>i.avg_data_blocks_per_key, clstfct=>i.clustering_factor, indlevel=>i.blevel); -- v_errcontext := 'fetch/close index_partition_stats'; -- end loop; -- end if; -- v_errcontext := 'open/fetch index_global_stats'; dbms_application_info.set_action(v_errcontext); for i in index_global_stats(p_in_owner, p_in_table) loop -- v_errcontext := 'SET_INDEX_STATS('|| p_in_owner||'.'|| i.index_name||')'; dbms_application_info.set_action(v_errcontext); dbms_stats.set_index_stats(ownname=>upper(p_in_owner), indname=>i.index_name, numrows=>i.num_rows, numlblks=>i.leaf_blocks, numdist=>i.distinct_keys, avglblk=>i.avg_leaf_blocks_per_key, avgdblk=>i.avg_data_blocks_per_key, clstfct=>i.clustering_factor, indlevel=>i.blevel); -- v_errcontext := 'fetch/close index_global_stats'; -- end loop; -- v_errcontext := 'initialize default values for srec structure'; v_srec_default.epc := 2; -- if p_in_calc_part = TRUE then -- v_errcontext := 'open/fetch col_partition_stats'; dbms_application_info.set_action(v_errcontext); for c in col_partition_stats(p_in_owner, p_in_table) loop -- v_srec := v_srec_default; /* re-initialize v_srec record structure*/ -- if c.data_type = 'NUMBER' then -- v_numarray := dbms_stats.numarray(10000000000000000000, -10000000000000000000); -- elsif c.data_type = 'DATE' then -- v_dtarray := dbms_stats.datearray(to_date('3999','YYYY'), to_date('1600','YYYY')); -- elsif c.data_type in ('VARCHAR2','CHAR') then -- v_txtarray := dbms_stats.chararray(rpad(chr(127),32,chr(127)), rpad(chr(0),32,chr(0))); -- else -- raise_application_error(-20001, upper(p_in_owner)||'.'||upper(p_in_table)|| ' column '||c.column_name||' is type '||c.data_type||' with stats; not supported'); -- end if; -- v_errcontext := 'open/fetch get_subpart_lowhigh'; dbms_application_info.set_action(v_errcontext); for lh in get_subpart_lowhigh(p_in_owner, p_in_table, c.column_name, c.partition_name) loop -- if c.data_type = 'NUMBER' then -- v_errcontext := 'part:convert_raw_value(low NUMBER)'; dbms_stats.convert_raw_value(lh.low_value, v_num); if v_num < v_numarray(1) then v_numarray(1) := v_num; end if; -- v_errcontext := 'part:convert_raw_value(high NUMBER)'; dbms_stats.convert_raw_value(lh.high_value, v_num); if v_num > v_numarray(2) then v_numarray(2) := v_num; end if; -- elsif c.data_type = 'DATE' then -- v_errcontext := 'part:convert_raw_value(low DATE)'; dbms_stats.convert_raw_value(lh.low_value, v_dt); if v_dt < v_dtarray(1) then v_dtarray(1) := v_dt; end if; -- v_errcontext := 'part:convert_raw_value(high DATE)'; dbms_stats.convert_raw_value(lh.high_value, v_dt); if v_dt > v_dtarray(2) then v_dtarray(2) := v_dt; end if; -- elsif c.data_type = 'VARCHAR2' then -- v_errcontext := 'part:convert_raw_value(low VARCHAR2)'; dbms_stats.convert_raw_value(lh.low_value, v_txt); if substr(v_txt,1,32) < v_txtarray(1) then v_txtarray(1) := substr(v_txt,1,32); end if; -- v_errcontext := 'part:convert_raw_value(high VARCHAR2)'; dbms_stats.convert_raw_value(lh.high_value, v_txt); if substr(v_txt,1,32) > v_txtarray(2) then v_txtarray(2) := substr(v_txt,1,32); end if; -- else /* CHAR */ -- v_errcontext := 'part:convert_raw_value(low CHAR)'; dbms_stats.convert_raw_value(lh.low_value, v_txt); if rpad(substr(v_txt,1,32),32,' ') < v_txtarray(1) then v_txtarray(1) := rpad(substr(v_txt,1,32),32,' '); end if; -- v_errcontext := 'part:convert_raw_value(high CHAR)'; dbms_stats.convert_raw_value(lh.high_value, v_txt); if rpad(substr(v_txt,1,32),32,' ') > v_txtarray(2) then v_txtarray(2) := rpad(substr(v_txt,1,32),32,' '); end if; -- end if; -- v_errcontext := 'fetch/close get_subpart_lowhigh'; -- end loop; -- v_errcontext := 'part:PREP_COL_VALS('||p_in_owner||'.'||p_in_table||'.'||c.column_name||')'; if c.data_type = 'NUMBER' then -- dbms_stats.prepare_column_values(v_srec, v_numarray); -- elsif c.data_type = 'DATE' then -- dbms_stats.prepare_column_values(v_srec, v_dtarray); -- else /* VARCHAR2 or CHAR */ -- dbms_stats.prepare_column_values(v_srec, v_txtarray); -- end if; -- v_errcontext := 'part:SET_COL_STATS('||p_in_owner||'.'||p_in_table||'.'||c.column_name||')'; dbms_application_info.set_action(v_errcontext); dbms_stats.set_column_stats(ownname=>upper(p_in_owner), tabname=>upper(p_in_table), colname=>c.column_name, partname=>c.partition_name, distcnt=>c.distcnt, density=>c.density, nullcnt=>c.nullcnt, avgclen=>c.avgclen, srec=>v_srec); -- v_errcontext := 'fetch/close col_partition_stats'; -- end loop; -- end if; -- v_errcontext := 'open/fetch col_global_stats'; dbms_application_info.set_action(v_errcontext); for c in col_global_stats(p_in_owner, p_in_table) loop -- v_srec := v_srec_default; /* re-initialize v_srec record structure*/ -- if c.data_type = 'NUMBER' then -- v_numarray := dbms_stats.numarray(10000000000000000000, -10000000000000000000); -- elsif c.data_type = 'DATE' then -- v_dtarray := dbms_stats.datearray(to_date('3999','YYYY'), to_date('1600','YYYY')); -- elsif c.data_type in ('VARCHAR2','CHAR') then -- v_txtarray := dbms_stats.chararray(rpad(chr(127),32,chr(127)), rpad(chr(0),32,chr(0))); -- else -- raise_application_error(-20001, upper(p_in_owner)||'.'||upper(p_in_table)|| ' column '||c.column_name||' is type '||c.data_type||' with stats; not supported'); -- end if; -- v_errcontext := 'open/fetch get_part_lowhigh'; dbms_application_info.set_action(v_errcontext); for lh in get_part_lowhigh(p_in_owner, p_in_table, c.column_name) loop -- if c.data_type = 'NUMBER' then -- v_errcontext := 'convert_raw_value(low NUMBER)'; dbms_stats.convert_raw_value(lh.low_value, v_num); if v_num < v_numarray(1) then v_numarray(1) := v_num; end if; -- v_errcontext := 'convert_raw_value(high NUMBER)'; dbms_stats.convert_raw_value(lh.high_value, v_num); if v_num > v_numarray(2) then v_numarray(2) := v_num; end if; -- elsif c.data_type = 'DATE' then -- v_errcontext := 'convert_raw_value(low DATE)'; dbms_stats.convert_raw_value(lh.low_value, v_dt); if v_dt < v_dtarray(1) then v_dtarray(1) := v_dt; end if; -- v_errcontext := 'convert_raw_value(high DATE)'; dbms_stats.convert_raw_value(lh.high_value, v_dt); if v_dt > v_dtarray(2) then v_dtarray(2) := v_dt; end if; -- elsif c.data_type = 'VARCHAR2' then -- v_errcontext := 'convert_raw_value(low VARCHAR2)'; dbms_stats.convert_raw_value(lh.low_value, v_txt); if substr(v_txt,1,32) < v_txtarray(1) then v_txtarray(1) := substr(v_txt,1,32); end if; -- v_errcontext := 'convert_raw_value(high VARCHAR2)'; dbms_stats.convert_raw_value(lh.high_value, v_txt); if substr(v_txt,1,32) > v_txtarray(2) then v_txtarray(2) := substr(v_txt,1,32); end if; -- else /* CHAR */ -- v_errcontext := 'convert_raw_value(low CHAR)'; dbms_stats.convert_raw_value(lh.low_value, v_txt); if rpad(substr(v_txt,1,32),32,' ') < v_txtarray(1) then v_txtarray(1) := rpad(substr(v_txt,1,32),32,' '); end if; -- v_errcontext := 'convert_raw_value(high CHAR)'; dbms_stats.convert_raw_value(lh.high_value, v_txt); if rpad(substr(v_txt,1,32),32,' ') > v_txtarray(2) then v_txtarray(2) := rpad(substr(v_txt,1,32),32,' '); end if; -- end if; -- v_errcontext := 'fetch/close get_part_lowhigh'; -- end loop; -- v_errcontext := 'PREP_COL_VALS('||p_in_owner||'.'||p_in_table||'.'||c.column_name||')'; if c.data_type = 'NUMBER' then -- dbms_stats.prepare_column_values(v_srec, v_numarray); -- elsif c.data_type = 'DATE' then -- dbms_stats.prepare_column_values(v_srec, v_dtarray); -- else /* VARCHAR2 or CHAR */ -- dbms_stats.prepare_column_values(v_srec, v_txtarray); -- end if; -- v_errcontext := 'SET_COL_STATS('||p_in_owner||'.'||p_in_table||'.'||c.column_name||')'; dbms_application_info.set_action(v_errcontext); dbms_stats.set_column_stats(ownname=>upper(p_in_owner), tabname=>upper(p_in_table), colname=>c.column_name, distcnt=>c.distcnt, density=>c.density, nullcnt=>c.nullcnt, avgclen=>c.avgclen, srec=>v_srec); -- v_errcontext := 'fetch/close col_global_stats'; -- end loop; -- exception -- when already_raised then raise; when others then v_errmsg := sqlerrm; raise_application_error(-20000, v_errcontext || ': '|| v_errmsg); -- end summarize_stats; / show errors spool off set echo off feedback 6 timing off