Script for move stats of previous to next month partition in Oracle
In one of our customer is facing problems on first of every month due to no data present on new partition that why its stats calculated in zero rows. To fixed this issue we copy the previous month stats to the next month stats manually so that it work properly on 1st of month.
Following script will calculate the stats of previous month then move to future month so stats remain same for SQL queries and run fast as it run for previous month.
Script Workaround
1. Script work with monthly partition name like SALE201901, SALE201902, SALES201903 .. So on
2. You need to specify the initial parameter such as partition prefix, schema name and table name.
3. It calculate the stats of previous month partition and move to upcoming partition.
4. Scheduled in CRON or Window Scheduler of last SATURDAY.
Example
Suppose March month is going and my partition name is SALES201903, it will calculate FEB month(SALES201902) stats again and copy to APRIL Month(SALES201904) for performance fixed.
Script to move stats from previous month to next future month
Set serveroutput on
declare
w_schema_name varchar2(20);
w_table_name varchar2(20);
w_PartitionName_prefix varchar2(20);
w_next_part varchar2(20);
w_prev_part varchar2(20);
begin
-- Enter Schema Name
w_schema_name := 'SALES';
-- Enter Table NAME
w_table_name := 'TRAN';
-- In my case prefix is SALES--- My partition names like SALES 201901,201902...
w_PartitionName_prefix := 'SALES';
dbms_output.put_line('Process started.' );
dbms_output.put_line('Schema Name: ' || w_schema_name );
dbms_output.put_line('Table Name: '||w_table_name );
dbms_output.put_line('Partition Prefix: '||w_PartitionName_prefix );
Select Case when (to_char(sysdate,'MM')+1) > 12 then w_PartitionName_prefix||(to_char(sysdate,'YYYY')+1)||'01' else w_PartitionName_prefix||(to_char(sysdate,'YYYYMM')+1) end into w_next_part from dual;
Select Case when (to_char(sysdate,'MM')-1) = 0 then w_PartitionName_prefix||(to_char(sysdate,'YYYY')+1)||'12' else w_PartitionName_prefix||(to_char(sysdate,'YYYYMM')-1) end into w_prev_part from dual;
DBMS_STATS.GATHER_TABLE_STATS (ownname => w_schema_name, tabname =>w_table_name, PARTNAME => w_prev_part,degree=>8,force=>true);
dbms_output.put_line('Copy stats: '||w_prev_part ||' to '|| w_next_part);
dbms_stats.copy_table_stats(ownname => w_schema_name, tabname => w_table_name, srcpartname => w_prev_part, dstpartname => w_next_part,force=>TRUE);
dbms_output.put_line('Process Completed');
end;
/