gather_system_stats

SQL> exec dbms_stats.gather_system_stats (gathering_mode => 'noworkload');

PL/SQL procedure successfully completed.

SQL> select sname, pname, pval1 from sys.aux_stats$;

SNAME                          PNAME                               PVAL1
------------------------------ ------------------------------ ----------
SYSSTATS_INFO                  STATUS
SYSSTATS_INFO                  DSTART
SYSSTATS_INFO                  DSTOP
SYSSTATS_INFO                  FLAGS                                   1
SYSSTATS_MAIN                  CPUSPEEDNW                       1715.303     -old: 890.876565
SYSSTATS_MAIN                  IOSEEKTIM                           3.481     -old: 10
SYSSTATS_MAIN                  IOTFRSPEED                      61233.656     -old: 4096
SYSSTATS_MAIN                  SREADTIM
SYSSTATS_MAIN                  MREADTIM
SYSSTATS_MAIN                  CPUSPEED
SYSSTATS_MAIN                  MBRC

SNAME                          PNAME                               PVAL1
------------------------------ ------------------------------ ----------
SYSSTATS_MAIN                  MAXTHR
SYSSTATS_MAIN                  SLAVETHR

13 rows selected.

SQL>


SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('interval', interval => 60);

PL/SQL procedure successfully completed.

SQL>

--The above automatically ends statistics calculations after an hour - you may want to specify 30 instead of 60.
--The procedure only needs to be run once, and after a significant hardware/software change.
--Make certain that you have also collected fixed object stats:
--EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL);


On 10g and above the dba needs to run stats on data dictionary and static tables. I did a test case on a non-statistics collected 10gr2 - run time was 20 minutes. I then collected stats and run time was 2 seconds. The commands are as follows:

exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_fixed_objects_stats;

BEGIN
SYS.DBMS_STATS.GATHER_DICTIONARY_STATS (
Granularity => 'DEFAULT'
,Options => 'GATHER'
,Estimate_Percent => NULL
,Method_Opt => NULL
,Degree => 4
,Cascade => FALSE
,No_Invalidate => FALSE);
END;
/
BEGIN
SYS.DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (
No_Invalidate => FALSE);
END;
/