Update Statistics

 

This section describes about the working with the Statistics. The The topics covered are:

Using Statistics

The optimizer uses statistics when determining access paths to the data.
This task explains how to use the statistics:
  1. In the navigation pad, select Tools > Statistics .

    You are prompted for the password.

  2. Enter the password, and then click OK.

    The Statistics page appears.
     

  3. The statistics can be collected for:

  • Entire Database

  • CISDBA Schema

  • Classes

  • Indexes

Collecting Statistics for Entire Database

The Collecting Statistics for the Entire Database allows you to collect statistics for all the objects in the database. You need to run this feature if your database has major updates.
This task explains how to collect statistics for the entire database:
  1. In the Statistics page, click the Database tab. You can collect statistics by clicking either:

Compute. Collects exact statistics for the cost-based optimizer. This is accurate but the process might take a long time depending on the number of objects in the
database.

Estimate. Generates approximate statistics for the cost-based optimizer. This
option estimates statistics for 40 percent of the rows only and the process is much faster. You can change the percentage of estimate statistics to any specified value.

Delete. Deletes the statistics for the entire database.

Collecting Statistics for Entire Schema

The Collecting Statistics for the Entire CISDBA Schema allows you to collect statistics for all the objects in the CISDBA schema. You need to run this feature if your database has major updates.
This task explains how to collect statistics for the entire schema:
  1. In the Statistics page, click the Schema  tab, and then select the Schema option. You can gather statistics by clicking either:

Compute. Collects exact statistics for the cost-based optimizer. This is accurate
but the process might take a long time depending on the number of classes in the
CISDBA schema.

Estimate. Collects approximate statistics for the cost-based optimizer. This option
estimates statistics for only 40 percent of the rows and the process is much faster.
You can change the percentage of estimate statistics to any specified value.

Delete. Deletes the statistics for the entire CISDBA schema.

DML Monitoring and Stale Statistics

You can also gather statistics on just-the-stale objects, that is, objects that have been modified since the last time statistics were gathered. It can be used only for tables with the MONITORING attribute set. The statistics are considered STALE only when there are statistics already gathered on the table. You can enable or disable DML monitoring feature of all the tables in the CISDBA schema by clicking the Enable or Disable. The MONITORING attribute for specific tables in the CISDBA schema can be enabled or disabled in the Classes tab.

Collecting Statistics for Classes

The Collecting Statistics for the Classes allows you to collect statistics for the classes (tables) in the CISDBA schema. From the Statistics page, select the Classes tab and select one or all the classes. The topics covered in this section are:

Collecting Statistics for Classes in the CISDBA Schema

You can collect statistics for classes in the CISDBA Schema.

This task explains how to collect statistics for classes in the CISDBA Schema:
  1. Click in the top left row header.

    All the classes are highlighted.

  2. Click either Compute or Estimate to gather statistics, or click Delete to delete the statistics for all the classes in the CISDBA schema.

Collecting Statistics for Specific Class or Classes

You can collect statistics for specific class or classes.

This task explains how to collect statistics for specific class or classes:
  1. In the Statistics page, click the Classes tab and click the row headers for the
    corresponding classes.

  2. Click either Compute or Estimate to gather statistics, or click Delete  the statistics for the
    selected classes.

 

Collecting Statistics for Indexes

The Collecting Statistics for Indexes allows you to collect statistics for the indexes in the CISDBA schema. Select the Indexes tab and select one or all the indexes. The topics covered in this section are:

Collecting Statistics for Indexes in CISDBA Schema

You can collect statistics for Indexes in the CISDBA Schema.

This task explains how to collect statistics for Indexes in the CISDBA Schema:
  1. In the Statistics page, click the Index and click the top left row header. All the indexes are highlighted

  2. Click either Compute or Estimate to gather statistics, or click Delete to delete the statistics for all the indexes in the CISDBA schema.

Collecting Statistics for Specific Index or Indexes

You can collect statistics for specific index or indexes.

This task explains how to collect statistics for specific index or indexes:
  1. In the Statistics page, click the Index tab and click the row headers for the
    corresponding indexes.

  2. Click either Compute or Estimate to gather statistics, or click Delete  the statistics for the
    selected indexes.