Collect stats for Large Partitioned table

Applies to: Database Management Software Oracle 11g all editions

A partitioned table of size 70G, When i start gathering stats, it goes up to 16 Hrs and doesn't end.

I need to think some other way to do it. So following is the approach:

1. Gather each table partition stat one by one.
2. Make SET_TABLE_PREFS parameter of table to "INCREMENTAL". Every time stats gathering will only gather stats for only updated partitions.
3. Gather whole table stats.

Gather Each partition stat:

DECLARE
LV_SQL VARCHAR2(1000);
CURSOR C1 IS 
SELECT TABLE_NAME T , PARTITION_NAME P , LAST_ANALYZED L  FROM USER_TAB_PARTITIONS WHERE LAST_ANALYZED < SYSDATE - 2 AND 
TABLE_NAME = 'TABLE_NAME';
BEGIN
  FOR I IN C1 LOOP
LV_SQL:= 'BEGIN  ';
   LV_SQL:= LV_SQL ||'dbms_stats.gather_table_stats (''SCHEMA_NAME'',''TABLE_NAME'',partname=>'||':1'||',granularity=>''partition'') ;' ;
   LV_SQL:= LV_SQL ||' END ;';
EXECUTE IMMEDIATE LV_SQL USING  I.P ;
END LOOP;
END;
/

Set SET_TABLE_PREFS parameter:


SQL> EXEC BMS_STATS.SET_TABLE_PREFS(‘SCHEMA_NAME’,’TABLE_NAME’,’INCREMENTAL’,’TRUE’);
Using above setting each time stats gather will happen in incremental mode.

Gather Table Stats:

EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SCHEMA_NAME’, ‘TABLE_NAME’);
This method reduce my stat gathering time from 18 hrs to 6 Hr, more ever i could crosscheck how much stats has been gathers from user_table_partitions table.


No comments:

Post a Comment