Entering content frameSyntax documentation -f stats Locate the document in its SAP Library structure

This BRCONNECT function updates optimizer statistics. For more information, see Update Statistics.

Recommendation

We strongly recommend you to use this BRCONNECT function rather than the equivalent SAPDBA ones:

The reason is that we will in future no longer develop the SAPDBA functions, whereas BRCONNECT functions will always be fully up-to-date.

Function options:

Input syntax: -b <count>

Default: 75

This option overrides the stats_bucket_count parameter.

Input syntax: -c <threshold>

Default: 50

This option overrides the stats_change_threshold parameter.

Input syntax: -d

Default: Collect outdated and delete damaging statistics

You can use this option to delete statistics for pool and cluster tables, and for tables specified in the DBSTATC control table with the active flags set to N or R.

Input syntax: -e [<owner>.]<table>,[<owner>.]<index>,<tablespace>|<object_list>|info_cubes|null

info_cubes excludes check and update statistics for InfoCube tables.

null invalidates the exclusion list defined by the stats_exclude parameter. This means that no tables or indexes are excluded from processing.

Default: no exclusions

You can use this option to exclude tables or indexes with exceptional statistics handling. This option overrides the stats_exclude parameter.

Input syntax: -f [allsel][,collect][,history][,keep][,limit][,method][,nocasc][,nocheck][,nocoll][,precision][,sample]

Default: Internal rules determine the update statistics method

Input syntax: -h

Default: no history records are saved

The history data in the tables DBSTATHORA and DBSTAIHORA is used by the application monitor.

Input syntax: -l <minutes>

Default: 0, no limit

You can use this parameter to terminate long-running update statistics jobs after a certain period of time. The processing terminates after statistics have been collected for the current table or index (this is the "soft limit"). If you set the option -f limit (see above), processing terminates immediately (this is the "hard limit").

This option overrides the stats_limit_time parameter.

Input syntax: -m E|EH|EI|EX|C|CH|CX|E=|C=|=H|=I|=X|+H|+I

Default: Internal rules determine the update statistics method

This option overrides the stats_method parameter.

E: Estimates

EH: Estimates with histograms

EI: Estimates with index validation

EX: Estimates with histograms and index validation

C: Computes

CH: Computes with histograms

CI: Computes with index validation

CX: Computes with histograms and index validation

E=: Forces estimate for all tables, including tables in DBSTATC control table. Option -f method must be set

C=: Forces compute for all tables, including tables in DBSTATC control table. Option -f method must be set

=H: Forces collect statistics with histograms for all tables, including tables in DBSTATC control table. Option -f method must be set

=I: Forces collect statistics with index validation for all tables, including tables in DBSTATC control table. Option -f method must be set

=X: Forces collect statistics with histograms and index validation for all tables, including tables in DBSTATC control table. Option -f method must be set

+H: Forces collect statistics with histograms for all tables, including tables in DBSTATC control table in addition to index validation, if specified in DBSTATC control table. Option -f method must be set.

+I: Forces collect statistics with index validation for all tables, including tables in DBSTATC control table in addition to index validation, if specified in DBSTATC control table. Option -f method must be set.

Input syntax: -o <owner1>[,<owner2>,...]

Default: SAPR3/SAP<SID> in a standard SAP database or all SAP owners in a multi-schema database

This option overrides the stats_owner parameter.

Input syntax: -p <number>

Default: 1

For example, you can set this parameter to the number of CPUs to speed up update statistics.

This option overrides the stats_parallel_degree parameter.

Input syntax: -r <days>|last

Default: last

Input syntax: -s P<p>|R<r>

Default: Internal rules determine the update statistics method

This option overrides the stats_sample_size parameter.

Input syntax: -t all|all_ind|missing|dbstatc_tab|dbstatc_mon|dbstatc_mona |[<owner>.]<table>|[<owner>.]<index>|<tablespace>|<object_list>|info_cubes

This option overrides the stats_table parameter.

Input syntax: -v table|index|cascade|index_store|cascade_store

Default: cascade

This function can run for a long time and locks the table and indexes in Oracle 8. Therefore, we recommend performing it when there is little processing on the database. However, in Oracle 9 it does not lock tables and indexes.

Leaving content frame