Search This Blog

Total Pageviews

Wednesday, 7 September 2011

Oracle GATHER_STATS_JOB in Oracle 11g or Automatic Optimizer Statistics Collection

gather_stats_job in 10g

BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;



in oracle 11g

dbms gather_stats_job or optimizer stats


SQL> BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 DBMS_AUTO_TASK_ADMIN.enable(
3 client_name => 'auto optimizer stats collection',
4 operation => NULL,
5 window_name => NULL);
6* END;
SQL> /

PL/SQL procedure successfully completed.


EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('OE',DBMS_STATS.AUTO_SAMPLE_SIZE);

AUTO_SAMPLE_SIZE lets Oracle determine the best sample size necessary for good statistics

No comments:

Oracle DBA

anuj blog Archive