Recalculation of Table Statistics

DBMS_OUTPUT.PUT_LINE('Row count for '||tab_name||': '||to_char(row_count));
DBMS_OUTPUT.PUT_LINE('Ratio: '||to_char(row_count/rows));
        IF (row_count/rows)>1.25 OR (rows/row_count)>1.25 THEN
               BEGIN
       IF (row_count<1000000) THEN
                     string :=
                                 'ANALYZE TABLE '||tab_name||'
COMPUTE STATISTICS ';
       ELSE
              string :=
        'ANALYZE TABLE '||tab_name||' ESTIMATE STATISTICS SAMPLE
30 PERCENT';
       END IF;
           cur := DBMS_SQL.OPEN_CURSOR;
DBMS_OUTPUT.PUT_LINE('Beginning analysis');
           DBMS_SQL.PARSE(cur,string,dbms_sql.v7);
           ret := DBMS_SQL.EXECUTE(cur)  ;
           DBMS_SQL.CLOSE_CURSOR(cur);
           DBMS_OUTPUT.PUT_LINE(' Table: '||tab_name||' had to be
analyzed.');
           write_out(' Table: '||tab_name||' had to be
analyzed.', row_count/rows,33,sysdate,0);
           EXCEPTION
           WHEN OTHERS THEN
            raise_application_error(-20002,'Error in analyze:
'||to_char(sqlcode)||' on '||tab_name,TRUE);
           write_out(' Table: '||tab_name||' error during
analyze. '||to_char(sqlcode), row_count/rows,33,sysdate,0);
            IF dbms_sql.is_open(cur) THEN
                dbms_sql.close_cursor(cur);
              END IF;
           END;
        END IF;
END LOOP;
CLOSE get_tab_count;
END check_tables;

Figure 6: Procedure to Check If Tables Need Analysis

SEE CODE DEPOT FOR FULL SCRIPTS

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