Show TOC

stats_dbms_statsLocate this document in the navigation structure

This parameter defines how the DBMS_STATS package is used to update statistics with BRCONNECT. DBMS_STATS is the default method used in Oracle 10g or higher.

stats_dbms_stats = all:R|B|H|G[<buckets>|A|S|R|D[A|I|P|X|D]]:0|1|<degree>|A|D
| all_part:R|B[<buckets>|A|S|R|D[A|I|P|X|D]]:0|1|<degree>|A|D
| info_cubes:R|B[<buckets>|A|S|R|D[A|I|P|X|D]]:0|1|<degree>|A|D
| [<owner>.]<table>:R|B[<buckets>|A|S|R|D[A|I|P|X|D]]:0|1|<degree>|A|D|
| [<owner>.][<prefix>]*[<suffix>]:R|B[<buckets>|A|S|R|D[A|I|P|X|D]]:0|1
| <degree>|A|D | (<object_list>) | NO

Default value: ALL:R:0

Possible values:

  • R|B[H|G|<buckets>|A|S|R|D lets you specify the sampling type and number of buckets for histograms:

    • R: row sampling

    • B: block sampling

    • H: histograms by row sampling

    • G: histograms by block sampling

    • <buckets>: the number of buckets for histograms

    • A: auto-bucket count

    • S: skew only

    • R: repeat

    • D: default bucket count (75)

  • [A|I|P|X|D] lets you specify columns with histograms:

    • A: all columns

    • I: indexed columns

    • P: partition columns

    • X: indexed and partition columns

    • D : default columns

  • <degree>|0|1|A|D lets you specify the degree of parallelism for DBMS_STATS:

    • <degree>: degree of parallelism

    • 0: table default degree

    • 1: no parallelism

    • A: auto-degree option

    • D: default-degree option

  • all_part: only processes SAP partitioned tables

  • info_cubes: only processes InfoCube tables

The optional entry ALL:R|B:<degree> activates the DBMS_STATS package for all selected tables and defines the standard sampling type and degree of parallelism.

The sampling type and degree of parallelism that you enter for the specified tables or table groups overrides the default sampling type and degree of parallelism (as defined for a table in Oracle dictionary).

The NO: ANALYZE statement is used to update statistics (not recommended in Oracle 10g or higher). This is the default method in Oracle 9i.

Example

  • stats_dbms_stats = ALL:R:1

    This parameter activates the DBMS_STATS package with row sampling for all selected tables, without parallelism.

  • stats_dbms_stats = (ALL:R:2, ATAB:B:3, RFBLG:B:4)

    This parameter activates the DBMS_STATS package for:

    • ATAB table with block sampling and parallelism degree 3

    • RFBLG table with block sampling and parallelism degree 4

    All other selected tables with row sampling and parallelism degree 2

  • stats_dbms_stats = (NO, ATAB:R:3, RFBLG:R:4)

    This parameter activates the DBMS_STATS package only for:

    • ATAB table with row sampling and parallelism degree 3

    • RFBLG table with row sampling and parallelism degree 4

    All other selected tables are processed with the ANALYZE statement (not recommended in Oracle 10g or higher).

  • stats_dbms_stats = (ALL:R:0, INFO_CUBES:R100:A)

    This parameter creates histograms with 100 buckets and parallelism degree “auto” for InfoCube tables. For other tables, statistics are created with default settings.

For more information on DBMS_STATS, see Update Statistics.