Dbms_stats Analysis Table (ZT) Dbms_stats.set_table_stats SET STATISTICS manually

Function: Dbms_stats. Gather_table_stats tables, columns, and index statistics.

Dbms_stats. The syntax for gather_table_stats is as follows:

Dbms_stats. Gather_table_stats (Ownname VARCHAR2, tabname VARCHAR2, PartName VARCHAR2, estimate_percent number, block_sample BOOLEAN , method_opt VARCHAR2, degree number, granularity VARCHAR2, Cascade BOOLEAN, Stattab VARCHAR2, Statid VARCHAR2, Statown VARCHAR2, No_invalidate Boolean, Force Boolean);

Parameter description:

Ownname: To analyze the owner of a table

TabName: The name of the table to parse.

PartName: The name of the partition that is useful only for partitioned tables or for partitioned indexes.

Estimate_percent: The percentage of the sample line, the value range [0.000001,100],null for all analysis, not sampled. Constants: Dbms_stats. Auto_sample_size is the default value, and Oracle determines the best sampling value.

Block_sapmple: Whether to use block sampling instead of row sampling.

Method_opt: Decide how histograms information is counted. The method_opt values are as follows:

For all columns: counts the histograms of all columns.

For all indexed columns: counts the histograms of all indexed columns.

For all hidden columns: statistics You can't see the column histograms

For columns <list> SIZE <N> | REPEAT | AUTO | Skewonly: Counts the histograms of the specified column. The value range of n [1,254]; Repeat the last statistic histograms; Auto by Oracle determines the size of n; Skewonly multiple end-points with the same value which are what we define by "there was skew in the data

Degree: Determines the degree of parallelism. The default value is null.

Granularity:granularity of statistics to collect, only pertinent if the table is partitioned.

Cascace: Is the information that collects the index. Default is Falase.

STATTAB specifies the table in which to store statistics, statid if the statistics for multiple tables are stored in the same stattab for distinction. Statown the owner of the stored statistics table. If the above three parameters are not specified, the statistic information will be updated directly to the data dictionary.

No_invalidate:does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.

Force: Collect statistics even if the table is locked.

Example:

Execute dbms_stats.gather_table_stats (ownname => ' owner ', tabname => ' table_name ', estimate_percent => null, Method_opt => ' For all indexed columns ', cascade => True);

For example:

When using the Dbms_stats analysis table, we often have to save the previous analysis to prevent the system from performing poorly after the analysis and then recover quickly.

1, first create an analysis table, which is used to save the previous analysis values:

Sql> begin
2 dbms_stats.create_stat_table (ownname=> ' TEST ',stattab=> ' stat_table ');
3 END;
4/

The PL/SQL process has completed successfully.

Sql> begin
2 dbms_stats.gather_table_stats (ownname=> ' TEST ',tabname=> ' T1 ');
3 END;
4/

The PL/SQL process has completed successfully.

2, the Export Table analysis information to the stat_table

Sql> begin
2 dbms_stats.export_table_stats (ownname=> ' TEST ',tabname=> ' T1 ',stattab=> ' stat_table ');
3 END;
4/

The PL/SQL process has completed successfully.

Sql> Select COUNT (*) from TEST. stat_table;

COUNT (*)
----------
4

Export_column_stats: Analyzing information for exporting columns
Export_index_stats: Export Index profiling information
Export_system_stats: Export System analysis information
Export_table_stats: Export Table profiling information
Export_schema_stats: Export Scenario Profiling information
Export_database_stats: Export Database profiling information
Import_column_stats: Import Column profiling information
Import_index_stats: Import Index profiling information
Import_system_stats: Import System analysis information
Import_table_stats: Import Table Profiling information
Import_schema_stats: Importing scenario profiling information
Import_database_stats: Importing database profiling information
Gather_index_stats: Analyzing index information
Gather_table_stats: Profiling table information, parsing table, column (index) information when Cascade is true
Gather_schema_stats: Analyzing Scenario Information
Gather_database_stats: Analyzing Database Information
Gather_system_stats: Analyzing System Information

4, delete analysis information

Sql> begin
2 dbms_stats.delete_table_stats (ownname=> ' TEST ',tabname=> ' T1 ');
3 END;
4/

The PL/SQL process has completed successfully.

Sql> SELECT num_rows,blocks,empty_blocks as empty, Avg_space, chain_cnt, Avg_row_len from dba_tables WHERE owner = ' TES T
and table_name = ' T1 ';

Num_rows BLOCKS EMPTY avg_space chain_cnt Avg_row_len
---------- ---------- ---------- ---------- ---------- -----------

No profiling data found.

5, Import analysis information

Sql> begin
2 dbms_stats.import_table_stats (ownname=> ' TEST ',tabname=> ' T1 ',stattab=> ' stat_table ');
3 END;
4/

The PL/SQL process has completed successfully.

Sql> SELECT num_rows,blocks,empty_blocks as empty, Avg_space, chain_cnt, Avg_row_len from dba_tables WHERE owner = ' TES T
and table_name = ' T1 ';

num_rows     blocks      EMPTY avg_space CHAIN_CNT AVG_ROW_LEN
- ------------------------------------------------------------
      1000           5          0           0          0          
You can find the profiling data