How to Gather Database Statistics

This post was tested on an Oracle 9 database. You can calculate optimizer statistics for an entire database using dbms_stats.gather_database_stats. The estimate_percent parameter allows you to base the statistics on a given percentage of the data, if you wish to do so. Note that statistics are not calculated for objects owned by SYS or DBSNMP. Conversely, you can delete database statistics with dbms_stats.delete_database_stats:

 

TEST9 > sqlplus '/ as sysdba'

 

SQL*Plus: Release 9.2.0.5.0 - Production on Mon Sep 12 16:02:16 2011

 

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

 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 - Production

 

SQL> select trunc(last_analyzed), count(*)

  2  from dba_tables

  3  group by trunc(last_analyzed);

 

TRUNC(LAS   COUNT(*)

--------- ----------

                 748

 

SQL> exec dbms_stats.gather_database_stats -

> (estimate_percent => 10);

 

PL/SQL procedure successfully completed.

 

SQL> select trunc(last_analyzed), count(*)

  2  from dba_tables

  3  group by trunc(last_analyzed);

 

TRUNC(LAS   COUNT(*)

--------- ----------

12-SEP-11        424

                 324

 

SQL> select distinct owner

  2  from dba_tables

  3  where last_analyzed is null;

 

OWNER

------------------------------

DBSNMP

SYS

 

SQL> exec dbms_stats.delete_database_stats;

 

PL/SQL procedure successfully completed.

 

SQL> select trunc(last_analyzed), count(*)

  2  from dba_tables

  3  group by trunc(last_analyzed);

 

TRUNC(LAS   COUNT(*)

--------- ----------

                 748

 

SQL>

The following statement was tested on Oracle 11.1. You can gather statistics just on tables with stale statistics as follows: 

SQL> exec dbms_stats.gather_database_stats -

> (options=>'gather stale',cascade=>true);

PL/SQL procedure successfully completed.

SQL>