Oracle table analysis description 1. Table analysis concepts 1. Simply put, information about tables and indexes is collected. The CBO (SQL Optimizer) determines the optimal SQL Execution path based on the information. Through table analysis, you can generate some statistical information, through which the oracle optimization program can be optimized. 1) The purpose of collecting statistics is to make the execution plan based on CBO (SQL Optimizer) more accurate. 2) What is the use of the result data [all_tables]? It depends on how to set the Oracle initialization parameter optimal_mode. 3) analyze and update the statistical information of the table, which may lead to a change in the execution plan. 2. use the analyze or dbms_stats package to analyze the statistical table. The analysis can determine the table usage, such as row migration and table data volume, for example: 1) select * from all_tables where table_name = 'temp _ table) analyze table tableName compute statistics; execution time is related to the table's data volume. 3) select * from all_tables where table_name = 'temp_table '; some fields that were previously empty, now the query has a value of 3. oracle online documentation describes how to Use analyze: Use the ANALYZE statement to collect non-optimizer st Atistics, for example, to: -- use the analyze function to Collect statistics that are not optimized, for example, Collect or delete statistics about an index or index partition, table or table partition, -- clustering or deletion of indexes or index partitions, index-organized table, cluster, or scalar object attribute. -- Statistical information such as index table, string, or cursor target attribute Validate the structure of an index or index partition, table or table partition, index-organized table, -- adds the validity of the following structures: Index or index partition, table or table partition, index table cluster, or object Reference (REF). -- string. For the target, refer to Identify migrated and chained rows of a table or cluster. -- to Identify the moving or locked columns in a table or string. 4. The function of the dbms_stats package is mainly to replace the function of collecting statistics of analyze, and it has been greatly enhanced in this aspect. 5. The ANALYZE operation with the ESTIMATE option can produce incorrect results for some tables, especially those tables with small samples. This is not a BUG, but a feature of each statistical sampling method. If the selected sample does not represent the entire dataset, you cannot generate correct statistics in the future. 6. statistical analysis: stores the distribution of column values in Oracle 10 Gb. when considering table analysis, you need to consider the following issues: 1) ANALYZE a table with an index will ANALYZE its related indexes. 2) If you perform ANALYZE... ESTIMATE analysis on a table, it is wise to perform analyze compute analysis on its related indexes. This ensures that the statistics of indexed fields are accurate. 3) The analysis index does not need to go to the temporary tablespace. 4) if you analyze an index without analyzing its base table, the CBO will not be selected on this single basis. If you need to use ESTIMATE-estimation (for example, due to the time limit), we recommend that you perform ANALYZE on several different sampling sizes... ESTIMATE to determine the ideal sampling size for each object. The overall goal is to find a sample size that can generate accurate statistics in the shortest time. A better start point is 10%-15% 5). If ANALYZE exceeds 50%,... ESTIMATE will cause/to become ANALYZE... COMPUTE.