-f stats 

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

Function options:

·        -b|-bucket: Defines the number of buckets in histograms

Input syntax: -b|-bucket <count>

Default: 75

This option overrides the stats_bucket_count parameter.

·        -c|change: Changes threshold for the percentage of inserted, updated, or deleted rows causing update statistics

Input syntax: -c|-change <threshold>

Default: 50

This option overrides the stats_change_threshold parameter.

·        -d|-delete: Deletes only damaging table and index statistics

Default: Collect outdated and delete damaging statistics

You can use this option to delete statistics for pool and cluster tables (only in Oracle 9i), 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|-exclude [<owner>.]<table>|[<owner>.]<index>
|[<owner>.]<prefix>*1|<tablespace>|<object_list>|all_part
|info_cubes|null

all_part excludes check and update statistics for partitioned tables and indexes

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|-force [allsel][,collect][,history][,keep][,limit]
[,method][,monit][,nocasc][,nocheck][,nocoll][,precision][,sample]
[,space][,lock][,unlock]

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 (relevant in Oracle 9i), 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 (relevant in Oracle 9i) (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.

¡        lock: Locks statistics after check or collect

¡        unlock: Unlocks statistics before check or collect

¡        method: Uses the method defined in option -m or stats_method parameter, also for tables specified in DBSTATC. See stats_method parameter

¡        monit: BRCONNECT automatically sets the MONITORING attribute for all tables without this attribute and forces update statistics for them.

¡        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

¡        space: Collects space statistics, taking into account space allocated to LOB segments

·        -h|-history: Stores the results of updating statistics in the history tables DBSTATHORA and DBSTAIHORA for tables not specified in DBSTATC.

Input syntax: -h|-history

Default: no history records are saved

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

·        -i|-interval: interval for collecting system (CPU, I/O) statistics

Syntax: -i|-interval <minutes>

Default: 10

You can also determine the interval for collecting system statistics using the init<SID>.sapparameter stats_system_interval.

·        -l|-limit: Defines the processing time limit in minutes for updating statistics

Input syntax: -l|-limit <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 DBSTATC.

Input syntax: -m|-method E|EH|EI|EX|C|CH|CX|A|AH|AI|AX|
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

A: Estimates with auto-sample size (DBMS.stats)

AH: Estimates with auto-sample size (DBMS.stats) and histograms

AI: Estimates with auto-sample size (DBMS.stats) and index validation

AX: Estimates with auto-sample size (DBMS.stats) and 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 histograms, 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|-owner <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|-parallel <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|-retain <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|-sample 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|-table all|all_ind|all_part|missing|harmful|dbstatc_tab|dbstatc_mon|
dbstatc_mona|[<owner>.]<table>|[<owner>.]<index>|[<owner>.]<prefix>*|<tablespace>|<object_list>|info_cubes|locked|system_stats|oradict_stats

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.

¡        all_part: Processes all partitioned tables and indexes

¡        missing: Only tables and indexes with missing statistics

¡        harmful: Processes all tables and indexes with damaging 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.

¡        locked: All tables with locked statistics

¡        system_stats: Collects system (CPU, I/O) statistics using the DBMS_STATS package. For more information, see SAP Note 601395.

¡        oradict_stats: Collects statistics for Oracle dictionary objects using DBMS_STATS package. For more information, see SAP Note 863811.

To collect system statistics you need to start BRCONNECT with the SYSTEM database user because the package procedure DBMS_STATS.GATHER_SYSTEM_STATS need DBA authorization.

·        -u|-run: lets you stop or suspend  the current run of update statistics or validation of table or index structures.

Input syntax: -u|-run stop|suspend|resume

Default:

¡        stop: Cleanly stops processing. The table or index currently being processed is completed but no further tables or indexes are processed. It might take some time to finish processing the current table or index if it is large.

¡        suspend: Suspends processing. It might take some time to finish processing the current table or index if it is large.

¡        resume: Resumes processing that has been suspended.

You normally perform these actions in parallel in a separate command window from the current run of update statistics or validation of table or index structures.

·        -v|-validate: Validates a table or index structure, but no update statistics is performed

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

Default: cascade

¡        table: Validates internal structure of table blocks. Tables are not locked.

¡        index: Validate sinternal structure of index blocks. Indexes are not locked.

¡        cascade: Validates internal structure of table and index blocks, including relation between index and data rows. Tables and indexes are not locked.

¡        index_store: Same as index, but also stores 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). Indexes are locked.

¡        cascade_store: Same as cascade, but also stores 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). Tables and indexes are locked.

We recommend performing it when there is little processing on the database. However, in Oracle 9 it does not lock tables and indexes for the attributes table, index, and cascade, but using attributes indexe_store and cascade_store locks the tables and indexes.