gather_schema_stats

You can gather statistics for a schema as follows:

ORACLE 9 > sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0.7.0 - Production on Thu Sep 16 10:31:39 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning option

JServer Release 9.2.0.7.0 - Production

SQL> exec dbms_stats.gather_schema_stats(ownname=>'SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats(ownname=>'SYSTEM');

PL/SQL procedure successfully completed.

SQL>

You used to be told not to analyze tables owned by SYS. However, we found that this was required before exporting constraints etc. from a production database during an upgrade from Oracle 9 to 11. It reduced the time taken from 2 to 3 hours down to 5 minutes.