Pavan DBA's Blog

The DBA Knowledge Store

Simple script for scheduling statistics collection job

Posted by Pavan DBA on November 6, 2009


The below script will allow oracle to run database statistics on the time you create this script and performs this on daily basis. Also, this example shows to collect full database statistics, but for other levels like schema,table etc, refer to dbms_stats package options

SET SERVEROUTPUT ON
DECLARE
  l_job  NUMBER;
BEGIN
  DBMS_JOB.submit(l_job,’BEGIN DBMS_STATS.gather_database_stats; END;’,SYSDATE,’SYSDATE + 1′);
  COMMIT;
  DBMS_OUTPUT.put_line(‘Job: ‘ || l_job);
END;
/

Before performing any statistics job, please do question “Is my database is not performing well?”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: