Recalculation of Table Statistics

The procedure, check_tables, accepts an owner name and then checks that owner’s tables for the amount they have changed, if the tables have changes by more than +/- 25% they are analyzed. If the row count for a given table exceeds a million rows then the table is analyzed using a  30 percent sample size. The command for submitting the check_tables procedure to execute every night at 10 pm would be:

VARIABLE x NUMBER;

EXECUTE DBMS_JOB.SUBMIT(:x,’BEGIN DBMS_REVEALNET.REDO_PIN; END;’, TRUNC(SYSDATE)+(22/24),’TRUNC(SYSDATE)+1+(22/24)’);

Again, the TRUNC command prevents time creep from moving the execution of the job later and later from the desired time. By automating the table analysis process you eliminate botched report runs, SQL selects and other query intensive operations that require good database statistics be present for frequently changing tables.

SEE CODE DEPOT FOR FULL SCRIPTS

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm