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

Http://blog.csdn.net/dataminer_2007/article/details/41519139

This article details how the method_opt parameter affects statistics on the target column and what types of statistics are collected for the target column.

Method_opt is probably the most puzzling parameter in stored procedure dbms_stats.gather_*_stats. The most common function of this parameter is to control the way histograms are collected, but in fact it is far from this. Its actual function is as follows:

Control which columns collect basic statistics collect histograms, collect extended statistics

The usage of the Method_opt parameter is divided into two parts, as shown in the following figure:

The "For all [indexed | hidden] columns" section controls which columns will collect basic statistics for the columns (minimum and maximum values on the target column, number of different values on the column, number of null values, etc.). The system defaults to FOR ALL COLUMNS, which collects basic statistics for all columns on the table, including hidden columns. In addition, its other optional values are as follows:

FOR ALL INDEXED COLUMNS specifies that only fields that contain indexes can collect basic statistics for columns. This option value is generally not recommended because fields used in all sql statements in a database environment, such as fields after select, fields behind where, fields in group by, do not just refer to fields that contain indexes. For ALL HIDDEN COLUMNS specifies that all invisible fields in the table can collect basic column statistics, that is, they do not collect statistics for actually visible columns on the table. This option value is also generally not recommended. This option is usually used only in this case. Add one or more invisible or virtual columns to a table where all column statistics are accurate. You only need to collect statistics for this or these invisible columns, instead of repeating statistics for other columns, then use the for all hidden columns option.

If a field (column) is not included in the list of columns that collect basic statistics, only the average length of the column will be calculated when the collection is complete. The average length of a column is usually used to calculate the average length of a row.

The "Size [size_clause]" section controls how histograms are collected, and the following options are available after size:

AUTO Oracle decides which columns need to collect histograms based on column statistics (sys.col_usage$) and column data skew (uniform distribution). Integer specifies the number of barrels to collect histograms, with a minimum of 1 and a maximum of 254. for 11g and earlier versions, there is no this restriction after 12c. Note that if the number of buckets is 1, size 1 means that the histogram is not created, and if the column already has a histogram, the histogram is deleted. REPEAT only recollects the histogram on columns that already have histograms. Repeat ensures that the histogram is re-collected at the global level for columns that already have histograms. This option is generally not recommended because the number of barrels used in the new histogram will not exceed the number of barrels in the old histogram. Assuming that the number of barrels in the current histogram is 5, when using size repeat to re-collect the histogram, the number of buckets used in the new histogram will not exceed 5, which may not achieve good results. SKEWONLY only collects histograms on columns with uneven distribution of data.

If FOR ALL COLUMNS SIZE AUTO, the default parameter of method_opt, is not applicable to your data environment, you may encounter the following two situations:

Create a histogram on a column other than the specified column, create a histogram on the specified column only

Let's assume that we only want to create a histogram on the cust_id column on the sales table. Remember, the method_opt parameter can specify not only which columns collect basic statistics, but also which columns collect histograms, so we need to consider dividing the method_opt parameter into two parts.

The first part of the figure below specifies which columns will collect basic statistics. Here we want to collect basic statistics for all the columns on the table, so we use For all columns. But with regard to the size section, how should we set the parameters? Because we only want to collect histograms on one column, we first specify size 1 to prevent all columns from collecting histograms (remove histograms for all columns)

The second part of the figure below specifies that the cust_id column needs to collect the histogram. "For columns" is an additional part of the method_opt syntax that allows explicit operations for the specified column in the parameter setting section. Here we use FOR COLUMNS SIZE 254 CUST_ID to specify the cust_id column to collect histograms. So the final Method_opt parameter is set as follows:

Let's use DBMS_STATS.GATHER_TABLE_STATS to collect statistics. Although there are many columns in the sales table in the system that are used in where query statements, here we just create a histogram on the cust_id column.

BEGIN

Dbms_stats.Gather_table_stats ('SH',' SALES', method_opt = > 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 CUST_ID')

END

/

PL/SQL procedure successfully completed.

SQL > SELECT column_name, num_distinct, histogram

FROM user_tab_col_statistics

WHERE table_name = 'SALES'

For method_opt parameters, you need to use more complex settings in many cases, such as you do not want to collect any statistics for the specified column. There is currently no way to tell Oracle how not to collect statistics for specified columns, but you can think about it the other way around. You can explicitly list the columns that need to collect statistics through the for columns syntax. Let's demonstrate the method of not collecting statistics for the prog_id column in the sales table:

BEGIN

Dbms_stats.delete_column_stats ('SH',' SALES', 'PROD_ID')

END

/

PL/SQL procedure completed successfully. BEGIN dbms_stats.Gather_table_stats ('SH',' SALES', method_opt = > 'FOR COLUMNS SIZE 254CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD'); END; / PL/SQL procedure completed successfully.

Even if we do not collect statistics for prod_id columns, oracle will still accurately calculate the average length of each row.

SQL > SELECT num_rows, avg_row_len

FROM user_tables

WHERE table_name = 'SALES'

SQL > SELECT column_name, num_distinct, histogram

FROM user_tab_col_statistics

WHERE table_name = 'SALES'

Note: for columns statements can only be used with gather_table_stats stored procedures.

Finally, I mentioned at the beginning of this article that the method_opt parameter can be used to collect extended statistics. The extended statistics contain statistics for two types of columns: the combination of columns and the statistics of column expressions. In the following example, oracle will collect statistics for the combination of prod_id and cust_id columns on the sales table, as well as statistics for all basic columns, and oracle will automatically generate names for the combined column statistics.

BEGIN

Dbms_stats.Gather_table_stats ('SH',' SALES'

Method_opt = > 'FOR ALL COLUMNS SIZE 254FOR COLUMNS SIZE 254( PROD_ID, CUST_ID)')

END; /

PL/SQL procedure successfully completed.

SQL > SELECT column_name, num_distinct, histogram

FROM user_tab_col_statistics

WHERE table_name = 'SALES'

In statements that collect statistics, we do not recommend specifying parameters directly in method_opt, but recommend using dbms_stats.set_table_prefs stored procedures

BEGIN

Dbms_stats.Set_table_prefs ('SH',' SALES', 'METHOD_OPT',-

'FOR ALL COLUMNS SIZE 254FOR COLUMNS SIZE 1 PROD_ID')

END; /

Summary, GATHER_DICTIONARY_STATS, GATHER_DATABASE_STATS, and GATHER_SCHEMA_STATS stored procedures only accept "FOR ALL [INDEXED | HIDDEN] columns" syntax and cannot specify specific column names.

GATHER_TABLE_STATS procedure stored procedures can accept additional parameters in the format "for columns", and this syntax allows you to control the following:

Which columns will collect basic statistics and which columns will collect histograms and specify the number of barrels of the histogram to collect extended statistics

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.