dbms_utility.analyze_database


I posted this reponse to the question on the fatcity newsgroup "Why shouldn't we use dbms_utility.analyze_database?". The answer is that it will cripple database performance as the following example displays:

SQL> conn sys/xxxx
SQL> select table_name, num_rows
  2  from user_tables
  3  where rownum < 10;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
ACCESS$
AQ$_MESSAGE_TYPES
AQ$_PENDING_MESSAGES
AQ$_PROPAGATION_STATUS
AQ$_QUEUE_STATISTICS
AQ$_QUEUE_TABLE_AFFINITIES
AQ$_SCHEDULES
ARGUMENT$
ASSOCIATION$

SQL> exec dbms_utility.analyze_database('ESTIMATE');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows
  2  from user_tables
  3  where rownum < 10;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
PROXY$                                  0
DUMMY                                   1
SALGRADE                                5
BONUS                                   0
DEPT                                    4
EMP                                    14
EMPTY_TABLE                             1
JACCELERATOR$STATUS                     0
JACCELERATOR$DLL_ERRORS               469