Normally ‘Automatic Optimizer Statistics Collection’ feature introduced in 10g should take care of updating the statistics by scheduled maintenance job in Database control or Grid control.
But in logical data guard without a Grid Control configuration, it is difficult to keep the optimizer statistics updated.
So a small job to refresh the statistics will be helpful.
OWNNAME => ‘USER’,
TABNAME => ‘TNAME’,
DEGREE => 2 ,
CASCADE => TRUE ,
METHOD_OPT=> ‘FOR ALL INDEXED COLUMNS’
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘ERROR :’ SUBSTR(SQLERRM,1,100));
Note: DBMS_STATS.GATHER_TABLE_STATS will fail against EXTERNAL tables.