Oracle analysis table and Index

Analyze table tablename compute statistics;

Analyze index indexname compute statistics;

It is very beneficial to use CBO, and more reliable table information can be used, so that the execution plan can be more accurate. In 10 Gb, analyze is automatically performed, and previous versions need to be manually scheduled.

Generate Statistics and select a reasonable execution plan ..

The online document of Oracle describes the role of analyze as follows:
Use the analyze statement to collect non-optimizer statistics, for example,:

1. Collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute.

2. Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF ).

3. Identify migrated and chained rows of a table or cluster.

For the collection of statistics, I generally use the package dbms_stats to collect statistics and use it to make the CBO Optimization Selection execution plan more accurate.

It is best to collect statistics regularly to determine different collection cycles based on different services.

 

After the advent of the dbms_stats package, 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.

ListDemonstrate 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-- Only analyze tables that do not have statistics.
  
• Gather stale-- Only the tables with more than 10% modified values are re-analyzed (including insert, update, and delete ).
  
• Gather auto-- Re-analyze the objects with no statistics 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 access. For example, in an index, assume that there is a column in the row 50%, suchList BAs shown in, 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 you re-analyze the statistical data, use the repeat option to re-analyze the task, which consumes less resources. Use the repeat option (List C), The index will only be re-analyzed for the existing histogram, and other histogram opportunities will not 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 will only 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;

When using the alter table xxx monitoring; command to implement Oracle table monitoring, you must use the auto option in dbms_stats. For exampleList DAs shown in, the auto option creates a histogram based on the data distribution and the way the application accesses the column (for example, the workload of a column determined by monitoring. Using method_opt => 'auto' is similar to using gather in the option parameter of dbms_stats.
Auto. 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.

 

How to Use dbms_stats to analyze statistics?

Dbms_stats is a new Oracle8i package, which makes it easier to generate and process statistical data. Many people recommend that you use dbms_stats instead of analyze. I have never used it very much. record it.

Estimate_percent -- estimated sampling percentage
Method_opt: for table -- only the statistical table for all indexed columns -- only the columns with indexes for all indexes -- only the Statistical Indexes

-- Create a statistical information History Table
SQL> exec dbms_stats.create_stat_table (ownname => 'Scott ', stattab => 'stat _ table ');

PL/SQL procedure successfully completed

-- Export the statistics of the entire scheme
SQL> exec dbms_stats.export_schema_stats (ownname => 'Scott ', stattab => 'stat _ table ');

PL/SQL procedure successfully completed

-- Analyze Scheme
Exec dbms_stats.gather_schema_stats (
Ownname => 'Scott ',
Options => 'Collect 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 ');

PL/SQL procedure successfully completed

-- Analysis index
SQL> exec dbms_stats.gather_index_stats (ownname => 'crm2', indname => 'idx _ adm_permission_pid_mid ', estimate_percent => '10', Degree => '4 ');

PL/SQL procedure successfully completed

-- If an execution plan error is found, delete the table statistics.
SQL> dbms_stats.delete_table_stats (ownname => 'Scott ', tabname => 'work _ list ');

PL/SQL procedure successfully completed

-- Historical statistics of the imported table
SQL> exec dbms_stats.import_table_stats (ownname => 'Scott ', tabname => 'work _ list', stattab => 'stat _ table ');

PL/SQL procedure successfully completed

-- If the execution plan of most tables is incorrect after analysis, the statistics of the entire scheme must be imported back.
SQL> exec dbms_stats.import_schema_stats (ownname => 'Scott ', stattab => 'stat _ table ');

PL/SQL procedure successfully completed

-- Import index statistics
SQL> exec dbms_stats.import_index_stats (ownname => 'crm2', indname => 'idx _ adm_permission_pid_mid ', stattab => 'stat _ table ')

-- Check whether the import is successful
SQL> select table_name, num_rows, A. blocks, A. last_analyzed from all_tables A where a. table_name = 'work _ list ';

Table_name num_rows blocks last_analyzed

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

Work_list 4005 186-10-12 15

SQL>

Analyze and dbms_stats are different:
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 statistics, when the statistical information of the table is updated and the index statistics are not updated, the CBO may choose the wrong plan.