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