Oracle analysis table and Index

Oracle analysis table and Index

analyze table tablename compute statistics;analyze index indexname compute statistics;

 

It is good for using CBO. You can use more reliable table information, so that the execution plan can be more accurate. In 10 Gb, analyze is automatically performed. In previous versions, statistical information needs to be generated manually on a regular basis ,, select a reasonable execution plan .. the online document of Oracle describes the role of analyze in this way: Use the ANALYZE statement to collect non-optimizer statistics, for example, to: 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. Oracle experts can collect statistics for CBO in a simple way after the advent of the dbms_stats package, which determines different collection cycles based on different services. 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. Listing A shows A demo execution of dbms_stats, where the options clause is used.

execdbms_stats.gather_schema_stats( -ownname => 'SCOTT', -options => 'GATHER 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. The options parameter uses one of the four preset methods. This option controls the refresh method of Oracle statistics: gather-re-analyzes 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. The estimate_percent option below 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. The method_opt parameter of the method_opt option 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 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 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--*************************************************************begindbms_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. When you use the repeat option (listing 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 for-- indexes that have histograms.--**************************************************************begindbms_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. As shown in listing D, 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 auto in the option parameter of dbms_stats.

begindbms_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 of Oracle allows the collection of CBO statistics in parallel, 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 statistics table for all indexed columns -- only statistics table columns with indexes for all indexes -- only statistics index analysis -- create statistics history retained 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 => 'gather auto ', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all indexed columns', degree => 6) -- analyze 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 -- analyze index SQL> exec partition (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 statistical information of the table 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, 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,. blocks,. last_analyzed from all_tables a where. table_name = 'work _ list'; TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED ------------------------------ ---------- ----------------- WORK_LIST 4005 186-15SQL>

 

Analyze is different from dbms_stats: analyze updates the statistical information of the table and index at the same time, while dbms_stats first updates the statistical information of the table, and then updates the statistical information of the index, the problem is that when the statistical information of the table is updated and the index statistics are not updated, the cbo may choose the wrong plan.