Friday, September 16, 2011

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>

No comments: