-- analyze_dwstg.sql
-- 01/08/09 Dmitri
set timing on
set serveroutput on
set feedback off
declare
V_PARTITION_CURR_MON varchar2(15);
V_PARTITION_CURR_YEAR varchar2(15);
V_PARTITION_PREV_MON varchar2(15);
V_PARTITION_PREV_YEAR varchar2(15);
function TIME_DIFF (B_ST timestamp,E_ST timestamp) return varchar2 is
v_diff number;
v_hr number;
v_min number;
v_sec number;
begin
v_diff :=
(extract(hour from E_ST)-extract(hour from B_ST))*3600+
(extract(minute from E_ST)-extract(minute from B_ST))*60+
extract(second from E_ST) - extract(second from B_ST);
v_hr := floor(v_diff/3600);
v_min := floor(mod(v_diff,3600)/60);
v_sec := trunc(mod(v_diff,60),2);
return trim(to_char(v_hr,'00'))||':'||trim(to_char(v_min,'00'))||':'||trim(to_char(v_sec,'00.00'));
end TIME_DIFF;
procedure analyze_tab_partition (
IP_TABLE_NAME varchar2,
IP_PARTITION varchar2,
IP_PERCENT number,
IP_DEGREE number ) is
V_BEGIN_TIME TIMESTAMP with time zone;
V_END_TIME TIMESTAMP with time zone;
begin
V_BEGIN_TIME := current_timestamp;
dbms_stats.gather_table_stats(ownname => 'SRDM', tabname => IP_TABLE_NAME, partname => IP_PARTITION, estimate_percent => IP_PERCENT, method_opt => 'for all columns size repeat', degree => IP_DEGREE, granularity => 'PARTITION', cascade => TRUE);
V_END_TIME := current_timestamp;
dbms_output.put_line(rpad(IP_TABLE_NAME,30) ||' '|| rpad(IP_PARTITION,15) ||' '|| TIME_DIFF(V_BEGIN_TIME,V_END_TIME));
exception
when others then
dbms_output.put_line('**************** Error '||IP_TABLE_NAME ||' '|| IP_PARTITION ||' ****************');
dbms_output.put_line( SQLERRM( SQLCODE ));
end analyze_tab_partition;
procedure analyze_tab_no_part (
IP_TABLE_NAME varchar2,
IP_PERCENT number,
IP_DEGREE number ) is
V_BEGIN_TIME TIMESTAMP with time zone;
V_END_TIME TIMESTAMP with time zone;
begin
V_BEGIN_TIME := current_timestamp;
dbms_stats.gather_table_stats(ownname => 'SRDM', tabname => IP_TABLE_NAME, estimate_percent => IP_PERCENT, method_opt => 'for all columns size repeat', degree => IP_DEGREE, cascade => TRUE);
V_END_TIME := current_timestamp;
dbms_output.put_line(rpad(IP_TABLE_NAME,46) ||' '|| TIME_DIFF(V_BEGIN_TIME,V_END_TIME));
exception
when others then
dbms_output.put_line('**************** Error '||IP_TABLE_NAME ||'****************');
dbms_output.put_line( SQLERRM( SQLCODE ));
end analyze_tab_no_part;
begin
V_PARTITION_CURR_MON := 'YEAR_' || to_char(sysdate, 'yyyy') ||'_'|| to_number(to_char(sysdate, 'mm'));
V_PARTITION_CURR_YEAR := 'YEAR_' || to_char(sysdate, 'yyyy');
V_PARTITION_PREV_MON := 'YEAR_' || to_char(add_months(sysdate, -1),'yyyy') ||'_'|| to_number(to_char(add_months(sysdate, -1),'mm'));
V_PARTITION_PREV_YEAR := 'YEAR_' || to_char(add_months(sysdate, -1),'yyyy');
dbms_output.put_line('.');
dbms_output.put_line(rpad('TABLE NAME',30) ||' '|| rpad('PARTITION',15) ||' '|| 'TIME' );
dbms_output.put_line(rpad('-----------------',30) ||' '|| rpad('---------',15) ||' '|| '----------' );
analyze_tab_partition( 'MONTHLY_PART_TABLE',V_PARTITION_CURR_MON,5,null);
analyze_tab_partition( 'MONTHLY_PART_TABLE',V_PARTITION_PREV_MON,5,null);
analyze_tab_partition( 'YEARLY_PART_TABLE',V_PARTITION_CURR_YEAR,5,null);
-- For January and February calculate stat for prev year
if to_char(sysdate, 'mm') < 3 then
analyze_tab_partition( 'YEARLY_PART_TABLE',V_PARTITION_PREV_YEAR,5,null);
end if;
-- Non Partitioned tables
analyze_tab_no_part( 'ANY_NOT_PART_TABLE',DBMS_STATS.AUTO_SAMPLE_SIZE,2);
dbms_output.put_line('.');
end;
/
exit