Show TOC

-f statsLocate this document in the navigation structure

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

Function options:

  • -b|-buckets: defines the number of buckets in histograms

    Syntax: -b|-buckets <count> auto|skewonly|repeat

    Default: 75

    This option overrides the stats_bucket_count parameter.

    • count: number of buckets

    • auto: creates histograms based on data distribution and use

    • skewonly: creates histograms on columns based on data distribution

    • repeat: creates histograms only for columns that already have histograms

    For more information, see the Oracle documentation.

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

    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.

  • -e|-exclude: defines tables and indexes to be excluded from update statistics

    Syntax:

    -e|-exclude [<owner>.]<table>|[<owner>.][<prefix>]*[<suffix>]|

    [<owner>.]<index>|<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 and by the valid_exclude parameter (if the -v|-validate option has been set). 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

    Syntax:

    -f|-force [achist][,allcol][,alldef][,allsel][,autoall]

    [,autocasc][,autogran][,autoinv][,buckets][,chained]

    [,chkunb][,collect][,dchist][,degree][,delete][,dimcol]

    [,globgran][,history][,ichist][,indcol][,keep][,limit]

    [,lock][,locked][,method][,monit][,nocasc][,nocheck]

    [,nocoll][,nodict][,noinval][,nosyst][,onlyind][,pchist]

    [,precision][,redcol][,samebks][,sample][,space][,unlock]

    Default: Internal rules determine the update statistics method

    • achist: creates histograms for all columns

    • allcol: checks whether all columns have statistics (relevant for BW tables where this check is not standard)

    • alldef: uses all DBMS_STATS defaults

    • 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

    • autoall: uses all DBMS_STATS auto options

    • autocasc: uses auto-cascade on indexes for new table statistics

    • autogran: uses auto-granularity on partitioned tables

    • autoinv: uses auto-invalidation of dependent cursors

    • buckets: uses option -b for number of buckets (overrides DBSTATC setting)

    • chained: identifies migrated or chained rows

    • chkunb: checks unbalanced indexes

    • collect: updates statistics without checking them first

    • dchist: reduces columns for histograms

    • degree: uses option -g for degree of parallelism (overrides DBSTATC setting)

    • delete: deletes statistics before recreating them

    • dimcol: reduces update of column statistics

      dimcol creates statistics only on indexed columns when one of the following conditions is met:

      • The table has at least 100,000,000 rows and column statistics are no older than 30 days

      • The table has at least 10,000,000 but not more than 99,999,999 rows, and column statistics are no older than 20 days

      • The table has at least 1,000,000 but not more than 9,999,999 rows, and column statistics are no older than 10 days

    • globgran: uses global granularity for partitioned tables

    • history: stores the results of update statistics in the history tables DBSTATHORA and DBSTAIHORA, also for tables specified in the DBSTATC control table, where the history flag is not set there

    • ichist: creates histograms only for indexed columns

    • indcol: creates statistics only for indexed columns

    • 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 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

    • locked: creates new statistics for tables with locked statistics

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

    • 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.

    • nodict: suppresses automatic collection of dictionary statistics

    • noinval: no invalidation of dependent cursors

    • nosyst: suppresses automatic collection of system statistics

    • onlyind: creates statistics only for indexes

    • pchist: creates histograms only for partitioning columns

    • 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.

    • redcol: reduces update of column statistics

      redcol creates statistics only on indexed columns except:

      • BW master, operational data store (ODS), or temporary tables, where statistics are created for all columns

      • Partitioned InfoCube tables (except for master tables), where statistics are also created for partitioning columns

    • samebks: same bucket count for indexed and partitioned columns

    • 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

    • unlock: unlocks table statistics before check and collect

  • -g|-degree: defines the degree of parallelism used by DBSM_STATS for update statistics

    Syntax: -g|-degree <number>|auto|default|null

    Default: null

    This setting is valid for all tables, for which there is no parallelism setting in stats_dbms_stats. However, note that the setting -f degree (see above), if used, takes precedence over the setting in stats_dbms_stats. You can also set a table-specific degree of parallelism using the table DBSTATC.

    • auto: auto degree

    • default: Oracle default degree

    • null: table degree

  • -h|-history: stores the results of updating statistics in the history tables DBSTATHORA and DBSTAIHORA for tables not specified in DBSTATC

    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: 0 - use NOWORKLOAD

    You can also determine the interval for collecting system statistics using the init<DBSID>.sap parameter stats_system_interval.

  • -k|-complete: complete validate structure from the last <days>/run

    Syntax: -k|-complete [<days>|last]

    Default: last

    • <days>: completes validate structure in the last <days> days

    • last: completes the last validate structure runs

  • -l|-limit: defines the processing time limit in minutes for updating statistics

    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

    Syntax: -m|-method E|EH|EI|EX|C|CH|CI|CX|A|AH|AI|AX|

    E=|C=|A=|=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 (locks indexes)

    EX: estimates with histograms and index validation (locks indexes)

    C: computes

    CH: computes with histograms

    CI: computes with index validation (locks indexes)

    CX: computes with histograms and index validation (locks indexes)

    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 (locks indexes)

    AX: estimates with auto-sample size (DBMS_STATS) and histograms and index validation (locks indexes)

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

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

    A= forces estimate without sample size (DBMS_STATS) for all specified tables, including tables in DBSTATC control table. Option -f method must be set

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

    =I: forces collect statistics with index validation (locks indexes) for all specified tables, including tables in DBSTATC control table. Option -f method must be set

    =X: forces collect statistics with histograms and index validation (locks indexes) for all specified 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 (locks indexes) for all tables, including tables in DBSTATC control table in addition to creating histograms, if specified in DBSTATC control table. Option -f method must be set.

  • -n|-ignore: specifies that BRCONNECT ignores the settings in control table DBSTATC. Instead, it uses settings from the stats_special parameter of profile init<DBSID>.sap, if available

  • -o|-owner: defines the database owner of tables and indexes for updating statistics

    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

    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

    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

    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, where <p> is from 1 to 100 and from .001 to .999.

    • R<r>: number of thousand rows

    Note

    While using DBMS_STATS, you can set the percentage of rows to less than 1, which is useful to improve performance on very large tables. For more information, see Sample Sizes for Update Statistics.

    For example, to set the sample size to 0.05%, enter -s P.05.

  • -t|-table: defines the objects to be processed by update statistics

    Syntax:

    -t|-table all|all_ind|all_part|missing|harmful|dbstatc_tab|

    dbstatc_mon|dbstatc_mona|[<owner>.]<table>|[<owner>.]<index>|

    [<owner>.][<prefix>]*[<suffix>]|<tablespace>|

    <object_list>|info_cubes|locked|system_stats|

    oradict_stats|oradict_tab

    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 (for example, for tables with ACTIVE flag set to N in DBSTATC)

    • 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 the init<DBSID>.sap parameter stats_table and SAP Note 601395 Information published on SAP site.

    • oradict_stats: collects statistics for Oracle dictionary objects using DBMS_STATS package

      For more information, see the init<DBSID>.sap parameter stats_table and SAP Note 863811 Information published on SAP site.

      Caution

      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.

    • oradict_tab: validates structure for Oracle dictionary objects

      Note

      You must use the -v option with oradict_tab:

      brconnect -u / -c -f stats -t oradict_tab -v

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

    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 occurs

    Syntax: -v|-validate table|index|cascade|cascfast|index_store|cascade_store

    Default: cascade

    • table: validates internal structure of table blocks. Tables are not locked.

    • index: validates internal 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.

    • cascfast: like cascade but uses the fast clause (Oracle 11g or higher). ). Indexes are locked in shared mode.

    • 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 or higher it does not lock tables and indexes for the attributes table, index, cascade and cascfast, but using attributes index_store and cascade_store locks the tables and indexes.