Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.

Monday, April 16, 2007

Gathering Table Statistics By Using Database Jobs

In Oracle9i Database, sometimes you need to gather statistics for a big table, since it will take long time
you may not want to wait for it to finish or maybe you do not want to be disconnected by your company's firewall
in the middle of the stat gathering work, in a situation like that you can create a database job for that purpose
and let the job do the work at the background. You can use the some idea for your other long running processes too,
convert them to database jobs, ideally using packaged logged procedures or functions, by doing that you can monitor the
work from log records. I use the same idea in most of my work.
Connect as SYS to the database and run the example code below, put the relevant parameters for the literal parts.

DECLARE
x NUMBER;
BEGIN
DBMS_JOB.submit
(job => x,
what => 'DBMS_STATS.gather_table_stats(ownname => ''OWNER1'', tabname => ''BIGTABLE1'', estimate_percent => 10, cascade => TRUE);',
next_date => SYSDATE,
interval => NULL,
no_parse => TRUE
);
DBMS_OUTPUT.put_line ('Job Number is: '  TO_CHAR (x));
COMMIT;
END;
/

No comments: