Statistics on Oracle collection objects

Prior to Oracle9i, DBA is responsible for collecting object statistics. By default, the database does not provide object statistics. At oracle10g, the database is created

Prior to Oracle9i, DBA is responsible for collecting object statistics. By default, the database does not provide object statistics. At oracle10g, the database is created

I. Overview

Prior to Oracle9i, DBA is responsible for collecting object statistics. By default, the database does not provide object statistics. At oracle10g, a job is created and scheduled to periodically collect object statistics when you create a database. In order to have the latest object statistics, because the table is changing at all times, if data is inserted at a certain time point, the object statistics need to be updated.

Ii. Collection objects

Oracle uses the Toolkit dbms_stats to collect statistics. The collection objects vary according to different purposes. You can collect indexes for the entire database, data dictionary, user, or single table. As follows:

Gather_database_stats: collects statistics on database objects.

Gather_dictionary_stats: collects statistics on data dictionary objects.

Gather_schema_stats: collects statistics on all objects in the entire mode.

Gather_table_stats: collects statistics on objects in a table.

Gather_index_stats: collects statistics on the index objects.

Iii. parameter description

Different parameters are provided for different collection objects, such:

Parameters can be divided into three categories: specifying the target object, specifying the collection option, and specifying whether to back up before overwriting the current statistical information

1. Target object

Specifies the object for collecting statistics.

Ownname: Mode name (that is, the user name)

Indname: Index name

Tabname: Table Name

Partname: partition or subpartition name. If this parameter is not specified, all partitions are collected. The default value is null.

Comp_id: component ID

Granularity: the statistical level of the partition object. An acceptable value, for example, until oracle9i. The default value is default and the default value is auto from oracle10g.

Cascade: whether to collect index statistics. The options are true, false, and dbms_stats.auto_cascade. (A constant with a value of null indicates that the database engine determines whether to collect indexes ). The default value is false before oracle9i, and dbms_stats.auto_cascade from oracle10g.

Gather_sys: whether to collect statistical information of sys users. Optional values: true and false. The default value is false.

Gather_temp: whether to collect statistics of temporary tables. Optional values: true and false. The default value is false.

Options: processing objects and processing methods

Objlist: return the list of objects to be processed based on different options values.

Force: overwrite locked object information

Obj_filter_list: Collects object information based on conditions. For example, it only counts the statistical information of tables whose names start with "C.

2. collection options

Specifies the method for collecting statistics, the statistical information of the columns to be collected, and whether the related SQL cursor is invalid.

Estimate_percent: whether to sample and collect statistics. A percentage value from 0.000001 to 100 is available. 100 is equivalent to NULL, indicating that sampling is not collected. You need to understand that this parameter only specifies the minimum percentage of sampling. If the database determines that the value is too small, it will automatically increase the value. The default value before oracle9i is NULL. The default value of oracle10g is dbms_stats.auto_sample_size (a constant, Which is 0, indicating that the sampling size is determined by the Stored Procedure). To accelerate the statistical process, a smaller sample value is recommended, for example, 0.5% or 0.1%.

Block_sample: whether block sampling is used (non-block sampling means row sampling). block sampling is faster and row sampling is more accurate. Data block sampling can be used only when the data is randomly and evenly distributed. TRUE: block sampling; FALSE: row sampling. The default value is FALSE.

Method_opt: whether to collect histogram statistics. If collected, the maximum number of buckets sampled. Optional values:

NULL or empty string: Collects only column statistics, and does not collect histogram statistics.

Collect the column statistics and the histogram statistics of all columns, for example, for all columns size 200, indicating a maximum of 200 buckets can be sampled for each column.

Collect only the histogram statistics of certain columns, such as for columns size 200 col1, col2, col3, col4 size 1, col5 size 1. Only histogram information is collected for these five columns, and a maximum of 200 buckets can be sampled for the first three columns.

The default value of oracle9i is for all columns size 1. After oracle10g, the default value is for all columns size auto.

Degree: Number of subordinate processes used for object statistics. The default value is null.

No_invalidate: whether the cursor related to the collected object is invalid. The available values are true, false, and dbms_stats.auto_invalidate. The default value of oracle9i is false (the relevant cursor is invalid immediately). After oracle10g, the default value is dbms_stats.auto_invalidate (the cursor is invalid after a period of time to avoid concentrated RE-resolution of the cursor)