Oracle table analysis statistics and application stored procedures

However, statistics are inaccurate after dbms_stats analysis on some forums, and some bugs are found on dbms_stats (which may be related to the version, which is to be checked ).

However, statistics are inaccurate after dbms_stats analysis on some forums, and some bugs are found on dbms_stats (which may be related to the version, which is to be checked ).

Use dbms_stats or analyze

Since Oracle8.1.5 introduced the dbms_stats package, Oracle and experts have recommended dbms_stats to replace analyze. The reasons are as follows:

1. dbms_stats can be analyzed in parallel.

2. dbms_stats has the automatic analysis function (alter table monitor)

3. The analyze analysis statistics are sometimes inaccurate.

The first, second, is easy to understand, and point 2nd is actually the most attractive in VLDB (Very Large Database). 3. I used to be vague and read the metalink21_35.1 explanation, when analyzing the Partition table, analyze sometimes calculates inaccurate Global statistics. The reason is that dbms_stats will actually analyze the global statistics of the table (when the parameter is specified), while analyze calculates the statistics of the table partition (partial) into the global statistics of the table, which may lead to errors. If there is no partition table, both tables can be used (depending on your habits, of course, you can also use dbms_stats for the partition table, and others use analyze ).

However, in some forums, we can see that the statistical data is inaccurate after dbms_stats analysis, and there are indeed bugs on dbms_stats (which may be related to the version and need to be identified). This should be a minority case, note that. Also, you are not recommended to mix analyze and dbms_stats. Experiment: If dbms_stats is used for statistics on partitioned tables and then analyze table is used for statistics, the table information will not be updated. After the statistics are deleted, the analysis is updated, or dbms_stats is used for analysis. Dbms_stats has the following bug examples:

The dbms_stats package can be used to analyze tables, indexes, or the entire user (schema), databases, and parallel analysis.

Packages of different versions are somewhat different. dbms_utility (Toolkit earlier than 8i) and dbms_stats (Toolkit provided after 8i). For more information about the functions of the specific dbms_stats package, see the following.

Summary of commands and toolkit:

1. For partitioned tables, we recommend that you use DBMS_STATS instead of the Analyze statement.

A) It can be performed in parallel for multiple users and tables.

B) data of the entire Partition Table and data of a single partition can be obtained.

C) Compute Statistics at different levels: single partition, sub-partition, full table, all partitions

D) statistics can be exported.

E) users can automatically collect statistical information (alter table monitor)

2. disadvantages of DBMS_STATS:

A) Validate Structure is not supported. (Note: validate structure mainly checks the validity of objects. compute statistics is used to collect statistics ).

B) You cannot collect CHAINED ROWS (row link) or CLUSTER TABLE (CLUSTER TABLE) information. The two still need to use the Analyze statement.

C) DBMS_STATS does not perform Analyze on the index by default. Because the default Cascade is False, you must manually specify it as True. That is, GATHER_TABLE_STATS: analysis table information. When cascade is set to true, the analysis table and column (INDEX) information are displayed.

Analyze updates the statistical information of both the table and the index, while dbms_stats updates the statistical information of the table and then updates the index statistical information (the default Cascade is False ), there is a problem here, that is, when the statistical information of the table is updated and the index statistics are not updated, the cbo may choose the wrong plan at this time.

3. for External tables in oracle 9, Analyze cannot be used. You can only use DBMS_STATS to collect information.

The Analyze command syntax is as follows:

ANALYZE

{TABLE [schema.] table

[PARTITION (partition) | SUBPARTITION (subpartition)]

| INDEX [schema.] index

[PARTITION (partition) | SUBPARTITION (subpartition)]

| CLUSTER [schema.] cluster

}

{COMPUTE [SYSTEM] STATISTICS [for_clause]

| ESTIMATE [SYSTEM] STATISTICS [for_clause] [SAMPLE integer {ROWS | PERCENT}]

| Validation_clauses

| List chained rows [comment _clause]

| DELETE [SYSTEM] STATISTICS

};

All function packages of dbms_stats are as follows:

GATHER_INDEX_STATS: Analyze index information

GATHER_TABLE_STATS: analysis table information. When cascade is set to true, the analysis table and column (INDEX) Information

GATHER_SCHEMA_STATS: Analysis Solution Information

GATHER_DATABASE_STATS: analyzes database information

GATHER_SYSTEM_STATS: Analyze System Information

EXPORT_COLUMN_STATS: analysis information of the exported Column

EXPORT_INDEX_STATS: Export Index analysis information

EXPORT_SYSTEM_STATS: Export System Analysis Information

EXPORT_TABLE_STATS: export table analysis information

EXPORT_SCHEMA_STATS: Export Solution Analysis Information

EXPORT_DATABASE_STATS: Export Database Analysis Information

IMPORT_COLUMN_STATS: Import column analysis information

IMPORT_INDEX_STATS: import index analysis information

IMPORT_SYSTEM_STATS: import system analysis information

IMPORT_TABLE_STATS: Import table analysis information

IMPORT_SCHEMA_STATS: Import Solution Analysis Information

IMPORT_DATABASE_STATS: import database analysis information

Discussion 2: How to Use analyze (dbms_stats is recommended for partitioned tables)