## Checking the statistics on a table

Oracle's Cost Based Optimizer collects statistics on tables and indexes in order to determine the best access method when presented with many options. Usually this boils down to *"to use, or not to use"* an index.

There are three likely cases where statistics are the problem, and they all have the same solution:

- There are no statistics on the table or index.
- The statistics are old, and no longer representative of the table/index contents.
- The statistics were estimated, and an insufficient sample size was used.

To determine whether any of these are true, you can display the statistics using Oracle Enterprise Manager. Open up your database in the Navigator window; open the *Schema* folder; open the schema that owns your query table; open *Tables*; find your table name and open it; open *Indexes*. When you click on the table and each of the indexes, click the *Statistics* tab on the right hand pane to view the statistics. If the table is partitioned, you should do the same with each partition as well.

Alternatively, download `stats.sql`

and run it from SQL*Plus as follows:

Where `my_table_name`

is the name of the table for which you want to list statistics. `stats.sql`

will display the statistics for the table, its partitions, and its indexes.

The things you are looking for are:

- The statistics fields are NULL, indicating the statistics have not been calculated at all (or have been deleted).
- The
*Last Analyzed* date is old, and statistics need to be recalculated. Other signs of this problem would be *Total Rows* showing a number that is nothing like the total number of rows in the table.
- The
*Sample Size* is less than 20% of the total number of rows in the table. In many cases, accurate statistics can be derived from a sample size of 5% or less of the total rows, but this is not always the case.

You should see the DBA if statistics need to be recalculated.

©Copyright 2003