Oracle dbms_stats package

Last Update:2018-12-03 Source: Internet

Author: User

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

Oracle experts can collect statistics for CBO in a simple way. Currently, it is no longer recommended that you use the old-fashioned analysis table and dbms_utility method to generate CBO statistics. Old methods may even compromise SQL Performance, because they are not always able to capture high-quality information about tables and indexes. CBO uses Object statistics to select the best execution plan for all SQL statements.
Dbms_stats can effectively estimate the statistical data (especially for large partition tables), obtain better statistical results, and finally develop a faster SQL Execution Plan.

The following shows a demo execution of dbms_stats, where the options clause is used.

Execdbms_stats.gather_schema_stats (-
Ownname => 'Scott ',-
Options => 'Collect auto ',-
Estimate_percent => dbms_stats.auto_sample_size ,-
Method_opt => 'for all columns size repeat ',-
Degree => 15-
)
To fully understand the benefits of dbms_stats, You need to carefully understand every major pre-compiled command (Directive ). Next let's study every command and learn how to use it to collect the highest quality statistics for the cost-based SQL optimizer.

Options Parameters
Use one of the four preset methods to control the refresh method of Oracle statistics:
Gather-re-analyze the entire schema ).
Gather empty -- analyze only tables that do not currently have statistics.
Gather stale-only re-analyze tables with a modified volume of more than 10% (these modifications include insert, update, and delete ).
Gather auto -- re-analyze the objects with no statistics currently and the objects with expired (dirty) statistics. Note that using gather auto is similar to using gather stale and gather empty in combination.

Note that both gather stale and gather auto require monitoring. If you run the alter table xxx monitoring command, Oracle uses the dba_tab_modifications view to track the changed tables. In this way, you will know exactly how many insert, update, and delete operations have taken place since the last statistical data analysis.

Estimate_percent Option
The following estimate_percent parameter is a relatively new design that allows Oracle dbms_stats to automatically estimate the optimal percentage of a segment to be sampled when collecting statistics:
Estimate_percent => dbms_stats.auto_sample_size
To verify the accuracy of automatic sampling statistics, you can view the dba_tables sample_size column. One interesting thing is that Oracle selects a percentage of 5 to 20 for a sample size when automatic sampling is used. Remember, the better the quality of statistical data, the better the decision made by CBO.

Method_opt Option
The method_opt parameter of dbms_stats is especially suitable for refreshing statistics when the table and index data change. The method_opt parameter is also suitable for determining which columns require a histogram (histograms ).
In some cases, the distribution of each value in the index will affect whether CBO uses an index or executes a full table scan decision. For example, if the number of values specified in the WHERE clause is asymmetrical, full table scan is more economical than index access.
If you have a highly skewed index (the number of rows for some values is asymmetrical), you can create an oracle histogram statistics. But in the real world, the probability of such a situation is quite small. When using CBO, one of the most common errors is to introduce histograms without having to do so in CBO statistics. Based on experience, histograms should be used only when the column value requires that the execution plan be modified.

To intelligently generate a histogram, Oracle has prepared the method_opt parameter for dbms_stats. There are also some important new options in the method_opt clause, including skewonly, repeat and auto: method_opt => 'for all columns size skewonly'
Method_opt => 'for all columns size repeat'
Method_opt => 'for all columns size auto'
The skewonly option takes a lot of processing time because it checks the distribution of values of each column in each index.
If dbms_stat finds that the columns of an index are unevenly distributed, a histogram is created for the index. The cost-based SQL optimizer determines that index access is performed, or perform full table scan. For example, in an index, assume that there is a row with 50% columns, as shown in list B. to retrieve these rows, the full table scan speed is faster than the index scan speed.

--*************************************** **********************
-- Skewonly option-detailed analysis
--
-- Use this method for a first-time analysis for Skewed Indexes
-- This runs a long time because all indexes are examined
--*************************************** **********************
Begin
Dbms_stats.gather_schema_stats (
Ownname => 'Scott ',
Estimate_percent => dbms_stats.auto_sample_size,
Method_opt => 'for all columns size skewonly ',
Degree => 7
);
End;

