-f stats
This BRCONNECT
function updates optimizer statistics. For more information, see Update Statistics.
We strongly recommend you to use this BRCONNECT function rather than the equivalent SAPDBA ones:
sapdba -statistics
sapdba -analyze
sapdba -delete
sapdba -checkopt
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:
- -b|-bucket
: Defines the number of buckets in histograms
Input syntax: -b <count>
Default: 75
This option overrides the
stats_bucket_count parameter.
- -c|change
: Changes threshold for the percentage of inserted or deleted rows causing update statistics
Input syntax: -c <threshold>
Default: 50
This option overrides the
stats_change_threshold parameter.
- -d|-delete
: Deletes only damaging table and index statistics
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.
- -e|-exclude
: Defines tables and indexes to be excluded from update statistics
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.
- -f|-force
: Forces a specific action while updating statistics
Input syntax: -f [allsel][,collect][,history][,keep][,limit][,method][,nocasc][,nocheck][,nocoll][,precision][,sample]
Default:
Internal rules determine the update statistics method
- allsel
: Updates statistics for all selected objects (option -t or the stats_table parameter), including pool and cluster tables, or deletes statistics for selected objects, including non-pool and non-cluster tables
- collect
: Update statistics without checking them first
- history
: Stores the results of update statistics in the history tables DBSTATHORA and DBSTAIHORA, also for tables specified in the DBSTATC control table
- keep
: Does not delete statistics after updating them for pool and cluster tables (option -f allsel) or for tables with the active flag set to N or R in DBSTATC
- limit
: Forces hard processing time limit defined in option -l or stats_limit_time parameter. Working threads are aborted.
- method
: Uses the method defined in option -m or stats_method parameter, also for tables specified in DBSTATC. See stats_method parameter
- nocasc
: Prevents update statistics from being implicitly performed for all indexes of the selected tables. Only use this option in exceptional situations.
- nocheck
: Does not check statistics. Instead, determine which tables to update statistics for by using the check results from the previous BRCONNECT run with -f nocoll. This is part of the two-phase concept for update statistics.
- nocoll
: Checks statistics only by analyzing the primary index. Statistics are updated for tables that need new statistics in the next BRCONNECT run with -f nocheck. This is part of the two-phase concept for update statistics.
- precision
: Forces minimum precision (that is, sample size) defined in option -s or stats_sample_size parameter for all tables if statistics are collected with method E.
- sample
: Uses sample size defined in option -s or stats_sample_size parameter, also for tables specified in the control table DBSTATC
- -h|-history
: Stores the results of updating statistics in the history tables DBSTATHORA and DBSTAIHORA for tables not specified in DBSTATC.
Input syntax: -h
Default: no history records are saved
The history data in the tables
DBSTATHORA and DBSTAIHORA is used by the application monitor.
- -l|-limit
: Defines the processing time limit in minutes for updating statistics
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.
- -m|-method
: Defines the method for updating statistics for tables that are not specified in the control table DBDSTATC.
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.
- -o|-owner
: Defines the database owner of tables and indexes for updating statistics
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.
- -p|-parallel
: Defines the number of parallel threads for updating statistics
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.
- -r|-retain
: Skips check and update of statistics of tables for which statistics were checked or updated in the specified time period
Input syntax: -r <days>|last
Default:
last
- <days>: Skips tables and indexes for which statistics were checked or updated in the last <days> days
- last
: Skips tables and indexes for which statistics were checked or updated in the last BRCONNECT run. You can use this option to restart an aborted BRCONNECT run of update statistics.
- -s|-sample
: Defines the sample size in percentage or thousands of rows for updating statistics with method E for tables that are not specified in the DBSTATC control table.
Input syntax: -s P<p>|R<r>
Default:
Internal rules determine the update statistics method
This option overrides the
stats_sample_size parameter.
- P<p>
: Percentage of rows
- R<r>
: Number of thousand rows
- -t|-table
: Defines the objects to be processed by update statistics.
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.
- all
: All SAP tables and indexes
- all_ind
: Processes all indexes only. For example, you can use this to create the space statistics for all indexes.
- missing
: Only tables and indexes with missing statistics
- dbstatc_tab
: Only tables specified in the DBSTATC control table
- dbstatc_mon
: Only tables specified in the DBSTATC control table that are relevant for the application monitor
- dbstatc_mona
: Only application tables specified in the DBSTATC control table that are relevant for the application monitor
- info_cubes
checks statistics only for InfoCube tables and updates them if necessary.
- -v|-validate: Validates a table or index structure, but no update statistics is performed
Input syntax: -v table|index|cascade|index_store|cascade_store
Default:
cascade
- table: Validate internal structure of table blocks
- index
: Validate internal structure of index blocks
- cascade
: Validate internal structure of table and index blocks, including relation between index and data rows
- index_store
: Same as index, but also store the statistical values, determined during the validation of the index structures, in the DBSTATIORA table, so that they are available for the application monitor (ST07).
- cascade_store
: Same as cascade, but also store the statistical values, determined during the validation of the index structures, in the DBSTAIHORA table, so that they are available for the application monitor (ST07).
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.