"Turn" dbms_stats. Gather_table_stats detailed 2012-04-22 09:20:10

"Turn" dbms_stats. Gather_table_stats detailed 2012-04-22 09:20:10

Category: Linux

Because Oracle's optimizer is a CBO, the statistics of objects are critical to the generation of the execution plan! Role: Dbms_stats. Gather_table_stats statistics, column, index (the default parameter is to collect the histogram information of the table, including the table's own-the table's number of rows, the number of data blocks, the president and other information; column analysis--the number of repetitions of a column value, the null value on a column, the distribution of data on a column Index analysis-Number of index page blocks, depth of index, index aggregation factor).

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 VA RCHAR2, 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, which is useful only for partitioned tables or partition indexes.

Estimate_percent: The percentage of sampled rows, the range of values [0.000001,100],null is all analyzed, not sampled. Constant: Dbms_stats. Auto_sample_size is the default value and is determined by Oracle to determine the best sampling value.

Block_sapmple: Whether block sampling is used instead of row sampling.

Method_opt: Determines how the histograms information is counted. The value of the method_opt is as follows (the default value is for all COLUMNS SIZE AUTO):

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 histograms of the column

For Columns SIZE | REPEAT | AUTO | Skewonly: Counts the histograms of the specified column. The value range of n [1,254]; Repeat the last statistic histograms; Auto is determined by Oracle for the size of N; Skewonly multiple end-points with the same value which are what we define by "there are skew in thedata

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

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

Cascade: Is the information that collects the index. The default is False.

Stattab: Specifies the table to store statistics for, Statid if the statistics for multiple tables are stored in the same stattab for differentiation. Statown stores the owner of the statistics table. If the above three parameters are not specified, the statistics 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: Collects 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);

--------------------------------------------------------------------------------------------------------------- ---------

Since Oracle8.1.5 introduced the Dbms_stats package, experts has recommended using dbms_stats instead of analyze. For the following reasons
Dbms_stats can be analyzed in parallel
Dbms_stats features automatic analysis (ALTER TABLE monitor)
Analyze inaccurate some of statistical information
Good understanding, and the 2nd is actually the most attractive in VLDB, 3 before the more vague, read metalink236935.1 explanation, analyze in the analysis of partition table, sometimes calculate the inaccurate global statistics.
The reason is that dbms_stats will actually parse the table global statistics (when specifying parameters), whereas analyze is the statistics summary of table partitioning (local) is calculated as a global statistics, which can lead to errors.
If you want to analyze the entire user or database, you can also use a toolkit that can be analyzed in parallel
Dbms_utility (8i old toolkit)
Dbms_stats (Toolkit available after 8i)
Such as
Dbms_stats.gather_schema_stats (user,estimate_percent=>100,cascade=> TRUE);
Dbms_stats.gather_table_stats (User,tablename,degree = 4,cascade = True);

How do I use dbms_stats to analyze statistics?
--Create a Statistics history reservation table

sql> exec dbms_stats.create_stat_table (ownname = ' Scott ', Stattab = ' stat_table ');

--Export the statistics of the whole scheme

sql> exec dbms_stats.export_schema_stats (ownname = ' Scott ', Stattab = ' stat_table ');

--Analysis scheme

Exec Dbms_stats.gather_schema_stats (
Ownname = ' Scott ',
Options = ' GATHER AUTO ',
Estimate_percent = Dbms_stats.auto_sample_size,
Method_opt = ' For all indexed columns ',
degree = 6)

--Analysis Table

sql> exec dbms_stats.gather_table_stats (ownname = ' Scott ', tabname = ' work_list ', estimate_percent = 10, Method_opt=> ' For all indexed columns ');

--Analysis Index

sql> exec dbms_stats.gather_index_stats (ownname = ' Crm2 ', indname = ' Idx_adm_permission_pid_mid ', Estimate_ Percent = ' ten ', degree = ' 4 ');

--If the execution plan is found to be wrong, delete the statistics of the table

Sql>dbms_stats.delete_table_stats (ownname = ' Scott ', tabname = ' work_list ');

--Historical statistics of imported tables

sql> exec dbms_stats.import_table_stats (ownname = ' Scott ', tabname = ' work_list ', stattab = ' stat_table ') ;

--If the execution plan for most tables goes wrong after analysis, you need to return the statistics for the entire scheme

sql> exec dbms_stats.import_schema_stats (ownname = ' Scott ', Stattab = ' stat_table ');

--Import the statistics of the index

sql> exec dbms_stats.import_index_stats (ownname = ' Crm2 ', indname = ' Idx_adm_permission_pid_mid ', Stattab = > ' stat_table ')

--Check whether the import was successful

Sql> Select table_name,num_rows,a.blocks,a.last_analyzed from All_tables a where a.table_name= ' work_list ';

Analysis database (including all user objects and System objects): Gather_database_stats
Analyze user-owned objects (including tables, indexes, clusters): Gather_schema_stats
Analysis Table: Gather_table_stats
Analysis Index: Gather_index_stats
Delete Database statistics: delete_database_stats
Delete User Scenario Statistics: delete_schema_stats
Delete Table statistics: delete_table_stats
Delete Index Statistics: delete_index_stats
Delete Column statistics: delete_column_stats
Set Table statistics: Set_table_stats
Set Index statistics: set_index_stats
Setting Column statistics: set_column_stats

You can view the table Dba_tables to see if the table has been parsed, such as:

SELECT table_name, last_analyzed from Dba_tables

This is a summary of the command and toolkit
1. For partitioned tables, it is recommended to use Dbms_stats instead of using the Analyze statement.
A) can be done in parallel, for multiple users, multiple table
b) data from the entire partitioned table and data for a single partition can be obtained.
c) can be compute at different levels Statistics: Single partition, sub-partition, full table, all partitions
d) Statistics can be poured out
e) Users can automatically collect statistical information
2, Dbms_stats's shortcomings
A) cannot validate Structure
b) cannot collect chained ROWS and cannot collect cluster table information, both of which still need to use the Analyze statement.
c) Dbms_stats default does not analyze the index because the default cascade is false and needs to be specified manually as true
3, for Oracle 9 inside the external table,analyze can not be used, can only use Dbms_stats to collect information.

"Turn" dbms_stats. Gather_table_stats detailed 2012-04-22 09:20:10