Updating Statistics using Integrated Approach with SAPDBA (SAP Library - SAP Database Guide: Oracle (BC-DB-ORA-DBA))

Updating Statistics using Integrated Approach with SAPDBA

Use

You can use this procedure to check and update statistics for the Oracle cost-based optimizer (CBO), resulting in improved database performance. This section describes the new integrated SAPDBA approach for update statistics, which you start from the command line. It uses parallel processing to reduce the time taken to update statistics: the father process starts child processes to perform the optimizer analyses for individual tables, and communication between father and child uses pipes.

Recommendation

We strongly recommend you to use the update statistics functionality in BRCONNECT instead of the SAPDBA functionality. The reason is that we will in future no longer develop the SAPDBA functions, whereas BRCONNECT functions will always be fully up-to-date.

Here is the detailed command syntax to update statistics with SAPDBA:

sapdba

-statistics ALL | DBSTATCO | <tables>

[ -method E | C | EH | CH

-option Pn | Rn ]

The command is explained in the procedure below.

For an overview of update statistics for the Oracle database, see Update Statistics for the Cost-Based Optimizer with SAPDBA.

Prerequisites

Degree of parallelism

The number of parallel child processes is determined by the cbo_parallel_degree parameter in the SAPDBA profile init<SID>.dba. If the parameter is not specified or zero, SAPDBA sets parallelism to the number of CPUs in the database host.

Logging

The operation log is stored in the SAPCHECK directory under <timestamp>.sta . For more information on the SAPCHECK directory, refer to Environment Variables (UNIX) and Environment Variables (Windows NT).

Procedure

To update all database statistics, enter the following command:

sapdba -statistics

Note

This is the usual command, which is equivalent to the following with the previous two-phase method:

sapdba -checkopt PSAP%
sapdba -analyze DBSTATCO

You can add the switch ALL to the command sapdba -statistics , but it is no longer necessary.

SAPDBA:

Deletes unwanted statistics

SAPDBA deletes the statistics on tables that do not need statistics. These include all R/3 pool and cluster tables as well as all tables listed in the DBSTATC table for which ACTIV is set to N .

Checks and, if necessary, updates statistics for normal tables (that is, tables not listed in the DBSTATC table)

SAPDBA proceeds as follows:

Check statistics

SAPDBA examines the primary index of each table to see how many records have changed. The analysis method used to see how many records have changed is described in Update Statistics: Internal Rules with SAPDBA.

Update statistics

If enough records in a table have changed, SAPDBA updates statistics for the table. The criteria that SAPDBA uses to decide whether enough records have changed are described in Update Statistics: Internal Rules with SAPDBA.

SAPDBA does not update statistics for the following tables:

All tables in the DBSTATC table with ACTIV set to N or I

All R/3 pool and cluster tables

Generates initial statistics for new tables

SAPDBA generates statistics by analyzing the primary index using the method "Estimate Statistics Sample 1 Percent." The next time that you run update statistics with SAPDBA, it handles the table like any other table.

Updates statistics for tables listed in the DBSTATC table

SAPDBA updates statistics for all tables in the DBSTATC table, to which the following apply:

The TOBDO flag is set.

ACTIV is set to U .

ACTIV is set to A or P and enough records in the table have changed since the last update statistics.

ACTIV is set to I , the DURAT flag is set, and enough records in the table have changed since the last update statistics.

The criteria that SAPDBA uses to decide whether enough records have changed are described in Update Statistics: Internal Rules with SAPDBA.

To only update statistics for tables listed in the DBSTATC table, enter the following command:

sapdba -statistics DBSTATCO

SAPDBA follows the rules given in "Updates statistics for tables listed in the DBSTATC table" in the previous step.

To check and update statistics for a specific table or set of tables, enter the following command:

sapdba -statistics <tables>

You can specify <tables> using the name of a single table or a wildcard (for example, psap* ).

SAPDBA proceeds as described for sapdba -statistics (see the first step above) for the table or tables that you specify.

To force SAPDBA to use a particular method or option for update statistics, enter a command containing method or option , such as the following:

sapdba -statistics ALL -method E -option P10

The methods and options are as follows:

method E means that statistics are updated using an estimate based on a sample of records in the table.

method C means that statistics are updated by computing the new statistics.

method EH or method CH means that histograms are generated using method E or C.

option Pn means that the sample is n percent (in the example above, 10 percent)

option Rn means that the sample is n thousand rows

Result

The database statistics are up-to-date, resulting in better performance.