Start of Content Area

Procedure documentation Updating Statistics for a Single Table (Oracle)  Locate the document in its SAP Library structure

You can use this procedure to update the statistics for a single table in your Oracle database if required. For example, use this function when:

        You or SAP’s EarlyWatch determine that the statistics for a particular table are out-of-date. That is, you have a performance problem relating to out-of-date table statistics.

        You import a large amount of data into a particular table (for example, using batch input). The additional records make the statistics inaccurate.

Prerequisites

        This procedure uses BRCONNECT to update statistics.

        For more information on updating statistics in the DBA Planning Calendar, see Updating Statistics in the DBA Planning Calendar (Oracle).

Procedure

...

       1.      Choose Administration CCMS DB Administration Cost-based optimizer Create statistics. Alternatively, call transaction DB20.

       2.      Enter the name of the table in Table (do not enter a generic name or pattern) and choose Refresh information to display information about current table statistics.

The system displays the following information:

Screen Area

Field

Description

Status

Status message

The system displays a status message and a color-coded status indicator for the displayed table:

Green: Statistics are up-to-date

Yellow: Statistics are not up-to-date

Red: Statistics are damaging or missing

Gray: Ignore flag is set in DBSTATC for this table

New method

The update statistics method to be used the next time update statistics runs:

E
Estimate table statistics including indexes

EH
Estimate table statistics and create histograms

EI
Estimate table statistics and verify index structure

EX
Estimate table statistics, create histograms, and verify index structure

C
Compute table statistics including indexes

CH
Compute table statistics and create histograms

CI
Compute table statistics and verify index structure

CX
Compute table statistics, create histograms, and verify index structure

For more information, see stats_method.

New sample size

The sample size to be used the next time update statistics runs. For more information, see stats_sample_size.

Last refresh

Old method

The update statistics method used in the last run of update statistics. See New method above for possible values.

Old sample size

The sample size for create statistics in the last run of update statistics. For more information, see stats_sample_size.

Date

Date when the last statistics were created

Time

Time when the last statistics were created

Number of Table Entries

Old value

Number of rows in the table when the last statistics were created

New value

Current number of rows in the table

Deviation old / new

Percentage deviation between Old value and New value

Deviation new / old

Percentage deviation between New value and Old value

DBSTATC Settings

Use

The service or application for which the table entry is used:

A (application monitor)
For the SAP application monitor (ST07) and the optimizer

O (optimizer)
Only for the optimizer (standard setting)

Active

Control flag, indicating if and how the statistics are updated or created, and in which context they are used. The possible settings are:

A (Active)
Statistics are checked and updated if needed.

N (No)
Statistics cannot be created. You can only set this if Use is O. BRCONNECT deletes existing statistics during next run of
brconnect -f stats.

P (Priority)
Priority Statistics (
P) are checked and updated if needed before statistics with lower priority ( A).

R (Restrictive)
Statistics can be temporarily created. Set this for pool or cluster tables if Use is A. BRCONNECT deletes statistics immediately after they have been stored in the
DBSTATTORA and DBSTATIORA tables.

U (Unconditional)
Statistics are updated without checking during every run of
brconnect -f stats.

I (Ignore)
BRCONNECT does not check whether create or update statistics is required. If statistics are present, they are not updated or deleted.

Analysis method

See New method in screen area Status above.

Sample size

See New sample size in screen area Status above.

History

Statistics results are archived to the DBSTATHORA and DBSTAIHORA tables.

Customer

Suppresses warnings issued by BRCONNECT if pool or cluster tables were specified in the DBSTATC table to always have statistics. Only use this field in exceptional circumstances.

Update

BRCONNECT updates statistics once without check, even when Active is I, N, or R. When it has finished, brconnect -f stats resets the Update field.

Check

BRCONNECT checks once whether new statistics are required and updates them if necessary, even when Active is I, N, or R. If required, you can manually change the entries in the DBSTATC control table with transaction DB21. When it has finished, brconnect -f stats resets the Check field.

       3.      Choose Create statistics to update statistics for the table using the values in Status. We recommend you to only change the values in Status in exceptional circumstances.

       4.      The system updates statistics for the table and updates Last statistics and Number of table entries.

       5.      Choose Delete statistics to delete statistics for the table.

       6.      Choose Check structure to verify table and index structure, using brconnect -f stats with the option -t <table name> -v cascade.

       7.      Choose Standard Operations to update statistics for groups of tables in the database.

       8.      Choose Configure to configure update statistics, that is, edit the DBSTATC table.

       9.      Choose DBA Operations to display DBA logs.

End of Content Area