When re-analyzing statistical data, the repeat option is used to re-analyze the task, which consumes less resources. When you use the repeat option (listing C), the index will only be re-analyzed for the existing histogram and no other histogram opportunities will be searched. This method should be used for regular re-analysis of statistical data. --*************************************** ***********************
-- Repeat option-only reanalyze histograms for Indexes
-- That have histograms
--
-- Following the initial analysis, the weekly analysis
-- Job will use the "repeat" option. The repeat Option
-- Tells dbms_stats that no indexes have changed, and
-- It willonly reanalyze histograms
-- Indexes that have histograms.
--*************************************** * *********************** Begin
Dbms_stats.gather_schema_stats (
Ownname => 'Scott ',
Estimate_percent => dbms_stats.auto_sample_size,
Method_opt => 'for all columns size repeat ',
Degree => 7
);
End;

To use the alter table xxx monitoring; command to implement Oracle table monitoring, use the auto option in dbms_stats. As shown in listing D, the auto option creates a histogram based on the data distribution and the way the program accesses the column (for example, the workload of a column determined by monitoring. Using method_opt => 'auto' is similar to using gather auto in the option parameter of dbms_stats. Begin
Dbms_stats.gather_schema_stats (
Ownname => 'Scott ',
Estimate_percent => dbms_stats.auto_sample_size,
Method_opt => 'for all columns size auto ',
Degree => 7
);
End;
Parallel collection
Oracle allows parallel collection of CBO statistics, which significantly increases the speed of data collection. However, to collect statistics in parallel, you need an SMP server with multiple CPUs installed.

Faster execution speed
Dbms_stats is an excellent mechanism for improving SQL Execution speed. By using dbms_stats to collect statistics of the highest quality, CBO can correctly determine the fastest way to execute any SQL query. Dbms_stats is constantly improving. Currently, some of its exciting new features (automatic sample size and automatic histogram generation) have significantly simplified the work of Oracle experts.

When using the dbms_stats analysis table, we often need to save the previous analysis to prevent system performance from being low and then perform quick recovery.
Create an analysis table to save the previous analysis value.
SQL> begin
2 dbms_stats.create_stat_table (ownname => 'test', stattab => 'stat _ table ');
3 end;
4/
The PL/SQL process is successfully completed.
Analysis table information
SQL> begin
2 -- dbms_stats.delete_table_stats (ownname => 'test', tabname => 'A ');
3 dbms_stats.gather_table_stats (ownname => 'test', tabname => 'A ');
4 end;
5/
The PL/SQL process is successfully completed.
Export the table analysis information to stat_table.
SQL> begin
2 dbms_stats.export_table_stats (ownname => 'test', tabname => 'A', stattab => 'stat _ table ');
3 end;
4/
The PL/SQL process is successfully completed.
SQL>

Likewise
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
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
SQL> select count (*) from stat_table;
Count (*)
----------
1
Delete analysis information
SQL> begin
2 dbms_stats.delete_table_stats (ownname => 'test', tabname => 'A ');
3 end;
4/
The PL/SQL process is successfully completed.
Import Analysis Information
SQL> begin
2 dbms_stats.import_table_stats (ownname => 'test', tabname => 'A', stattab => 'stat _ table ');
3 end;
4/
The PL/SQL process is successfully completed.

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/liuya1985liuya/archive/2008/03/02/2140257.aspx

Dbms_stats analysis table

Role: dbms_stats.gather_table_stats Statistical Table, column, and index statistical information.

The syntax of dbms_stats.gather_table_stats is as follows:

Struct (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: owner of the table to be analyzed

Tabname: name of the table to be analyzed.

Partname: partition name, which is only useful for partitioned tables or partition indexes.

Estimate_percent: Percentage of the sample row. value range: [0.000001, 100]. If null is set to all analyses, sampling is not performed. Constant: dbms_stats.auto_sample_size is the default value. The optimal sampling value is determined by Oracle.

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

Method_opt: determines how the histograms information is counted. The value of method_opt is as follows:

For all columns: counts histograms of all columns.

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

For all hidden columns: Statistics on histograms of columns that you cannot see

For columns <list> size <n> | repeat | auto | skewonly: counts histograms of a specified column. value range of N: [1, 1,254]; repeat the histograms measured last time; Auto is determined by Oracle's n size; skewonly multiple end-points with the same value which is what we define by "there is 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: collects index information. The default value is falase.

Stattab specifies the table for storing statistics. If statid is used for distinguishing multiple tables, the statistical information is stored in the same stattab. the owner of the statown storage statistics table. if the preceding three parameters are not specified, the statistics are directly updated 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 );

For example:

When using the dbms_stats analysis table, we often need to save the previous analysis to prevent system performance from being low and then perform quick recovery.

1. Create an analysis table to save the previous analysis values:

SQL> begin
2 dbms_stats.create_stat_table (ownname => 'test', stattab => 'stat _ table ');
3 end;
4/