Entering content frameSyntax documentation sapdba -analyze Locate the document in its SAP Library structure

Use

You can use the SAPDBA command sapdba -analyze with the Oracle database to:

Recommendation

We strongly recommend you to use the BRCONNECT equivalent function -f stats instead of this SAPDBA command. The reason is that we will in future no longer develop the SAPDBA functions, whereas BRCONNECT functions will always be fully up-to-date.

Note

This command and sapdba -checkopt are equivalent to the newer integrated approach using sapdba -statistics. We recommend you to use the new approach.

For a comparison of the old and new approaches, see Update Statistics for the Cost-Based Optimizer with SAPDBA.

For more information on the new approach, see Updating Statistics using Integrated Approach with SAPDBA.

The logs for this command are stored in the directory sapcheck/<timestamp>.aly.

Syntax

Detailed Command Syntax

sapdba

-u system/<password>

-analyze <tablespace> | <tablespace list> | <table> | <keyword> |

[ -method E | C ]

[ -option P<n> | R<m (*1000)> ]

[ -time <days> ]

[ -T <hours> ]

SAPDBA Command Options

Command option

Meaning

-u

Database system and password

-analyze

Database objects and key words (see below) for the operation

-method

Analysis method, estimate E or compute C (default is estimate)

-option

Option for the analysis method (only for the estimate method E, for which the default option is P10)

-time

Updates all statistics older than <days> days

-T

Restricts the operation to <hours> hours

Key Words

Key word

Meaning

DBSTATCO

All tables for which the TODO flag is set in the control table DBSTATC and all tables and indexes without statistics (that is, NOOPTSTAT is executed implicitly).

NOOPTSTAT

All tables and indexes without statistics (excluding pooled and cluster tables and tables in the control table DBSTATC with active = N or R).

Analyze Space Statistics in a Table

To analyze space statistics, you enter a command such as the following:

sapdba –analyze PSAPBTABD –method C –T 1

This command analyzes the statistics for the tablespace PSAPBTABD using the compute method, and sets a time limit of one hour for the operation.

Note 

The analysis can be time-consuming and resource-intensive, depending on the hardware used. We recommend you to limit the number of records to be analyzed. For example, analyze selected tables or tablespaces rather than the entire database. You can also use the estimate method to reduce the time taken.

sapdba -analyze produces a report as follows:

You can use the information produced to manually optimize the storage of the table, index or tablespace during a reorganization. For more information, see Checking Statistics for a Reorganization. SAPDBA can also perform the optimization for you automatically during a reorganization.

Update Statistics

To update statistics, you normally enter the following command:

sapdba -analyze DBSTATCO

The first call of sapdba -analyze DBSTATCO uses the analysis method E to analyze all tables without statistics that are not in the control table DBSTATC. The next analyze run creates the statistics of these tables with the correct analysis method.

The sapdba -analyze DBSTATCO call creates up-to-date statistics for all tables entered in the control table DBSTATC for which the TODO flag is set. sapdba -analyze NOOPTSTAT is called implicitly. If a table is already in the control table, the analysis method specified there is used; otherwise SAPDBA uses the analysis method adapted to the size of the table according to the SAPDBA internal rules.

The analysis method and option specified with the command option have no effect if the key word DBSTATCO is used and an analysis method and option are entered for the appropriate tables.

For more information on update statistics, see:

 Leaving content frame