we are running stats gather on the partition of the table , this process takes only 4-5 mins in old machine but after migration to exadata , we see this stats is taking 4 hours.
exec dbms_stats.gather_table_stats(ownname=> 'SCHEMA' ,tabname=> 'TABLE_NAME', partname=> 'PART_NAME',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1' ,degree=>4,granularity=> 'PARTITION' ,cascade=>TRUE);
We tried troubleshooting this command , but found the solution here.
We have enabled the incremental stats on the table
SQL> select DBMS_STATS.GET_PREFS('INCREMENTAL','SCHEMA','TABLE_NAME') from dual;
DBMS_STATS.GET_PREFS('INCREMENTAL','SCHEMA','TABLE_NAME')
--------------------------------------------------------------------------------
TRUE
SQL> select DBMS_STATS.GET_PREFS('PUBLISH','SCHEMA','TABLE_NAME') from dual;
DBMS_STATS.GET_PREFS('PUBLISH','SCHEMA','TABLE_NAME')
--------------------------------------------------------------------------------
TRUE
We went ahead and disabled the incremental stats and stats job took only 5 secs to complete.
SQL> exec DBMS_STATS.SET_TABLE_PREFS ('SCHEMA','TABLE_NAME','INCREMENTAL', 'FALSE');
PL/SQL procedure successfully completed.
Oracle support came back with a bug Bug:11710056 but it applicable to 11.2.0.2 only and not to 11.2.0.3
exec dbms_stats.gather_table_stats(ownname=> 'SCHEMA' ,tabname=> 'TABLE_NAME', partname=> 'PART_NAME',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1' ,degree=>4,granularity=> 'PARTITION' ,cascade=>TRUE);
We tried troubleshooting this command , but found the solution here.
We have enabled the incremental stats on the table
SQL> select DBMS_STATS.GET_PREFS('INCREMENTAL','SCHEMA','TABLE_NAME') from dual;
DBMS_STATS.GET_PREFS('INCREMENTAL','SCHEMA','TABLE_NAME')
--------------------------------------------------------------------------------
TRUE
SQL> select DBMS_STATS.GET_PREFS('PUBLISH','SCHEMA','TABLE_NAME') from dual;
DBMS_STATS.GET_PREFS('PUBLISH','SCHEMA','TABLE_NAME')
--------------------------------------------------------------------------------
TRUE
We went ahead and disabled the incremental stats and stats job took only 5 secs to complete.
SQL> exec DBMS_STATS.SET_TABLE_PREFS ('SCHEMA','TABLE_NAME','INCREMENTAL', 'FALSE');
PL/SQL procedure successfully completed.
Oracle support came back with a bug Bug:11710056 but it applicable to 11.2.0.2 only and not to 11.2.0.3
No comments:
Post a Comment