-- 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

 

1