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:

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:

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

©Copyright 2003