Shulou(Shulou.com)06/01 Report--

DBMS_STATS.SET_GLOBAL_PREFS (

Pname IN VARCHAR2

Pvalue IN VARCHAR2)

DBMS_STATS.SET_DATABASE_PREFS (

Pname IN VARCHAR2

Pvalue IN VARCHAR2

Add_sys IN BOOLEAN DEFAULT FALSE)

DBMS_STATS.SET_SCHEMA_PREFS (

Ownname IN VARCHAR2

Pname IN VARCHAR2

Pvalue IN VARCHAR2)

DBMS_STATS.SET_TABLE_PREFS (

Ownname IN VARCHAR2

Tabname IN VARCHAR2

Pname IN VARCHAR2

Pvalue IN VARCHAR2)

DBMS_STATS.Constants

Name TypeValue

AUTO_CASCADE BOOLEAN NULL

AUTO_DEGREE NUMBER 32768

AUTO_INVALIDATE BOOLEAN NULL

AUTO_SAMPLE_SIZE NUMBER 0

Pname:Preference name. The default value for following preferences can be set:CASCADE 、 DEGREE 、 ESTIMATE_PERCENT 、 METHOD_OPT 、 NO_INVALIDATE 、 GRANULARITY 、 PUBLISH 、 INCREMENTAL 、 STALE_PERCENT

Pvalue:Preference value. If NULL is specified, it will set the Oracle default value.

CASCADE-Determines whether or not index statistics are collected as part of gathering table statistics.

Determines whether index statistics are collected as part of collecting table statistics.

The default value is DBMS_STATS.AUTO_CASCADE,AUTO_CASCADE. The default value is NULL, which is false, because the _ optimizer_compute_index_stats implicit parameter indicates that the statistics of the index will be collected automatically only when the index is created or rebuilt force index stats collection on index creation/rebuild.

DEGREE-Determines degree of parallelism used for gathering statistics.

Determine the degree of parallelism used to collect statistics

The default value is DBMS_STATS.AUTO_DEGREE,oracle based on the size of the object, the number of CPU, and initialization parameters. Based on size of the object, number of CPUs and initialization parameters

ESTIMATE_PERCENT-Determines the percentage of rows to estimate. The valid range is [0.000001100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.

Determine the percentage of rows to estimate. The valid range is [0.000001100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle to determine the appropriate sample size to get good statistics

The default value is DBMS_STATS.AUTO_SAMPLE_SIZE,oracle using the automatic sample size algorithm, Indicates that auto-sample size algorithms should be used

METHOD_OPT-Controls column statistics collection and histogram creation. It accepts either of the following options, or both in combination:

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

FOR COLUMNS [size clause] column [size_clause] [, column [size_clause]...]

Size_clause is defined as size_clause: = SIZE {integer | REPEAT | AUTO | SKEWONLY}

Column is defined as column: = column_name | extension name | extension

-integer: Number of histogram buckets. Must be in the range [1254].

-REPEAT: Collects histograms only on the columns that already have histograms

-AUTO: Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

-SKEWONLY: Oracle determines the columns to collect histograms based on the data distribution of the columns.

-column_name: Name of a column

-extension: Can be either a column group in the format of (column_name, colume_name [,...]) Or an expression

The default is FOR ALL COLUMNS SIZE AUTO.

Controls column statistics collection and histogram creation. It accepts one of the following options, or a combination of both

Size_clause uses integers: all data is sampled into XX portions

Size 1: is to sample all the data into one part.

A total of 1200 rows, if the data is unevenly distributed, such as the birthday month field, it usually takes 12 months, but the first 1000 rows are all in January, and the last 1000 rows are from February to December

If it is divided into one share, it will be 1 / 12 / month.

Size 12: is to sample all the data into 12 parts

A total of 1200 rows, if the data is unevenly distributed, such as the birthday month field, it usually takes 12 months, but the first 1000 rows are all in January, and the last 1000 rows are from February to December

If it is divided into 12 parts, it will be 10 / 12 in January and (200 / 11) / 1200 in all other months.

Size 254is to sample all the data into 254pieces.

The default value is ALL COLUMNS SIZE AUTO, which collects all data skewed and used columns

NO_INVALIDATE-The value controls the invalidation of dependent cursors of the tables for which statistics are being gathered. Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE to have Oracle decide when to invalidate dependent cursors. This is the default.

This value controls the invalidity of the dependent cursor (that is, the execution plan that depends on the table) of the table on which statistics are being collected. Does not invalidate dependencies if the cursor is set to TRUE. If set to FALSE, the procedure immediately invalidates dependent cursors.

True: when the statistics are collected, the cursor of the collected object will not expire (no new execution plan will be generated)

False: when the statistics are collected, the cursor of the collected object will immediately expire (generate a new execution plan)

The default value, DBMS_STATS.AUTO_INVALIDATE, is controlled by the parameter _ optimizer_invalidation_period. The default is 18000 seconds, or 5 hours.

GRANULARITY-Determines granularity of statistics to collect (only pertinent if the table is partitioned)

Determine the granularity of statistics to be collected (relevant only if the table is partitioned)

PUBLISH-Determines whether or not newly gathered statistics will be published once the gather job has completed. Prior to Oracle Database 11g, Release 1 (11.1), once a statistic gathering job completed the new statistics were automatically published into the dictionary tables. The user now has the ability to gather statistics but not publish them immediately. This allows the DBA to test the new statistics before publishing them.

Determines whether newly released statistics will be released after the collection job is completed. Before Oracle Database 11g version 1 (11.1), once the statistical collection was completed, the new statistics were automatically published to the dictionary table. Users now have the ability to collect statistics, but do not publish them immediately. This allows DBA to test new statistics before releasing them

INCREMENTAL-Determines whether or not the global statistics of a partitioned table will be maintained without doing a full table scan. With partitioned tables it is very common to load new data into a new partition. As new partitions are added and data loaded, the global table statistics need to be kept up to date. Oracle will update the global table statistics by scanning only the partitions that have been changed instead of the entire table if the following conditions hold:

INCREMENTAL value for the partitioned table is set to TRUE

PUBLISH value for the partitioned table is set to TRUE

User specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.

If the INCREMENTAL value for the partitioned table was set to FALSE (default value), a full table scan is used to maintain the global statistics which is a much more resource intensive and time-consuming operation for large tables.

Determines whether global statistics for partitioned tables are maintained without a full table scan. It is common to use partition tables to load new data into new partitions. As new partitions are added and data is loaded, global table statistics need to be kept up to date. Update global table statistics are scanned only for changed partitions, not for the entire table, if the following conditions are met:

The InCREMENTAL value of the partition table is set to TRUE

The publish value of the partition table is set to TRUE

When collecting statistics on the table, the user specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY.

If the InCREMENTAL value of the partitioned table is set to FALSE (the default), full table scans are used to maintain large, resource-intensive and time-consuming desktop operations with more global statistics.

STALE_PERCENT-Determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be regathered. The valid domain for stale_percent is non-negative numbers. The default value is 10%.

Determines the percentage of rows in the table that must be changed, and the statistics before the table are considered obsolete and should be merged. The valid domain of the stale_percent is a nonnegative number. The default value is 10%

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.